Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Keys in Depth

begriffs.com

Post reply on HN
Print view View original

131–140 of 174 posts

Re: SQL Keys in Depth

#131

Post by Groxx »

luhn wrote: post #97
extrapickles wrote: post #91

Earlier quoted context omitted.

UUIDs are the best choice when developer time is more important than space usage. Also, they can be generated and used by the client when the connection to the db is frequently down (eg: clients store/query data locally in SQLlite and replicate to master).

UUIDs are a great option for creating IDs offline, but I don't understand your first comment: How do UUIDs optimize for developer time? In my experience, autoincrementing integers are the easiest choice for primary keys.

Sometimes yes, sometimes no, but mostly I agree that uuids are better for dev time. The first time something is written, they're roughly the same (or a very small bias towards autoincrement), but UUIDs have fewer dev-problems in the long run.

When you're starting out and don't have (m)any simultaneous writes to your DB, autoincrements are often perfectly sequential and identically sorted as a created_at column... which is convenient, but too often I see systems implicitly relying on this and having subtle problems when they start growing. E.g. pagination that doesn't account for gaps, or a missing "order by" that leads to the 1, 3, 2 issue mentioned above. It's also not often caught with tests, since they're frequently not run in parallel or at high enough velocity, and that lack of warning, time-coincidence with when the buggy code was introduced, or easily-testable reproducibility can make them hard to track down and fix.

