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

On Sun, Feb 24, 2019 at 10:02 AM Gunther <raj(at)gusw(dot)net> wrote:
> David Rowley and Justin Pryzby suggested things about autovacuum. But I don't think autovacuum has any helpful role here.

My suspicion is that this has something to do with the behavior of
B-Tree indexes with lots of duplicates. See also:

https://www.postgresql.org/message-id/flat/CAH2-Wznf1uVBguutwrvR%2B6NcXTKYhagvNOY3-dg9dzcYiu_vKw%40mail.gmail.com#993f152a41a1e2c257d12d118aa7ebfc

I am working on a patch to address the problem which is slated for
Postgres 12. I give an illustrative example of one of the problems
that my patch addresses here (it actually addresses a number of
distinct issues all at once):

https://postgr.es/m/CAH2-Wzmf0fvVhU+SSZpGW4Qe9t--j_DmXdX3it5JcdB8FF2EsA@mail.gmail.com

Do you think that that could be a significant factor here? I found
your response to my initial questions unclear.

Using a Postgres table as a queue is known to create particular
problems with bloat, especially index bloat. Fixing the underlying
behavior in the nbtree code would likely sharply limit the growth in
index bloat over time, though it still may not make queue-like tables
completely painless to operate. The problem is described in high level
terms from a user's perspective here:

https://brandur.org/postgres-queues

--
Peter Geoghegan