Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Keys in Depth

begriffs.com

Post reply on HN
Print view View original

71–80 of 174 posts

Re: SQL Keys in Depth

#71

Post by chrisconroy »

barrkel wrote: post #66
foreigner wrote: post #33

At what scale does all this stuff start to actually matter? I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems. We use auto-incrementing integers as primary keys and try not to expose them externally. Indexes are added as necessary to enable specific queries. We don't enforce any other constraints (e.g. not-null or foreign keys) at the database level. ...…

I've seen auto_increment fail to scale on MySQL. Large, long-running insert statements (e.g. using insert into ... select from, or load data infile) can lock the the table resource and block concurrent inserts. Pretty easy to work around: have a table containing the next available key for every table, and use a separate small transaction to bump it before bulk inserts. Another reason not to use auto-inc is if you nee…

auto_increment doesn't lock the table. When you use an auto_increment column, MySQL will grab the next int as it creates the insert write-ahead log message. Two concurrent transactions with T1 beginning first and T2 beginning second but actually committing out of order can thus have out of order ids. e.g. T2(id=10) T1(id=9)

Also, note that this means auto_increment IDs are not continuous (read: a reader looking after T2 commits but before T1 will see a gap, and if T1 fails that gap is permanent!)

Re: SQL Keys in Depth

#72

Post by pc86 »

onion2k wrote: post #26
simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

It's also worth noting that the same is true for email addresses. People share them, so two people can have the same address.

While technically accurate, this is non-standard to such a degree, and represents such a minority percentage of the population, that "don't do this" really is an appropriate response to the user.

Re: SQL Keys in Depth

#73

Post by adambatkin »

foreigner wrote: post #33

At what scale does all this stuff start to actually matter? I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems. We use auto-incrementing integers as primary keys and try not to expose them externally. Indexes are added as necessary to enable specific queries. We don't enforce any other constraints (e.g. not-null or foreign keys) at the database level. ...…

I disagree that UUIDs are generally preferable over integers. For one, they take up more space (on disk and in memory). And for something like a key, it is likely that there will be multiple copies of that value stored, since it will exist in the table itself, at least one index (possibly more) and foreign keys. More space means fewer records per page on disk, more I/O and more memory usage (potentially leading to more I/O). I would wager that for most users (including this case), the cost of this additional I/O is far greater than some theoretical scalability limitation on generating IDs.

Most database vendors make sequence generation (whether through explicit SEQUENCE objects or auto-incrementing columns) performant by making a few compromises:

* Numbers may not always be sequential (you might get 1, 3, 2 - in that order)

* There may be gaps (you might get 1, 2, 5, 6)

But since these are supposed to be opaque identifiers, neither of these compromises should be a concern for most users. But this means that these sequences can live outside of a transaction (you might grab an ID, rollback, and that ID is gone) and that systems with multiple nodes can be allocated a "block" of numbers from which they can quickly pull new values, without needing to coordinate with a master node.

Re: SQL Keys in Depth

#75

Post by laumars »

simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

That's not the only point of the article I really disagree with:

> Here are some values that often work as natural keys:

> login names

This makes user names static. Which would annoy people if they change name (eg marriage, gender change, nationalization change (Chinese name vs English name)) or they just want to update their online handle.

> email addresses

Same problem as above. What happens if someone wants to change email addresses (eg they used a work one and changed jobs, or they got a new email to combat spam on the old one, or a new email address to reflect a name change (see above))

> mac address on a network

This makes more sense than above, but MAC addresses can be spoofed or even just used as a proxy, so you would need to be careful that the information you're uniquely storing against the MAC address is intended to be unique. Most use cases that immediately springs to my mind wouldn't follow his unique rule but I'm sure there will some examples that do.

> (lat,lon) for points on the earth

That might work if you also included an elevation point as well but office and apartment blocks (for example) will often have different tenants at the same latitude and longitude coordinates.

> Some people seem to struggle with the choice of “natural” key attributes because they hypothesize situations where a particular key might not be unique in some given population. This misses the point.

Actually no it doesn't. That's entirely the point as it demonstrates good planning and future-proofing rather than setting arbitrary business rules that are hard to rectify if and when you do run into those particular edge case scenarios.

