Database IDs Have No Place in URIs (2008) | Hacker News Reader

Database IDs Have No Place in URIs (2008)

johntopley.com

71 pointstosh167 comments

This argument seems backwards.

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.)

On the contrary, it's much easier to keep IDs stable over time, than any other field. The problem with using a different field as an identifier (e.g. a URL component) is that almost any property with an objective meaning, might change over time or become non-unique. This is why it's a best practice not to use, e.g., user email as a primary key.

For instance, Stack Overflow allows users to edit the title of a post. Right now when that happens, the canonical URL changes (to include the new slug), but the old URL is easily 301'd to the new one because it includes the ID. If the numeric ID was not in the URL, you'd be stuck with either a misleading slug, or having to maintain a list of former slugs/redirects for each post. Plus, you'd have to hack your slug-generation algorithm to ensure the generated slugs to be unique, and the easiest way to do that is... by appending an incrementing ID to the end of the slug.

The point about avoiding cruft like .aspx in URLs is well taken, but unfortunately it's in direct tension with the point about keeping them stable given that "meaningful" non-cruft tends to change over time!

(And if you don't want someone to guess your metrics from IDs, you can use a random non-numeric ID instead!)

>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.

>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.

  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

> 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.

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...

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.

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.

> 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.

> If you have to move the site to a different box, can you guarantee that those database IDs will remain the same?

If you don't, then your foreign keys will be broken.

> If you have to restore the site’s data from a backup, can you guarantee that those database IDs will remain the same?

Ditto. If they're not, then that backup seems broken.

> If you have to switch to a different database server (say from Microsoft SQL Server to Oracle), can you guarantee that those database IDs will remain the same?

Ditto.

I've only had one situation where I had to change the database ids, including making sure to change all foreign keys accordingly in all tables, and that was when I was migrating multiple company-hosted instances of a webapp they had in their intranets to a unified internet-hosted server. I was merging multiple databases into one so modifying ids was necessary so they wouldn't clash.

At least in my case, I don't think any of those companies' employees would have had the expectation for their intranet URLs to work. I mean, even the host/domain part of the URI changed. Why would they have the expectation for the path part to still work?

I don't understand why people are saying this is a security issue.

If user a has private document 1234 at /doc/1234 and user b has private document 1235 at /doc/1235

when user a goes to /dec/1235 IT SHOULD NOT RETURN THAT DOCUMENT.

Who in their right minds considers obscurity to be a replacement for authorization?

If a random anonymous individual can access private data by hitting a url, the problem is not the id used in the url.

Also, what about when your users are all generating multiple entries with the same name/slug? thing-name-1532 isn't any easier than things/1532/thing-name

I have no reason to enforce uniqueness of the name of things, but I MUST do this because some jerk said so in 2008?

> when user a goes to /dec/1235 IT SHOULD NOT RETURN THAT DOCUMENT.

As a matter of fact, it should return 404 NOT FOUND for both users A and B

ehutch79 said

> user b has private document 1235 at /doc/1235

So, why do you say it should return a 404 NOT FOUND for user B?

There’s a typo where doc is spelled as dec later on. That’s why the 404 requirement.

There’s some time for the OP to edit and fix that typo. But yeah, I noticed that at first glance too.

Yes, it was a typo

It's at least a user enumeration issue

Why? what's the actual issue? If you have access to it, wouldn't you know the id anyways?

It leaks information about the company - for example, you can see precisely how many documents have been created over x days

the /dec/1235 bit was a typo.

I mean really?

Laugh a little, maybe

UUID's are a good solution to this. Less and less do I see sites using an increment primary key as an id. I'd like to think that using a UUID is more scalable too -- no need to keep track of what the last ID was.

One idea I like is to have a normal internal database ID, but also have a public UUID column to use for URLs, APIs and that sort of thing.

This way you can change the record's UUID at any time to display different data without having to worry about updating a bunch of internal foreign keys. You don't have to worry about a user making easy edits to the UUID in the URL to find nearby records (although you still need real protections if you need to restrict data by user).

I've used that in the past to retrofit an old database and move away from the id=123 in a URL to a UUID. You can keep all the old relationships triggers ect... Just add an index for the new col... UUIDs are not perfect but for most applications they are good enough (I think)

There was a story posted recently about how the properties of UUIDs are not a good match for how databases index columns. It proposed the 'ulid' which helps in that regard.

Thank you for mentioning ulid. Very interesting,and perhaps also useful.

I'm curious about this if you remember the reference. My gut reaction is UUID is better because the keys would be more uniformly distributed across the key-space. With sequential IDs, a binary tree would end up always being unbalanced -- though, this effect would be reduced as the database gets larger.

I recall seeing UUID generators that are specifically tuned for use as keys in databases that specifically support them as primary keys. E.g. this has been around for a while:

https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ...

I've used this to good effect before.

Note that the endianness may catch you by surprise, though. For example, what's good in postgres may not be good for SQL server.

This is kind of how I've always built applications - the standard ID is viewable to admins, but every public facing utilisation is a random string (with a uniqueness validator).

Access to resources is either locked down by an authorisation policy, or sometimes you need open resources (like a public share link), in which case, security through obscurity (with perhaps a secondary string on the record acting as an additional key for non-logged in users).

Funny me too, didn't think others would do the same.

We do that. Treating the API key as distinct from DB id also helps with loosely coupled microservices, in which the key for a given item may have come from any of several source systems.

Why not just use the UUID as the ID?

Using a UUID as your primary key can make certain optimisations much harder down the road, most notably sharding which tends to balance really nicely if you do it on an auto-incrementing numeric ID, and less so if you're using random strings because you'll end up with with some of them clustering.

They're also much harder to pass around in cases where you need to refer to a specific record, for example if you've got someone in tech support who needs help with a particular object its much easier to say "its record 18434" than it is to try and read out a UUID over the phone.

And it's much easier for that person to mishear/miskey that ID and work on the wrong record. With a random UUID you will not have that mistake unless you have some seriously huge data. Base64 encode it everywhere and it's 22 characters.

Even with the world's largest dataset, I don't think you will experience an "off-by-one" error with the UUID4 standard.

> This way you can change the record's UUID at any time to display different data without having to worry about updating a bunch of internal foreign keys.

That is true, but changing the UUID is also likely to break a lot of assumptions that your users make. If your user indexes your forum posts, for instance, and you change the UUIDs, they'll re-index posts with new UUIDs as new posts, and send end users to now invalid UUIDs until the old index entries are evicted.

Yeah, I thought that line was weird. It's more like – you can change the record's internal foreign key without breaking your users! Once users have bookmarks, IDs more or less permanent.

Naturally it depends on the data. I've used it when we want to show the most recent version of a record on the public website but we want to keep older copies of of the data to reference with past events. Eg customer's current address can always be identified with id x but the address records for specific past orders reference other records.

Why do you need an internal database ID?

I've gone the route of a random number only (similar to a UUID but encoded in a way similar to Youtube video IDs). I used to do the auto-incremented integer but once I got into database sharding, maintaining that global counter started getting a lot more complicated.

This lets me have inserts going to several databases without worrying about the counter. (Of course, as with any eventual consistency method, there's a mechanism to ensure no duplicates exist, however unlikely they are.)

Those integer IDs can still be pretty darn efficient for a lot of use cases.

And inefficient in some others. It all depends on the use case.

"URIs should be meaningful, not cluttered with meaningless information."

Actually, URIs are opaque. The parts of an URI are implementation details - even the protocol, domain and port. To the user, changing database IDs, that happen to be part of the URI, is no different to changing the domain, port number or the protocol.

Whenever I get into a discussion about "URI design", I state two rules:

1) Care about the URI design as much as you want. But never expect from someone else - especially the client - to make sense of that design.

2) When necessary, handle URI changes in a way that does not break the client (redirects are your friend).

