Hi!
My organization recently migrated from Vertica to SingleStore, and one feature that was extremely useful to us when we used the former was “SWAP_PARTITIONS_BETWEEN_TABLES.” This enabled us to safely and very quickly “hot swap” two tables without any disruption to clients. The use-case here is that we periodically need to replace all of the data in certain tables, but we must be able to do it atomically/without the table momentarily not existing or being empty.
The flow right now is:
- Load the new data into a staging table
- Start a transaction, delete data from the destination table (one bottleneck - we can’t use truncate because that can’t be rolled back)
- Insert data from the staging table to the destination
- Commit
This technically works, but it’s much slower and more resource intensive than when we could simply use “SWAP_PARTITIONS” in Vertica.
The best alternative I’ve found is this:
This could technically work, but would add a whole new layer of complexity to the workflow.
The ideal would be:
- Load data into staging table
- SWAP_PARTITIONS_BETWEEN_TABLES between staging and destination
- Truncate staging table
Is this a feature that you might consider? Or something similar that could enable atomically swapping tables?
Thanks!