PostgreSQL Table Partitioning for Time-Series Data and Retention

What range partitioning does for a time-series table, and when it doesn't help

Declarative range partitioning splits one logical table into child tables, each holding a contiguous range of a partition key. For time-series data, that key is usually a timestamp. The parent holds no rows. Inserts are routed to the matching child, and queries against the parent see every child.

Where it pays off

• Retention becomes a metadata operation. Expiring a month of data means detaching or dropping one partition. A DELETE of the same rows writes WAL for every row, leaves dead tuples, and hands the cleanup to vacuum.
• Partition pruning. When a query filters on the partition key, the planner skips partitions that cannot contain matching rows. Pruning can also happen at execution time when the bound is a parameter.
• Smaller maintenance units. Each partition has its own indexes and is vacuumed on its own. Cold partitions rarely change, so maintenance concentrates on the recent ones.

Where it doesn't

• Queries that do not filter on the time column must scan every partition. That is often slower than a single index lookup on one table.
• A large number of partitions adds planning and locking overhead to every query.
• Partitioning manages data. It does not make arbitrary queries faster.

Alternatives

Copy  INSERT (ts in June)             SELECT ... WHERE ts >= 'June 1'
        |                                   |
        v                                   v
 +-------------------------------------------------------------+
 |  metrics  (parent, PARTITION BY RANGE (ts), holds no rows)  |
 |  routes each insert to one child; planner prunes by ts      |
 +-------------------------------------------------------------+
       |               |               |               |
       v               v               v               v
 +-----------+   +-----------+   +-----------+   +-----------+
 | p_march   |   | p_april   |   | p_may     |   | p_june    |
 | [Mar,Apr) |   | [Apr,May) |   | [May,Jun) |   | [Jun,Jul) |
 | own index |   | own index |   | own index |   | own index |
 +-----------+   +-----------+   +-----------+   +-----------+
       |            pruned          pruned      scanned+insert
       v
 +----------------------------+
 | DETACH / DROP PARTITION    |
 | retention as metadata op:  |
 | no per-row WAL, no bloat   |
 +----------------------------+

 SELECT ... WHERE device_id = 42   (no ts filter)
        |
        +--> p_march, p_april, p_may, p_june: every partition scanned

A range-partitioned time-series table: the parent routes inserts to monthly children, a time-bounded query prunes to one partition, retention drops the oldest partition, and a query without a time filter scans every partition.

Creating a range-partitioned parent and its child partitions

The parent table holds no rows itself. You declare it with PARTITION BY RANGE (created_at), and PostgreSQL routes each insert to the child whose bounds contain the row's value. Each child is created with PARTITION OF the parent and a clause like FOR VALUES FROM ('2025-03-01') TO ('2025-03-02'). The lower bound is inclusive and the upper bound is exclusive. That lets adjacent partitions share a boundary value without overlapping: a row stamped exactly at midnight belongs to the new day, not the old one. If you ever write 23:59:59 as an upper bound, you have a gap.

Choose the column type before you write any bounds. With timestamptz, a bound literal without an offset is interpreted in the session's TimeZone when the partition is created. A "day" partition therefore covers midnight-to-midnight in whatever zone the creating session used. If reports define a day in UTC, write the bounds with an explicit +00 offset. Plain timestamp has no zone at all. It only works if every writer stores the same implied zone, which is hard to enforce.

Primary keys and unique constraints must include the partition key. This is because each partition enforces uniqueness only through its own local index. A composite (id, created_at) key works, but it does not guarantee that id alone is unique across partitions. Rely on the sequence or UUID generator for that. Foreign keys that point at this table must also carry both columns.

Updating created_at across a boundary moves the row. PostgreSQL performs it internally as a delete from one partition and an insert into another, so delete and insert triggers on the partitions fire.

