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?
Thank you for the great description of the scenario. Yes, this is a feature we will consider, but it is going to be “swap tables” not “swap partitions between tables” because we don’t have range partitioning, so swapping partitions does not make sense for us.
Checking in again! This feature would make a huge difference for my org. I know there’s likely a lot of other priorities on your agenda but I hope this can be picked up in the not-too-distant future!
I was just discussing this with our dev team. Would you be open to a call about this? Please shoot me an email at hanson at singlestore dot com if you have time.
We just shipped 8.9 which supports updatable views. You can now have a view that points to your desired table, then prepare a new table with modified data, and ALTER the view to point to the new table. You can both read and update the data (within limits) via the view name, which can stay the same every day.
This approach should satisfy Erica’s original requirement from May '22. Subsequent queries on the view will have to recompile after the ALTER but for most apps that won’t be a problem.