Postgres Indexing: When Does BRIN Win?

https://www.crunchydata.com/blog/postgres-indexing-when-does-brin-win

by krnaveen14 • 4 years ago

27 6 4 years ago

aspyct3• 4 years ago

Oh nice, I have just the tables for that! Billions of well-compacted rows with a big ol' timestamp on them. Btree indices are almost as big as the table itself...

Strum355• 4 years ago

Note that if you delete entries in non sequential order (aka not at the start-only or the end-only) and continue inserting, BRIN index becomes much less effective. Its very much predicated upon the concept of append-only, as postgres filling deleted rows with new rows throws off the physical layout part of how BRIN works

aspyct3• 4 years ago

I don't delete anything in those tables, however I do one update on 9/10 of the rows, at least.

I seem to remember there was a way to reorder rows in a table, maybe that could be useful.

geordee• 4 years ago

This is similar to Zone Maps in Netezza. https://www.ibm.com/docs/en/psfa/7.2.1?topic=statistics-zone...

Incidentally, Netezza was built on Postgres engine, years back.

Postgres Indexing: When Does BRIN Win?

Related Stories