4 ms·

> Personally, I'm a fan of giving everything a random UUID, because it's more flexible Unless of course, you're using a relational database like OP and incur a

> Personally, I'm a fan of giving everything a random UUID, because it's more flexible Unless of course, you're using a relational database like OP and incur a performance hit from using a UUID as your primary key. Additionally, they're not sortable like autoinc id's. I've always wanted to try out Twitter's Snowflake ID [1] algorithm to get around this, but it requires requires using something like Zookeeper. I've seen some people on the net talk about UUIDv6 being sortable by time, but there's still the potential performance hit of index size. While I'm bringing this up I've never actually tested how slow PK UUIDv1's are and at what magnitude their performance hit becomes noticeable. [1] https://blog.twitter.com/engineering/en_us/a/2010/announcing-snowflake.html https://blog.twitter.com/engineering/en_us/a/2010/announcing...

dale_glass 6y ago
Things like Twitter are special. I'm talking more about a generally sensible way of doing things, which one may need to deviate from in special circumstances. Why would you want to sort by ID? Sort by something sensible, like the signup date instead. An autoincrement may stop corresponding to time if for instance at some point a database has an external dataset imported into it. IMO, using an ID for anything other than an opaque identifier is asking for trouble.
- simonw 6y ago
  I like IDs I can read out over a call, or recognize when I spot them in a log file. The few times I've used UUIDs for IDs I've later regretted it.
  - mosdl 6y ago
    Add a prefix to the if then.
  - orf 6y ago
    > or recognize when I spot them in a log file “14632” in a log file could be anything, whereas a UUID is way more explicit and searchable.
    - simonw 6y ago
      I have frequently memorized IDs of things in systems I interact with often: "Oh, it's user 14632 - yeah I've seen that ID crop up against this issue a bunch of times in the past" I can't do that with UUIDs.
      - orf 6y ago
        Sure, but I think you would usually optimize for the inverse - actively finding a needle in a haystack rather than passively hoping a needle falls in your lap. I get 1k hits for "14632" in our Splunk for the last 60 minutes, all of it rubbish. Durations, image sizes, worker IDs, primary keys, port numbers. Search for a UUID and the results are guaranteed to always be relevant. Can't do that with integers.
bbirk 6y ago
There's no need for zookeeper or any centralised/decentralised service. In the article you link they mention why depending on something like zookeeper is suboptimal. Given that you have less than something like 2048 web server instances, (don't remember how many bits they give to worker_number and the snowflake github repo is basically unavaible) all you need to do is make sure every instance has a rank/worker_number (infrastructure/devops problem) which the instance will use when it generates the snowflake ids. Sidenote snowflake also suffers from the unix epoch 2038 problem, but that can be simply solved by adding bits for epoch number.
cratermoon 6y ago
> they're not sortable like autoinc id's. ksuid https://segment.com/blog/a-brief-history-of-the-uuid/ https://segment.com/blog/a-brief-history-of-the-uuid/ Using DB autogenerated IDs risks that nightmare situation I've seen in more than one organization I've worked with: the IDs "leak" and become actual identifiers, in perpetuity, for the related entity. Now you can no longer renumber your table, and if you dump and restore you get all new IDs.
- hodgesrm 6y ago
  Your comment seems to conflate autogenerated IDs like MySQL AUTO_INCREMENT or PostgreSQL SERIAL with internal row IDs. Autogenerated keys are set on insert if you don't provide a value. After that they are stable. (And very popular, too.) The case you describe sounds like using Oracle ROWID as a key, which I suppose people do as a hack to get around schema problems instead of fixing the schema. [1] That's an exceptionally bad idea. [1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseud...
  - cratermoon 6y ago
    Can you clarify how autogenerated keys are different from an autoincrement id column? In my experience, the autogenerated keys are built on top of auto-increment columns. Of course, if you use a stored procedure or something to generate a value, then you're just using a unique ID, same as anything else, but making your system dependent on the DB, which isn't awesome if you have a distributed system with replication and all that.
    - hodgesrm 6y ago
      I might have missed something upthread but autogenerated keys to me just mean that the key is somehow generated automatically for you rather than using a natural key like social security number (SSAN) when inserting rows. Autoincrement IDs are one way of autogenerating a key that delegates generation to the DBMS server. To expand on this, there are three common approaches to create automatic keys in SQL applications. 1.) Generate it in the application itself. You can make UUIDs yourself with a simple call in most languages. Ensuring uniqueness is your problem--to generate integers, for example, you'll need some sort of coordination if there's more than one application thread. 2.) Generate it from a SEQUENCE. Sequences are database-side key generators that hand back a unique sequence number from a block of available sequence numbers when you call for the next number. Uniqueness is pretty much guaranteed since numbers come from the shared database server. This approach is popular in PostgreSQL and Oracle. 3.) Generate it from an auto-increment column, such as MySQL AUTO_INCREMENT columns. Auto-increment columns generate the key server-side at insert time. Uniqueness is guaranteed, but you can't see the key until it has been inserted. If you need to know the key for follow-on INSERT or UPDATE commands you have to select it back using LAST_INSERT_ID(). [1] Auto-increment keys are popular in MySQL. ORMs like Hibernate JPA tend to provide all of these as choices. I don't use ORMs much myself hence can't comment much on the details. [1] https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id https://dev.mysql.com/doc/refman/8.0/en/information-function...

