Swap Partitions


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:

  1. Load the new data into a staging table
  2. Start a transaction, delete data from the destination table (one bottleneck - we can’t use truncate because that can’t be rolled back)
  3. Insert data from the staging table to the destination
  4. 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:

  1. Load data into staging table
  2. SWAP_PARTITIONS_BETWEEN_TABLES between staging and destination
  3. Truncate staging table

Is this a feature that you might consider? Or something similar that could enable atomically swapping tables?


Dear Erica,

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.

Will “swap tables” meet your needs?

Best regards,

1 Like

Yeah, sounds like that would suit our use-case!

1 Like

Hi! Is this on the roadmap? Just curious to know if it’s in-scope in the near-ish future. Thanks!

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.

1 Like

Just sent you an email. Thanks!