I’m trying to evaluate CockroachDB for a production database, however I’m struggling to get large datasets (10^7 rows) into candidate table structures. For example, in order to evaluate query performance I need the table to have a primary key on it, seeing as a PK must be specified when creating the table if I want to use the IMPORT option then I can’t evaluate the read performance.
I’ve tried importing into a PKless copy of the table and then INSERT…SELECT into the final table, but I get the error:
transaction is too large to complete; try splitting into pieces
However, I’ve also tried chunking the query down, but it in order for it to be happy with the size it would literally take days to port the data over, which is unacceptable.
An example structure is:
CREATE TABLE IF NOT EXISTS ckm_dimjsonb (
id INT8 NOT NULL DEFAULT nextval(‘cell_key’),
composite_key JSONB NOT NULL,
revision INT8 NOT NULL,
value DECIMAL(18,8) NULL,
CONSTRAINT ckm_pk PRIMARY KEY (id ASC, revision DESC));
For each incrementing value in the “revision” column there are ~46K rows, and there are 215 revisions in the test dataset.
Do you have any suggestions on how I can achieve this?