dale_glass 6y ago

Things like Twitter are special. I'm talking more about a generally sensible way of doing things, which one may need to deviate from in special circumstances. Why would you want to sort by ID? Sort by something sensible, like the signup date instead. An autoincrement may stop corresponding to time if for instance at some point a database has an external dataset imported into it. IMO, using an ID for anything other than an opaque identifier is asking for trouble.

simonw 6y ago
I like IDs I can read out over a call, or recognize when I spot them in a log file. The few times I've used UUIDs for IDs I've later regretted it.
- mosdl 6y ago
  Add a prefix to the if then.
- orf 6y ago
  > or recognize when I spot them in a log file “14632” in a log file could be anything, whereas a UUID is way more explicit and searchable.
  - simonw 6y ago
    I have frequently memorized IDs of things in systems I interact with often: "Oh, it's user 14632 - yeah I've seen that ID crop up against this issue a bunch of times in the past" I can't do that with UUIDs.
    - orf 6y ago
      Sure, but I think you would usually optimize for the inverse - actively finding a needle in a haystack rather than passively hoping a needle falls in your lap. I get 1k hits for "14632" in our Splunk for the last 60 minutes, all of it rubbish. Durations, image sizes, worker IDs, primary keys, port numbers. Search for a UUID and the results are guaranteed to always be relevant. Can't do that with integers.

bbirk 6y ago

There's no need for zookeeper or any centralised/decentralised service. In the article you link they mention why depending on something like zookeeper is suboptimal. Given that you have less than something like 2048 web server instances, (don't remember how many bits they give to worker_number and the snowflake github repo is basically unavaible) all you need to do is make sure every instance has a rank/worker_number (infrastructure/devops problem) which the instance will use when it generates the snowflake ids. Sidenote snowflake also suffers from the unix epoch 2038 problem, but that can be simply solved by adding bits for epoch number.

cratermoon 6y ago

> they're not sortable like autoinc id's. ksuid https://segment.com/blog/a-brief-history-of-the-uuid/ https://segment.com/blog/a-brief-history-of-the-uuid/ Using DB autogenerated IDs risks that nightmare situation I've seen in more than one organization I've worked with: the IDs "leak" and become actual identifiers, in perpetuity, for the related entity. Now you can no longer renumber your table, and if you dump and restore you get all new IDs.

hodgesrm 6y ago
Your comment seems to conflate autogenerated IDs like MySQL AUTO_INCREMENT or PostgreSQL SERIAL with internal row IDs. Autogenerated keys are set on insert if you don't provide a value. After that they are stable. (And very popular, too.) The case you describe sounds like using Oracle ROWID as a key, which I suppose people do as a hack to get around schema problems instead of fixing the schema. [1] That's an exceptionally bad idea. [1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseud...
- cratermoon 6y ago
  Can you clarify how autogenerated keys are different from an autoincrement id column? In my experience, the autogenerated keys are built on top of auto-increment columns. Of course, if you use a stored procedure or something to generate a value, then you're just using a unique ID, same as anything else, but making your system dependent on the DB, which isn't awesome if you have a distributed system with replication and all that.
  - hodgesrm 6y ago
    I might have missed something upthread but autogenerated keys to me just mean that the key is somehow generated automatically for you rather than using a natural key like social security number (SSAN) when inserting rows. Autoincrement IDs are one way of autogenerating a key that delegates generation to the DBMS server. To expand on this, there are three common approaches to create automatic keys in SQL applications. 1.) Generate it in the application itself. You can make UUIDs yourself with a simple call in most languages. Ensuring uniqueness is your problem--to generate integers, for example, you'll need some sort of coordination if there's more than one application thread. 2.) Generate it from a SEQUENCE. Sequences are database-side key generators that hand back a unique sequence number from a block of available sequence numbers when you call for the next number. Uniqueness is pretty much guaranteed since numbers come from the shared database server. This approach is popular in PostgreSQL and Oracle. 3.) Generate it from an auto-increment column, such as MySQL AUTO_INCREMENT columns. Auto-increment columns generate the key server-side at insert time. Uniqueness is guaranteed, but you can't see the key until it has been inserted. If you need to know the key for follow-on INSERT or UPDATE commands you have to select it back using LAST_INSERT_ID(). [1] Auto-increment keys are popular in MySQL. ORMs like Hibernate JPA tend to provide all of these as choices. I don't use ORMs much myself hence can't comment much on the details. [1] https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id https://dev.mysql.com/doc/refman/8.0/en/information-function...