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.

Was this content helpful?

White Paper

Free System Design on AWS E-Book

Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.

Free System Design on AWS E-Book
Start building today

Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement.