8 ms·

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/re

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

dexen 7y ago
>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.
coldtea 7y ago
>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.
- thecatspaw 7y ago
  > 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.
  - coldtea 7y ago
    For one, they expose the number of users and other internal details. Even that is a security concern, access or not.
    - adjkant 7y ago
      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.
      - kadoban 7y ago
        At the point where you're doing all of that obfuscation, wouldn't it be easier and safer to use something like a uuid?
michaelt 7y ago
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.
- TeMPOraL 7y ago
  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 https://www.sqlite.org/rowidtable.html.
  - Zancarius 7y ago
    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 https://www.sqlite.org/autoinc.html
yetihehe 7y ago
> 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.
- esonderegger 7y ago
  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.
  - yetihehe 7y ago
    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.
skybrian 7y ago
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-reuse-deleted-ids-when-auto-increment-is-applied https://stackoverflow.com/questions/18692068/will-mysql-reus...
saltcured 7y ago
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.
- rlucas 7y ago
  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.
  - saltcured 7y ago
    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.
cryptonector 7y ago
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...
- wvenable 7y ago
  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.
- derefr 7y ago
  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 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.)
  - cryptonector 7y ago
    The slug has to be computed at INSERT time (e.g., by a trigger) and not allowed to change thereafter.
    - aflag 7y ago
      It needs to change if the title changes, though.
speedplane 7y ago
> 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.

dexen 7y ago

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

coldtea 7y ago

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

thecatspaw 7y ago
> 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.
- coldtea 7y ago
  For one, they expose the number of users and other internal details. Even that is a security concern, access or not.
  - adjkant 7y ago
    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.
    - kadoban 7y ago
      At the point where you're doing all of that obfuscation, wouldn't it be easier and safer to use something like a uuid?

michaelt 7y ago

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.

TeMPOraL 7y ago
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 https://www.sqlite.org/rowidtable.html.
- Zancarius 7y ago
  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 https://www.sqlite.org/autoinc.html

yetihehe 7y ago

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

esonderegger 7y ago
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.
- yetihehe 7y ago
  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.

skybrian 7y ago

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-reuse-deleted-ids-when-auto-increment-is-applied https://stackoverflow.com/questions/18692068/will-mysql-reus...

saltcured 7y ago

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.

rlucas 7y ago
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.
- saltcured 7y ago
  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.

cryptonector 7y ago

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

wvenable 7y ago
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.
derefr 7y ago
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 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.)
- cryptonector 7y ago
  The slug has to be computed at INSERT time (e.g., by a trigger) and not allowed to change thereafter.
  - aflag 7y ago
    It needs to change if the title changes, though.

speedplane 7y ago

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