Best Practice for Primary Keys

Hi there, I’m creating a game that’s going to have more than 300 tables, all using CockroachDB. There are really two types of table :

  1. Static game data that only updates with a new patch
  2. Live game data that’s constantly getting updated

I’ve started off using id INT AUTO_INCREMENT primary keys just because it’s what I’ve been used to in the past. However, I’ve read that this needs all the nodes to co-ordinate which leads to performance problems… especially if you’ve got huge numbers of inserts, which I expect with the live game data tables.

So I considered using ints for the static data because they don’t change often, and then UUID default gen_random_uuid() for the live tables. The issue there is because I’ve got 150 or so of each type of table the keys are going to be different types and it would become messy. I’d love to have a function in my ORM that did something like -

  // These fields are used by every model
  Field("id", &FieldParams{
  	datatype:      UUID,
  	primaryKey:    true,
  	default:    "gen_random_uuid()",

So it would just add the same type of column for all 300 tables. Do you think this is a good idea? There’s a lot of foreign key relationships in the game and I don’t want to shoot myself in the foot by using UUIDs everywhere if it’s going to use a lot of space and become a perfomance issue.

The game’s not going to be launched for a few years so I’ve got time to figure this out.

Hi @borovan,

You are right that using sequences could cause performance issues, but in CockroachDB, SERIAL (the equivalent type to AUTO_INCREMENT from MySQL) uses unique_rowid(), which is not slow and half the size of a UUID (64 bits v. 128). The size difference likely won’t have a big impact on the performance of your system, though. What’s probably more important is the difference in ordering, as discussed here: SERIAL and javascript.

If you decide to stick with unique_rowid() for all tables, you can declare your keys as: id int primary key default unique_rowid().

Hope this helps! Don’t hesitate to ask if you have other questions.
– Becca

Thank you, makes a lot of sense. I think we’re going with UUIDs for everything and not worry about any potential performance hit at this point. Yeah there may be some tables that would benefit from SERIAL/unique_row_id() but it’s much cleaner code-wise to just have everything using the same system.

You’re welcome! Let us know how it goes.