The Practical Developer

Postgres Partitioning For Time-Series: The Boring Setup That Saves Your Database

September 1, 2023 by The Practical Developer

A 2 TB events table is hard to manage and impossible to clean. Time-based partitioning turns it into 30 small tables you can drop on a cron. Here is the working pattern with declarative partitioning, automated partition management, and the three traps that catch teams new to it.

database

postgres

data-modeling

A circuit board close-up — the right metaphor for the hidden machinery of partition management

The events table is 2 TB. Queries that filter by date take 8 seconds. The retention policy says “keep 30 days” but the only way to delete rows is DELETE FROM events WHERE created_at < now() - interval '30 days' — which takes 4 hours, generates a fresh full-table-scan-worth of WAL, and bloats the table because VACUUM cannot keep up.

This is the situation Postgres declarative partitioning is built to solve. With monthly partitions, “delete a month’s worth of data” becomes DROP TABLE events_2023_01 — instant, no bloat, no WAL flood. Queries that filter by date scan only the relevant partitions. Backups can target hot vs cold ranges differently.

This post is the working setup, the partition-management automation, and the three gotchas that bite teams the first time.

Declarative partitioning, in 30 seconds

Postgres 10+ supports table partitioning as a first-class feature. You declare a parent table partitioned by some key, then create child tables that hold ranges of values:

CREATE TABLE events (
  id          bigserial,
  created_at  timestamptz NOT NULL,
  user_id     bigint NOT NULL,
  payload     jsonb,
  PRIMARY KEY (id, created_at)        -- partition key must be in PK
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2023_09 PARTITION OF events
  FOR VALUES FROM ('2023-09-01') TO ('2023-10-01');

CREATE TABLE events_2023_10 PARTITION OF events
  FOR VALUES FROM ('2023-10-01') TO ('2023-11-01');

SELECT * FROM events queries the parent and Postgres routes to the right partition based on the WHERE clause. Inserts also route automatically.

The first quirk: the partition key (created_at) must be part of the primary key. You cannot have just id as the PK — Postgres needs the partition column to enforce uniqueness across partitions.

Automated partition management

You do not want to remember to add next month’s partition before the first of the month. Use pg_partman:

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control      => 'created_at',
  p_type         => 'native',
  p_interval     => 'monthly',
  p_premake      => 4              -- create 4 months ahead
);

-- Schedule daily maintenance.
SELECT cron.schedule('partman-maintenance', '0 1 * * *', 'CALL partman.run_maintenance_proc()');

run_maintenance_proc creates new partitions and drops old ones according to your retention. With this scheduled, you never think about partitions again.

If you cannot install pg_partman (managed databases sometimes restrict extensions), the manual version is a Postgres function:

CREATE OR REPLACE FUNCTION ensure_next_events_partition() RETURNS void AS $$
DECLARE
  next_month date := date_trunc('month', now() + interval '1 month');
  partition_name text := 'events_' || to_char(next_month, 'YYYY_MM');
BEGIN
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS %I PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
    partition_name,
    next_month,
    next_month + interval '1 month'
  );
END
$$ LANGUAGE plpgsql;

-- Schedule via pg_cron or your application:
-- CALL ensure_next_events_partition();

Indexes on partitioned tables

You declare indexes on the parent; Postgres creates them on each partition automatically:

CREATE INDEX events_user_id_idx ON events (user_id);
CREATE INDEX events_payload_gin ON events USING GIN (payload);

Each new partition (created by pg_partman) inherits these. You do not need to touch indexes again.

For the partition key itself, Postgres creates a btree on (created_at) per partition automatically when used in queries — but you may want to declare it explicitly to ensure a desired order:

CREATE INDEX events_created_at_idx ON events (created_at DESC);

Pruning: the speedup that pays for partitioning

When you query with a WHERE on the partition column, Postgres only scans the relevant partitions:

EXPLAIN ANALYZE
SELECT * FROM events
 WHERE created_at >= '2023-09-15' AND created_at < '2023-09-20';

Plan shows only events_2023_09 is scanned — not events_2023_08 or any other partition. This is “partition pruning” and it is the main performance benefit.

For pruning to work:

  • The WHERE clause must reference the partition column directly, not via a function. WHERE created_at >= now() - interval '7 days' is fine. WHERE date_trunc('day', created_at) >= '2023-09-15' is not (the function call defeats pruning).
  • For prepared statements, generic plans may not prune; use enable_partition_pruning = on (default since PG 11).
  • For partition pruning across partitioned tables in joins, Postgres 12+ handles it well.

