| From: | Michael Lewis <mlewis(at)entrata(dot)com> |
|---|---|
| To: | Christian Ramseyer <rc(at)networkz(dot)ch> |
| Cc: | PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Abnormal Growth of Index Size - Index Size 3x large than table size. |
| Date: | 2020-05-06 13:36:21 |
| Message-ID: | CAHOFxGqFVqvj7mUKJMSKW5cwPbbfe6HRwGSZd8-LY5wYXkyb_g@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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...
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Wolff, Ken L | 2020-05-06 13:36:52 | RE: EXTERNAL: Re: Lock Postgres account after X number of failed logins? |
| Previous Message | Christian Ramseyer | 2020-05-06 13:29:36 | Re: Encoding conversion: Use replacement character instead of failing query with "ERROR: character with byte sequence 0xd0 0xad in encoding "UTF8" has no equivalent in encoding LATIN1" ? |