A partitioned table is a logical table divided into independent child tables. A partitioning rule sends rows with different key values to different partitions, letting a large workload be split into smaller units. In PostgreSQL declarative partitioning, define a partitioning method, a key of one or more columns or expressions, and boundaries with DDL. section 4.2
Use partitioning when a large table’s access and retention patterns match the partition key. A high-volume payments table without an index can require a full scan to find one timestamp and a large delete to remove an old month. Range partitions by timestamp allow PostgreSQL to target the relevant monthly table and allow obsolete data to be removed by dropping its partition. section 4.2 section 4.5
Partition monthly payments by timestamp
Create payments_p with PARTITION BY RANGE (tstamp), then create one child partition per month. After moving the data, a timestamp lookup scans only payments_p_2022_3 rather than the entire payments table. The improvement is substantial, but the selected partition can still be scanned sequentially when no index exists. section 4.2 section 4.2
Avoid two common design mistakes:
- Do not use table inheritance to implement new partitioning designs. Declarative partitioning and inheritance are incompatible; replace an inheritance relationship with ordinary tables and foreign keys before attaching partitions. section 4.1
- Do not mistake RANGE (reading_time, branch_id) for separate monthly partitions per branch range. Multi-column bounds restrict combined values; use sub-partitioning when each time partition must itself be partitioned by branch_id. section 4.3
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM erp.payments_p
WHERE tstamp='2022-03-09 22:58:20.431946+00';
QUERY PLAN
----------------------------------------------------------------------------
Seq Scan on payments_p_2022_3 payments_p (cost=0.00..111450.00 rows=1
width=32) (actual time=1780.649..2535.625 rows=1 loops=1)
Filter: (tstamp = '2022-03-09 22:58:20.431946+00'::timestamp with time
zone)
Rows Removed by Filter: 5349599
Buffers: shared read=44580
Planning:
Buffers: shared hit=20 dirtied=2
Planning Time: 0.406 ms
Execution Time: 2535.658 ms
(8 rows)
The query plan shows partition pruning in practice: the timestamp predicate reaches one monthly partition, reducing scanned rows and buffers without adding an index. section 4.2
