111–120 of 174 posts
I wouldn't say "unique constraints considered harmful" but I would definitely say they can be surprising and must be used with care.
[1]: https://rcoh.me/posts/postgres-unique-constraints-deadlock/
After a decade of large systems relying on RDBMs, we now use 64-bit integers for all primary keys with a global Hi/Lo id generation system (app reserves a range of numbers on startup to assign to records automatically). This means plenty of ID space, maintains rough numeric ordering, allows ID creation without a roundtrip for every insert, is easily portable across different databases, and produces unique IDs for eve…
Literally the first Colum of almost every SQL table I have written has had a column called id with auto increment.
One thing to keep in mind with Postgres is that unique key indices can lead to deadlock and stall other transactions that insert the same key.[1] I wouldn't say "unique constraints considered harmful" but I would definitely say they can be surprising and must be used with care. [1]: https://rcoh.me/posts/postgres-unique-constraints-deadlock/
"There’s no need to manually create indexes on columns already declared unique; doing so would just duplicate the automatically-created index." Is the last part real? Makes me feel like forking postgesql just to save the world from accidental duplicate expensive indexes.
Earlier quoted context omitted.
That's not a bad option, but I think I'd find locally-unique integer IDs spread across tables to be a little confusing. In that case I think I'd lean towards UUIDs, which may be a little easier overall, or use separate sequences but expose IDs via Hashids in my API/frontend: http://hashids.org
In Postgres, having multiple tables share a single ID sequence is trivial thanks to SEQUENCE/NEXTVAL.
It doesn't matter how difficult picking a sequence is (and your post is odd because I don't think I gave any indication of unfamiliarity with Postgres?). It's a cognitive concern with keys that look simple but are not obviously unrelated (as a normal sequence is by convention or UUIDs are by definition) to the rest of the table.
After a decade of large systems relying on RDBMs, we now use 64-bit integers for all primary keys with a global Hi/Lo id generation system (app reserves a range of numbers on startup to assign to records automatically). This means plenty of ID space, maintains rough numeric ordering, allows ID creation without a roundtrip for every insert, is easily portable across different databases, and produces unique IDs for eve…
does seem to me that the author is a hobbyist or a junior developer with little real word experience. Literally the first Colum of almost every SQL table I have written has had a column called id with auto increment.
"There’s no need to manually create indexes on columns already declared unique; doing so would just duplicate the automatically-created index." Is the last part real? Makes me feel like forking postgesql just to save the world from accidental duplicate expensive indexes.
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…
This is particularly useful in some other scenarios such as distributed, eventually-consistent systems that are able to resolve concurrency conflicts. Send out changes optimistically (with those pregenerated IDs), resolve conflicts by untangling the ones that didn't work.
Earlier quoted context omitted.
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.
Thanks!
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.