4 ms·

I actually have been using a combination of a numeric integer ID as the PK and a UUID as a lookup field to do routing lookups etc. for this purpose in Postgres

I actually have been using a combination of a numeric integer ID as the PK and a UUID as a lookup field to do routing lookups etc. for this purpose in Postgres backed app I'm working on. I found this approach to be more trouble than it's worth and plan on switching to a UUID PK key and doing away with the integer sequence. Here are the complications I ran into: The libraries I'm using for the ORM and API are designed to work with a primary key for single record get access. For example they want you to do `resource.get(ID)` where ID is the primary key, however, I now have to do `resource.find({ where: { uuid: 'myuuid' }})`. This is for all resources on all pages. In Postgres, the integer PK sequence has a state that keeps track of what number it's at. In certain circumstances it can get out of sequence and this can trip up migrations. We already have created_at and updated_at fields and these are probably better for ordering than the sequencing. Had I to do it again, I would just use UUIDv4 until it runs into issues and either date fields or a sequence where necessary. If anyone has better ideas I would be most grateful as this is something I go back and forth on.

bavell 3y ago
As someone who has been on the fence about this for awhile now, thank you for your valuable anecdote!
damethos 3y ago
> Had I to do it again, I would just use UUIDv4 until it runs into issues and either date fields or a sequence where necessary How soon is soon? And how would you handle it if you already had all PKs as UUIDs
- babbledabbler 3y ago
  Probably when there is some downtime or as the opportunity presents. I would use a date field to do ordering or a sequenced field when needed.
3pt14159 3y ago
Well, I'll give another take. If you do not have two separate forms of identifier AND you have a "public" API (including basically any client apps, frontend JS, or anything found in query params) then you are making compliance with European regulators a massive headache when it comes to erasure of PII, since shared identifiers must be destroyed one way or another. Trying to merely delete the records is complicated by the fact that you need legal holds to comply with various federal laws. Between the work involved in using two identifiers, one for joins and one for external lookups, versus the work involved in manually coding up all sorts of erasure work arounds, something I was in charge of in the past, I would strongly consider just using two IDs. If your ORM gets in the way, just modify the ORM. This is easier than you'd think. For example, in Django just make a helpers module with something like this: class OurModel(Model): def get(...): And have some sort of programatic way (lint, etc) of ensuring that your models.py doesn't use the stock class. It's simpler. It ruffles some feathers at first, but if your framework is getting in the way of a real use case just change the framework and don't worry about it.
- babbledabbler 3y ago
  I think you raise some good questions around IDs and PII and we definitely will be tackling GDPR sooner or later. I don't quite follow on the European regulation issues raised by using as a UUID in a route and that being the PK of the record. I know you should not expose PII or any information that can be used to identify a person, however, in our case any route is behind an authed login on an SSL connection which encrypts the path (we don't use query params). The only place that contains data that ties a UUID to a person is in the database. This would be the case whether we used a PK as an integer or not. Could you elaborate or share any resources around dual IDs DB design for PII compliance? That would be super helpful. Regarding framework hacking or workarounds, I have a principle to not go against the grain of a framework. The reason for this is that modifying/hacking adds complexity when building on top of it or onboarding other software engineers. If necessary I'll do it as a last resort.
  - 3pt14159 3y ago
    > any route is behind an authed login on an SSL connection which encrypts the path If your application only services a single user with their own resources then you have nothing to fear. But few applications meet this definition. If, for example, you're running an invoicing application, then at some point you'll want to share some resource, say an invoice or an expense or a time sheet, with another party. If your API exposes the identifiers from one resource to another, or even a user's id when potentially adding them to a team, then these identifiers are considered PII according to European regulators. I understand that this is frustrating, but it comes from a posture that prioritizes right-to-be-forgotten over programmer ergonomics. Imagine, for example, API crawlers that hit your /search endpoint with email=[some predetermined list of emails] and harvest user ids to match with future data. In the end, the best thing you can do is keep join keys internal and API keys separated. There are other workarounds, but they're so much trouble that they aren't really viable alternatives. Now, whether you use UUIDs for both identifiers or UUID for external and integer ids for join keys is up to you and your performance and scaling requirements. Personally, I prefer integer keys for internal unless I really expect the database to grow to more than 200m rows before the company hits 1000 people, since int ids mean you do not need secondary indexes on things like the created_at fields, but even there, it's not such a big deal to have an extra index on every table. > I have a principle to not go against the grain of a framework. > hacking adds complexity Here we essentially agree, but with the right integration tests, upgrading and onboarding is a lot easier than feared. That said, do not add to the framework unless the benefit is worth it.
