Re: Massive parallel queue table causes index deterioration, butREINDEX 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. Rename new table to queue
> 4. add old_queue as the default partition of queue
> 5. add a new partition for pending = true rows, set the fillfactor kind of

FYI, the "default partition" isn't just for various and sundry uncategorized
tuples (like a relkind='r' inheritence without any constraint).  It's for
"tuples which are excluded by every other partition".  And "row migration"
doesn't happen during "ALTER..ATTACH", only UPDATE.  So you'll be unable to
attach a partition for pending=true if the default partition includes any such
rows:

|ERROR:  updated partition constraint for default partition "t0" would be violated by some row

I think you'll need to schedule a maintenance window, create a new partitioned
heirarchy, and INSERT INTO queue SELECT * FROM old_queue, or similar.

Justin