Swap Partitions

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:

  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?

Thanks!

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,
Eric

1 Like

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

1 Like