Re:Database Design Mistakes
by EGreg on Saturday July 20, 2013 @08:39PM
(#6077227)
Okay so what can I say ... from my experience I disagree with his conclusions for 3, 4 and 5.
3. If you are building any kind of table that you expect to horizontally partition, do NOT use sequential things like autoincrementing ids! The mutex lock will be your bottleneck. Instead, use this algorithm to generate ids:
10: id = GENERATE_UNIQUE_STRING(20)
20: IF ( SELECT_ROW_BY_ID(id) ) GOTO 10
30: INSERT_ROW_WITH_ID(id)
(yeah, it's written in BASIC just for fun)
20 lowercase alphanumeric characters gives you 26^20 combinations. And the chance of a clash becomes around 50% only after you have filled 26^10 ids (birthday "paradox").
If you really have to have a sequentially incrementing "ordinal" field, e.g. for messages posted to a stream in a specific order, then use transactions and lock only one row, in the related table, which will contain the max value of the field. For example in MySQL use InnoDB which implements row-level locking, and have stream.message_count field, which you use when you INSERT into the message table.
4. Again, when you are sharding you probably don't want to have foreign key constraints defined in your database layer. If this guy is shocked he must have not really done sharding before.
5. While you can use surrogate keys, that limits your horizontal partition to the way you generate these keys. Oftentimes you will want to look things up in your shards by a certain RANGE, and for that you want to preserve the ordering in your primary key (up to normalization).
3. If you are building any kind of table that you expect to horizontally partition, do NOT use sequential things like autoincrementing ids! The mutex lock will be your bottleneck. Instead, use this algorithm to generate ids:
20 lowercase alphanumeric characters gives you 26^20 combinations. And the chance of a clash becomes around 50% only after you have filled 26^10 ids (birthday "paradox").If you really have to have a sequentially incrementing "ordinal" field, e.g. for messages posted to a stream in a specific order, then use transactions and lock only one row, in the related table, which will contain the max value of the field. For example in MySQL use InnoDB which implements row-level locking, and have stream.message_count field, which you use when you INSERT into the message table.
4. Again, when you are sharding you probably don't want to have foreign key constraints defined in your database layer. If this guy is shocked he must have not really done sharding before.
5. While you can use surrogate keys, that limits your horizontal partition to the way you generate these keys. Oftentimes you will want to look things up in your shards by a certain RANGE, and for that you want to preserve the ordering in your primary key (up to normalization).
So tehre you have it, it's not that simple :)