Using a view + trigger table swap

[lukeasrodgers]

First of all, thanks for this gem, it is very nearly exactly what I was looking for.

I'm curious if you have any interest in supporting an approach like the one outlined here: https://www.2ndquadrant.com/en/blog/partitioning-a-large-table-without-a-long-running-lock/

Basically, instead of the current approach (IIUC) of:

1. create partitioned table
2. fill
3. swap
4. fill again for rows that were inserted into original table between completing step 2 and starting step 3
5. cleanup

it would:

1. create partitioned table
2. create a view that unions the old, unpartitioned table with the new, partitioned one. rename the old table and use its name for the view, so that clients can continue to read (and write) unawares of a change. the view has a trigger such that modifications get written to the new partitioned table, and removed from the old one.
3. begin backfill
4. cleanup (which includes deleting the view and renaming the newly partitioned table to the old name)

This seems to me to have two main advantages:

1. data is not duplicated during the fill portion, since it is deleted from the old table. i think this is particularly useful given that if you need to partition an existing table, it's probably pretty big, and you'd probably prefer to not double your disk usage
2. there is no period of time where data is missing from the new, partitioned table as alluded to in step 4 above (I may be misunderstanding the steps here, perhaps this tool avoids that somehow). again, i think this is particularly useful given that large tables where partitioning is desirable are more likely to have high write volume, and if client apps cannot find rows that should be there, even for a brief period of time, that is more likely to be problematic.

[lukeasrodgers]

JFYI there is a proof of concept here https://github.com/alice-financial/pgslice/pull/1/commits/94bf29c27cbc68d400df7e09451a9f5f9d76ec88.

• Needs more (manual) testing and refactoring.
• Also the view trigger has to make some assumptions about how to handle inserts that may not generalize well to other table structures (auto-incrementing primary key - this could maybe be handled at runtime, but would obviously make things more complex)
• Only tested on pg 11, it looks like pg 10 supports INSTEAD OF triggers on views, so may be fine there as well.

Because of the second two issues mentioned above, I probably will not bother putting together a PR against this repo, but if you are interested in the functionality I'm happy to rework it a bit (or happy to just have someone else do that).

[ankane]

Hi @lukeasrodgers, thanks for sharing! It's a neat pattern. I don't think it'll save space (since Postgres doesn't reclaim space on deletes), but does have the advantage of working with updates. I don't think I want to support another pattern right now, but may revisit in the future.

[lukeasrodgers]

Thanks @ankane - you seem to be right about this not actually saving space. I had erroneously thought that even though postgres wouldn't "return" the disk space back to the OS, it would internally reuse it, but that's not the case. This doesn't even seem to happen if I issue manual VACUUM commands during the fill process.