One table, many bodies

The bookstore's orders table spans years, while most queries and retention work concern one month. Adding an index can locate a date range, but it does not make dropping an entire old month a metadata operation. PostgreSQL partitioning keeps one logical table while routing rows into child relations according to a declared partition key and non-overlapping bounds.

Range partitioning maps values to ordered intervals: January orders here, February orders there. List partitioning maps specific values: region `EU` here, region `US` there. Hash partitioning spreads rows across buckets for more even distribution when natural ranges do not help. Each strategy encodes a different assumption about access patterns. A time-series workload often wants range partitions. A multi-tenant workload may want tenant-based isolation or hash balance.

Pruning is the payoff

Partition pruning is the planner's main reward. If a query filters on the partition key, the planner can exclude partitions that cannot contain matching rows. A request for last week's events should not scan five years of partitions. Runtime pruning can also help when parameter values are not known until execution. Without pruning, partitioning risks becoming many-table overhead with little benefit.

Trace pruning and routing

A February query carries a predicate on the partition key. From partition bounds, PostgreSQL proves that January and older children cannot contribute rows, so it plans or executes only the February child; an index inside that child may narrow the search further. An inserted February order travels the opposite direction: routing evaluates its key and chooses exactly one matching child or the default partition.

The invariant is complete, exclusive coverage for accepted rows. Pruning may exclude a child only when its bounds prove no match, and routing must not guess when no bound accepts the row. Partitioning changes which local relations participate. It does not by itself distribute them across servers, duplicate them for availability, or replace access paths within them.

The shape is not an index

Partitioning and indexing solve different shapes of search. An index helps find rows within a structure. Partitioning changes the structure being searched. A query can use both: prune to the relevant monthly partition, then use an index inside that partition. But partitioning is not a substitute for indexing. If every query touches every partition, the system may do more planning and execution work than a single well-indexed table would.

Partitioning helps maintenance because pieces can be managed independently. Dropping old data can become dropping an old partition, which is far cheaper than deleting billions of rows and vacuuming the remains. Indexes can be smaller per partition. Vacuum can work on partitions with churn instead of scanning a massive quiet table. Backups and restores may be organized around natural slices. The operational shape improves when data has real boundaries.

There are costs. Queries that aggregate across all partitions may open many relations and combine many plans. Unique constraints are harder when the partition key is not included, because uniqueness must be enforced across pieces. Foreign keys, global indexes, and updates that move rows between partitions complicate the design. Partitioning makes some operations cheaper at the cost of making others more complex.

Routing writes, not just reads

Partition routing must be correct for writes. An insert into the logical table needs to find the target partition from the row values. If no partition matches, the database must reject the row or route it to a default partition. Updates that change the partition key may need to move a row from one child to another, which is closer to a delete plus an insert than a simple in-place update. The logical table hides this motion, but storage must perform it.

Distributed databases push division across machines, where shards introduce networking, placement metadata, cross-node transactions, distributed planning, and failure handling. PostgreSQL declarative partitioning does not become sharding merely because child tables exist. Distribution adds ownership and availability questions beyond local partition bounds.

The contrast between partitioning and replication is useful. Replication copies the same changes to another place. Partitioning divides different rows among places. Replication improves availability and read capacity for the same data. Partitioning improves manageability or write capacity by splitting data. Real systems often combine them: each shard may have replicas, and each replica may contain partitioned tables.

Partitioning should follow data reality. If the key appears in most queries and maintenance naturally follows that boundary, it can make the database feel much smaller. If the key is chosen only because the table is large but queries ignore it, the system merely inherits more objects to plan, lock, vacuum, and monitor. The architecture offers a lever, and the workload decides whether the lever moves anything.

A partition-key test

Ask whether common predicates expose the key, whether retention and maintenance follow the same boundary, whether constraints remain enforceable, and how many children broad queries must open. A useful key lets PostgreSQL prove absence cheaply and manage slices independently. A fashionable key that queries ignore merely multiplies catalogs, plans, locks, indexes, and vacuum work without reducing the rows examined.