liveBook · Manning

Partitioning method

As explained in PostgreSQL Mistakes and How to Avoid Them

A partitioning method specifies the rule used to assign table rows to partitions.

A partitioning method defines how PostgreSQL assigns table rows to partitions. For a large payments table, choosing RANGE on the timestamp separates data into time intervals, making maintenance easier than scanning or deleting from one enormous table. section 4.2

The method works with a partition key and partition boundaries. Here, tstamp is the key, while monthly FROM and TO values determine each partition’s interval. section 4.2

Monthly payment partitions

CREATE TABLE erp.payments_p (...) PARTITION BY RANGE (tstamp); CREATE TABLE erp.payments_p_2021_9 PARTITION OF erp.payments_p FOR VALUES FROM (<first day of month>) TO (<last day of month>); Dropping an old monthly partition takes about 43 milliseconds, compared with deleting historical rows from the unpartitioned table for about 66 seconds.

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Defines the method, key, and boundaries, then demonstrates monthly RANGE partitions without detailing other methods.
Examples
Uses a timestamped payments table, monthly RANGE partitions, query plans, bulk migration, deletion timing, and partition dropping.
What it assumes
Assumes SQL and table fundamentals while defining partitioning components explicitly.
What you can do after
Build it from scratch with PostgreSQL DDL by selecting a method, key, and boundaries for partitioned tables.
How it connects
Connects partitioning method to partition key, partition boundaries, table partitioning, and declarative partitioning.
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

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.