Hacker News Reader

>As far as I know, no database has ever had any of the problems he mentions with IDs not round-tripping properly during backup/restore.

Don't know of any such either. Typical SQLite, MySQL, or PostgerSQL dump/backup is just bunch of SQL statements that re-create the content, including PKs.

You can imagine a non-native (perhaps DB-agnostic?) DB dump format that would use generated IDs for PK, rather than actual PKs. Why would one do that, I don't know.

Another possible case is using file (or memory) offset as the PK. This gives certain nice properties - one less indirection level, and object identity being pointer identity. Perhaps there's a memory image-based LISP version out there that uses such. Perhaps there's a NoSQL engine like that somewhere.

>Plus, you'd have to hack your slug-generation algorithm to ensure the generated slugs to be unique

...or you put the moderators to the task of ruthlessly stamping out duplicate questions. There was a popular website like that, oh wait, that's the Stack Overflow ;-)

[edit]

I've just remembered that PostgreSQL's ctid and SQLite's ROWID are physical identifiers (offset-like identifiers) of records. They are (to certain extent) exposed to userspace and could be misused as IDs in URLs. As physical identifiers (offset-like), they are naturally subject to change upon dump/restore, vacuum, other maintenance tasks, etc.. Clearly those should not be used as end-user visible IDs in URLs.

PostgreSQL today is finally deprecating the OID feature they inherited from the original Postgres code. The OID used to be an implicit row ID that would have these problems.

The OID is generated from a sequence shared by all databases in the "cluster" i.e. the PostgreSQL server instance. So it is not only non-deterministic based on insert orders to your DB, but also is affected by inserts in other databases on the same server.

True, but for over a decade Postgres common wisdom has discouraged using the OID for anything in the app level.

As I recall, OIDs only ever came up in practice if you wound up wrapping the 32 bit uint.

Right, but the article under discussion here is from over a decade ago and might well have been targeting older practices that hadn't adopted this perspective yet.

The original goal for OID was a bit like UUIDs, but just at the cluster level rather than truly global. Someone steeped in that background and focused on "tuning", where you would consider things like storage consumption and index efficiency for int4 vs int8, might well have baked OIDs into a URL while dismissing the uuid type as extravagantly expensive.

>As far as I know, no database has ever had any of the problems he mentions with IDs not round-tripping properly during backup/restore. (Please correct me if I'm wrong! But why do I think this is true? Because any database that doesn't roundtrip primary keys would break all foreign keys on backup/restore. Databases with broken backup/restore tend to either fix it real fast, or go away.)

You might not use foreign keys, but still have autoincremented primary key.

Database ids on the URL are also a security concern.

> Database ids on the URL are also a security concern.

They shouldnt be. Users should only be allowed access to their own records, even if they know the id's of records belonging to other users.

For one, they expose the number of users and other internal details. Even that is a security concern, access or not.

Not if you don't start at zero, and no one knows where you started. Heck, add a multiplier for use in URL's even. It's not hard to come up with reversible obfuscation functions that would hide your number of users for 99.99% of cases. If you have a competitor that set on knowing your count of whatever objects then maybe you have a case. But let's say this is number of posts, clearly viewable on the website anyways. Who cares? If that sort of info is exploitable, you have to have much bigger security holes present.

At the point where you're doing all of that obfuscation, wouldn't it be easier and safer to use something like a uuid?

So you rather your DB performance tank because of unsortable and unclusterable (sp?) PKs than expose a tiny bit information that shouldn't even be that important? You can use a UUID as a URI link, but you shouldn't use it as a PK on most databases. That means you would have to add another column for the link ID (which I think is a relatively good and clean solution)

Postgres doesn't have issues with UUIDs as primary keys; they don't need to be sorted (hash indexes were dicey before Postgres 10 but are just fine today) and Postgres doesn't row-cluster by default.

What performance cost there is (and in my experience it is not much) is because a UUID is twice as large as a bigint. And there are situations where that matters. But not that many of them.

Hash indexes can't be used for primary keys:

   postgres[7878][1]=# SELECT amname, pg_indexam_has_property(oid, 'can_unique') FROM pg_am WHERE amtype = 'i';
   ┌────────┬─────────────────────────┐
   │ amname │ pg_indexam_has_property │
   ├────────┼─────────────────────────┤
   │ btree  │ t                       │
   │ hash   │ f                       │
   │ gist   │ f                       │
   │ gin    │ f                       │
   │ spgist │ f                       │
   │ brin   │ f                       │
   └────────┴─────────────────────────┘
   (6 rows)

And postgres' btree indexes definitely are affected by the randomness in many UUID generation schemes.

Huh, so it is. And looking at our schema, it looks like the UUID/hash tables we've got don't use pkeys at all.

Looks like I have a conversation to have. Thanks. :)

Why shouldn’t you use a uuid as a primary key on most databases?

Use an encoded time prefix and a random part to create your UUID. I've been using such UUIDs as primary keys in Postgresql for years and I can't tell the difference in performance with say 64-bit numbers. If you don't need millions of records, even a 64-bit number with timestamp and random part will do – like "Twitter snowflake" ids.

