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

Was wondering when that would come up, taking queuing logic outside the database. Can be overly painful architecting queuing logic in relational databases. imho. Regards, Michael Vitale Jeff Janes Monday, February 25, 2019 3:30 PM On Sat, Feb 23, 2019 at 4:06

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

On Mon, Feb 25, 2019 at 11:13 AM Gunther Schadow wrote: > Anyway, I think the partitioned table is the right and brilliant solution, > because an index really isn't required. The actual pending partition will > always remain quite small, and being a queue, it doesn't even matter how > big it

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

On Sat, Feb 23, 2019 at 4:06 PM Gunther wrote: > Hi, > > I am using an SQL queue for distributing work to massively parallel > workers. > You should look into specialized queueing software. ... > I figured I might just pause all workers briefly to schedule the REINDEX > Queue command, but the

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

> > > Anyway, I think the partitioned table is the right and brilliant solution, > because an index really isn't required. The actual pending partition will > always remain quite small, and being a queue, it doesn't even matter how > big it might grow, as long as new rows are inserted at the end

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

Wow, yes, partition instead of index, that is interesting. Thanks Corey and Justin. The index isn't required at all if all my pending jobs are in a partition of only pending jobs. In that case the plan can just be a sequential scan. And Jeff James, sorry, I failed to show the LIMIT 1 clause

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

On Sun, Feb 24, 2019 at 10:06:10PM -0500, Gunther wrote: > The index isn't required at all if all my pending jobs are in a partition of > only pending jobs. In that case the plan can just be a sequential scan. .. > because an index really isn't required. The actual pending partition will > always

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

Wow, yes, partition instead of index, that is interesting. Thanks Corey and Justin. The index isn't required at all if all my pending jobs are in a partition of only pending jobs. In that case the plan can just be a sequential scan. And Jeff James, sorry, I failed to show the LIMIT 1 clause

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

On Sun, Feb 24, 2019 at 1:02 PM Gunther wrote: > 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.

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

On Sat, Feb 23, 2019 at 4:06 PM Gunther wrote: > the dequeue operation is essentially this: > > BEGIN > > SELECT jobId, action > FROM Queue > WHERE pending > FOR UPDATE SKIP LOCKED > > There is no LIMIT shown. Wouldn't the first thread to start up just lock all the rows and everyone else

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

On Sun, Feb 24, 2019 at 5:43 PM Justin Pryzby wrote: > On Sun, Feb 24, 2019 at 04:34:34PM -0500, Corey Huinker wrote: > > I think your solution may be something like this: > > 1. Create a new table, same columns, partitioned on the pending column. > > 2. Rename your existing queue table

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

On Sun, Feb 24, 2019 at 04:34:34PM -0500, Corey Huinker wrote: > I think your solution may be something like this: > 1. Create a new table, same columns, partitioned on the pending column. > 2. Rename your existing queue table old_queue to the partitioned table as a > default partition. > 3.

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

> > 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 > REINDEX doesn't work

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

On Sun, Feb 24, 2019 at 12:45:34PM -0500, Gunther wrote: > What I am most puzzled by is that no matter how long I wait, the DROP INDEX > CONCURRENTLY never completes. Why is that? https://www.postgresql.org/docs/11/sql-dropindex.html CONCURRENTLY [...] With this option, the command instead waits

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

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

On Sun, Feb 24, 2019 at 2:04 AM Justin Pryzby 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. > >

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

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

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

On Sun, 24 Feb 2019 at 10:06, Gunther wrote: > I am using an SQL queue for distributing work to massively parallel workers. > Workers come in servers with 12 parallel threads. One of those worker sets > handles 7 transactions per second. If I add a second one, for 24 parallel > workers, it

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

On 2/23/2019 16:13, Peter Geoghegan wrote: On Sat, Feb 23, 2019 at 1:06 PM Gunther wrote: I thought to keep my index tight, I would define it like this: CREATE UNIQUE INDEX Queue_idx_pending ON Queue(jobId) WHERE pending; so that only pending jobs are in that index. When a job is done,

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

On Sat, Feb 23, 2019 at 1:06 PM Gunther wrote: > I thought to keep my index tight, I would define it like this: > > CREATE UNIQUE INDEX Queue_idx_pending ON Queue(jobId) WHERE pending; > > so that only pending jobs are in that index. > > When a job is done, follow up work is often inserted into