[gregwebs]

I personally find the numeric id extremely valuable for internal data analysis and sharing. I can refer to rows by the numeric id, including a range of rows, and seeing the ids gives exactly that intuitive information about it's relation in the set that we are hiding from end users. Numeric ids can also be used for the same reason in an admin-only UI.

On the efficiency side, joining and querying by id is generally more efficient on CPU usage for querying, but you do have to pay the cost of having the additional column and index.

[babbledabbler]

This was generally the reason I went with numeric ID as PK originally. It makes working with and analyzing the data as well as cross referencing relations easier.

For all my tables I have a base schema that looks something like this.

id: integer sequence PK uuid: uuidV4 created_at: datetime updated_at: datetime

The concern I have is when I have to distribute my system when scaling. Those numeric IDs will have to be replaced with the UUIDs so I figure I might as well do it now.

[gregwebs]

Everything breaks at scale. In my experience most tables don't end up with more than a few million rows and will work fine with this. If you did want to transition a large table to be UUID only, the nice thing about this approach is that you could do it with no down time. If you are using a DB that only scales writes vertically though (most DBs, including distributed DBs) then how are you actually going to scale the DB layer horizontally? Pretty much just CRDB (PG) or TiDB (MySQL) are the options there- look at their docs for how to setup your ids.

[babbledabbler]

I'm not so much concerned with figuring out scaling in terms of volume as I expect to be able to handle millions of rows in a single DB and that would be an implementation detail and fine tuning. I'm more concerned about scaling in terms of complexity and keeping the system easy to reason about when more people, tech are involved.

Lets say I have a <CAR>-[1:N]-<TRIP> in two tables in a relational DB. This works fine at first even for millions of rows as you say.

At some point in the future it makes sense to have these two entities managed by different team/services/db. Let's say TRIP becomes a whole feature laden thing with fares, hotels, itinerary, dates. So I need to take this local relation and move it to different services and different DB.

If I had been using an integer PK/FK this would be a more complicated migration than if I used UUIDs.

My assumption is that we would not want to have a sequenced integer key used in a distributed system.

In other words it seems safer bet if there's a possibility of needing to move to a distributed system to use a UUID for the key from the beginning.

[gregwebs]

I think switching this with zero downtime to do foreign key references with UUIDs will be easier than any of the pain you would deal with from having to do cross-DB joins.

What specific issues are you worried about with the integer key? Usually the issue is dupming data into something like a staging or development environment rather than a production concern. If you attempt to dump 2 datasets into one db you will have a conflict. Or if you write to an environment and then dump into you will have a conflict.

[babbledabbler]

Mainly portability of data and options for the future. I'm all on one postgres instance right now and don't plan on breaking it up until necessary. If at some point I need to take a table and move it to another type of database I want that migration process to be straightforward. If I have integer keys with sequencing behavior I anticipate having to do that porting. That internal key would then become external to do the lookup and if it's an external key I want it to be UUID for security as well. Integers as IDs are guessable so I want to keep them internal.