> Not if you don't start at zero, and no one knows where you started.

Yes, in fact - even then. There are fascinating historical stories about lay-people who scoffed and then failed to account for the dark art of statistics. https://www.statisticshowto.datasciencecentral.com/german-ta...

I once scraped a representative slice of Slashdot comments a while back and noticed some odd changes in their UIDs. They were originally sequential and then they switched to even numbers for a while, then odd numbers. I don't know if they were doing it to juice the UID stats to look like more people were joining the platform.

> They were originally sequential and then they switched to even numbers for a while, then odd numbers.

That sounds like they wanted to migrate to another cluster, and wanted to have both clusters running at the same time (for testing) without risking an ID overlap, so they turned the least significant bit of the ID into a cluster identifier. The older cluster was 0 (even numbers), the new cluster was 1 (odd numbers).

> Database ids on the URL are also a security concern.

More so than in mark up? If so why?

Should surrogate keys never be exposed to users in any form? Then what's the alternative? Nonces for everything, all the time?

> More so than in mark up? If so why?

Easy to enumerate, if that's a concern.

In the case of URLs, you can have /questions/:id/:slug, and then load the question by :id, but if :slug doesn't match the slug you've got in the database, then return 404.

Sure, but that means that you have a unique key combination of id + slug for all intents and purposes. The argument for having the ID is that the slug can change and you can redirect. If you don't redirect, that argument falls flat, and if you redirect you're easier to enumerate.

Isn't most of the security concern fixed by using guids instead of incrementing ints?

Yes, but please don't use guids in urls.

why?

Seems like your options are:

1. Mutable data (looks nice but can change)

2. Incrementing data (looks nice but can be guessed and measured)

3. Random looking data (immutable and unguessable but ugly)

Guids aren't the only option for 3 and some of the others may not look as bad, but it still seems the safest option. Any other compelling reasons to not use guids?

There's no reason not to use guids in urls.

Other than them being obnoxiously long, that is.

Pick a primary key (i.e., set of columns) for a table. If you expose this outside the database in stable ways (e.g., in URIs), then do not permit changes to that table's rows' primary keys.

There's NO NEED for a primary key to be an INTEGER or any sort of ROWID.

I agree with TFA -if this is what it says- that one should not put INTEGER PRIMARY KEY values in URIs -- it's useless to users. Just make the "slug" a column, make a NOT NULL and UNIQUE, disallow changes, and use that. You don't have to make the slug a PRIMARY KEY -- NOT NULL and UNIQUE has the same semantics as PRIMARY KEY.

Surely TFA wouldn't argue to have no DB key at all in URIs, so I won't bother with that possibility. And yes, I should read TFA...

This seems like a lot of effort, with plenty of potentially fatal edge cases, for extremely little gain.

A small number in a URL isn't going to confuse anyone.

What do you do when literally every "natural" column of your table is mutable, but you need the row's identity to be stable?

Well, you need what is called a https://en.wikipedia.org/wiki/Surrogate_key.

