8 ms·

Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach f

Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have. I think this isn't a question of ints being a mistake and UUIDs being better, it's about using the most appropriate type based on requirements.

Illniyar 10y ago
With scale its not about key exhustation, its the ability to be able to create a key in multiple servers without having to consult a central location.
- Someone1234 10y ago
  Indeed, which the article fails to even explain. I'm legitimately not sure the author understands why large distributed systems utilise UIDs so just made up a reason that sounded good to them. I haven't used a non-64 bit database in, let's say ten years, so making design decisions because a 32 bit int might be exhausted is rather dated advice at best.
- ashmud 10y ago
  For processing data types without quality natural unique keys, being able to generate a unique key beforehand is very useful. I've written code both ways: inserting to get an auto-incremented integer, and generating a UUID, and the UUID approach was much easier to write and maintain.
  - jameshart 10y ago
    I'm going to make a bold claim: no datatype has a quality natural unique key. I am struggling to think of a counterexample. Maybe chemical elements? Where the natural key is of course the atomic number, not the chemical symbol... Before you suggest 'zipcodes' or 'states', consider that zipcodes are not in any sense natural, and anyway, like state codes, are so US-centric that they don't belong as top level elements in most real database schemas.
- VLM 10y ago
  That brings back the title of the article. Beginners aren't making distributed systems on legacy 32 bit hardware (microcontrollers?) that max out a 32 bit space. Really the article needs to bifurcate into 1) Actual mistakes beginners really make, like sucking entire tables over the network into local arrays and then hand writing a (slow and buggy) emulation of the DB server's JOIN. Bonus points for not memoizing/caching those giant tables. Another beginner comedy is NIH reinventing of the concept of having an index for speed, written entirely in slow application code. Turing complete being what it is, beginners sometimes try to write a rational database manager in their application code, not knowing if its really handy and could cut down on round trips and bandwidth, someone probably added that to the RDBMS code back in 1990. Another mistake beginners make is scaling, designing a 1E9 system for a 1E3 problem or vice versa. Another mistake beginners make is thinking some web post from '96 is relevant because nothing has changed since then (like storage and speed and application load) vs ignoring other posts from '96 because this time really nothing has changes since then (like basic computer science concepts), or rephrased 90% of the web should be ignored and beginners will ignore the wrong 90%. 2) Things beginners do that sounded like a great idea like not normalizing their data that turn into a 3-ring circus of writing convoluted application layer code to work around it. Sometimes you can write hundreds of LoC to avoid each line of SQL. Or getting into a habit of writing "baby's first todo app" using small 32 bit ints and then blowing it when they move up in the world into giant distributed systems.
acveilleux 10y ago
This was probably the weakest suggestion in the article. That said, the default SERIAL type in postgres is 32-bit signed integer so it can be surprising when you exceed 2B items.
- _quick_q 10y ago
  That's why BIG SERIAL exists. :-)
nieksand 10y ago
Actually, uuid4 as a pkey will cause pathological insertion performance on a b-tree since you are randomly inserting across the tree (having to constantly split pages) rather than appending to a far extent. If you want to use uuids, uuid1 may be a better bet.
ma1069 10y ago
I had the same thought... And not just about point 3. All of these seems complex points with no absolute answer, as not every database really needs to scale up to several terabytes... Sometimes the reported "advices" might be useful, sometimes they might just unnecessarily complicate things...
falcolas 10y ago
Personally, understanding how the engines usually work under the hood [0], I'd keep an integral PK, and have a `UNIQUE` external ID field. [0] Having a string for a primary key will typically make there be a hidden integer primary key; better to have it explicit than implicit.
- h43k3r 10y ago
  Can you point me to some docs that explain this ?
donpdonp 10y ago
a feature of UUID PKs is to allow merging of groups of records. Say there were 1000 records from an old backup that need to get into the database because they were left out/forgotten for some reason. Doing an insert will just work. If both tables started with integer keys, you've got a mess trying to move/renumber the keys of the new records. Drop them you say and let the db make new ones? Sure but now you've just suggested throwing away the primary key! Seems like a poor feature for a PK and impossible to do if existing records reference it.

