| From: | Tom Dearman <tom(dot)dearman(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Frequetly updated partial index leads to bloat on index for Postresql 11 |
| Date: | 2021-03-18 13:51:27 |
| Message-ID: | CAGRwtPy0stc_XSuWdxis7GtvVUV2=xWjsd-g4hjzOSaTi5L72A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hi,
We have a partial index on a column of the form:
CREATE TABLE table_p2021q1 (
pk_id BIGINT,
col1 BIGINT NOT NULL,
status character varying(255) NOT NULL,
...other columns
PRIMARY KEY (pk_id)
);
CREATE INDEX table_p2021q1_ix04 ON table_p2021q1 (col1) WHERE status =
'IN_PROGRESS';
(the table is also a partitioned table on a date field)
Every one of our entries in the table will go through a state where
status = 'IN_PROGRESS' but there are only a few hundred of them at any
one time. The index grows quite quickly to about 300MB. I have run
some bloat queries and the size should always be 8KB and bloat is
always high (99.99....% bloat). We have found that the only way to
reduce the bloat is through reindexing, but after a couple of days the
size goes back to 300MB or so. Running a vacuum does not reduce the
bloat.
Is this a known issue, are they any ways around it, and if it is an
issue is there a plan to fix it if a fix is possible?
Thanks.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | basti | 2021-03-18 14:39:52 | postgresql order lowercase before uppercase |
| Previous Message | DAVID Nicolas | 2021-03-18 13:32:49 | Open source licenses |