6 ms·

The solution proposed by this article (using MD5 hashes instead of numeric identifiers for primary keys) omits the point that strings are way more expensive to

by dpifke 17y ago

The solution proposed by this article (using MD5 hashes instead of numeric identifiers for primary keys) omits the point that strings are way more expensive to look up than integers. You're creating a bunch of extra work for the database on every single lookup. A better solution is to use a global sequence rather than per-table sequences. That way your primary key is still numeric (and fast). A curious onlooker can guess how fast your database is growing, but they don't know whether that growth is users, orders, log messages, etc. etc.

jeffiel 17y ago
dpifke, you're right that it's definitely slower and more space consuming to index and query 32 character strings vs. ints. However, it's probably inconsequential for most applications, especially if your queries are simple SELECTs and INSERTS, as most web apps are doing.
- tptacek 17y ago
  Pedantic: MD5 doesn't produce a 32-character string, it produces a 128 bit number. With full precision, you can represent it in 25 bytes base32. But since you don't need security, just uniformity and unpredictability, you can truncate that to whatever size you're comfortable with.
teej 17y ago
If you only use the first 4-8 characters of your md5 hash, can't you just convert that from hex to base 10 and get an int?
- dpifke 17y ago
  You could, but then you need to implement collision avoidance when creating new records. Database sequences are guaranteed to be unique.
edw519 17y ago
"omits the point that strings are way more expensive to look up than integers" So what? We're talking microseconds. I take it a step further than OP. Every data base record has 2 IDs, an internal sequential one and an external one. The external one is cross-referenced to the internal one, which is never seen by human eyes. So every read is actually 2 reads, which is way more expensive still. I have been doing this for years and have never seen any noticeable degradation. The reason I started this has nothing to do with security. It's so that I can change any key on any data base at any time without actually changing the primary key and without a conversion. (You'd be surprised how often you need to do this in commercial applications.)
- dpifke 17y ago
  Using database OIDs as publicly accessible identifiers is a neat idea, one I haven't tried. Looking at the Postgres docs (http://www.postgresql.org/docs/8.0/static/datatype-oid.html http://www.postgresql.org/docs/8.0/static/datatype-oid.html) they discourage the use of OIDs, going so far as to say the default in future versions is not to create OIDs for user-created tables. Also, this would mean that a dump/restore of your database results in invalidating all your identifiers. What RDBMS do you do this on and have you run into any problems? EDIT: Sure enough, in modern Postgres, these are disabled by default. Additionally, they appear to be sequentially assigned: dev01=> select *,oid from test; test_id | test_string | oid ---------+-----------------+------- 1 | blah | 29837 2 | blahblahblah | 29838 3 | xxxblahblahblah | 29839
  - edw519 17y ago
    I implement double IDs in my own code, not in any built-in function of any DBMS. You're right to be suspicious.
- jasonkester 17y ago
  You can get around this by simply having a 2nd way to reference that single integer ID. I've built systems in the past where we simply gave out base26Encode(ID+20000) whenever an ID was desplayed on the URL, which gives a nice typeable 4-digit string like "6cw8". Pulling up a record, you'd simply check whether you were looking at an integer and if not, (base26Decode(key)-20000) and you're in business. It doesn't need to be rocket science crypto stuff. After all, it's just obfuscation to confuse the casual observer.
mindhacker 17y ago
Please correct me if I am wrong but doesn't using a global sequence mean all the threads wanting to insert a record have to queue up and wait for the db to provide the auto increment id? I am not sure what time this takes but may be it creates a bottleneck for really large, write heavy applications.
- russss 17y ago
  Most reasonable databases do this very quickly. Postgres specifically can hold a cache of a number of sequence values on a per-process basis so each insert doesn't have to acquire a global lock on the sequence. The disadvantage of this is that sequences in Postgres will have gaps in (as sequence ids aren't reused if transactions are rolled back). That said, one good reason why centralized global sequences are not ideal, especially in very large systems where consistency is not paramount, is that they tie you to a single point of failure. In those cases it's better to implement a distributed sequence generator (of which GUIDs are probably the simplest type).

by dpifke 17y ago

jeffiel 17y ago

dpifke, you're right that it's definitely slower and more space consuming to index and query 32 character strings vs. ints. However, it's probably inconsequential for most applications, especially if your queries are simple SELECTs and INSERTS, as most web apps are doing.

tptacek 17y ago
Pedantic: MD5 doesn't produce a 32-character string, it produces a 128 bit number. With full precision, you can represent it in 25 bytes base32. But since you don't need security, just uniformity and unpredictability, you can truncate that to whatever size you're comfortable with.

teej 17y ago

If you only use the first 4-8 characters of your md5 hash, can't you just convert that from hex to base 10 and get an int?

dpifke 17y ago
You could, but then you need to implement collision avoidance when creating new records. Database sequences are guaranteed to be unique.

edw519 17y ago

"omits the point that strings are way more expensive to look up than integers" So what? We're talking microseconds. I take it a step further than OP. Every data base record has 2 IDs, an internal sequential one and an external one. The external one is cross-referenced to the internal one, which is never seen by human eyes. So every read is actually 2 reads, which is way more expensive still. I have been doing this for years and have never seen any noticeable degradation. The reason I started this has nothing to do with security. It's so that I can change any key on any data base at any time without actually changing the primary key and without a conversion. (You'd be surprised how often you need to do this in commercial applications.)

dpifke 17y ago
Using database OIDs as publicly accessible identifiers is a neat idea, one I haven't tried. Looking at the Postgres docs (http://www.postgresql.org/docs/8.0/static/datatype-oid.html http://www.postgresql.org/docs/8.0/static/datatype-oid.html) they discourage the use of OIDs, going so far as to say the default in future versions is not to create OIDs for user-created tables. Also, this would mean that a dump/restore of your database results in invalidating all your identifiers. What RDBMS do you do this on and have you run into any problems? EDIT: Sure enough, in modern Postgres, these are disabled by default. Additionally, they appear to be sequentially assigned: dev01=> select *,oid from test; test_id | test_string | oid ---------+-----------------+------- 1 | blah | 29837 2 | blahblahblah | 29838 3 | xxxblahblahblah | 29839
- edw519 17y ago
  I implement double IDs in my own code, not in any built-in function of any DBMS. You're right to be suspicious.
jasonkester 17y ago
You can get around this by simply having a 2nd way to reference that single integer ID. I've built systems in the past where we simply gave out base26Encode(ID+20000) whenever an ID was desplayed on the URL, which gives a nice typeable 4-digit string like "6cw8". Pulling up a record, you'd simply check whether you were looking at an integer and if not, (base26Decode(key)-20000) and you're in business. It doesn't need to be rocket science crypto stuff. After all, it's just obfuscation to confuse the casual observer.

mindhacker 17y ago

Please correct me if I am wrong but doesn't using a global sequence mean all the threads wanting to insert a record have to queue up and wait for the db to provide the auto increment id? I am not sure what time this takes but may be it creates a bottleneck for really large, write heavy applications.

russss 17y ago
Most reasonable databases do this very quickly. Postgres specifically can hold a cache of a number of sequence values on a per-process basis so each insert doesn't have to acquire a global lock on the sequence. The disadvantage of this is that sequences in Postgres will have gaps in (as sequence ids aren't reused if transactions are rolled back). That said, one good reason why centralized global sequences are not ideal, especially in very large systems where consistency is not paramount, is that they tie you to a single point of failure. In those cases it's better to implement a distributed sequence generator (of which GUIDs are probably the simplest type).