Re: SQL Keys in Depth

#76

Post by barrkel »

chrisconroy wrote: post #71
barrkel wrote: post #66

Earlier quoted context omitted.

I've seen auto_increment fail to scale on MySQL. Large, long-running insert statements (e.g. using insert into ... select from, or load data infile) can lock the the table resource and block concurrent inserts. Pretty easy to work around: have a table containing the next available key for every table, and use a separate small transaction to bump it before bulk inserts. Another reason not to use auto-inc is if you nee…

auto_increment doesn't lock the table. When you use an auto_increment column, MySQL will grab the next int as it creates the insert write-ahead log message. Two concurrent transactions with T1 beginning first and T2 beginning second but actually committing out of order can thus have out of order ids. e.g. T2(id=10) T1(id=9) Also, note that this means auto_increment IDs are not continuous (read: a reader looking after…

I'm not talking about locking the table. I'm talking about locking a table resource.

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.htm...

Quote:

"While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column. In accessing the auto-increment counter, InnoDB uses a specific AUTO-INC table lock mode where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction. Other sessions cannot insert into the table while the AUTO-INC table lock is held; see Section 14.5.2, “InnoDB Transaction Model”. "

If you have a long-running statement, it can block concurrent transactions. I've seen it specifically with 'load data infile', IIRC. We had to go through some painful migrations to remove auto-increment on some fairly large tables when we started seeing this.

Re: SQL Keys in Depth

#77

Post by Jwarder »

planetjones wrote: post #24
simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

Completely agree. Adding a proper primary key e.g. from a sequence involves very little extra overhead and protects against this. Asking them to drop the constraint in the future sounds a bit silly as that may mean two members get deleted when you intended only one. Plus your foreign keys now have to be maintained. So if a member changes their name you have to update all foreign keys. Also if the hobbyist club then b…

To expand a little on your last point: one of the guide lines I try to follow is expect your system to be successful. If your club is popular then it will eventually have two people with the same name. Don't waste people's time by putting in artificial constraints that will eventually become invalid.

Re: SQL Keys in Depth

#78

Post by marcosdumay »

tome wrote: post #40
hug wrote: post #21

Earlier quoted context omitted.

I mean, maybe I am, maybe I'm not. How many other languages from the '70s haven't been replaced in a domain where they should have been? And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-val…

> And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-value worth mentioning. Do you believe that all keyboard layouts are of indistinguishable practicality? That seems to be the inescapable c…

Again, can you name any study that found a benefit with a non-laughable p-value?

If nobody can, then one just has to conclude they are indistinguishable.

Re: SQL Keys in Depth

#79

Post by tome »

marcosdumay wrote: post #78
tome wrote: post #40

Earlier quoted context omitted.

> And, I mean, maybe your example isn't really the best example you could have given. Name a layout quote-unquote more practical than QWERTY that should have caught on but didn't. Dvorak? No faster. Colmac? Nah. Any, y'know, studied benefits? None I can find, at least not with a P-value worth mentioning. Do you believe that all keyboard layouts are of indistinguishable practicality? That seems to be the inescapable c…

Again, can you name any study that found a benefit with a non-laughable p-value? If nobody can, then one just has to conclude they are indistinguishable.

I'm not asking what hug concludes, I'm asking what he/she believes. What we believe and what we scientifically conclude can be quite divergent things.

Re: SQL Keys in Depth

#80

Post by dullgiulio »

ProblemFactory wrote: post #32
dragonwriter wrote: post #8

Earlier quoted context omitted.

> Unfortunately, current DBMSs provide little to no support for properly implemented surrogates. UUIDs instead of per-table autoincrement keys seem to be a solution which covers the part of the problem that involves DB support as opposed to data model design, and several DBs have adequate support for UUIDs.

UUIDs (and other mostly random IDs) also have the important advantage that they can be generated offline - in mobile apps, client-side javascript or elsewhere. With autoincrement keys, you need to either contact the database server for each data object you create, or implement complex logic to replace temporary IDs with database IDs later.

Nope, you can't. You still have to check for uniqueness on the server side before committing them, so you are back from the start.

Always keep in mind that anything coming from the client cannot be trusted.

Post reply on HN