Partitioning

About

Partioning is splitting one table into, multiple smaller, logically divided and more manageable tables.

Postgres' query optimizer is able to exclude partitions that, for sure, won't be affected by the data that it is trying to be read or written. (Postgres automatically routes reads and writes to the right partition)

Postgres does not:

• Create the partions automatically, you must manually create and define the partion's boundaries.

• Manage old partitions.

• Indexes on new partitions, you must create indexes on each new partition. (There are no global indexes, indexes are always per-partition)

• Constraints on partitions.

Don't think about partitioning before reaching several million records or having memory problems with queries, as there would be no gain in performance.

The administrative overhead of maintaining the partitions will outweight performance benefits.

Be very careful with multi-level partitioning because the number of partition files can grow very quickly.

You can use pg_partman extension for some more automatic management of partitions.

Types of Partitions

range

The table is partitioned into ranges defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions.

Useful when working with dates, e.g. Daily/Weekly/Monthly/Yearly partitions.

Creating partition table

/*
    -- This will be the master table
    CREATE TABLE table_name (
        ...
    ) PARTITION BY RANGE (col_used_to_partition)
    
    -- Then create the partition tables manually
    CREATE TABLE table_name_partitionX PARTITION OF table_name
        FOR VALUES FROM value1 TO value2;
*/

CREATE TABLE table_name (
    created_at DATE NOT NULL
) PARTITION BY RANGE (created_at)

CREATE TABLE table_name_range_2024 PARTITION OF table_name
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')

CREATE TABLE table_name_range_2025 PARTITION OF table_name
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

list

The table is partitioned by explicitly listing which key values appear in each partition.

Ideal for conditions when you know known values of partition key, e.g contry_codes, month_names, etc.

Creating partition table

/*
    -- This will be the master table
    CREATE TABLE table_name (
        ...
    ) PARTITION BY LIST (col_used_to_partition)
    
    -- Then create the partition tables manually
    CREATE TABLE table_name_partitionX PARTITION OF table_name
        FOR VALUES IN (list_of_values);
*/

CREATE TABLE table_name (
    contry_code VARCHAR NOT NULL
    population INT
) PARTITION BY LIST (contry_code)

CREATE TABLE table_name_list_am PARTITION OF table_name
    FOR VALUES IN ('US', 'BR', 'MX')

CREATE TABLE table_name_list_eu PARTITION OF table_name
    FOR VALUES IN ('UK', 'DE');

hash

The table is partitioned by specifying a modulus and a remainder for each partition.

Each partition will hold rows for the remainer, which is the hash value of the partition key divided by the specified modulus.

This type is useful when we can't logically divide the data.

Creating partition table

/*
    -- This will be the master table
    CREATE TABLE table_name (
        ...
    ) PARTITION BY HASH (col_used_to_partition)
    
    -- Then create the partition tables manually
    CREATE TABLE table_name_partitionX PARTITION OF table_name
        FOR VALUES WITH (MODULUS m, PARTITION n);
*/

CREATE TABLE table_name (
    id BIGINT NOT NULL
) PARTITION BY HASH (id)

CREATE TABLE table_name_hash_1 PARTITION OF table_name
    FOR VALUES WITH (MODULUS 3, REMAINDER 0)

CREATE TABLE table_name_hash_2 PARTITION OF table_name
    FOR VALUES WITH (MODULUS 3, REMAINDER 1);

default

Default partition tables are the ones that will receive all the data that does not fit in any of the created partitions.

/*
    CREATE TABLE table_name_partitionX_default PARTITION OF table_name DEFAULT;
*/

CREATE TABLE table_name (
    contry_code VARCHAR NOT NULL
    population INT
) PARTITION BY LIST (contry_code)

CREATE TABLE table_name_list_default PARTITION OF table_name DEFAULT;

Accessing Data

Almost always you interact with the master (aka parent or partitioned) table, not the individual partitions.

If you try to insert a row that doesn't fit any partition you will get a:

ERROR: no partition of relation "table_name" found for row

Unless you have setup a default partition table.

Operation	Use Parent Table?	What Happens Internally
SELECT	✅ Yes	Postgres uses partition pruning to scan only relevant partitions.
INSERT	✅ Yes	Data is routed to the correct partition automatically.
UPDATE	✅ Yes	Postgres finds which partition the row is in and moves it if necessary.
DELETE	✅ Yes	Postgres finds and deletes from the correct partition(s).
COPY	✅ Yes	Same as insert; routed correctly.
ANALYZE, VACUUM	❌ Run per partition	Not automatic on children unless specifically enabled.

SELECT * FROM table_name WHERE created_at >= '2025-01-01';

INSERT INTO table_name (created_at) VALUES ('2025-07-16');

UPDATE table_name SET population = 1000 WHERE contry_code = 'US';

DELETE FROM table_name WHERE created_at < '2024-01-01';

Maintainance

detach

Unlink a partition from the partition group. (It does not delete it)

ALTER TABLE table_name DETACH PARTITION table_name_partitionX;

attach

You can atach an unlinked partition again with:

ALTER TABLE table_name ATACH PARTITION table_name_partitionX
    FOR VALUES value1 TO value2;

When you reatach yo specifiy the partition type values again RANGE, LIST or HASH.

To change a partition table range value after created you have to detach and reatach it.

BEGIN TRANSACTION
    ALTER TABLE table_name DETACH PARTITION table_name_partitionX
    ALTER TABLE table_name ATACH PARTITION table_name_partitionX
        FOR VALUES value1 TO value2
COMMIT TRANSACTION;