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

91–100 of 174 posts

Re: SQL Keys in Depth

#91

Post by extrapickles »

adambatkin wrote: post #73
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 mo…

UUIDs are the best choice when developer time is more important than space usage. Also, they can be generated and used by the client when the connection to the db is frequently down (eg: clients store/query data locally in SQLlite and replicate to master).

Re: SQL Keys in Depth

#92

Post by mohaine »

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

No constraints will often be faster, but your data will not be 100% consistent.

Over time you _will_ have child child records pointing to non existing parents and the same with foreign keys. These often don't really hurt anything, until they do. Since most of the testing is with clean data, most issues due to bad data are in production and reported by end users.

I've worked successfully on systems without any real constraints and it is usually much better to start with and remove them later as needed. Adding them later is a pain as you first have to clean up the data and fix the code issues that created the bad data in the first place.

Re: SQL Keys in Depth

#93

Post by laumars »

Sean1708 wrote: post #88
laumars wrote: post #75

Earlier quoted context omitted.

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 cha…

>> login names > This makes user names static. Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.

If i understand the article correctly it's discussing using natural keys as primary keys. Eg if you're using login_name as a natural key then that's presumably replacing a more traditional user_id. So it would be hard not to use login_name as a foreign key in that scenario.

Where as if you still had a user_id as your primary key, you could still have user_id as a unique key (most RDBMS I've used support "unique keys") to enable the business logic discussed but without encouraging it's usage as a foreign key.

I think the article did touch on the fact that you can have multiple keys, but given it's heavy emphasis on finding natural keys over the more traditional logic of incremental integers, I can only assume the point of it wasn't about additional unique keys but rather alternatives for primary keys.

Re: SQL Keys in Depth

#94

Post by jeltz »

barrkel wrote: post #76
chrisconroy wrote: post #71

Earlier quoted context omitted.

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 w…

That is an odd design. PostgreSQL only holds the lock long enough to increment a counter in memory, and every 32th time also write a write-ahead log memory in RAM. I can't see why one would need to lock the counter for the duration of the query.

Re: SQL Keys in Depth

#95

Post by IgorPartola »

LeonM wrote: post #44
danielovichdk wrote: post #42

Funny example in regards of a card-deck. How would you have unique keys for the 3 jokers in the deck ?

A joker does not have a suit, nor a number, so it would not fit in the example table anyway.

Represent the suit as J and number them sequentially? Also not all games use jokers.

Re: SQL Keys in Depth

#96

Post by openasocket »

Sean1708 wrote: post #88
laumars wrote: post #75

Earlier quoted context omitted.

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 cha…

>> login names > This makes user names static. Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.

I agree, I can't think of any system off the top of my head that lets you change your handle, or why you would really want to. If it's a forum or has commenting capabilities, your handle is your absolute identity. If it's not a forum, no one ever really sees your username, so there's little motivation to change it.

Re: SQL Keys in Depth

#97

Post by luhn »

extrapickles wrote: post #91
adambatkin wrote: post #73

Earlier quoted context omitted.

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 mo…

UUIDs are the best choice when developer time is more important than space usage. Also, they can be generated and used by the client when the connection to the db is frequently down (eg: clients store/query data locally in SQLlite and replicate to master).

UUIDs are a great option for creating IDs offline, but I don't understand your first comment: How do UUIDs optimize for developer time? In my experience, autoincrementing integers are the easiest choice for primary keys.

Re: SQL Keys in Depth

#98

Post by mannykannot »

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

You might not have any observable effects until the day you do, at which point you may be faced with an arbitrarily large problem.

What you are doing is passing up on the opportunity of catching various errors (you are also passing up the opportunity for some optimizations, but that is probably a secondary issue.) In particular, you are passing up on some opportunities to catch inconsistencies in how different applications (or different parts of the same application) create and use data.

One argument made against putting these sort of rules in effect is that they constrain what application developers can do, but that is the wrong way to look at it: any such conflict is an indication of a misunderstanding (not necessarily on the part of the application developers) that has been caught before it can lead to bigger problems, such as a database full of irretrievably inconsistent or incomplete data.

When the problems finally do arise, it is often the case that some sort of workaround is the only practical solution. This, in my experience, is one of the common ways by which systems accrue gratuitous complexity, which in turn has at least two real-world consequences: an increased time to make changes, upgrades and extensions, and an increased frequency of errors, especially WTF-type errors (and probably also efficiency/performance hits.)

Re: SQL Keys in Depth

#99

Post by laumars »

openasocket wrote: post #96
Sean1708 wrote: post #88

Earlier quoted context omitted.

>> login names > This makes user names static. Does it? I can understand why making it a foreign key would make it static, but why would making it a normal key make it static? It seems to me that making login names unique would be preferable, same with emails.

I agree, I can't think of any system off the top of my head that lets you change your handle, or why you would really want to. If it's a forum or has commenting capabilities, your handle is your absolute identity. If it's not a forum, no one ever really sees your username, so there's little motivation to change it.

Twitter does (though I don't know of anyone who has). As does Google and Facebook. Github too.

Away from web services, gaming platforms like Nintendo's Switch allow for user name changes. As does the more enterprisey databases like ActiveDirectory (Windows logins), ldap and Linuxes / Unix passwd table also allows for login name changes.

To be honest, I've encountered more systems that do let you change your user name than systems that don't.

Re: SQL Keys in Depth

#100

Post by txutxu »

daenney wrote: post #62
txutxu wrote: post #35

Great article. But MAC address cannot be considered unique, as soon as software like keepalived is running.

OK. But how does that relate to this article? If you're using keepalived what you're doing is moving the connection from one instance to another if the current one is detected as being dead. Even if only the MAC was used to generate the UUID (which it is not, timestamp is factored in too) I struggle to find a scenario in which this would be a problem. Even if you time it down to the nanosecond, if keepalived is used…

The article list the MAC address as a natural unique key

    Here are some values that often work as natural keys:

    [...]
    mac address on a network
    [...]
that was the reason for my comment.
Post reply on HN