Table partitioning and data lifecycle

One table, several partitions

A partition key supplies the value that chooses a row's piece. Here it is placed_at, and each piece has a month of UTC timestamps. October begins at 1 October 00:00 inclusive and ends at 1 November 00:00 exclusive: a half-open range, written [start, end).

Order 103’s 20 October timestamp belongs to October. A separate probe exactly at 1 November 00:00 UTC belongs to November.

The adjoining ranges have neither an overlap nor a missing instant. A timestamp exactly at 1 November belongs to November, without inventing a final microsecond of October. The same rule applies to reads: October's upper bound should be < 1 November, not an inclusive end at midnight.

In PostgreSQL, the partitioned parent has no row storage of its own. Inserts through it are routed to the appropriate partition. Each partition has its own stored rows and indexes. This abbreviated definition shows the parent and its October piece; the experiment creates all twelve months.

CREATE TABLE orders_monthly (
  order_id integer NOT NULL,
  customer_id integer NOT NULL,
  placed_at timestamptz NOT NULL,
  status text NOT NULL,
  PRIMARY KEY (order_id, placed_at)
) PARTITION BY RANGE (placed_at);

CREATE TABLE orders_2026_10 PARTITION OF orders_monthly
  FOR VALUES FROM ('2026-10-01 00:00+00')
             TO   ('2026-11-01 00:00+00');

The composite primary key is deliberate and needs explanation below. It is not the original shop's PRIMARY KEY (order_id). This is a reporting copy, not a migration of that transactional contract.

These pieces can all live in one PostgreSQL instance. They do not supply another server's processor, memory or failure boundary, and their existence does not promise parallel execution. Sharding and distributed SQL chooses a boundary for work placed across machines. Table partitioning first answers which pieces of this table need work.

Partition pruning

Partition pruning excludes a partition when its bounds prove that it cannot contain a row the query needs. October's report requires placed_at >= 1 October and placed_at < 1 November. January ends before that interval starts; November starts where it ends. Neither can contribute an order.

A full-year query needs all twelve monthly pieces. A lookup for order 103 with no time condition also cannot exclude a month from that order ID alone. Excluding order partitions still leaves whatever work the query needs in other tables.

Pruning uses the partition bounds, independently of an index. An index then offers an access path inside a remaining partition. Reading most October rows may favour a scan; finding a few may favour an index. Indexes and access paths develops that choice.

The report also tests status = 'paid'. October contains pending orders too, including order 103, so retaining October does not mean returning all its rows. Nor has pruning calculated any line value. The report still joins eligible orders to order_items, adds agreed line values by customer and sorts the totals:

SELECT o.customer_id,
       SUM(i.quantity * i.unit_price) AS line_value
FROM orders_monthly AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.placed_at >= timestamptz '2026-10-01 00:00+00'
  AND o.placed_at <  timestamptz '2026-11-01 00:00+00'
GROUP BY o.customer_id
ORDER BY line_value DESC, o.customer_id;

In this fixture order_items is unpartitioned. It has no monthly boundary for pruning to exclude. The engine may look up lines by order ID or scan them for a join; that is a separate choice. In the small starting rows, the report returns Tariq's 40.00 and Mina's 39.50, excluding pending order 103. The generated history adds contributing customers and values; these remain paid-order line values, not payments received.

Predicates that expose the boundary

Write the intended interval directly against the partition key, with the correct value type and time zone. The two explicit timestamptz bounds above expose the same UTC month as the partition definition.

A request written as EXTRACT(MONTH FROM placed_at) = 10 is different: it includes October in every year and its calendar interpretation depends on time zone. Even after adding the year and fixing the zone, do not assume the engine can turn a transformed expression back into bounds on the stored key. Check the actual SQL, parameter types and plan.

Known constants can permit pruning while PostgreSQL plans a query. Values supplied later, including prepared-statement parameters, can permit pruning during execution. A generic plan that lists several partitions is therefore not by itself proof that they all ran. Check the actual execution with representative parameters; Reading and repairing PostgreSQL plans follows the native evidence.

Choosing the partition key and size

A useful boundary groups rows that queries can exclude or maintenance can treat together. Monthly time ranges suit this report and a whole-month retirement. They do little for an ID-only lookup and no time pruning for a full-year report. Start from the frequent requests and the approved lifecycle unit, rather than the table's row count alone.

Customer 1001 owns 70% of the generated orders. Hashing customer_id does not spread that customer's rows across buckets: the same key selects the same bucket. A customer-based layout also spreads January's orders across customer pieces, so retiring January is no longer one partition operation. A boundary that helps a customer lookup can make the lifecycle job harder.

Finer time ranges make smaller retirement units and can leave fewer irrelevant rows in a selective read. They also create more tables and indexes to plan around, collect statistics for, provision and monitor. A daily layout across years is a different maintenance burden from twelve monthly pieces. Too many remaining partitions can add planning time and memory even when each is small.

Choose a size whose exclusions are useful and whose future growth you can maintain. An unpartitioned table with suitable indexes remains a good outcome when it already meets the query and lifecycle needs.

Keys and references across partitions

The shop means an order number to identify one order, whatever its date. A local unique index can check only its own partition. Two unique indexes, one in October and one in November, can each accept 103 without consulting the other.

PostgreSQL 18 therefore requires a unique or primary-key constraint on a partitioned table to include every partition-key column, and the partition key must contain no expressions for this purpose. Adding placed_at satisfies that engine requirement, but changes what is unique.

The pair (103, 20 October 10:15) differs from (103, 1 November 00:00). A unique pair permits both. It cannot enforce the business rule that there is only one order 103. Generating IDs that are unlikely to collide does not make an unenforced duplicate impossible, including through imports or explicit writes.

