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.