Surrogate keys need to be made up from whole cloth, apart from the natural data (because if they are derived from any of the natural data, they'll become incorrect when the data changes.) And what's easy to make up from whole cloth? Sequential integers. (Or random numbers, which—if you want to guarantee no collisions across a distributed DB cluster—gives you ≥128-bit random numbers, i.e. UUIDv4s.)

The slug has to be computed at INSERT time (e.g., by a trigger) and not allowed to change thereafter.

It needs to change if the title changes, though.

Why?

A lie is worse than nothing at all. Imagine if you will, a post originally written with the title of "Party A Wins Election", and thus the slug of "party-a-wins-election"; but not published in that state. Then, before publication, the facts that publication was waiting on come in, and the title is amended to "Party B Wins Election."

But now the slug in the URL of the published article (about party B winning the election) is still "party-a-wins-election"!

There are thousands of use-cases just like this. For another example, someone might have a real claim on a libel suit if you assert something false about them in a URL slug in a link hosted on your own site, even if it points to a page that says only true things about them! ("People don't always click through to find out what the actual article is about", their lawyer would argue; "sometimes they just read the URL itself." The judge would agree, being just the sort of person who is too busy to read everything that crosses their desk, instead triaging the importance of things [read oneself, delegate to their legal analysts, forget] by just such sloppy criteria as looking at the URL slug.)

This is why, in CMSes, you need to always be able to exert editorial control over every part of a page, including the URL. The slug, just like all other parts of the page, must be mutable. And so you can't use the slug as the primary key.

("But what if you just delete posts with bad slugs and make new posts to replace them?" Not always possible; in a CMS for a publisher with a hard-core editorial flow, often the "post" you see is just a view onto the same work-object that passes through the rest of the CMS process of edits and approvals, and you can't just delete the work-object and make another one, without getting whoever assigned you the job to cancel your assignment and re-assign it to you. Heck, often at time-of-assignment you don't even know what you're going to use as the headline. How should a system assign a slug for that?)

(And, while, yes, you could restructure things so as to decouple content-asset objects from business-domain objects, such that it's the content-asset object that has a slug, and maybe content-assets never change at all, just get new versions of them published with the previous versions hidden, with some router using rules to determine to which content-asset a given slug should route... at that point you're talking about the sort of Event Sourcing architecture which people on HN like to fantasize about but which 99% of businesses refuse to implement for the same reason they don't want to use unpopular programming languages: it makes it far harder to hire Joe Average Java-School Dev to maintain the code.)

You keep a history of all the slugs, never reuse them, and have them all (but the current) redirect to the current. Done. No pesky integers in URIs.

It seems a very cumbersome approach (and potentially slow) just to avoid ids in the URL. Whereas, having ids in the URL is simpler and has very few drawbacks.

But that's no good because it means the slug may end up having no relation at all to the actual content of the page (after the page has been edited). The only thing worse than no slug is a bad slug. Don't bake bad slugs into your database design.

An integer has even less relation to the actual content.

Yes, that's the whole point.

That's why you pair the immutable integer with a mutable slug. Nobody memorizes slugs so you don't have to worry about supporting the case of a user manually typing in the URL from memory (which is about the only time when having a short integer in the URL would matter). With this scheme, you can change the slug at will, and in fact you can support loading the URL without having a slug at all, or with a made-up slug. This makes the URL resilient to minor transcription errors (e.g. accidentally deleting the last character of the URL, or screwing up when retyping it), and it also means anyone who's aware of this behavior can skip the slug entirely when retyping the URL.

That is terrible advice.

1. Numeric based Primary Key Lookups are some of the fastest operations on modern databases. 2. "Slugs", are a set of strings, which occupy more memory, space, now you have to come up with some way to make them unique. If you are building a CMS, sure, SEO is important. If you are building a line of business application, no. 3. Just checked, Google and Facebook expose numeric based identifiers. My Facebook Business ID is numeric, my Google Analytics ID is numeric with a prefix on it. 4. There are plenty of libraries out there that will take a numeric value, convert it to a small alphanumeric value with a check digit. 5. No end user cares about URLs (except for SEO). Your business user is not going to navigate your application by going to myapp/settings, they are going to click on the gear icon.

> it's useless to users

It's pretty useful to me when scraping/syncing data from a website. I'm also a user.

  IDs not round-tripping properly during backup/restore

Oracle has a "ROWID pseudocolumn" which uniquely identifies each row.

(I know this because I once had to fix a database table with no primary key, and some rows where every field was identical. The rowid lets you delete one out of two identical rows. So it's not an entirely useless feature)

If you had an inexperienced developer, and no-one reviewing their work, and they heard the rowid is the fastest way of looking up a row - faster even than the primary key - so they decided to use that in URLs, that would be tremendously inconvenient.

Few days ago I discovered that SQLite has ROWID too; the extra column gets created if your table doesn't have an INTEGER PRIMARY KEY and doesn't declare WITHOUT ROWID. Caveats apply, though. See: https://www.sqlite.org/rowidtable.html.

Funny timing. I just discovered the same about 3 days ago, too. I've been a consumer of SQLite databases but never had much of a need to create one. I was somewhat surprised to say the least. Interestingly, it appears AUTOINCREMENT changes ROWID's behavior [1] to stop ID roll over and reuse.

Apparently you can't use AUTOINCREMENT on tables using WITHOUT ROWID either.

[1] https://www.sqlite.org/autoinc.html

Old versions of MySQL had an edge case where, if you delete the last autoincremented id and restart the database, the ID was reused:

https://stackoverflow.com/questions/18692068/will-mysql-reus...

> it's much easier to keep IDs stable over time ... [otherwise] you'd have to hack your slug-generation algorithm to ensure the generated slugs to be unique[.] ... [A]voiding cruft ... [is] in direct tension with the point about keeping them stable given that "meaningful" non-cruft tends to change over time!

Many applications have meaningful unique identifiers, creating a unique slug is not always a "hack". Any app that displays events that occurred in the past often have unique identifiers. Examples include software logs, email, government records, financial transactions, i.e., anything that displays past events.

These records are inherently "write-once", and the primary key can be something more meaningful than an ID. These may be just a subset of apps out there, but in these cases there is a strong argument for removing any ID in a URL.

> As far as I know, no database has ever had any of the problems he mentions with IDs not round-tripping properly during backup/restore.

Simple example - neo4j. Node id's are reused when deleted and you can't create node with id you want. Documentation mentions this. We've just used unique random strings as keys.

I suppose it depends on the method used for backup and restore. If you replace the entire graph.db directory with one from a backup, ids are kept intact.

I was relying on this just yesterday. We use graphenedb for our production database and I needed to load a production backup locally for testing. It was really useful that I could query a node by id both locally and on prod and get the same result.

Yeah, for debugging it's ok, but we once had to migrate between versions where we couldn't keep graph.db but had to recreate all nodes and relationships. Relying on id's in production anywhere outside of single transaction is too unreliable.