Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.

On Sun, Feb 24, 2019 at 2:04 AM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> Some ideas:
>
> You could ALTER TABLE SET (fillfactor=50) to try to maximize use of HOT
> indices
> during UPDATEs (check pg_stat_user_indexes).
>
> You could also ALTER TABLE SET autovacuum parameters for more aggressive
> vacuuming.
>
> You could recreate indices using the CONCURRENTLY trick
> (CREATE INDEX CONCURRENTLY new; DROP old; ALTER .. RENAME;)
>

I have basically the same issue with a table. Each new row enters the table
with a active=true kind of flag. The row is updated a lot, until a business
condition expires, it is updated to active=false and then the row is almost
never updated after that.

We also used a partial index, to good effect, but also had/have an issue
where the index bloats and performs worse rather quickly, only to recover a
bit after an autovacuum pass completes.

Lowering the fillfactor isn't a good solution because 99%+ of the table is
"cold".

One manual VACUUM FREEZE coupled with lowering the vacuum sensitivity on
that one table helps quite a bit by increasing the frequency shortening the
runtimes of autovacuums, but it's not a total solution.

My next step is to partition the table on the "active" boolean flag, which
eliminates the need for the partial indexes, and allows for different
fillfactor for each partition (50 for true, 100 for false). This should
also aid vacuum speed and make re-indexing the hot partition much faster.
However, we have to upgrade to v11 first to enable row migration, so I
can't yet report on how much of a solution that is.