Search Postgresql Archives

Frequetly updated partial index leads to bloat on index for Postresql 11

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

 



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.





[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 Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux