Moving from Integer to Bigint or UUID for Primary Key

[BenMorganIO]

Hey guys, with Rails using bigint as the default now, perhaps it would be good to talk about moving Solidus from integer PK to bigint or UUID PK. I personally would prefer UUID, but Rails has standardized to bigint.

Thoughts on this?

[skukx]

I think this is a good idea. Using BigInt would probably be the best since it is what rails defaults to. We recently hit an issues where one of our databases hit the int limit and it crashed a whole lot of services.

[kennyadsl]

I'm ok moving to bigint to reflect Rails standard (who wants UUID can probably run an extra migration in their project?). What about adding a new migration that converts all primary keys?

[ericsaupe]

I think keeping with Rails standard is probably the best. I'm usually for keeping things "standard" in the community following Rails' lead unless there is a strong reason giving great benefits using UUID. I'd love to hear them.

[BenMorganIO]

If we add a migration that converts all of the primary keys to bigint, we could also probably add an option to make it uuid since it will all be abstracted out enough. If not, bigint is an improvement to be at least Rails standard.

[BenMorganIO]

@ericsaupe there's really three main reasons why people use UUIDs over bigints:

1. Faster writes.
2. Security.
3. Checking IDs in tests is easier.

If Rails does the UUID generation, you don't need to do a round trip and ask for what the ID is on return. This saves a round trip after insertion. So in SQL you just do INSERT x instead of INSERT x RETURNING id.

With the security benefit, you can hide data that reveals information about your system. This is an amazing bonus.

The main drawback with UUIDs is the storage requirement, which is only about ~150MB for 10,000,000 rows. I could be wrong though. Since that's not much storage space for the benefits of performance and security, plus storage is always getting cheaper and the speed of light ain't getting faster through a vacuum let alone mediums, I, therefore, use UUIDs.

[cedum]

UUIDs as a primary are required mostly in distributed architectures/datastores, because generating correctly serial numbers in such architectures is much more challenging. IMHO this is the main reason when choosing UUIDs becomes sound from a practical perspective.
Solidus, being basically a monolith, has no such limitations.

When evaluating "faster writes", keep in mind that when having primary UUIDs in almost all cases it'll require to have also a "sort index" on a column like created_at. This means every INSERT will require (at least) two writes on disk.

I'd stick with autoincrementing integers as a primary. There's quite a big cost of migrating to UUIDs and honestly I don't see strong benefits to justify the conversion of all the primary indexes on a production database to a completely different datatype.

[kennyadsl]

@cedum thanks for your insights. 👍

I think we can close this one in a couple of weeks if no other opinions in favor of choosing UUIDs are shared.

[BenMorganIO]

Wasn't saying we should use one or the other, just that if we abstract it out for bigint, we could give the option for UUID.

If we add a migration that converts all of the primary keys to bigint, we could also probably add an option to make it uuid since it will all be abstracted out enough. If not, bigint is an improvement to be at least Rails standard.

I've never heard of this "sort index" and have never run into a problem relevant to sorting with UUIDs. Can you elaborate why you'd need an index on created_at with UUIDs?

[skukx]

@BenMorganIO I'm shooting from the hip here so anyone correct me if I'm wrong. I believe the reasoning is the you can usually determine the order rows were inserted into the table by the traditional BigInt ids because typically they increment (Not always the case). If you have a very large table, indexing the created_at column will enable faster sorts. I think by default Rails sorts by id which is a primary key enabling quick sorts.

You can't make the same assumptions on UUID since it is a string and ordering by it would cause unexpected results as far as which order the rows were created.

The index for created_at would probably be something that would go hand in hand with the extension which uses uuid since it would not be needed on an incremental BigInt column.

[BenMorganIO]

Sounds like the default sort column for rails would change. But, I don't see many apps wanting to even sort by ID. They tend to already sort by created_at in practice.

[skukx]

@BenMorganIO This is the sql query when grabbing last N orders

Spree::Order.last(5)

SELECT  "spree_orders".* FROM "spree_orders" ORDER BY "spree_orders"."id" DESC LIMIT $1

Completed orders
Spree::Order.complete.last(5)

SELECT  "spree_orders".* FROM "spree_orders" WHERE ("spree_orders"."completed_at" IS NOT NULL) ORDER BY "spree_orders"."id" DESC LIMIT $1

So it is using the id to sort. But you're right, there are places where sorting is called on the created_at field. I just can't think of them right now

[BenMorganIO]

Sorry, I think the verbage was wrong. I was actually thinking the default column to ORDER BY should be configurable in Rails. It is a little frustrating in Ruby on Rails where the ActiveRecord::Base.where causes problems by ordering by id. I have a feeling this may have been added to reduce randomness when listing data.

But whenever data is listed in an app, it's usually ordered by the name or the created_at. This is an interesting drawback and one where developers should be more aware of when using UUIDs.

Anywho, the discussion of UUID is just that it is configurable for developers who would prefer UUID over bigint.

[cedum]

@BenMorganIO ouch, sorry, I've missed the part proposing UUIDs as an optional ❤️

I was actually thinking the default column to ORDER BY should be configurable in Rails

There's a merged PR (for rails 6) introducing this: rails/rails#34480
There's also an interesting concern about using created_at as a sort index when having UUIDs since there could be duplicate timestamps resulting in inconsistent results (the RailsConf talk about this).