Btw. For this reason, browsers tend to remove the address bar from the UI (as much as possible).

For a content website such as SO, the biggest advantage of using /<id>/<slug>/ is being able to update the `slug` whenever the `title` of your content changes, and then it's very easy do a 301 redirect and you're good to go.

If you're afraid of Google bombing, the redirect is not even necessary, just make sure the <link rel=canonical> tag contains the correct URL.

That's a great suggestion, thanks!

I prefer redirects because if someone edits a URL to look like /1234/offensive-phrase-here/ and shares the link I don't want it to appear that we choose that phrase as a slug.

That article seems to immediately contradict itself by saying that the most important aspect of URIs is that they never change, and then suggests using the slug derived from the title as unique identifier. Titles change all the time on Stack Overflow, this would immediately break URLs.

The title and slug are related but not tied together. It's quite common to visit slug-based sites and find the slug is a relic of the original title. In addition, slugs have to be unique when used this way, but titles don't. eg a title of Database IDs Have No Place in URIs (2008) may appear again next year, but the url would need to point to a different conversation; your CMS would suggest or generate the slug based on the original title.

The title of a news article sometimes changes because of new reporting that makes the original title inaccurate. Leaving the old title as the slug could cause confusion. Think about a breaking news event where details are very sketchy at first but the article is updated as more becomes known.

This guy sounds like he has bad backups and bad database management. Nothing more. That "database ID" is probably an auto-incrementing primary key, and yes, moving it to another box, restoring from backups, and even migration to another server type should keep the same ID. If not, you've done it wrong.

In none of those situations will new entries necessarily follow the same pattern, i.e. there may be huge gaps or it might start filling in deleted questions so entries are "out of order", but those are separate problems and also remedied by using your schema correctly.

In short: this post is very misinformed and I hope that it's presented for that purpose and not taken as serious advice.

> this post is very misinformed.

This. The answer to 3 question in article is always "YES".

> The slug itself can easily be automatically generated when a new question is saved. Then you can simply retrieve a question by its slug.

And that was just pure ignorance. What happens to slug when question(or product) is edited?

I get the vibe a lot of these critiques are rooted in academic understanding of the issues rather than real world practice. In practice, these are all problems that can be addressed...some rather trivially.

The biggest real world concern I've ever had with embedding incremental IDs into URLs is that incremental IDs can leak information, like how many records you have and how quickly that's growing. Depending on your business, you may want to obfuscate that.

Also, using a numerical auto-incrementing ID in the URL will expose how many users you have so far. Not a problem for Stack Overflow, but I’ve had clients (startups) who wanted to put IDs in the URL, when you sign up and your URL is /profile/432 it’s quite clear the product doesn’t have many users.

An easy solution to that is simply not to start at zero/one and pick a random number somewhere around 5-6 digits. Can do that at the database level or programatically.

It's still easy for anyone to work out how fast you're gaining users. Just sign up for an account every day and note how much higher the new id is.

you can also add zeroes (or other digits which get stripped before used for querying) at the end, that way the gain looks orders of magnitudes faster

Sure, but anyone signing up for 3 accounts in quick succession can probably figure out what you're doing.

All of these problems go away if you just assign random numbers instead.

Why would honest people want to spend time implementing ways to lie to their users? Because that's what we're talking about here.

