Time-series data in plain PostgreSQL: partitions and BRIN

Prices, sensor readings, audit events: rows arrive in time order, are rarely updated, and old data eventually goes away. That shape suits a few PostgreSQL features very well.

Partition by time

Declarative partitioning splits one logical table into child tables by a key, here the timestamp:

CREATE TABLE ticks (
  ts     timestamptz NOT NULL,
  symbol text        NOT NULL,
  price  numeric     NOT NULL
) PARTITION BY RANGE (ts);

CREATE TABLE ticks_2026_09 PARTITION OF ticks
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

Queries with a time condition only touch the relevant partitions (partition pruning), and each partition and its indexes are smaller, so they fit in memory more easily.

The main practical benefit is retention. Deleting old data with DELETE FROM ticks WHERE ts < ... on a big table is slow, produces a large number of dead rows and puts pressure on vacuum. ALTER TABLE ticks DETACH PARTITION ticks_2026_01 followed by DROP TABLE is nearly instant and leaves no bloat. Some things to plan: partitions must be created before data arrives for their range (otherwise inserts fail unless there is a default partition), so use a job or the pg_partman extension to create them ahead of time.

BRIN indexes for ordered data

A B-tree index stores an entry per row. For a table with billions of rows, the index itself becomes large. A BRIN (block range) index stores only the minimum and maximum value for each range of table pages. If the physical order of rows follows the indexed column, as it does for append-only time data, a BRIN index is tiny (often orders of magnitude smaller than a B-tree) and still lets the planner skip most of the table for a time-range query.

CREATE INDEX ticks_ts_brin ON ticks USING brin (ts);

It is less precise than a B-tree, since it points to ranges of pages, so it suits range scans and not single-row lookups. It works only while the correlation holds. Random updates, or backfilling old data into new pages, reduce its effectiveness. Check the physical correlation in pg_stats.correlation for the column.

Other habits that help

• Insert in batches, with COPY or multi-row INSERT, rather than one statement per row.
• Keep rows narrow. Put rarely used attributes elsewhere.
• Pre-aggregate: a per-minute or per-hour summary table answers most dashboards without scanning raw ticks.
• Compress cold data with an appropriate technique. Plain PostgreSQL has TOAST for large values but no columnar compression for ordinary rows.

When something specialised is worth it

Extensions such as TimescaleDB automate partition management (hypertables), provide compression for older chunks and add time-based functions, and they stay inside PostgreSQL. They are worth evaluating when partition maintenance, retention policies and storage volume become real work. For moderate volumes, plain partitioning with BRIN is simple and has no extra moving parts.

The default partition can quietly become the bottleneck

A partitioned table can have one DEFAULT partition that catches any row not matched by another partition's range, which is a reasonable safety net for genuinely unexpected data but a common misconfiguration when it is relied on for ordinary future partitions that a maintenance job simply has not created yet. Every row that lands in the default partition still has to be checked against every other partition's bounds during planning to confirm it does not belong elsewhere, and the default partition itself accumulates data without the retention benefit the rest of the scheme was built for, since it was never designed to be dropped as a unit. If you find rows piling up in the default partition, that is a sign the partition-creation job has fallen behind, not a sign the design needs a bigger default partition.

PostgreSQL also has partition-wise join and aggregate optimisations (enable_partitionwise_join, enable_partitionwise_aggregate, both off by default in many versions because they trade planning time and memory for potential execution speedups) that let the planner join or aggregate matching partitions from two identically partitioned tables independently rather than treating the parent tables as monolithic. They are worth testing when queries regularly join or aggregate across two large tables partitioned the same way, but check the current default and the documented trade-offs for your version before turning them on everywhere.