Why should you care?

PostgresVerse event_logs append-only by created_at — BRIN on timestamp skips terabytes of blocks when querying one day.

See it live — copy this example

Run in pgAdmin or psql.

CREATE TABLE event_logs (
  log_id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL,
  payload jsonb
);
CREATE INDEX idx_event_logs_created_brin
ON event_logs USING BRIN (created_at);

SELECT count(*) FROM event_logs
WHERE created_at >= '2026-07-19'::date
  AND created_at < '2026-07-20'::date;

What happened?

• Rows insert in time order so each BRIN range summarizes contiguous timestamps.
• Day filter excludes most heap blocks using a small index.

Practice next

1. Insert ordered timestamps into event_logs (bulk COPY helps).
2. Compare pg_relation_size of BRIN vs B-Tree on created_at.
3. EXPLAIN ANALYZE the day-bound query.
4. Tune pages_per_range with WITH (pages_per_range=32).
5. Query month range and compare blocks read with B-Tree index.

Remember

BRIN is minimal storage for sequential data. Works best when physical order correlates with indexed column. Combine with partitioning for warehouse-scale logs.

PostgresVerse audit trail

Compliance queries one day of 500M audit rows using BRIN on logged_at.

Outcome: Index fits in RAM; nightly reports finish on time.