Copy-- Parent table: holds no rows itself, only routes them.
-- timestamptz so each bound is a fixed instant, not a zone-less wall-clock time.
CREATE TABLE events (
    id          bigserial,
    created_at  timestamptz NOT NULL,
    payload     jsonb,
    -- The PK must include the partition key. Each partition enforces this
    -- only in its own local index, so id alone is not checked globally.
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Daily partitions with UTC days, set by an explicit +00 offset.
-- FROM is inclusive and TO is exclusive, so adjacent days share a bound
-- without overlapping.
CREATE TABLE events_2025_03_01 PARTITION OF events
    FOR VALUES FROM ('2025-03-01 00:00:00+00') TO ('2025-03-02 00:00:00+00');
CREATE TABLE events_2025_03_02 PARTITION OF events
    FOR VALUES FROM ('2025-03-02 00:00:00+00') TO ('2025-03-03 00:00:00+00');

-- Exactly midnight UTC is routed to the 03-02 partition, not 03-01.
INSERT INTO events (created_at, payload)
VALUES ('2025-03-02 00:00:00+00', '{"type": "login"}');

-- A table that references events must carry both key columns.
CREATE TABLE event_tags (
    event_id          bigint      NOT NULL,
    event_created_at  timestamptz NOT NULL,
    tag               text        NOT NULL,
    FOREIGN KEY (event_id, event_created_at)
        REFERENCES events (id, created_at)
);

-- This update moves the row from events_2025_03_02 to events_2025_03_01.
-- Internally it is a delete plus an insert, so delete and insert triggers
-- on the partitions fire. Run it before tagging the row, because the
-- event_tags foreign key would block this update.
UPDATE events SET created_at = '2025-03-01 12:00:00+00' WHERE id = 1;

-- tableoid shows which partition now holds the row.
SELECT tableoid::regclass AS partition, id, created_at FROM events;

Creates a range-partitioned events table with UTC-bounded daily partitions and a composite (id, created_at) primary key that a foreign key must reference with both columns, then updates the partition key so the row moves to the other partition.

Choosing partition granularity: daily, weekly, or monthly

Illustrative estimate

Assume a metrics table ingesting an average of 500 rows per second, around the clock, with a 90-day retention requirement. That is 500 × 86,400 = 43.2 million rows per day.

The last column follows from retention working in whole partitions. You can only drop a partition once its newest row has aged out, so coarser boundaries mean you keep older data longer. That costs storage and can matter for compliance.

Small partitions have their own costs. Each partition is a full relation with its own catalog entries, and so is each of its indexes. When the planner cannot prune at plan time, it must consider every partition, which increases planning time. A query that touches many partitions also takes a lock on each partition and each of its indexes. That puts load on the lock manager and can exhaust the lock table.

Large partitions weaken pruning. A query for the last six hours still runs against a partition holding a month of rows, with a correspondingly large index. Vacuum and index rebuilds on that partition also take longer.

For this workload, daily partitions are a reasonable starting point: 90 is a manageable partition count, and retention stays precise. At a much lower ingest rate, weekly partitions would avoid creating many small tables with little benefit.

Indexes and verifying partition pruning with EXPLAIN

An index created on the parent with a plain CREATE INDEX is created on every existing partition and on every partition added later. That is convenient on a new table, but on a large one it is a problem: CONCURRENTLY is not supported on a partitioned parent, so a plain build blocks writes to each partition while its index is built.

Adding an index without long write blocks

First, run CREATE INDEX ... ON ONLY on the parent. This creates the parent index alone, marked invalid, and touches no partitions. Next, build the matching index on each partition with CREATE INDEX CONCURRENTLY, which allows writes to continue during the build. Then attach each partition index with ALTER INDEX parent_idx ATTACH PARTITION partition_idx. When every partition has an attached index, PostgreSQL marks the parent index valid. Future partitions get the index automatically.

Checking pruning

Run EXPLAIN on a query with a constant range on the partition key, such as recorded_at >= '2024-06-01'. The plan should list only the partitions that overlap the range. That is plan-time pruning.

When the boundary is known only at run time, the planner keeps every partition in the plan, and the executor removes the ones that cannot match. Examples are a generic plan for a prepared statement, or a predicate such as now() - interval '1 day'. EXPLAIN then shows a line like Subplans Removed: N. Under EXPLAIN ANALYZE, a partition that was pruned during execution appears as (never executed).

Drop the partition-key predicate and the plan lists every partition under an Append node. With 90 daily partitions, one lookup becomes 90 index probes.

Copy-- 1. Parent index only: marked invalid, builds nothing on partitions.
CREATE INDEX metrics_device_time_idx
    ON ONLY metrics (device_id, recorded_at);

-- 2. Build each partition's index without blocking writes.
--    CONCURRENTLY cannot run inside a transaction block.
CREATE INDEX CONCURRENTLY metrics_2024_06_01_device_time_idx
    ON metrics_2024_06_01 (device_id, recorded_at);

-- 3. Attach it. Repeat steps 2-3 for every partition.
ALTER INDEX metrics_device_time_idx
    ATTACH PARTITION metrics_2024_06_01_device_time_idx;

-- Becomes true once every partition has an attached index.
SELECT indisvalid FROM pg_index
WHERE indexrelid = 'metrics_device_time_idx'::regclass;

-- Plan-time pruning: constant bounds, only matching partitions appear.
EXPLAIN SELECT * FROM metrics
WHERE device_id = 42
  AND recorded_at >= '2024-06-01' AND recorded_at < '2024-06-02';

-- Execution-time pruning: generic plan, bound unknown at plan time.
PREPARE recent(timestamptz) AS
  SELECT * FROM metrics WHERE device_id = 42 AND recorded_at >= $1;
SET plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE recent('2024-06-01');  -- look for "Subplans Removed: N"

-- No partition-key predicate: Append over every partition.
EXPLAIN SELECT * FROM metrics WHERE device_id = 42;

This example adds an index to a partitioned table using ON ONLY, CREATE INDEX CONCURRENTLY and ATTACH PARTITION. It then uses EXPLAIN to compare plan-time pruning, execution-time pruning and a query with no partition-key predicate that scans every partition.

Implementing retention with DETACH and DROP instead of DELETE

Retention follows the same logic: once each day lives in its own table, expiring a day means removing a table, not deleting rows. The workflow has three steps. Run ALTER TABLE ... DETACH PARTITION to remove the oldest partition from the parent. The detached table then becomes an ordinary standalone table. Optionally archive it, for example with pg_dump -t or COPY to object storage. Finally, run DROP TABLE.

A DELETE ... WHERE ts < now() - interval '90 days' on an unpartitioned table creates three costs:

• WAL volume. Every removed row is logged, and replicas must replay all of it.
• Bloat. Each row becomes a dead tuple, and every index keeps entries pointing at it.
• Vacuum load. Autovacuum must scan the heap and indexes to reclaim the space. The freed pages are usually reused rather than returned to the operating system.

DROP TABLE instead unlinks the partition's files and logs only the catalog change. The cost stays roughly the same whether the partition holds a thousand rows or a billion.

A plain DETACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent. It completes quickly, but it waits behind any long-running query on the table and blocks every query queued behind it. Setting lock_timeout on the session prevents that pile-up.

Servers that support DETACH PARTITION ... CONCURRENTLY use weaker locks and split the operation into two transactions. This option has three restrictions:

• It cannot run inside a transaction block.
• It is not allowed when the parent has a DEFAULT partition.
• If it is interrupted, the partition is left in a pending state, which you complete with DETACH PARTITION ... FINALIZE.

Copy#!/usr/bin/env bash
# Expire one daily partition: detach, archive, drop.
set -euo pipefail

DB="metrics_db"                # placeholder database name
PARENT="metrics"               # partitioned parent table
PART="metrics_2026_07_24"       # oldest partition past the 90-day window

# Give up quickly instead of queueing behind long queries.
# PGOPTIONS applies lock_timeout to every session psql opens.
export PGOPTIONS="-c lock_timeout=5s"

# Step 1a (PostgreSQL 14+, no DEFAULT partition on the parent):
# CONCURRENTLY cannot run in a transaction block, so it gets its own
# -c (a single -c with several statements runs as one transaction).
if ! psql -X -v ON_ERROR_STOP=1 -d "$DB" \
     -c "ALTER TABLE ${PARENT} DETACH PARTITION ${PART} CONCURRENTLY;"; then
  # If the command was interrupted, the partition is left pending.
  # Finish the detach instead of retrying from the start.
  psql -X -v ON_ERROR_STOP=1 -d "$DB" \
       -c "ALTER TABLE ${PARENT} DETACH PARTITION ${PART} FINALIZE;"
fi

# Step 1b (alternative for older servers): plain DETACH takes an
# ACCESS EXCLUSIVE lock on the parent. lock_timeout bounds the wait.
# psql -X -v ON_ERROR_STOP=1 -d "$DB" \
#      -c "ALTER TABLE ${PARENT} DETACH PARTITION ${PART};"

# Step 2 (optional): archive the now-standalone table.
pg_dump -d "$DB" -t "${PART}" -Fc -f "/backups/${PART}.dump"

# Step 3: drop it. This unlinks the files and logs only the catalog
# change, so there are no dead tuples, no vacuum work, and little WAL.
psql -X -v ON_ERROR_STOP=1 -d "$DB" -c "DROP TABLE ${PART};"

A retention script that detaches the oldest daily partition (concurrently where supported, with FINALIZE as the recovery path and lock_timeout as a safeguard), archives it with pg_dump, and drops it instead of deleting rows.

Automating partition creation: scheduled scripts, pg_partman, and the DEFAULT partition

If no partition covers an incoming row's timestamp, the insert fails with an error. On a time-series table that error arrives at midnight on the first day nobody prepared for, so partitions have to exist ahead of the data. Keep a buffer of several future intervals so that one missed run doesn't cause an outage.

There are two common ways to do this. A scheduled script is a cron or scheduler job that runs CREATE TABLE ... PARTITION OF for upcoming ranges and detaches and drops expired ones. pg_partman stores each parent's interval, how many partitions to pre-create, and its retention policy in a configuration table. Its maintenance routine then creates future partitions and enforces retention when it runs.

Neither approach removes the need for monitoring. A silent scheduler failure looks the same either way: you find out when inserts fail.

A DEFAULT partition catches rows that match no other partition, so inserts don't fail. It has costs. When you create a partition for a range, PostgreSQL scans the default partition to confirm none of its rows belong in that range. If matching rows exist, the creation fails, and you have to move those rows out before you can add the partition. The default partition can also hide a broken creation job, because inserts keep succeeding while data piles up in an unpruned table. If you use one, alert when its row count is non-zero.

Migrating an existing table, and the impact on cost and engineering time

There is no in-place conversion. PostgreSQL cannot change an ordinary table into a partitioned one, so every migration either moves the data or wraps it.

Path 1: New partitioned table plus batched backfill

Create the partitioned parent and its partitions alongside the old table. Copy rows in bounded batches by time range so each transaction stays short and WAL volume stays predictable. For the cutover, dual-write new rows to both tables, backfill the history, check row counts per range, and then switch reads and writes in one deploy. Swapping table names inside a single transaction keeps the switch atomic. This path costs the most I/O, but it leaves every row in a correctly sized partition.

Path 2: Attach the old table as one historical partition

Add a CHECK constraint on the old table that exactly matches the partition bounds you plan to give it. Add it as NOT VALID and validate it in a separate step, so the long scan runs under a lighter lock. When ATTACH PARTITION finds a proven constraint, it skips its own validation scan. The tradeoff is that the history sits in one oversized partition, which you can drop only after its newest row has aged past retention.

Both paths make you redesign constraints. Primary keys and unique constraints must include the partition key, which can affect application code and foreign keys.

The cost tradeoff

The upfront cost is real: schema design, migration scripting, cutover rehearsal, and constraint changes. In return, retention DELETE jobs, bloat cleanup, and per-table vacuum tuning become routine detach-and-drop operations that change metadata and do not rewrite data.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql table partitioning, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.