Search Postgresql Archives

Re: Abnormal Growth of Index Size - Index Size 3x large than table size.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Indexes larger than the table may be expected if there are many. It may be prudent to check if they are being used in pg_stat_all_indexes.

If there are just a few indexes that are becoming bloated quickly, you'd want to ensure your autovacuum settings are tuned more aggressively, and consider lowering FILLFACTOR on the table to better support heap-only tuples (HOT) updates such that the index isn't touched when other columns are updated in the table. If you are on PG12, you can reindex concurrently assuming you have the space. If not, you can do the same manually like below-

CREATE INDEX CONCURRENTLY idx_new...
DROP INDEX CONCURRENTLY idx_old...
ALTER INDEX idx_new... RENAME TO idx_old...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux