Question: What is the syntax for partitioning tables in PostgreSQL?

Answer

PostgreSQL supports table partitioning, which allows for dividing a large table into smaller, more manageable pieces called partitions. Partitioning can help improve performance on very large datasets, especially for queries that filter on the partition key. PostgreSQL provides two primary methods of partitioning: RANGE and LIST, with HASH partitioning added in version 11.

Range Partitioning

Range partitioning means that each partition will hold rows for which the partition key is within a certain range. This type of partitioning is useful for data that naturally orders itself, such as timestamps or serial numbers.

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2020 PARTITION OF measurement
    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

CREATE TABLE measurement_y2021 PARTITION OF measurement
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

List Partitioning

List partitioning means that each partition holds rows for which the partition key matches one of a specified set of values. It’s good for categorizing rows by specific identifiers, such as country codes or department IDs.

CREATE TABLE customer (
    country_id      text not null,
    customer_id     int not null,
    name            text not null
) PARTITION BY LIST (country_id);

CREATE TABLE customer_us PARTITION OF customer
    FOR VALUES IN ('US');

CREATE TABLE customer_uk PARTITION OF customer
    FOR VALUES IN ('UK');

Hash Partitioning

Hash partitioning, available from PostgreSQL 11 onwards, distributes rows across partitions based on the hash value of the partition key. This is useful for evenly distributing a workload across partitions.

CREATE TABLE measurement_hash (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY HASH (city_id);

CREATE TABLE measurement_hash_part1 PARTITION OF measurement_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE measurement_hash_part2 PARTITION OF measurement_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Partitioning in PostgreSQL also supports subpartitioning, where partitions can be further divided using any combination of partitioning types.

Remember, when designing partitioned tables, carefully consider the partitioning strategy to ensure it aligns with your query patterns. Properly designed partitions can significantly improve query performance but require thoughtful planning regarding the partition keys and ranges.