Overview
This page is the atomic definition. PostgreSQL declarative partitioning lives at postgres.
Definition
A partition key is the column (or expression) that determines which partition or shard a row belongs to. In PostgreSQL declarative partitioning, the key is declared with PARTITION BY RANGE (created_at) or PARTITION BY LIST (region) or PARTITION BY HASH (tenant_id). Every INSERT evaluates the key and routes the row to the matching child table. Every query that includes an equality or range predicate on the key allows the query-planner to perform partition pruning, reading only the relevant partitions instead of all of them. A poorly chosen key creates hot partitions. For time-series data, partitioning by date is natural and allows old partitions to be archived or dropped cheaply without a full-table DELETE. For multi-tenant SaaS, tenant_id distributes load by customer. For sharding across nodes, the partition key also determines the target shard; consistent hashing is commonly used to map key values to shard IDs while minimizing remapping when shards are added or removed.
When it applies
Choose a partition key based on the dominant query filter pattern. If most queries filter by created_at, partition by time. If they filter by user_id, partition by user or tenant. Avoid partition keys with very low cardinality (boolean, status with three values) because they create uneven partitions with no pruning benefit.
Example
An events table is partitioned by month: PARTITION BY RANGE (created_at). A query for WHERE created_at >= '2026-04-01' AND created_at < '2026-05-01' accesses only the April partition (one of 48 monthly partitions), reading 1/48 of the data.
Related concepts
- sharding - the partition key is also the shard key when distributing across nodes.
- postgres - PostgreSQL declarative partitioning syntax and partition pruning.
- hot-cold-storage - the partition key defines the time boundary between hot and cold partitions.
- query-planner - the planner uses the partition key for pruning; predicate must match the key expression exactly.
- btree-index - local indexes on each partition accelerate non-key column lookups within a partition.
Citing this term
See Partition Key (llmbestpractices.com/glossary/partition-key).