Nine Megabytes Of Index

An append-only events table has reached 900 GB. Rows are inserted in created_at order and never updated. The B-tree on created_at is 41 GB and every insert dirties a leaf page. Every query filters a six hour window. A colleague proposes replacing it with:

CREATE INDEX events_brin ON events USING BRIN (created_at)
  WITH (pages_per_range = 128);

which builds in minutes and occupies 9 MB. What is the honest characterization of that trade?

BRIN keeps the same sorted structure as a B-tree but compresses its leaf pages, so it degrades once the table no longer fits in shared buffers.

BRIN keeps a min and max per 1 MiB block range, so the window narrows to the overlapping ranges and their rows are rechecked. It works only because insert order matches created_at order.

BRIN summaries are lossy, so a small fraction of qualifying rows never gets returned at all. You are trading index size directly against result completeness.

BRIN gives the same single-row lookup performance as a B-tree at a fraction of the size, so on this table there is no downside at all.