DEV Community

Cover image for Billions of Log Rows, One Command to Make Them Disappear
Chizee
Chizee

Posted on Originally published at omenabyte.com

Billions of Log Rows, One Command to Make Them Disappear

#postgres #database #timeseries #tutorial

Telemetry and event logs only grow. Dump them into one giant table and every query — even one scoped to a single day — ends up scanning far more data than it needs to. That's usually the exact moment a team reaches for a dedicated time-series database like InfluxDB.

Two native Postgres features cover most of what people reach for InfluxDB to get: declarative partitioning and BRIN indexes.

Split the table without splitting the app 

CREATE TABLE events (
  id          bigserial,
  event_time  timestamptz NOT NULL,
  payload     jsonb
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
Enter fullscreen mode Exit fullscreen mode

One note that will save you a confusing error: id is deliberately not a primary key here. On a partitioned table, any unique or primary key constraint has to include the partition column. If you need one, it's PRIMARY KEY (id, event_time).

A featherweight index built for this exact shape of data 

CREATE INDEX idx_events_time_brin
  ON events USING BRIN (event_time);
Enter fullscreen mode Exit fullscreen mode

A BRIN (Block Range Index) stores just the minimum and maximum value for each physical block of disk, instead of indexing every single row the way a B-Tree does. For data that arrives roughly in time order — which almost all event data does — that's enough to skip millions of irrelevant pages instantly, at a fraction of a B-Tree's size.

SELECT count(*) FROM events
WHERE event_time BETWEEN '2026-06-10' AND '2026-06-11';
Enter fullscreen mode Exit fullscreen mode

Run EXPLAIN on that and you'll see the planner only touches the one relevant partition — the rest are pruned before the query even runs.

Retention becomes a metadata operation 

DROP TABLE events_2026_06;
Enter fullscreen mode Exit fullscreen mode

That's it. No DELETE scan, no bloat left behind to vacuum. Dropping a partition removes its rows instantly — I tested this directly: 500 seeded rows, one DROP TABLE, zero rows left, no measurable delay.

Why this beats a dedicated time-series database

  • Retention policies are a DROP TABLE, not a scheduled bulk delete job.
  • BRIN indexes cost a sliver of what an equivalent B-Tree would.
  • It's the same SQL as the rest of your schema — no separate query language, no separate client library.

Where InfluxDB still wins

Purpose-built downsampling and retention policies out of the box, at ingest rates in the millions of points per second. If you're managing your own partitions and indexes comfortably below that, you're not missing anything by staying on Postgres.

This is one of eight infrastructure swaps in Just Use Postgres, a 24-page field manual on replacing MongoDB, Redis, Elasticsearch, Pinecone, and more with the database you're probably already running. Every recipe in it — including this one — was run against a live Postgres instance before it went in the book.

👉 Get the field manual — launch price $14 instead of scanning a billion-row table for yesterday's data: https://payhip.com/omenabyte
🐳 Or take the $24 bundle with the full docker-compose up starter repo — all 8 modules as tested, runnable migrations + seed data: https://4693433176360.gumroad.com/

Read this on omenabyte.com → https://omenabyte.com/blog/replace-influxdb-with-partitioning-brin

Top comments (0)