Is it possible to partition a table after it’s created and data inserted into it?
I read the following:
- Sharding and Partitioning Guide for Time Series Data
- Partitioned tables — CrateDB: Reference
- Altering tables — CrateDB: Reference
- ALTER TABLE — CrateDB: Reference
The last document says “While columns can be added at any time, adding a new generated column is only possible if the table is empty.” So I guess it’s not possible.
I have some tables with time series data, millions of rows, that are not partitioned. This is why I’ve been trying to find out if this is possible.
If it’s not possible to partition an existing table with data already inserted, would the following be feasible in CrateDB?
- Create a similar table, but with an added generated column to be used as the partitioning column.
- INSERT INTO that similar table from the un-partitioned table.
- RENAME the un-partitioned to some other name.
- RENAME the partitioned table to the original un-partitioned name.
- DROP the un-partitioned table.
My concern with the plan above:
- Is renaming tables an atomic operation?
- How disruptive it is? If the tables in question have a few hundred million rows (on the order of 10s of GBs), would the renaming operation take more than a few seconds?