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

On Sun, Feb 24, 2019 at 5:43 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> 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 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
>

Good point, I forgot about that. I had also considered making a partitioned
table, adding a "true" partition to that, and then making the partitioned
table an *inheritance* partition of the existing table, then siphoning off
rows from the original table until such time as it has no more pending
rows, then doing a transaction where you de-inherit the partitioned table,
and then attach the original table as the false partition. It's all a lot
of acrobatics to try to minimize downtime and it could be done better by
having a longer maintenance window, but I got the impression from the OP
that big windows were not to be had.