News for hackers. Stuff that matters.
Re:How to Get the Most Out of Postgres Memory Settings by sgarland on Wednesday June 12, 2024 @10:45AM (#40658777)
If you have pgstattuple [0], you can check the bloat of indices. Otherwise, you can just make it a cron on a monthly / quarterly / semi-annually / whatever basis. Since PG12 you can do `REINDEX INDEX CONCURRENTLY` with zero downtime, so it really doesn't hurt to do it more often than necessary. Even before PG12, you can do an atomic version of it:

`CREATE INDEX new_<index_name> CONCURRENTLY;`

`RENAME INDEX <index_name> TO old_<index_name>;`

`RENAME INDEX new_<index_name> TO <index_name>;`

`DROP INDEX CONCURRENTLY old_<index_name>;`

[0]: https://www.postgresql.org/docs/current/pgstattuple.html

Re:How to Get the Most Out of Postgres Memory Settings by blackenedgem on Wednesday June 12, 2024 @04:53PM (#40662983)
I find average leaf density to be the best metric of them all. Most btree indexes with default settings (fill factor 90%) will converge to 67.5% leaf density over time. So anything below that is bloated and a candidate for reindexing.