1

I have a requirement where I have to move records from table original_table to table new_table for the last N days.

Below is a small sample of my basic script that does this.

while True:

    result = INSERT INTO new_table (SELECT * FROM original_table WHERE (crdb_internal_expiration > (current_timestamp() + INTERVAL 'X days')) ORDER BY (unique_id) DESC LIMIT batch_limit OFFSET starting_point);

    if result.rowcount < batch_size:
        break
    offset += batch_size

The problem is we have to move the records between the tables in a timely manner and at the moment moving records with a LIMIT of 10_000 takes around 30 seconds each time. We have around 2 million records for the last 2 days let's say and we want to limit downtime.

The reason we are doing this is because we need to use column families in our table but cockroachdb does not allow us to alter an existing table to add new column families if it doesn't have any already.

Is there any way to optimise this query so it runs quicker please? Perhaps not using an OFFSET and tracking the record id would be quicker?

AND id > last_processed_id
ORDER BY id DESC
LIMIT 100;

cockroachdb version is at 23

1 Answer 1

1

In CockroachDB, doing an INSERT INTO <table> SELECT * FROM <other_table>; operation is generally a bad idea. I would suggest using CREATE TABLE AS syntax (https://www.cockroachlabs.com/docs/stable/create-table-as) which is much more efficient because it can bypass all the machinery around consistency since it's always creating a new table.

5
  • Ok but this doesnt solve the problem with the column families. Which the 'old' table doesn't have. From what I can see in the docs there are column family limitations with CREATE TABLE AS ?
    – Brunaldo
    Commented Feb 14 at 22:58
  • Apologies, I think I have got it working, I will get back to you when I have run it in test env. Thank you!
    – Brunaldo
    Commented Feb 14 at 23:25
  • @Brunaldo, if you have then please either tick this answer or post your own answer. Commented Feb 15 at 3:04
  • @Brunaldo you can add column families to an existing table by adding a new computed column taking its value from another column, then converting to a regular stored column and renaming. I can share a script if you like. Commented Feb 15 at 12:02
  • You can specify the column families of the newly created table too using the CREATE TABLE AS syntax: cockroachlabs.com/docs/stable/… Commented Feb 15 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.