Is it possible to partition a table after it's created and data inserted into it?


Is it possible to partition a table after it’s created and data inserted into it?

I read the following:

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?

You could probably use ALTER CLUSTER SWAP TABLE (see ALTER CLUSTER — CrateDB: Reference ) which only takes a few ms/seconds

Changing from non-partitioned to partitioned is unfortunately not possible right now.

1 Like