If you’ve been using PostgreSQL long enough, you know that B+tree indices are the way to go in most cases.

But that’s not the case for some immutable tables.

What index should you use for immutable tables (no updates/deletes) where the column values are stored in ascending order physically and logically?

BRIN Indices.

A good candidate for this type of index would be a created_at column, each new row has an increasing value similar to its physical layout on disc.

For most data types, they work by storing the minimum and maximum of each block range on disc and use several hundred times less space than a B+tree to maintain.

The size of the block range (how many pages get summarized by min/max values) can even be configured at index creation time.

This gives a configurable tradeoff between space and time: as the page range size increases, search time increases while space usage drops.

If your table is not sorted to begin with, you can use the RECLUSTER command with a B+tree index to reorganize the table into sorted order and drop that B+tree index afterwards.