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