How to create a partitioned table in PostgreSQL for performance

This tutorial explains how to implement declarative partitioning in PostgreSQL to improve query performance and manage large datasets. The steps target PostgreSQL 15 running on a Linux system. You will create a parent table, define child partitions, and insert data without manual table creation.

Prerequisites

• PostgreSQL 15 or later installed on the server.
• A database user with CREATE and DROP privileges on the target database.
• A table schema containing a column suitable for partitioning, such as created_at or year_month.
• Access to the pg_stat_progress_partition view for monitoring partition creation progress.

Step 1: Create the parent table

Create a standard table that defines the structure for all partitions. This table must have the partition key column, which will be used to route rows to the correct child table. Ensure the partition key is NOT NULL and has an appropriate data type for range partitioning.

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    event_data JSONB NOT NULL,
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE

Step 2: Create the first partition

Define the first child table that will hold data for a specific time range. Use the CREATE TABLE command with the LIKE clause to inherit the column definitions from the parent table. Specify the WITH (appendonly = true) option to optimize storage for large partitioned tables.

CREATE TABLE events_2023_01
    PARTITION OF events
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE

Step 3: Create additional partitions

Repeat the process for subsequent months or years. You can create multiple partitions in a single session or script. Each partition must have a non-overlapping range defined by the FOR VALUES clause.

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

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

CREATE TABLE

Step 4: Insert data into the parent table

Insert rows into the parent table. PostgreSQL automatically routes each row to the correct child partition based on the created_at value. Do not insert rows with dates outside the defined ranges, as this will cause an error.

INSERT INTO events (event_type, event_data, created_at)
VALUES
    ('login', '{"ip": "192.168.1.1"}', '2023-01-15 10:00:00'),
    ('logout', '{"ip": "192.168.1.1"}', '2023-01-15 10:05:00'),
    ('login', '{"ip": "192.168.1.2"}', '2023-02-10 14:30:00');

INSERT 0 3

Step 5: Add a partition for future data

Create a partition for the current month and the next month to avoid errors when inserting recent data. This ensures that new rows are always routed to an existing partition.

CREATE TABLE events_2023_04
    PARTITION OF events
    FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');

CREATE TABLE events_2023_05
    PARTITION OF events
    FOR VALUES FROM ('2023-05-01') TO ('2023-06-01');

CREATE TABLE

Verify the installation

Run a query to list all partitions and their row counts. This confirms that data is correctly distributed across the child tables.

SELECT schemaname, tablename, n_live_table, n_inherited, n_inheritrel, n_inheritpart
FROM pg_inherits
JOIN pg_class ON pg_inherits.inhparentrelid = pg_class.oid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_class.relname = 'events';

 schemaname | tablename | n_live_table | n_inherited | n_inheritrel | n_inheritpart
------------+-----------+--------------+-------------+--------------+---------------
 public     | events    | 0            | 3           | 0            | 0

Execute a query that targets a specific partition to verify that the partition pruning works correctly.

SELECT COUNT(*) FROM events WHERE created_at >= '2023-01-01' AND created_at < '2023-02-01';

 count
-------
     2

Troubleshooting

Error: "relation 'events_2023_06' does not exist" This occurs when you insert a row with a date that falls outside the range of all existing partitions. Create a new partition for the missing range before inserting the data.

CREATE TABLE events_2023_06
    PARTITION OF events
    FOR VALUES FROM ('2023-06-01') TO ('2023-07-01');

Error: "column 'created_at' must be partition key" Ensure that the partition key column is defined in the parent table and that all child tables inherit its definition. If you manually create a child table, specify the PARTITION OF clause to link it to the parent.

Performance issue: Slow inserts If inserts are slow, check the pg_stat_progress_partition view to see if partition creation is in progress. Large partitions may take time to create. Consider using ALTER TABLE ... ATTACH PARTITION to attach pre-created partitions instead of creating them inline.

SELECT * FROM pg_stat_progress_partition;

Partition pruning not working Verify that the partition key is indexed. Create an index on the partition key column in the parent table to ensure efficient routing.

CREATE INDEX idx_events_created_at ON events(created_at);

Dropping old partitions To remove partitions that are no longer needed, use the ALTER TABLE ... DETACH PARTITION command followed by DROP TABLE.

ALTER TABLE events DETACH PARTITION events_2023_01;
DROP TABLE events_2023_01;

Always backup data before dropping partitions to prevent accidental data loss.