3 ms·

If you don't care about performance, then obviously it's fine from a performance perspective to have both... but what does it actually give you other than probl

If you don't care about performance, then obviously it's fine from a performance perspective to have both... but what does it actually give you other than problems?

hot_gril 4y ago
Not having to import a DB extension or get a lib to generate the UUIDv4s. Not exposing your primary keys. Keeping an ordering of insertion, if you need that. Not having to read huge strings during debug. Overall it's kinda minor as UUID is at least a much better option than semantic PKs, bigger concern is performance. Performance-wise, bigserial is probably a lot faster than UUID as a PK, even if you also have a UUID secondary index. PKs are used in tons of places in the DB and in your server-side code. The DBMS is also gonna be optimized around the regular way of doing things. What problem do you get from bigserial? The one thing I can think of is, if you're trying to merge two databases together for whatever reason, you can't just copy the entire rows. So you copy all cols except the ID, let them get new IDs, and use the secondary identifiers when copying in related tables. It's more work, but you don't do this often, and if you do, you can automate it.
- cameronh90 4y ago
  What I said was what's the point of having both? If you have both, you still need a UUID generation library. As I said previously, I can see the performance advantages of using ints in some cases, but in my experience they're relatively rare so I don't see the point to default to them. As for the other advantages of UUID, I and others have covered many of them above: security, fewer roundtrips, shardable, easier to find in logs, data warehousing, backups, disaster recovery, etc. etc. The advantages of UUIDs are so great that my view is, in any serious app, you actually need to justify _not_ using them with concrete performance data that shows why using ints is a worthwhile trade-off. There are cases where ints make sense. However my main point is, as I said, that having both ints AND uuids is of very limited usefulness.
  - hot_gril 4y ago
    > If you have both, you still need a UUID generation library Oops, yeah, that's true. The point of having both is that you're already using the bigserial as the PK, and you also have opaque, external-facing identifiers that refer to particular rows in some tables without you having to expose any PKs. Might only be some tables, might be some other kind of string rather than a UUID. There may even be multiple ways for users to refer to something in your system; you keep that all separate from your PKs. > security, fewer roundtrips, shardable, easier to find in logs, data warehousing, backups, disaster recovery I see security pros/cons on both sides; exposing PKs to users does feel wrong to me though. I don't see how UUID PKs reduce roundtrips; if your API takes a UUID, you don't have to convert it to a row ID right away, only when you're actually querying what data the client wants. If you're printing row keys in logs, you ought to prefix them either way (like "user:35" or "user:deadbeef-..."). For backups/recovery, I haven't found UUIDs helpful, maybe cause I never want to just copy whole rows. A sharded DB takes special consideration and could go many different ways, so defaulting to UUIDs in anticipation of sharding one day is probably not going to help when that day comes. In some setups, the PK is just for that one node, and you have a global ID across nodes (which may be a composite of node-local PK + shard ID). Or you're switching to a specialized, not-so-relational DBMS for horizontal scaling. Like, serial IDs are a terrible idea in Google Spanner. > you actually need to justify _not_ using them with concrete performance data For what it's worth, I encountered this situation in a DB with millions of rows. UUID PKs were significantly increasing our overall application latency, so I switched us to bigserials. I'd rather not put newer systems on a track to hit that hurdle later on. It can start being a noticeable problem well before you're thinking about sharding.

If you don't care about performance, then obviously it's fine from a performance perspective to have both... but what does it actually give you other than problems?

hot_gril 4y ago

Not having to import a DB extension or get a lib to generate the UUIDv4s. Not exposing your primary keys. Keeping an ordering of insertion, if you need that. Not having to read huge strings during debug. Overall it's kinda minor as UUID is at least a much better option than semantic PKs, bigger concern is performance. Performance-wise, bigserial is probably a lot faster than UUID as a PK, even if you also have a UUID secondary index. PKs are used in tons of places in the DB and in your server-side code. The DBMS is also gonna be optimized around the regular way of doing things. What problem do you get from bigserial? The one thing I can think of is, if you're trying to merge two databases together for whatever reason, you can't just copy the entire rows. So you copy all cols except the ID, let them get new IDs, and use the secondary identifiers when copying in related tables. It's more work, but you don't do this often, and if you do, you can automate it.

cameronh90 4y ago
What I said was what's the point of having both? If you have both, you still need a UUID generation library. As I said previously, I can see the performance advantages of using ints in some cases, but in my experience they're relatively rare so I don't see the point to default to them. As for the other advantages of UUID, I and others have covered many of them above: security, fewer roundtrips, shardable, easier to find in logs, data warehousing, backups, disaster recovery, etc. etc. The advantages of UUIDs are so great that my view is, in any serious app, you actually need to justify _not_ using them with concrete performance data that shows why using ints is a worthwhile trade-off. There are cases where ints make sense. However my main point is, as I said, that having both ints AND uuids is of very limited usefulness.
- hot_gril 4y ago
  > If you have both, you still need a UUID generation library Oops, yeah, that's true. The point of having both is that you're already using the bigserial as the PK, and you also have opaque, external-facing identifiers that refer to particular rows in some tables without you having to expose any PKs. Might only be some tables, might be some other kind of string rather than a UUID. There may even be multiple ways for users to refer to something in your system; you keep that all separate from your PKs. > security, fewer roundtrips, shardable, easier to find in logs, data warehousing, backups, disaster recovery I see security pros/cons on both sides; exposing PKs to users does feel wrong to me though. I don't see how UUID PKs reduce roundtrips; if your API takes a UUID, you don't have to convert it to a row ID right away, only when you're actually querying what data the client wants. If you're printing row keys in logs, you ought to prefix them either way (like "user:35" or "user:deadbeef-..."). For backups/recovery, I haven't found UUIDs helpful, maybe cause I never want to just copy whole rows. A sharded DB takes special consideration and could go many different ways, so defaulting to UUIDs in anticipation of sharding one day is probably not going to help when that day comes. In some setups, the PK is just for that one node, and you have a global ID across nodes (which may be a composite of node-local PK + shard ID). Or you're switching to a specialized, not-so-relational DBMS for horizontal scaling. Like, serial IDs are a terrible idea in Google Spanner. > you actually need to justify _not_ using them with concrete performance data For what it's worth, I encountered this situation in a DB with millions of rows. UUID PKs were significantly increasing our overall application latency, so I switched us to bigserials. I'd rather not put newer systems on a track to hit that hurdle later on. It can start being a noticeable problem well before you're thinking about sharding.