Hacker News Reader

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...

View on HN

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

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.

You can check if your data remain correlated after your updates. It's possible they do: SELECT correlation FROM pg_stats WHERE tablename = 'mytable' AND attname = 'mytimestamp';

Oh nice! I had no idea, I'll try it, thanks :)

Reply on news.ycombinator.com