Member-only story
Inside PostgreSQL BRIN Indexes: A Tiny Index for Huge Tables
Part 6 of the series “PostgreSQL Indexes Explained”. A few kilobytes of index can stand in for gigabytes, if your data cooperates.
Not a Medium member? Read this story for free here.
💥 Master AI & Tech Skills
💰 One Plan. Multiple Courses. More Savings.
🔥 Get Up to 50% OFF | ⏰ Limited-Time Offer
👉 Enroll Now & Start Learning
Every index so far has had one thing in common: an entry (or several) per row. That is fine for millions of rows. For a log table with a billion rows, a B-tree on the timestamp can be tens of gigabytes. It has to be built, cached, kept in sync on every insert and backed up.
But think about how that table is filled. Rows arrive over time, so rows with similar timestamps sit next to each other on disk. The pages near the start of the table hold January, the pages in the middle hold June, and the last pages hold today. The data is already sorted, by accident.
BRIN (Block Range INdex) takes advantage of exactly that. Instead of remembering every row, it remembers, for each group of pages, the smallest and largest value in the group. That is the entire…