(And I know I'm asking a rhetorical question.)

Assigning random ids isn't lying to your users, unless you also make representations that the user id corresponds to the number of users that there are.

While there might be some edge cases (e.g. startups associated with a high profile brand/person), in most cases there are usually better things for a startup to do than obfuscate the number of users they're gaining.

A slightly more advanced version of this to XOR the number with some other number.

Simply doing XOR on two integers doesn’t really provide meaningful obfuscation if one of the inputs are constant as I assume they would be in this case.

For example:

    for i in range(25):
       167535774 ^ i

    167535774
    167535775
    167535772
    167535773
    167535770
    167535771
    167535768
    167535769
    167535766
    167535767
    167535764
    167535765
    167535762
    167535763
    167535760
    167535761
    167535758
    167535759
    167535756
    167535757
    167535754
    167535755
    167535752
    167535753
    167535750

    for i in range(250000,250025):
       167535774 ^ i

    167752718
    167752719
    167752716
    167752717
    167752714
    167752715
    167752712
    167752713
    167752710
    167752711
    167752708
    167752709
    167752706
    167752707
    167752704
    167752705
    167752766
    167752767
    167752764
    167752765
    167752762
    167752763
    167752760
    167752761
    167752758

At that point your requirements seem to require a number with no pattern, so it should probably just be a random unique number or string. But for a lot of use cases the ability to measure growth or enumerate URLs doesn't matter.

    ALTER SEQUENCE users_id_seq RESTART WITH 54321;

"Wow, this service is probably mature, well-tested and reliable!"

Note that there is a legitimate reason to set the initial sequence in this way: oftentimes the system works fine with 31/32-bit numbers but not with larger ones. Starting with the large sequence number (we have frequently used 10 billion) proactively eliminates this problem.

And then come back a week later to see that 500001 => 500004...

The number of users who notice the user ID is a small fraction of your total users. The number of users who would notice the ID didn't grow much over the week is a small fraction of that fraction.

Namely, cour competition that tries to figure out what to do about you or whether to even care, yes.

Exactly. I have done this in research on competition many times.

This is why I always obfuscate IDs using Hashids: https://hashids.org

Hashids are slow though

Just use GUIDs instead.

And if you want to keep using integer IDs internally, put the GUIDs into an alternate key.

GUIDs are really unreadable and long. You can read and remember some numeric ID, but GUID/UUIDs – totally impossible. I hated them after Microsoft COM.

I'm not sure I need "readable and short" to be qualities of my unique identifiers. I copy/paste and UUID/GUID I come across and if you have foreign keys setup you can jump around the DB in most GUI clients with ease, never even having to copy/paste. Furthermore when I'm talking with my coworkers we always just read off the 2nd "set" of numbers/letters in the UUID. In the example of `c495d443-cc7d-4030-840c-8f7e4e0289c3` I will just say "cc7d", 99.9% of the time that is enough to uniquely identify a UUID when we are only dealing with a handful of them and want to pick one out by eye quickly. For anything else I just send them a chat with the UUID and they can query on it, grep, or find it in the results with Ctrl+F or whatever their tools support doing.

Pretty sure the last 10yrs of stackoverflow proves that this is not a concern.

I'd file this under: things to not worry about.

Sequential numeric ids leak information regarding your operations.

https://en.m.wikipedia.org/wiki/German_tank_problem

This is exactly right, but in my opinion usually not important. If Joe Public, or a competitor, or whoever knows how many posts you have, users you have, administrators you have, etc how much does that actually affect your operation? I guess they can better compete against you in some small way with that information, but in my opinion it's almost never worth the effort to obfuscate. It's like optimizing for a problem you don't have.

> how much does that actually affect your operation?

If it's a business service then the operation is not the end goal but the means to the end.

If it's a business service then the question you should be asking is "should I care that my competitors are able to observe and monitor my service metrics?".

Quite often, businesses do care a lot.

Looks like Hacker News does this too:

https://news.ycombinator.com/item?id=1

That can be either good or bad depending on situation. There are plenty of cases where concise sequential identifiers are an advantage.

>where concise sequential identifiers are an advantage

For example, certain kinds of attacks are helped with sequential IDs. Wasn't there an AT&T "hack" where the attackers iterated URLs over sequential account numbers to grab nearly all data? Would've been nigh impossible to perform with UUIDs as identifiers.

About the only use case I can think of where sequential IDs are helpful is using MAX(PRIMARY KEY) to roughly estimate total number of records. And that's only relevant where there's special cased support for MAX(PRIMARY KEY). Think MySQL, but not PostgreSQL. And that's also subject to the over-estimation due to holes left in the sequence, whether due to deleted records, or backed off transactions.

Sequential integer IDs have some advantages over GUIDs at the database level. Integers are smaller (and cache friendlier!), while sequential values generally improve the fill factor of B-Trees (the DBMS can special-case the insertions that match the B-Tree key order, e.g. Oracle does a 90/10 index block split).

On the other hand, they can cause latch contention on INSERT-heavy tables (because of the competition for the last B-Tree leaf), though this is not common.

You raise valid points, but there's a hidden cost: if you decide to use slugs for URLs, or any identifier other than the Primary Key, you need to perform one extra step of mapping. Either one extra round-trip to the DB, or one extra join, or condition on a non-PK field, with, presumably, the extra index, that in turn needs to be maintained.

Not the worst thing to happen, but at any serious scale you take that into account.

OTOH, storage for binary UUID representation is just 16 bytes. You get non-sequential big big ints. Possibly use UUID v1, with sequential (time based) trailing part.

> if you decide to use slugs for URLs

I agree that using slugs alone is probably not a good idea (simply because they are not stable). They are there to make the URLs friendlier to search engines, but are strictly redundant (and therefore safe to ignore during URL request routing).

> any identifier other than the Primary Key

Well, an index can be covering, for the price of more storage and more expensive index maintenance.

But even if the index is not covering, the additional ROWID lookup (or clustered index seek) is likely to be so fast as to be drowned-out by other factors.

> OTOH, storage for binary UUID representation is just 16 bytes.

Times number of rows in that table and every other table that references it, which may or may not be significant.

>>OTOH, storage for binary UUID representation is just 16 bytes.

>Times number of rows

Still merely twice as much as typical int64 PK, which isn't terrible. Meanwhile - unless the programmer has been exceedingly careful - much more space is wasted on column (field) alignment in records.

Frankly I'd sooner worry about PK index size here; the larger, the more I/O is necessary on average for look-up, for insertion, and for maintenance tasks. RN I don't remember if typical BTree index size is strictly proportional to field size, or if it's optimized away to any extent, perhaps via prefix coding.

> much more space is wasted on column (field) alignment in records

Hmmm... I don't think there is much alignment going on within the data pages. Efficiently packing data is a matter of avoiding excessive I/O and I can't imagine databases were not carefully optimized in that regard, especially at the time most of them were invented (with less than a hundred I/O operations per second on that hardware).

Plus the DBMS is free to rearrange the physical order of fields as it sees fit (e.g. MS SQL Server coalesces boolean fields into a bitmap).

But I'm not an expert on physical row structure, so I might be pulling this out of my backside. :)

> Frankly I'd sooner worry about PK index size here;

Yes. And how many indexes are there.

> if typical BTree index size is strictly proportional to field size

Also the fill factor (i.e. how much free space is left in the B-Tree nodes after splitting). Sequential values can have a big advantage here due to 90/10 optimization I mentioned earlier.

BTW, some DBMSes can encode integers in variable format, so the actual values matter, not just the nominal "column width". The same is of course true for varchar, but also for char (typically). The char(n) is just a logical concept, it doesn't require n characters of storage unless the value is actually n characters wide (unless you are still working in Clipper ;) ).

> or if it's optimized away to any extent, perhaps via prefix coding

Prefix compression is supported in some DBMSes but not others (e.g. Oracle does it via CREATE INDEX ... COMPRESS).

>> much more space is wasted on column (field) alignment in records

>Hmmm... I don't think there is much alignment going on within the data pages.

Depending on the RDBM engine, the atom fields (INTs, FLOATs, etc.) in storage are aligned to multiplies of their sizes. This also applies to fields that are stored as pointers to external data - TEXT, BLOB, etc. get stored like that; either in all cases, or when content length exceeds a threshold.

Not sure why that's done, but probably to help the CPU handle data read/update at full speed and with atomic operation. Or perhaps just to make the format portable to platforms that expect memory accesses to be aligned.

Example: given a record of (INT8, INT64), common RDBMs will waste 7 bytes - padding the first field (INT8) so that the second field (INT64) is aligned to its natural alignment. Thus the physical order of columns matters. And thus my remark on "exceedingly careful". Example for PostgreSQL: typalign [0]

MySQL and PostgreSQL diverge here: when you re-order columns, MySQL normally adjusts only the map of fields, keeping the physical structure unchanged, while PostgreSQL doesn't[1] support such quick change; you either add the new column(s) at the end of record, or re-write the whole table from scratch. This may seem more cumbersome, but gives explicit control over the record's physical format.

[0] https://www.postgresql.org/docs/current/catalog-pg-type.html

[1] last time I checked was 9.x, may have changed since then.

It would have been nigh impossible if they actually had security and didn't return data to users that should be able to access it.

Fully agree. For example, it's very handy that GitHub issues are auto incrementing integers starting at 1, so that I can type "Fixes #123" in a commit message instead of "Fixes #<some-uuid-or-something>".

Of course, that short numeric ID doesn't necessarily need to be part of the DB's primary key.

Is there any best practice on implementing an ID field that starts at 1 for each repository (in GH's example)?

If you're using a UUID you can just use whichever huge integer your UUID represents and then increment from there so the records are all sequential 128bit numbers.

I guess it would be pretty easy in postgres to create a sequence per repository and have the field use the next value in the sequence (i.e. like it normally does for auto incrementing IDs but where you'd specify the sequence to use at insert time).

Thanks, I'll have a look!

How would one go about implementing user scoped autoincrement fields efficiently in modern databases anyway? Triggers?

pretty cool how the urls stayed the same:

http://stackoverflow.com/questions/13204/why-doesnt-my-cron-... (still works)

http://stackoverflow.com/questions/21064/massive-reputation-... ("this question was removed from stack overflow for reasons of moderation", still available: https://i.imgur.com/Csy94IL.png)

this seems backward, as far as the pk remain meaningless they're easier to port around, not harder. it's once you give them a meaning then trouble starts (for example you can't change the slug down the road if the title changes)

and with integer keys the worst that can happen is that the sequence is not in the backup so after a restore you need to have the p sequence restart from the previous max id + 1; meanwhile with slug built off user entered data a change in collation can really screw up your day, unless you want to mangle all texts into basic ascii, and SO doesn't https://stackoverflow.com/questions/41102371/sql-doesnt-diff... (see the ü in the link more than the question itself)

"URIs in Stack Overflow look like this:

http://stackoverflow.com/questions/13204/why-doesnt-my-cron-...

Article gives only cons. What are the pros?

https://stackoverflow.com/q/13204

Shorter, easier for users to work with. Could be even shorter with a domain hack. Does Stack Overflow have one?

https://en.wikipedia.org/wiki/Domain_hack

http://xona.com/domainhacks/s.cgi?q=stackoverflow.com

They have s.tk but it's mostly used for internal/intranet links: https://meta.stackexchange.com/q/109795/134300

I don't get this argument at all.

If the ID comes from something like a SERIAL column, then yes, you can port those easily. I think every database system has something like SERIAL, as well as the ability to control an initial value, taking care of portability concerns.

SERIAL has other problems, namely predictability, so another possibility is UUIDs. And those should be pretty portable too. I know DBMS UUID implementations are different from OS implementations, but the risk of collision should still be vanishingly small.

And if you don't use IDs, then what's the choice? If the URI contains an article name then what about article names being changed? What about duplicate article names?

I can't believe people have been giving this post this much credit in the first place. All of his contrived examples are solved with trivial methods and it just sounds like he doesn't know how to migrate data from one database system to another.

The issue I see with searching by slugs is thats its slower than searching by integer, and its more likely to be entered incorrectly by users when typing a url.

You also can not change the slugging algorithm if you want to keep bookmarks valid

I don’t agree it’s slower in a practical sense. If your service is backed by a relational database like PostgreSQL then you’d have an index on the column you’re looking up. So a request involves network round-trip latency, parsing the request and preparing the response, and searching for the object in the database using the index, most likely a tree with a depth of 3 or 4 levels. I don’t think the number of characters in the key, or the data type, will play a big role here. Happy to see evidence to the contrary though if I’m wrong!

The algorithm flexibility might not be a big problem either. The old slugs are stored in the database. Update the algorithm, new slugs adhere to the new algorithm, old ones unchanged, everything still works.

1) Indexing text vs indexing integer it's very different when it comes to memory/space required by the database.

2) There is already an index on ids assuming they are used as primary keys, why adding another index (on text)?

3) To support changing slugs (titles) you will have to keep all the old slugs as well (to redirect them to their new versions). With ids you don't have to do this - ids have no reason to change.

In the end you might not feel the difference in execution time, but hardware requirements for servers...

I agree that they are annoying, but when the only reason against them is to improve URLs aesthetics (assuming they don't pose a security/information disclosure risk) - the trade of performance, hardware requirements and extra code needed is not worth it IMHO.

> 3) To support changing slugs (titles) you will have to keep all the old slugs as well (to redirect them to their new versions). With ids you don't have to do this - ids have no reason to change.

Which basically means a join in the database. While not terribly expensive, its a lot more involved than a simple column value check.

When talking about public pieces of information, being able to refer to them by their identity is useful and any hashing would be counterproductive (just wasting power), also, I'm sure any database can operate on unique, incrementing numbers more quickly than uuids. If we're talking about private information, it should be transferred in another way than the url in the first place, yes, the chance of "guessing" a hash is lower than an incrementing number, but there are other ways it could be intercepted, a screenshot, accidential copy/paste for example.

Say I'm convinced that IDs could possibly leak information, but UUIDs are problematic (e.g. cost of enforcing uniqueness grows with each added row). Would encrypting the ID with some symmetric key plus a salt be sufficient? The client sees a number where the ID can be recovered on the server side, but which the benefits of having an easily generatable unique identifier while making it challenging to recover the actual ID).

If you want to estimate the total # of items in a DB based on auto-increment ID numbers, you can use: https://en.m.wikipedia.org/wiki/German_tank_problem

(Apparently this was used by militaries in WW2, to estimate the total number of tanks from the serial numbers printed on them.)

What does he propose I do if two questions map to the same slug or if - beware - someone wants to edit their question?

I worked with a customer that was concerned about the "security" of database IDs in the URL. I though their solution was novel: they chose a random long integer whenever a new row was created. It's fast like an integer and random like a UUID, the best of both worlds!

Genuine question: What's the proposal for items in an API which have no concept of a "slug"? We could generate a separate mapping of "public ids" to the database internal ones, but I don't even see how that would solve any of the issues here.

I'm not a database expert, but concepts like surrogate and natural keys come to mind. A unique slug is a natural key, but for most, if not all, items, you can come up with a natural key by combining a few bits of data (I've seen name * date of birth used by doctors in general practice, for example). Often there's a pre-existing unique code external to the app which can be used, like a driver's licence number or something like that. That removes the need for a surrogate key in the database.

