Master PostgreSQL Table Partitioning: Types, Commands, and Best Practices

1. What Is Table Partitioning?

Table partitioning splits a large table into smaller, more manageable child tables based on a partitioning strategy, while the parent table maintains a logical relationship to the children.

Before PostgreSQL 10, partitioning required manual inheritance and trigger mechanisms, which were complex. Starting with PostgreSQL 10, built‑in partitioning simplifies the process, supporting RANGE and LIST partitions, with HASH added in version 11.

2. Why Use Partitioning?

Allows a single table to store far more data.

Facilitates easier maintenance: entire partitions can be dropped, archived, or loaded quickly.

Improves query performance when predicates limit data to a few partitions.

Enables distribution of data across multiple physical devices.

Helps avoid specific bottlenecks and allows backup/restore of individual partitions.

3. Advantages and Disadvantages

Advantages

Reduced maintenance cost; data can be detached or restored without affecting the whole table.

Different partitions can reside on different storage tiers (cold vs. hot data).

Queries on partitioned tables can be faster than scanning a massive monolithic table.

Disadvantages

Potential performance trade‑offs: querying through the parent table may be slower than a single flat table.

Older versions (10.x) do not support cross‑partition updates; this is only available from 11.x onward.

Primary‑key uniqueness must be enforced per partition, which can lead to duplicate keys unless additional strategies are applied.

4. Partition Types

4.1 RANGE Partition

Range partitions divide data into continuous intervals, ideal for time‑series data such as dates.

CREATE TABLE orders (
    id serial,
    user_id int4,
    create_time timestamp(0)
) PARTITION BY RANGE (create_time);

CREATE TABLE orders_history PARTITION OF orders
    FOR VALUES FROM ('2000-01-01') TO ('2020-03-01');
CREATE TABLE orders_202003 PARTITION OF orders
    FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
-- additional monthly partitions omitted for brevity

CREATE INDEX order_idx_history_create_time ON orders_history (create_time);
CREATE INDEX order_idx_202003_create_time ON orders_202003 (create_time);

4.2 LIST Partition

List partitions assign rows to partitions based on discrete values, useful for categorizing by region, department, etc.

CREATE TABLE cities (
    city_id bigint NOT NULL,
    name text NOT NULL,
    population bigint
) PARTITION BY LIST (name);

CREATE TABLE cities_A PARTITION OF cities FOR VALUES IN ('A');
CREATE TABLE cities_B PARTITION OF cities FOR VALUES IN ('B');
CREATE TABLE cities_C PARTITION OF cities FOR VALUES IN ('C');
CREATE TABLE cities_D PARTITION OF cities FOR VALUES IN ('D');

4.3 HASH Partition

CREATE TABLE emp (
    emp_id int,
    emp_name text,
    dep_code int
) PARTITION BY HASH (emp_id);

CREATE TABLE emp_0 PARTITION OF emp FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE emp_1 PARTITION OF emp FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE emp_2 PARTITION OF emp FOR VALUES WITH (MODULUS 3, REMAINDER 2);

5. Common Partition Management Commands

-- View row counts per partition
SELECT relname, reltuples AS rows FROM pg_class
WHERE relname IN ('emp','emp_0','emp_1','emp_2')
ORDER BY relname;

-- Detach a partition
ALTER TABLE emp DETACH PARTITION emp_0;

-- Rename a partition
ALTER TABLE emp_0 RENAME TO emp_0_bkp;

-- Add a new partition (example for range)
ALTER TABLE table_name ADD PARTITION partition_name VALUES LESS THAN ('value');

-- Drop an existing partition
ALTER TABLE table_name DROP PARTITION partition_name;

-- List all partition information
SELECT * FROM pg_partitions WHERE tablename = 'table_name';

6. Conclusion

In short, partitioning breaks a large relational table into smaller pieces, improving query speed and maintenance. This article covered PostgreSQL's RANGE, LIST, and HASH partitioning methods and demonstrated how to create, index, and manage them effectively.