How to partition a table in PostgreSQL

Partitioning a large table improves query performance and simplifies retention management by splitting data into smaller, easier-to-scan chunks. It is especially useful for time-series, audit, and event tables where most queries target a recent range and old data must be removed on a schedule.

PostgreSQL declarative partitioning uses a single logical parent table and multiple child tables called partitions. Each partition covers a non-overlapping slice of the partition key (for example, a monthly RANGE on a timestamp), and PostgreSQL routes inserts to the correct partition automatically.

Partitioning benefits depend on query patterns and design choices. The partition key must appear in common WHERE filters for partition pruning to skip irrelevant partitions, and partitions must exist for incoming data (or a DEFAULT partition must be present). PRIMARY KEY and UNIQUE constraints must include the partition key, and an excessive number of partitions can increase planning overhead and maintenance time.

1. Connect to the target database using psql with a role that can create tables.

   $ psql -d appdb -c "SELECT current_database();"
    current_database 
   ------------------
    appdb
   (1 row)

2. Create the partitioned parent table using RANGE partitioning on the timestamp column.

   $ psql -d appdb -c "CREATE TABLE events (event_id bigint GENERATED ALWAYS AS IDENTITY, event_ts timestamptz NOT NULL, user_id bigint NOT NULL, event_type text NOT NULL, payload jsonb, PRIMARY KEY (event_ts, event_id)) PARTITION BY RANGE (event_ts);"
   CREATE TABLE

   PRIMARY KEY and UNIQUE constraints on a partitioned table must include the partition key column(s).

3. Create child partitions that cover the initial time ranges.

   $ psql -d appdb -c "CREATE TABLE events_2025_01 PARTITION OF events FOR VALUES FROM ('2025-01-01 00:00:00+00') TO ('2025-02-01 00:00:00+00');"
   CREATE TABLE
   $ psql -d appdb -c "CREATE TABLE events_2025_02 PARTITION OF events FOR VALUES FROM ('2025-02-01 00:00:00+00') TO ('2025-03-01 00:00:00+00');"
   CREATE TABLE

   Partition bounds must be non-overlapping, and each row must match exactly one partition.

4. Create a DEFAULT partition to accept rows that do not match any defined range.

   $ psql -d appdb -c "CREATE TABLE events_default PARTITION OF events DEFAULT;"
   CREATE TABLE

   A DEFAULT partition prevents insert failures for out-of-range data, and later partitions may require moving matching rows out of the default partition before attachment.

5. Create commonly used indexes on the parent table to propagate them to partitions.

   $ psql -d appdb -c "CREATE INDEX events_user_id_idx ON events (user_id);"
   CREATE INDEX
   $ psql -d appdb -c "CREATE INDEX events_event_type_idx ON events (event_type);"
   CREATE INDEX

   Indexes created on the partitioned parent become partitioned indexes, and PostgreSQL maintains matching indexes on each partition.

6. List the attached partitions to confirm the partition key and bounds.

   $ psql -d appdb -c "\d+ events"
                                                            Partitioned table "public.events"
      Column   |           Type           | Collation | Nullable |           Default            | Storage  | Compression | Stats target | Description 
   ------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
    event_id   | bigint                   |           | not null | generated always as identity | plain    |             |              | 
    event_ts   | timestamp with time zone |           | not null |                              | plain    |             |              | 
    user_id    | bigint                   |           | not null |                              | plain    |             |              | 
    event_type | text                     |           | not null |                              | extended |             |              | 
    payload    | jsonb                    |           |          |                              | extended |             |              | 
   Partition key: RANGE (event_ts)
   Indexes:
       "events_pkey" PRIMARY KEY, btree (event_ts, event_id)
       "events_event_type_idx" btree (event_type)
       "events_user_id_idx" btree (user_id)
   Partitions: events_2025_01 FOR VALUES FROM ('2025-01-01 00:00:00+00') TO ('2025-02-01 00:00:00+00'),
               events_2025_02 FOR VALUES FROM ('2025-02-01 00:00:00+00') TO ('2025-03-01 00:00:00+00'),
               events_default DEFAULT

7. Insert test rows into the parent table to confirm automatic routing.

   $ psql -d appdb -c "INSERT INTO events (event_ts, user_id, event_type, payload) VALUES ('2025-01-15 12:34:56+00', 1001, 'login', '{\"ip\":\"203.0.113.10\"}'), ('2025-02-05 08:00:00+00', 1002, 'logout', '{\"reason\":\"timeout\"}'), ('2030-01-01 00:00:00+00', 1003, 'login', '{\"ip\":\"203.0.113.11\"}');"
   INSERT 0 3

   Without a matching partition or a DEFAULT partition, inserts fail with no partition of relation found for row.

8. Confirm which partition stores each row using tableoid.

   $ psql -d appdb -c "SELECT tableoid::regclass AS partition, count(*) AS rows FROM events GROUP BY 1 ORDER BY 1;"
      partition    | rows 
   ----------------+------
    events_2025_01 |    1
    events_2025_02 |    1
    events_default |    1
   (3 rows)

9. Verify partition pruning by running an EXPLAIN that filters on the partition key.

   $ psql -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM events WHERE event_ts >= '2025-02-01 00:00:00+00'::timestamptz AND event_ts < '2025-03-01 00:00:00+00'::timestamptz;"
                                                                               QUERY PLAN                                                                            
   ------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Aggregate  (cost=12.67..12.68 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)
      Buffers: shared hit=2
      ->  Bitmap Heap Scan on events_2025_02 events  (cost=4.19..12.66 rows=4 width=0) (actual time=0.004..0.004 rows=1 loops=1)
            Recheck Cond: ((event_ts >= '2025-02-01 00:00:00+00'::timestamp with time zone) AND (event_ts < '2025-03-01 00:00:00+00'::timestamp with time zone))
            Heap Blocks: exact=1
            Buffers: shared hit=2
            ->  Bitmap Index Scan on events_2025_02_pkey  (cost=0.00..4.19 rows=4 width=0) (actual time=0.001..0.001 rows=1 loops=1)
                  Index Cond: ((event_ts >= '2025-02-01 00:00:00+00'::timestamp with time zone) AND (event_ts < '2025-03-01 00:00:00+00'::timestamp with time zone))
                  Buffers: shared hit=1
    Planning:
      Buffers: shared hit=229
    Planning Time: 0.350 ms
    Execution Time: 0.021 ms
   (13 rows)

   Partition pruning is most reliable with direct range predicates on the partition key, and expressions like date_trunc('month', event_ts) can prevent pruning.

10. Add the next partition before new data arrives to avoid insert failures or default partition growth.

    $ psql -d appdb -c "CREATE TABLE events_2025_03 PARTITION OF events FOR VALUES FROM ('2025-03-01 00:00:00+00') TO ('2025-04-01 00:00:00+00');"
    CREATE TABLE

    Regular partition creation can be automated with scheduled jobs, and partition naming conventions should encode the covered range.

11. Detach an old partition from the parent table to remove it from partitioned queries without deleting its data immediately.

    $ psql -d appdb -c "ALTER TABLE events DETACH PARTITION events_2025_01;"
    ALTER TABLE

    A detached partition remains as a standalone table and can be queried directly if needed.

12. Drop the detached partition table to permanently remove its data.

    $ psql -d appdb -c "DROP TABLE events_2025_01;"
    DROP TABLE

    Dropping a partition table permanently deletes its data and cannot be reversed without backups.