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

Поиск
Список
Период
Сортировка
От Tom Dearman
Тема Frequetly updated partial index leads to bloat on index for Postresql 11
Дата
Msg-id CAGRwtPy0stc_XSuWdxis7GtvVUV2=xWjsd-g4hjzOSaTi5L72A@mail.gmail.com
обсуждение исходный текст
Ответы Re: Frequetly updated partial index leads to bloat on index for Postresql 11
Список 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.



В списке pgsql-general по дате отправления:

Предыдущее
От: DAVID Nicolas
Дата:
Сообщение: Open source licenses
Следующее
От: basti
Дата:
Сообщение: postgresql order lowercase before uppercase