moonchrome 3y ago
That sounds like a very simplistic framework and I'm sure you could do some metaprogramming to abstract boilerplate. Like you couldn't do database multitenancy with those constraints. I've used the int keys and UUID public keys on multiple projects - it wasn't an issue for EF core or RoR
- babbledabbler 3y ago
  I'm simplifying a bit for brevity and we can do some abstraction to handle it so it's not that the framework is simplistic. I'm just having trouble justifying adding this complexity with two types of IDs.
gregwebs 3y ago
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 3y ago
  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 3y ago
    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 3y ago
      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.

bavell 3y ago

As someone who has been on the fence about this for awhile now, thank you for your valuable anecdote!

damethos 3y ago

> Had I to do it again, I would just use UUIDv4 until it runs into issues and either date fields or a sequence where necessary How soon is soon? And how would you handle it if you already had all PKs as UUIDs

babbledabbler 3y ago
Probably when there is some downtime or as the opportunity presents. I would use a date field to do ordering or a sequenced field when needed.

3pt14159 3y ago

Well, I'll give another take. If you do not have two separate forms of identifier AND you have a "public" API (including basically any client apps, frontend JS, or anything found in query params) then you are making compliance with European regulators a massive headache when it comes to erasure of PII, since shared identifiers must be destroyed one way or another. Trying to merely delete the records is complicated by the fact that you need legal holds to comply with various federal laws. Between the work involved in using two identifiers, one for joins and one for external lookups, versus the work involved in manually coding up all sorts of erasure work arounds, something I was in charge of in the past, I would strongly consider just using two IDs. If your ORM gets in the way, just modify the ORM. This is easier than you'd think. For example, in Django just make a helpers module with something like this: class OurModel(Model): def get(...): And have some sort of programatic way (lint, etc) of ensuring that your models.py doesn't use the stock class. It's simpler. It ruffles some feathers at first, but if your framework is getting in the way of a real use case just change the framework and don't worry about it.

babbledabbler 3y ago
I think you raise some good questions around IDs and PII and we definitely will be tackling GDPR sooner or later. I don't quite follow on the European regulation issues raised by using as a UUID in a route and that being the PK of the record. I know you should not expose PII or any information that can be used to identify a person, however, in our case any route is behind an authed login on an SSL connection which encrypts the path (we don't use query params). The only place that contains data that ties a UUID to a person is in the database. This would be the case whether we used a PK as an integer or not. Could you elaborate or share any resources around dual IDs DB design for PII compliance? That would be super helpful. Regarding framework hacking or workarounds, I have a principle to not go against the grain of a framework. The reason for this is that modifying/hacking adds complexity when building on top of it or onboarding other software engineers. If necessary I'll do it as a last resort.
- 3pt14159 3y ago
  > any route is behind an authed login on an SSL connection which encrypts the path If your application only services a single user with their own resources then you have nothing to fear. But few applications meet this definition. If, for example, you're running an invoicing application, then at some point you'll want to share some resource, say an invoice or an expense or a time sheet, with another party. If your API exposes the identifiers from one resource to another, or even a user's id when potentially adding them to a team, then these identifiers are considered PII according to European regulators. I understand that this is frustrating, but it comes from a posture that prioritizes right-to-be-forgotten over programmer ergonomics. Imagine, for example, API crawlers that hit your /search endpoint with email=[some predetermined list of emails] and harvest user ids to match with future data. In the end, the best thing you can do is keep join keys internal and API keys separated. There are other workarounds, but they're so much trouble that they aren't really viable alternatives. Now, whether you use UUIDs for both identifiers or UUID for external and integer ids for join keys is up to you and your performance and scaling requirements. Personally, I prefer integer keys for internal unless I really expect the database to grow to more than 200m rows before the company hits 1000 people, since int ids mean you do not need secondary indexes on things like the created_at fields, but even there, it's not such a big deal to have an extra index on every table. > I have a principle to not go against the grain of a framework. > hacking adds complexity Here we essentially agree, but with the right integration tests, upgrading and onboarding is a lot easier than feared. That said, do not add to the framework unless the benefit is worth it.

moonchrome 3y ago

That sounds like a very simplistic framework and I'm sure you could do some metaprogramming to abstract boilerplate. Like you couldn't do database multitenancy with those constraints. I've used the int keys and UUID public keys on multiple projects - it wasn't an issue for EF core or RoR

babbledabbler 3y ago
I'm simplifying a bit for brevity and we can do some abstraction to handle it so it's not that the framework is simplistic. I'm just having trouble justifying adding this complexity with two types of IDs.

gregwebs 3y ago

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 3y ago
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 3y ago
  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 3y ago
    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.