Cockroach oddly auto incrementing on PK id at server but not local (knex.js seeding cockroach public cloud hosted db)

user_13014509 0
Viewed 101

I can't for the life of me figure out why this is happening. Each time I run my table delete / create migration then seeding logic on the server DB it will give me bizarre 18 digit ids instead of incrementing 1, 2, 3. Locally all works fine, my db is a free tier hosted Cockroach database. I am seeding 3 records here are example ids that are generated (725368665646530561,725368665646694401,725368665646727169)

EDIT:

Based on a comment and some extra research I found that Cockroach DB, although is Postgres compatible, is not truly a Postgres DB. I also didn't realize how disliked and non-performant the AUTO_INCREMENT approach is. I ended up using an extension to generate UUIDS as the PK and then I query the newly created data and grab those if I need some FK relationships in another seed.

t.uuid('id').primary().notNullable().defaultTo(knex.raw('uuid_generate_v4()'));
cockroachdb knex.js node.js sql
1 Answers

The answer you linked to is a little old (from 2017). CockroachDB can generate auto-incrementing IDs, but there is a performance cost. (1) Having a primary index on sequential data is worse for performance, and (2) extra coordination between nodes is required to generate incrementing values.

If those performance tradeoffs are fine for you, then you can use the serial_normalization=sql_sequence_cached setting to get what you want. See https://www.cockroachlabs.com/docs/stable/serial.html

Also, v21.2 supports identity columns, which are a different syntax to get something similar: https://www.cockroachlabs.com/docs/stable/create-table.html#identity-columns

user_36423590
Related