Dropping old partitions in zero time

This is the operational win:

DROP TABLE events_2023_06;

Removes 100 GB of data in milliseconds. No DELETE, no VACUUM, no WAL flood. The “delete old data” cron is now trivial:

-- Drop partitions older than 6 months.
DO $$
DECLARE
  rec record;
BEGIN
  FOR rec IN
    SELECT inhrelid::regclass AS partition
    FROM pg_inherits
    WHERE inhparent = 'events'::regclass
  LOOP
    -- Parse the date out of the partition name and compare.
    IF substring(rec.partition::text from 'events_(\d{4}_\d{2})')::text <
       to_char(now() - interval '6 months', 'YYYY_MM')
    THEN
      EXECUTE format('DROP TABLE %s', rec.partition);
    END IF;
  END LOOP;
END $$;

pg_partman handles this with one config option (retention = '6 months').

The three traps

1. Partition key must match access patterns. If you partition by created_at but most queries are WHERE user_id = ? with no time filter, every query scans every partition — slower than the original table. Pick the column that most queries filter on.

2. Foreign keys to partitioned tables. Until Postgres 12, FKs from a child of one partitioned table to another were not supported. PG 12+ supports them, but with limits — declarative FKs on partitioned tables are still less flexible than on plain tables. Test before you assume your existing schema “just works” partitioned.

3. Default partitions are dangerous. If you create a default partition, rows that don’t match any explicit partition land there. Forget to create next month’s partition before the month starts and all the new data accumulates in the default — which then has to be split out. Better: do not create a default partition. Insertion will fail loudly if a partition is missing, which is the right behavior.

Migrating an existing table

The tricky case is partitioning an existing populated table. Three options:

1. Use pg_partman’s existing-table workflow. It can convert in place using triggers and copy data over time.

2. Build new, swap. Create the partitioned table with a temporary name. Copy data in chunks (INSERT INTO events_partitioned SELECT * FROM events ORDER BY created_at). Set up a trigger on the old table to dual-write. After backfill, swap names atomically.

3. Accept downtime. For tables under ~100 GB, a maintenance window is sometimes simpler.

Option 2 is the most flexible but the most engineering effort. Option 1 is the easiest if you can install pg_partman. Don’t pick option 3 unless the table is tiny.

Common mistakes

Range too small. Daily partitions for a low-volume table = thousands of partitions = catalog bloat, slower planning. Aim for 1-10 GB per partition.

Range too large. Yearly partitions for a high-volume table give you only one partition per year being written to, which limits the operational benefit of dropping old data. Monthly is the sweet spot for most time-series.

Forgetting to monitor. A partition that is missing for tomorrow means tomorrow’s writes fail. Alert on “is there a partition for the current and next month?”

Partitioning by a UUID hash. Hash partitioning works but rarely buys you anything. Range or list is what you want most of the time.

When NOT to partition

A few cases:

  • Small tables (< 10 GB). Partitioning overhead is real (planning time, catalog rows). Don’t bother for small tables.
  • No clear partition key. If queries access random rows by ID with no time or category dimension, partitioning hurts.
  • Heavy cross-partition joins. Partitioning works best when each query stays within one partition.

For most cases that are not time-series-shaped (events, logs, audit trails, metrics), partitioning is overkill.

The takeaway

Time-series partitioning turns a runaway table problem into a routine maintenance task. Declarative partitioning + pg_partman + a daily cron is 30 minutes of setup and saves the year of “we need to delete old data but can’t” conversations.

Pick the right partition key (the column most queries filter on), pick the right interval (1-10 GB per partition), automate creation and dropping. The next time someone says “we need to clean up old events,” the answer is DROP TABLE, not a four-hour DELETE.

A note from Yojji

The kind of database operations work that turns “the events table is 2 TB and we can’t delete from it” into “we drop a partition every morning” is the kind of long-haul backend engineering Yojji’s teams build into the products they ship.

Yojji is an international custom software development company founded in 2016, with offices across Europe, the US, and the UK. They specialize in the JavaScript ecosystem, cloud platforms (AWS, Azure, GCP), and Postgres operations — including the partitioning, retention, and lifecycle work that decides whether your database stays manageable as it grows.