PostgreSQL Power User Cheatsheet

Concept

A partitioned table is a logical "parent" table that is physically composed of multiple child tables (partitions). Rows inserted into the parent are automatically routed to the correct partition based on the partition key and method.

Partitioning Methods

• RANGE: Partitions based on ranges of the partition key (e.g., date ranges, numeric ranges). `PARTITION BY RANGE (created_at)` [docs]
• LIST: Partitions based on specific values of the partition key (e.g., country codes, status codes). `PARTITION BY LIST (country_code)` [docs]
• HASH: Partitions based on a hash of the partition key, distributing rows roughly evenly. `PARTITION BY HASH (user_id)` [docs]

Creating Partitions

-- Parent Table Definition
CREATE TABLE measurements (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

-- Creating Partitions
CREATE TABLE measurements_y2023m01 PARTITION OF measurements
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE measurements_y2023m02 PARTITION OF measurements
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- List Partition Example
CREATE TABLE cities ( ... ) PARTITION BY LIST (country);
CREATE TABLE cities_us PARTITION OF cities FOR VALUES IN ('US');
CREATE TABLE cities_jp PARTITION OF cities FOR VALUES IN ('JP');
CREATE TABLE cities_other PARTITION OF cities DEFAULT; -- Optional default

Benefits

• Performance (Partition Pruning): Queries filtering by the partition key can skip scanning irrelevant partitions entirely.
• Manageability: Easier bulk data loading/deletion by manipulating partitions (`ATTACH`/`DETACH PARTITION`, `DROP TABLE partition_name`). Efficient for time-series data retention (drop old partitions).
• Maintenance: Operations like `VACUUM` or `REINDEX` can often be performed on individual partitions.

Considerations

• Indexes and constraints usually need to be created on each partition individually (though primary keys/unique constraints must include the partition key). Use `CREATE INDEX ON parent_table ...` to automatically create on all partitions (and future ones).
• Partition key cannot easily be changed after creation.
• Too many partitions can increase planning time and resource usage.
• Use extensions like `pg_partman` to automate partition creation and maintenance.

Adding Partitions

• Direct Creation: Create a new table that is immediately a partition.

  CREATE TABLE measurements_y2023m03 PARTITION OF measurements
      FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

• Attaching Existing Table: Attach a regular table (matching the parent's structure) as a new partition. Useful for bulk loading data into a separate table first. [docs]

  CREATE TABLE measurements_staging (...); -- Load data here
  ALTER TABLE measurements ATTACH PARTITION measurements_staging
      FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

  This operation takes a brief `ACCESS EXCLUSIVE` lock on the parent table.

Removing Partitions

• Detaching Partition: Convert a partition back into a standalone regular table. The data remains but is no longer part of the partitioned table. Takes a brief `ACCESS EXCLUSIVE` lock. [docs]

  ALTER TABLE measurements DETACH PARTITION measurements_y2023m01;
  -- Now measurements_y2023m01 is a regular table.
  -- Can archive or drop it later.

• Dropping Partition: Directly drop the partition table. This permanently deletes the data in that partition. Fast operation.

  DROP TABLE measurements_y2023m01;

Automation

• Manual creation/dropping of range partitions (e.g., monthly) is tedious and error-prone.
• Use extensions like `pg_partman` which provide functions to automatically create new partitions ahead of time and drop old ones based on retention policies. Often scheduled via `pg_cron` or external schedulers.

Indexes & Constraints

• Remember to create indexes on new partitions. Using `CREATE INDEX ON parent_table ...` handles this automatically for future partitions.
• Foreign keys referencing partitioned tables are possible but have limitations. [limitations]