This matters to both order_items and payments. Their original foreign keys reference an order ID that is uniquely enforced. They cannot simply reference orders_monthly(order_id) when that column is no longer a unique key. A composite foreign key would need the timestamp as well:

FOREIGN KEY (order_id, placed_at)
  REFERENCES orders_monthly (order_id, placed_at)

Now every dependant must carry the correct timestamp. A wrong one is a wrong reference; correcting an order's partition key can move its row and change that referenced pair. Choose the update behaviour and coordinate the dependants rather than treating the timestamp as harmless routing metadata.

There are several possible designs. Accept a composite identity only if the application really means that identity. Use a documented global uniqueness mechanism where the chosen engine provides one. Or keep an unpartitioned order authority with the globally unique ID and have all relevant records refer to it. That authority must govern the relationship between ID and partitioned data too; merely adding an ID registry does not prove there is exactly one matching detail row.

The reporting experiment keeps the original globally keyed orders and its dependent lines intact, then copies the same rows into the monthly table for comparison. The copy's duplicate-pair protection is deliberately weaker. It is not authority for new purchases, payments or order lines.

Adding and retiring partitions

The partition set needs upkeep. Provide future ranges before their first writes arrive, monitor that coverage and choose what should happen to late rows. In PostgreSQL, an insert that matches no partition fails unless a default partition accepts it. A default can catch unexpected values, but then it needs monitoring and a checked path into the intended range; it does not make the missing range irrelevant.

A late October order routes by its October placed_at, not by the date it reaches the database. After October has retired, decide whether to reject, quarantine or deliberately reopen that scope. Letting a catch-all silently accumulate retired data would undermine the lifecycle boundary.

Retirement starts with a policy decision about the records, not a convenient command. In a separate hypothetical scenario, an approved cutoff is 1 February 2026 at 00:00 UTC. All January timestamps lie before it. A cutoff of 15 January would permit only part of that month to retire. Removing the whole January partition would also remove ineligible rows, so that operation must stop.

Detaching removes the partition from the parent while keeping it as a standalone table. A parent query no longer returns January; a direct query of the detached table still can. Where the agreed policy permits a recoverable stage, that distinction gives room to verify the result before destroying the table. Access and retention still apply while it exists.

ALTER TABLE orders_monthly
  DETACH PARTITION orders_2026_01;

-- January is absent from this parent's result:
SELECT COUNT(*) FROM orders_monthly;

-- The detached rows still exist here:
SELECT COUNT(*) FROM orders_2026_01;

Dropping a partition can remove many rows without deleting them individually. That can avoid the row-by-row deletion and cleanup work, but it does not mean no locking or waiting. PostgreSQL's ordinary detach takes an ACCESS EXCLUSIVE lock on the parent. The concurrent form reduces the parent lock, uses two internal transactions and has restrictions: it cannot run inside a transaction block or with a default partition. Check the exact operation and references on the deployed version before scheduling it.

Retiring related data safely

An order's age does not establish that every related fact may go. Account for order lines, payments and any incoming references before detachment. A foreign key can prevent removing still-referenced parent rows from the partitioned table. Removing that protection or using CASCADE just to make maintenance succeed would change the integrity contract, not settle the lifecycle decision.

First identify the complete eligible set and the action for each dependant. Coordinate writers so no new reference, correction or late insert can invalidate that set between checking and removal. Apply the approved dependent actions in an order that preserves their constraints, then perform the partition operation. Confirm which rows the parent returns, whether the detached table still exists, and whether the retained records remain valid.

The experiment's reporting copy has no incoming foreign keys, so detaching its January partition does not retire any original order lines or payments. Those remain attached to the original order authority. This deliberately bounded demonstration proves a membership change, not completion of a business deletion.

Finally, track the other copies. A backup, export, document or derived store does not disappear when a partition is dropped. History, correction and deletion follows the meaning and scope of that removal, including recovery copies and restored data. Live schema changes and backfills explains converting an existing live schema while callers continue to use it.

Checking that partitioning earns its cost

Make a prediction before comparing the layouts. October should exclude eleven monthly order partitions. The full year should exclude none. An ID-only lookup should have no time-based exclusion. The line join remains work in both reporting layouts. Check those claims against equivalent results and the actual plans, rather than treating the presence of partitions as success.

The experiment SQL builds the exact generated workload and its separate monthly copy. It compares both reports, records October and full-year plans, and probes ID-only and transformed-time predicates. The recorded results retain the runtime and checks. Boundary, missing-range, duplicate-key and reference counterexamples run separately from the reporting data.

The unpartitioned October plan examined all 120,003 orders; the monthly copy examined 10,196. That order-side exclusion worked, while the line scan stayed. The two layouts returned identical report rows and totals: 991 customer groups for October and 1,002 for the full year. In the separate retirement check, detaching January left 109,812 rows visible through the monthly parent and 10,191 still stored in the detached table. The original orders, lines and payments remained unchanged.

When comparing results, retain customer IDs, exact totals, multiplicity and requested ordering. For work, separate the order partitions removed from scans or index searches inside the remaining pieces and from the independent line-table work. A faster repeated query may simply have warmer caches. Compare planning and execution, then include writes, statistics upkeep, future partition creation and retirement under representative concurrent load. Query execution and performance supplies that measurement method.

The maintenance tests matter as much as the report. Try the exact month boundary, an out-of-range write, a late row after detachment and a dependant that still refers to the month. Confirm that removing a whole partition removes only eligible rows from the intended query surface. Practise the return path if policy permits one.

Partitioning earns its cost when a named boundary removes enough recurring work or makes an authorised lifecycle operation manageable, while the identity and reference rules still hold. This small fixture can demonstrate the mechanism. It cannot establish when your production table is large enough, how many partitions your workload can afford, or whether the maintenance path is safe under its writers.