5 ms·

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

by kozak 7y ago

dexen 7y ago
>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.
- branko_d 7y ago
  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.
  - dexen 7y ago
    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.
    - branko_d 7y ago
      > 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.
      - dexen 7y ago
        >>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.
        branko_d 7y ago
        > 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).
- ehutch79 7y ago
  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.
kevsim 7y ago
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.
- slig 7y ago
  Is there any best practice on implementing an ID field that starts at 1 for each repository (in GH's example)?
  - dexterdog 7y ago
    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.
  - kevsim 7y ago
    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).
    - slig 7y ago
      Thanks, I'll have a look!
- nly 7y ago
  How would one go about implementing user scoped autoincrement fields efficiently in modern databases anyway? Triggers?

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

by kozak 7y ago

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

dexen 7y ago

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

branko_d 7y ago
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.
- dexen 7y ago
  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.
  - branko_d 7y ago
    > 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.
    - dexen 7y ago
      >>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.
      - branko_d 7y ago
        > 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).
ehutch79 7y ago
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.

kevsim 7y ago

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.

slig 7y ago
Is there any best practice on implementing an ID field that starts at 1 for each repository (in GH's example)?
- dexterdog 7y ago
  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.
- kevsim 7y ago
  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).
  - slig 7y ago
    Thanks, I'll have a look!
nly 7y ago
How would one go about implementing user scoped autoincrement fields efficiently in modern databases anyway? Triggers?