The tricky case is something like email drafts: it's possible for a user to create and then save a draft with no addressee, subject or content. In that case there is little alternative but to make the system use a surrogate key (a database internal key) because the other possible unique identifying values (e.g. time of creation of the draft) are sort of unsatisfactory, because they don't really have a strong, meaningful connection with the content of the item.

And then the question gets updated and you either have a broken URI, or a slug that doesn’t match the content.

I’ve never heard of IDs being something mutable. When would that ever happen?

This is bullshit. It's absolutely necessary for the user to be able to refer to the resource by an ID of some sort. A slug is not an ID.

One reason would be that the slugs are not guaranteed to be unique - they're based on user-entered post titles.

Better blog title: "Do not use INTEGER PRIMARY KEYs in URIs" or "Do not use ROWIDs in URIs".

It's a good thing™ Stack Overflow doesn't have those IDs in the URI anymore.

>of Atwood's calibre

What was Atwood's calibre at that point? (pre StackOverflow)

Coding Horror began in 2004, that's where I knew him from.

You should not expose any internal identifier (numeric or otherwise) because it then becomes public & part of your API (even if undocumented).

In one case the api I worked on used encrypted IDs on the way out to the client, and decrypted them on the way back to the db. Even when multiple clients referred to the same resource, they would see different public IDs, but any instance of the API could reach the underlying value from those public values.

Uuids

I like the way medium.com deals with IDs, 10 random hex chars (i.e. > 10^12) put at the end of the slug are more than enough for most use cases that can be addressed via a url and still clean for search engines and humans.

But also the way letterboxd and also quora deal with URLs is admirable but it needs special care (at least in the case of quora)

Reply on news.ycombinator.com