Illniyar 10y ago

With scale its not about key exhustation, its the ability to be able to create a key in multiple servers without having to consult a central location.

Someone1234 10y ago
Indeed, which the article fails to even explain. I'm legitimately not sure the author understands why large distributed systems utilise UIDs so just made up a reason that sounded good to them. I haven't used a non-64 bit database in, let's say ten years, so making design decisions because a 32 bit int might be exhausted is rather dated advice at best.
ashmud 10y ago
For processing data types without quality natural unique keys, being able to generate a unique key beforehand is very useful. I've written code both ways: inserting to get an auto-incremented integer, and generating a UUID, and the UUID approach was much easier to write and maintain.
- jameshart 10y ago
  I'm going to make a bold claim: no datatype has a quality natural unique key. I am struggling to think of a counterexample. Maybe chemical elements? Where the natural key is of course the atomic number, not the chemical symbol... Before you suggest 'zipcodes' or 'states', consider that zipcodes are not in any sense natural, and anyway, like state codes, are so US-centric that they don't belong as top level elements in most real database schemas.
VLM 10y ago
That brings back the title of the article. Beginners aren't making distributed systems on legacy 32 bit hardware (microcontrollers?) that max out a 32 bit space. Really the article needs to bifurcate into 1) Actual mistakes beginners really make, like sucking entire tables over the network into local arrays and then hand writing a (slow and buggy) emulation of the DB server's JOIN. Bonus points for not memoizing/caching those giant tables. Another beginner comedy is NIH reinventing of the concept of having an index for speed, written entirely in slow application code. Turing complete being what it is, beginners sometimes try to write a rational database manager in their application code, not knowing if its really handy and could cut down on round trips and bandwidth, someone probably added that to the RDBMS code back in 1990. Another mistake beginners make is scaling, designing a 1E9 system for a 1E3 problem or vice versa. Another mistake beginners make is thinking some web post from '96 is relevant because nothing has changed since then (like storage and speed and application load) vs ignoring other posts from '96 because this time really nothing has changes since then (like basic computer science concepts), or rephrased 90% of the web should be ignored and beginners will ignore the wrong 90%. 2) Things beginners do that sounded like a great idea like not normalizing their data that turn into a 3-ring circus of writing convoluted application layer code to work around it. Sometimes you can write hundreds of LoC to avoid each line of SQL. Or getting into a habit of writing "baby's first todo app" using small 32 bit ints and then blowing it when they move up in the world into giant distributed systems.

acveilleux 10y ago

This was probably the weakest suggestion in the article. That said, the default SERIAL type in postgres is 32-bit signed integer so it can be surprising when you exceed 2B items.

_quick_q 10y ago
That's why BIG SERIAL exists. :-)

nieksand 10y ago

Actually, uuid4 as a pkey will cause pathological insertion performance on a b-tree since you are randomly inserting across the tree (having to constantly split pages) rather than appending to a far extent. If you want to use uuids, uuid1 may be a better bet.

ma1069 10y ago

I had the same thought... And not just about point 3. All of these seems complex points with no absolute answer, as not every database really needs to scale up to several terabytes... Sometimes the reported "advices" might be useful, sometimes they might just unnecessarily complicate things...

falcolas 10y ago

Personally, understanding how the engines usually work under the hood [0], I'd keep an integral PK, and have a `UNIQUE` external ID field. [0] Having a string for a primary key will typically make there be a hidden integer primary key; better to have it explicit than implicit.

h43k3r 10y ago
Can you point me to some docs that explain this ?

donpdonp 10y ago

a feature of UUID PKs is to allow merging of groups of records. Say there were 1000 records from an old backup that need to get into the database because they were left out/forgotten for some reason. Doing an insert will just work. If both tables started with integer keys, you've got a mess trying to move/renumber the keys of the new records. Drop them you say and let the db make new ones? Sure but now you've just suggested throwing away the primary key! Seems like a poor feature for a PK and impossible to do if existing records reference it.