There are a bunch more fairly minor things that others have mentioned (e.g. UUIDs make it much harder to write joins incorrectly, as they'll just always be empty until you do it right), but generally I'd just call it "death by a thousand papercuts". Autoincrements are great and I love them, but generally I lean towards UUIDs since they're a bit more bug-resistant. And performance-wise, if you're having problems I generally doubt you'd be able to fix it by switching to autoincrements (assuming it was just flipping a switch). For a short time, possibly, but generally at that point you'll be beyond the "pick whatever is easiest" stage and can make an informed decision that'll have a far larger impact.

---

tl;dr: I disagree with primary keys being "opaque identifiers" because they do have an affect on behavior in buggy code. Autoincrements mask more problems than UUIDs, and fixing even one of those costs more time than autoincrement saves.

Re: SQL Keys in Depth

#132

Post by sparkie »

pasta wrote: post #124
sparkie wrote: post #116

Earlier quoted context omitted.

The trouble with "natural keys" is that they're rarely actually unique. The barcode is a typical example. A naive developer might use a barcode as a primary key, but will soon be in for a world of pain when he realizes that products often use the same barcodes for different configurations (packaging etc), which usually need different SKUs. The same product from different origins may have the same barcode, which often…

And a product might get multiple barcodes. I was a fan of natural keys but it is just too much trouble. For example you have to url encode everything when a key is used in the url. But sometimes I still use natural keys for tables with for example ISO standards like country codes.

ISO Country codes, language codes and currency codes are so common (and don't change very often) that they should really be part of the databases's standard distribution as types (eg, enums), so that everyone is singing from the same hymn book and that not every developer needs to implement the same tables in their DB.

Re: SQL Keys in Depth

#133

Post by l_t »

RobertRoberts wrote: post #129
extrapickles wrote: post #91

Earlier quoted context omitted.

UUIDs are the best choice when developer time is more important than space usage. Also, they can be generated and used by the client when the connection to the db is frequently down (eg: clients store/query data locally in SQLlite and replicate to master).

I am not a db expert, which is why I like read these kinds of comment threads. But what about this? https://en.wikipedia.org/wiki/Universally_unique_identifier#... "The random nature of standard version 3, 4, and 5 UUIDs and the ordering of the fields within standard version 1 and 2 UUIDs may create problems with database locality or performance when UUIDs are used as primary keys. For example, in 2002 Jimmy Nilsson…

I'm also not a DB expert, but I definitely think locality is a concern with UUIDs (at least in Postgresql, which stores records in sorted order).

But, storage locality has always struck me as a use-case-specific optimization, in the sense that it's hard to take locality into account without knowing what traffic patterns the table is expected to experience. So, although UUIDs provide locality for exactly no types of queries, that may not be a problem for you.

For instance, you could use an autoincrementing sequence, but that only provides good locality for queries over sequential records. The COMB method would provide good locality for queries over time periods. But if you aren't performing those types of queries, you might be better off with a different type of PK.

Re: SQL Keys in Depth

#134

Post by 9034725985 »

sparkie wrote: post #132
pasta wrote: post #124

Earlier quoted context omitted.

And a product might get multiple barcodes. I was a fan of natural keys but it is just too much trouble. For example you have to url encode everything when a key is used in the url. But sometimes I still use natural keys for tables with for example ISO standards like country codes.

ISO Country codes, language codes and currency codes are so common (and don't change very often) that they should really be part of the databases's standard distribution as types (eg, enums), so that everyone is singing from the same hymn book and that not every developer needs to implement the same tables in their DB.

Are you thinking something like pg_timezone_name?

https://www.postgresql.org/docs/current/static/view-pg-timez...

https://gist.github.com/justqyx/11245900

Re: SQL Keys in Depth

#135

Post by twic »

tzahola wrote: post #39
MarHoff wrote: post #34

Earlier quoted context omitted.

I agree with you, but I think you did overthink his metaphorical example a little. I think the point was that for a 40 people club odd are very very poor that two people would have the same name. And even if so a club manager could still differentiate by adding a middle name or a nickname (in a 40 people scenario). Of course IF your neighborhood club expand and you need to manage a lot of people you'll have to switch…

>I think the point was that for a 40 people club odd are very very poor that two people would have the same name. Wrong. For example I have exactly the same name as my father and we're members of the same squash club.

I don't have exactly the same name as my father, and we are not members of the same squash club. So, in the sample so far, there's only a 50% chance of that happening. I suspect that if we enlarged the sample, it would fall much further.

A single counterexample does not falsify the theory that the odds are very very poor!

Re: SQL Keys in Depth

#136

Post by tatersolid »

slg wrote: post #16
jeremy_wiebe wrote: post #11

Earlier quoted context omitted.

Isn’t that exactly what the NEWSEQUENTIALID function was created for? https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ...

In theory yes, it solves some of the problems and introduces others. It isn't usable everywhere NEWID is usable, so if you assign UUIDS as part of a query you are out of luck. It also assumes you are generating all your UUIDs on the same machine without restarting which once again negates some of the benefits of UUIDs.

ULIDs solve these issues.

Basically 48 bits of millisecond timestamp then 80 bits of randomness.

The technique has been used in MSSQL apps since about 2003 (although people called them COMBs instead of ULIDs back then)

https://github.com/oklog/ulid

Re: SQL Keys in Depth

#137

Post by WorldMaker »

RobertRoberts wrote: post #129
extrapickles wrote: post #91

Earlier quoted context omitted.

UUIDs are the best choice when developer time is more important than space usage. Also, they can be generated and used by the client when the connection to the db is frequently down (eg: clients store/query data locally in SQLlite and replicate to master).

I am not a db expert, which is why I like read these kinds of comment threads. But what about this? https://en.wikipedia.org/wiki/Universally_unique_identifier#... "The random nature of standard version 3, 4, and 5 UUIDs and the ordering of the fields within standard version 1 and 2 UUIDs may create problems with database locality or performance when UUIDs are used as primary keys. For example, in 2002 Jimmy Nilsson…

This article mentions this question. Database locality is a complex scaling question that may not be a problem depending on how read/write heavy your operations are. On the one hand, randomly distributed UUIDs may be a bad idea on a single SQL server trying to balance a single B-Tree index (and thrashing that index with a lot of incoming data); on the other hand it can be somewhat ideal for partition sharding across multiple servers. As with any trade-offs in database design, your mileage will vary with your application needs and resource availability.

Also, there are other options for time-ordered GUID/UUID alternatives. ULID is the one I've been heavily using in projects lately: https://github.com/ulid

(ULID uses a timestamp prefix and random suffix for reasonable time-ordered database locality; an interesting compromise between V1 and V4 UUIDs, though not directly compatible with either. The L stands for "lexicographic" in that its also meant to be sortable in string indices as well, which can be important for database locality in many document/NoSQL databases.)

Re: SQL Keys in Depth

#138

Post by WorldMaker »

tzahola wrote: post #47
nickjackson wrote: post #45

Earlier quoted context omitted.

I prefer /@/ https://davidcel.is/posts/stop-validating-email-addresses-wi...

This is the only sane way.

Until for some reason a user really needs to use a classic UUCP bang-path. Poor help!trapped!in!lost!server ;)

Re: SQL Keys in Depth

#139

Post by gxs »

mannykannot wrote: post #98
foreigner wrote: post #33

At what scale does all this stuff start to actually matter? I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems. We use auto-incrementing integers as primary keys and try not to expose them externally. Indexes are added as necessary to enable specific queries. We don't enforce any other constraints (e.g. not-null or foreign keys) at the database level. ...…

You might not have any observable effects until the day you do, at which point you may be faced with an arbitrarily large problem. What you are doing is passing up on the opportunity of catching various errors (you are also passing up the opportunity for some optimizations, but that is probably a secondary issue.) In particular, you are passing up on some opportunities to catch inconsistencies in how different applic…

I think OPs question was exactly asking the question of when these problems do arise, not how large they are when they do.

I'd be curious to hear when they do, would be great if you had some real world examples.

Re: SQL Keys in Depth

#140

Post by wvenable »

pasta wrote: post #124
sparkie wrote: post #116

Earlier quoted context omitted.

The trouble with "natural keys" is that they're rarely actually unique. The barcode is a typical example. A naive developer might use a barcode as a primary key, but will soon be in for a world of pain when he realizes that products often use the same barcodes for different configurations (packaging etc), which usually need different SKUs. The same product from different origins may have the same barcode, which often…

And a product might get multiple barcodes. I was a fan of natural keys but it is just too much trouble. For example you have to url encode everything when a key is used in the url. But sometimes I still use natural keys for tables with for example ISO standards like country codes.

I feel like an ISO country code is more of a surrogate key anyway; it's a mnemonic made-up value specifically designed to be used as key.
Post reply on HN