BRIN indexes for huge append-only tables
On an append-only table whose rows arrive in physical order, a BRIN index stores per-block ranges instead of per-row pointers. Here that is 24 kB versus 21 MB for a B-tree, for a range scan that is still fast.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
A B-tree on a huge, naturally-ordered column (a timestamp on an append-only log) is enormous and mostly redundant, because the data is already clustered on disk. BRIN summarizes each block range instead, trading a little scan precision for a tiny index.
Meridian's scan_history is append-only and ordered by scan time. A B-tree on scanned_at is 21 MB; a BRIN index covering the same column is 24 kB and still serves the day-range report.
Simple terms
When rows arrive in order and never move, an append-only log ordered by time, the data is already sorted on disk, so a full B-tree that stores a pointer for every single row is mostly wasted space. A BRIN index instead records just the smallest and largest value in each block of the table. It gives up a little precision for an enormous size saving, and for a range scan over already-ordered data it is still fast.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Indexing runbooks
Neighbouring incidents that share the same diagnostic surface.
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.