liveBook · Manning

Range partitioning

As explained in PostgreSQL Mistakes and How to Avoid Them

Range partitioning assigns rows to partitions according to ordered lower and upper bounds of a partition key.

Range partitioning assigns rows to ordered lower and upper bounds, making it a practical choice for time-based data whose size and retention requirements are predictable. section 4.2

Monthly payments partitions

Define the parent table with PARTITION BY RANGE (tstamp), then create one partition per month. A timestamp predicate lets PostgreSQL prune unrelated partitions: the example query reads only payments_p_2022_3 and finishes in about 2.5 seconds, compared with 62 seconds against the unpartitioned table.

Operationally, range partitioning also changes data maintenance:

  • Create partitions ahead of ingestion and size them using capacity and retention estimates. section 4.2

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Explains range bounds, partition creation, pruning, data migration, query measurement, and partition replacement for deletion.
Examples
Demonstrates monthly timestamp partitions for payments, EXPLAIN measurements, bulk copying, deletion, and dropping an obsolete partition.
What it assumes
Assumes SQL and PostgreSQL familiarity while requiring capacity, ingestion, and retention estimates to choose a partition key and size.
What you can do after
Operate/configure monthly PostgreSQL partitions and diagnose pruning and maintenance performance with EXPLAIN and timing.
How it connects
Connects partition keys with partition pruning, capacity planning, data retention, and table maintenance.
Cover of PostgreSQL Mistakes and How to Avoid Them
liveBookmanning.com
Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

Compare how 2 books explain range partitioning →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.