Designing a Minimal Time‑Series Partitioned Table in PostgreSQL

Requirements

Assume a workload that inserts millions of rows per day from IoT devices and queries recent data for dashboards. The table must support fast inserts, efficient pruning of old data, and a simple schema evolution path.

Smallest Suitable Design

Use declarative range partitioning on a timestamp column, creating one partition per day. This keeps the number of partitions low enough to avoid catalog bloat while still enabling partition pruning for typical WHERE ts >= ... predicates.

-- Run as a superuser or a role with CREATETABLE privilege
CREATE TABLE sensor_data (
    id BIGSERIAL PRIMARY KEY,
    ts TIMESTAMPTZ NOT NULL,
    value DOUBLE PRECISION,
    device_id TEXT
) PARTITION BY RANGE (ts);

-- Example: create a partition for 2026‑10‑01
CREATE TABLE sensor_data_2026_10_01
    PARTITION OF sensor_data
    FOR VALUES FROM ('2026-10-01 00:00:00+00')
    TO ('2026-10-02 00:00:00+00');

To avoid manual DDL for each day, a lightweight maintenance script (run via cron or pgAgent) can generate the next day's partition:

-- Placeholder: replace ${NEXT_DAY} with the target date at 00:00 UTC
DO $$
DECLARE
    start_ts TIMESTAMPTZ := '${NEXT_DAY} 00:00:00+00'::timestamptz;
    end_ts   TIMESTAMPTZ := start_ts + interval '1 day';
    part_name TEXT := format('sensor_data_%s', to_char(start_ts, 'YYYY_MM_DD'));
BEGIN
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF sensor_data FOR VALUES FROM (%L) TO (%L)',
        part_name, start_ts, end_ts
    );
END $$;

Trust / Data Boundaries

• The parent table sensor_data owns the partitioning key; all child partitions inherit its indexes and constraints.
• Write access to the parent is sufficient for inserts; the planner routes rows to the correct child based on ts.
• Read‑only reporting roles need only SELECT on the parent; they automatically see data from all existing partitions.

Operational Checks

1. Verify pruning: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM sensor_data WHERE ts >= '2026-10-01 00:00:00+00' AND ts < '2026-10-02 00:00:00+00'; Look for Partition Select nodes that scan only the expected child.
2. List partitions: SELECT * FROM pg_partition_tree('sensor_data'::regclass); Ensure each partition’s range_start and range_end match the intended day.
3. Monitor bloat: compare pg_class.reltuples with pg_total_relation_size for old partitions; a large discrepancy indicates stale data that should be dropped or vacuumed.
4. Retention check: run a periodic job that drops partitions older than the retention window, e.g., DROP TABLE sensor_data_2026_09_01; (requires DROP privilege).

Failure Modes

• Missing partition for an incoming timestamp causes an error: no partition of relation "sensor_data" found for row. Mitigation: create a default partition or ensure the maintenance script runs before data arrives.
• Orphaned partitions (left after a failed drop) consume disk and increase planner overhead. Regularly run pg_partition_tree() and compare with expected date ranges.
• Over‑partitioning (e.g., hourly partitions) inflates the system catalog, slowing DDL and query planning. Monitor pg_namespace and pg_class row counts; if they grow disproportionately, consider coarser granularity.

Conditions That Would Change the Design

• If queries frequently filter on a column other than ts (e.g., device_id), consider list or hash partitioning on that column, or a composite partitioning strategy.
• When a global unique constraint is required across all time (e.g., unique (device_id, ts)), it must be declared on the parent; unique indexes on children alone do not enforce cross‑partition uniqueness.
• If the retention policy changes to keep data indefinitely, the drop‑old‑partitions step can be removed, but monitoring for catalog growth becomes more critical.
• Should the workload shift to heavy cross‑partition joins (e.g., comparing same‑day data across multiple devices), evaluate whether a non‑partitioned table with appropriate indexing might yield better join performance.