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

Thank you all for responding so far.

David Rowley  and Justin Pryzby suggested things about autovacuum. But I
don't think autovacuum has any helpful role here. I am explicitly doing
a vacuum on that table. And it doesn't help at all. Almost not at all.

I want to believe that

VACUUM FREEZE Queue;

will push the database CPU% down again once it is climbing up, and I can
do this may be 3 to 4 times, but ultimately I will always have to
rebuild the index. But also, none of these vaccuum operations I do takes
very long at all. It is just not efficacious at all.

Rebuilding the index by building  a new index and removing the old, then
rename, and vacuum again, is prone to get stuck.

I tried to do it in a transaction. But it says CREATE INDEX can't be
done in a transaction.

Need to CREATE INDEX CONCURRENTLY ... and I can't even do that in a
procedure.

If I do it manually by issuing first CREATE INDEX CONCURRENTLY new and
then DROP INDEX CONCURRENTLY old,  it might work once, but usually it
just gets stuck with two indexes.  Although I noticed that it would
actually put CPU back down and improve transaction throughput.

I also noticed that after I quit from DROP INDEX CONCURRENTLY old, that
index is shown as INVALID

\d Queue
...
Indexes:
"queue_idx_pending" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending2" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending3" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending4" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending5" UNIQUE, btree (jobId, action) WHERE pending INVALID
"queue_idx_pending6" UNIQUE, btree (jobId, action) WHERE pending
...

and so I keep doing that same routine hands-on, every time that the CPU%
creeps above 50% I do

CREATE UNIQUE INDEX CONCURRENTLY Queue_idx_pending6 ON Queue(jobId, action) WHERE currentlyOpen;
DROP INDEX CONCURRENTLY Queue_idx_pending5;

at which place it hangs, I interrupt the DROP command, which leaves the
old index behind as "INVALID".

VACUUM FREEZE ANALYZE Queue;

At this point the db's CPU% dropping below 20% after the new index has
been built.

Unfortunately this is totally hands on approach I have to do this every
5 minutes or so. And possibly the time between these necessities
decreases. It also leads to inefficiency over time, even despite the CPU
seemingly recovering.

So this isn't sustainable like that (worse because my Internet
constantly drops).

What I am most puzzled by is that no matter how long I wait, the DROP
INDEX CONCURRENTLY never completes. Why is that?

Also, the REINDEX command always fails with a deadlock because there is
a row lock and a complete table lock involved.

I consider this ultimately a bug, or at the very least there is room for
improvement. And I am on version 11.1.

regards,
-Gunther