Postgres Pro
Downloads
Home   >   mailing lists

Re: Abnormal Growth of Index Size - Index Size 3x large than table size. - Mailing list pgsql-general

From Michael Lewis
Subject Re: Abnormal Growth of Index Size - Index Size 3x large than table size.
Date
Msg-id CAHOFxGqFVqvj7mUKJMSKW5cwPbbfe6HRwGSZd8-LY5wYXkyb_g@mail.gmail.com
Whole thread Raw
In response to Re: Abnormal Growth of Index Size - Index Size 3x large than tablesize.  (Christian Ramseyer <rc@networkz.ch>)
List pgsql-general
Tree view
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...

pgsql-general by date:

Previous
From: Christian Ramseyer
Date:
Subject: Re: Encoding conversion: Use replacement character instead of failingquery with "ERROR: character with byte sequence 0xd0 0xad in encoding "UTF8"has no equivalent in encoding LATIN1" ?
Next
From: "Wolff, Ken L"
Date:
Subject: RE: EXTERNAL: Re: Lock Postgres account after X number of failedlogins?