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

Design better databases

web.archive.org

Post reply on HN
Print view View original

71–80 of 182 posts

Re: Design better databases

#71

Post by mvitorino »

ww520 wrote: post #43
mvitorino wrote: post #25

Earlier quoted context omitted.

The problem is that most uuids are generated in a way that, when sorted alphabetically as strings, would have a random order. Example: t0: 7458e3a9-716b-4352-b2e4-b5b67d0c089b t1: 4d8d753c-1777-439d-8725-b093b1bd8430 Using this as a PK in any relational database will mean the rows are stored in the clustered index order, which causes extreme fragmentation because the db engine constantly has to find "holes" in the da…

This is a non-issue. Most db engines just append new data rows at the end of the data table and assign an internal row ID for it. The keys (UUID in this case) are stored in the separate index pages using B+ tree, which searches random key (UUID) or sequentially incremented key equally well. UUID key is a problem only if your main query is a range query on the PK of a clustered indexed table. If your main query is a r…

A lot of times when a UUID is used, it's not generated in the db, such as when you need to generate guaranteed unique IDs client side. Also 4 bytes vs 16 is not "equally well" in terms of performance. Note that the the diagram linked at the root of this thread is encoding uuids as strings which means 32-36 bytes instead of 16.

Re: Design better databases

#73

Post by dv35z »

cmrdporcupine wrote: post #8

Y'know I really miss designing relational schemas. As much as I hate SQL the language I love the relational model that it is a (butchered and ugly and compromised) implementation of. But working where I do on the systems I do now this is something I never have to deal with anymore. There's something very therapeutic about organizing data using a system of rules, and the relational data model is a powerful one.

Completely agree with you. I have always hoped that some of the GUI modelers would allow you to add notes/explanations on tables & relations, explaining the motivation behind design decisions. Example, "M2M relation here allows us to 'tag' objects with multiple attributes".

Re: Design better databases

#74

Post by mvitorino »

gnaritas wrote: post #31
mvitorino wrote: post #25

Earlier quoted context omitted.

The problem is that most uuids are generated in a way that, when sorted alphabetically as strings, would have a random order. Example: t0: 7458e3a9-716b-4352-b2e4-b5b67d0c089b t1: 4d8d753c-1777-439d-8725-b093b1bd8430 Using this as a PK in any relational database will mean the rows are stored in the clustered index order, which causes extreme fragmentation because the db engine constantly has to find "holes" in the da…

That would be true except for that fact that database designers know this, and offer a way to generate sequential GUID's; so it's actually not a problem for that reason. The only real downside is performance, an int key performs better.

A number of scenarios that require UUIDs as keys, involve generation client side where such guarantees may not be available. In my view, UUIDs should not be used unless there is a strong reason for it (such as making keys not guessable). Most people will implement them incorrectly/inefficiently.

Re: Design better databases

#75

Post by ddorian43 »

ww520 wrote: post #43
mvitorino wrote: post #25

Earlier quoted context omitted.

The problem is that most uuids are generated in a way that, when sorted alphabetically as strings, would have a random order. Example: t0: 7458e3a9-716b-4352-b2e4-b5b67d0c089b t1: 4d8d753c-1777-439d-8725-b093b1bd8430 Using this as a PK in any relational database will mean the rows are stored in the clustered index order, which causes extreme fragmentation because the db engine constantly has to find "holes" in the da…

This is a non-issue. Most db engines just append new data rows at the end of the data table and assign an internal row ID for it. The keys (UUID in this case) are stored in the separate index pages using B+ tree, which searches random key (UUID) or sequentially incremented key equally well. UUID key is a problem only if your main query is a range query on the PK of a clustered indexed table. If your main query is a r…

Actually only postgresql does it this way (by storing data in the heap and not in the primary index). Mysql(innodb),mssql,oracle uses the store the row in the primary-key.

Edit: I'm ~wrong, see below.

Re: Design better databases

#76

Post by Mahn »

developer2 wrote: post #67
mortehu wrote: post #35

Tip for SQL users: If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you can do stuff like: SELECT * FROM reservation JOIN guest USING (reservation_id); By doing "USING (reservation_id)" instead of "ON reservation.id = guest.reservation_id", the field will be automatically deduplicated, so you don't have to qualify it elsewhere in…

Not really a fan. When your table name is "reservation", you're prefixing "id" with the table name ("reservation_id"), but you don't do the same for the other columns. I've never liked the inconsistency of having "reservation_id", but other columns like "name" instead of "reservation_name". Especially on longer table names where you wind up with columns like "this_really_long_table_id". All just to shorten join claus…

Personally, for me it makes sense that the id field has a different distintive name for each table because it's a specific kind of id, i.e., customerID is not interchangeable with reservationID. For me, this is not so much about prepending the field with the table name, but about calling things by their name. "id" is just too vague, even if you use it in the context of a specific table.

Re: Design better databases

#77

Post by marcosdumay »

jessedhillon wrote: post #69
petepete wrote: post #49

Earlier quoted context omitted.

Or if you're feeling lucky: SELECT * FROM reservation NATURAL INNER JOIN guest;

Err, be careful with natural joins: suppose both tables have a column called "name" or "created_at" -- natural join will create join conditions from those.

I never understood why the thing named "natural join" don't use relationships to determine the joining columns. Does not look natural for me.

Re: Design better databases

#78

Post by swah »

mortehu wrote: post #35

Tip for SQL users: If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you can do stuff like: SELECT * FROM reservation JOIN guest USING (reservation_id); By doing "USING (reservation_id)" instead of "ON reservation.id = guest.reservation_id", the field will be automatically deduplicated, so you don't have to qualify it elsewhere in…

Another option:

    SELECT * FROM reservation r JOIN guest USING (r.id);

Re: Design better databases

#79

Post by jessedhillon »

gnaritas wrote: post #54
jsmthrowaway wrote: post #50

Earlier quoted context omitted.

> an int key performs better. A UUID is a 128-bit integer. That people do not store, generate, or interact with them that way is the bug. I'd kill for a 128-bit architecture so a UUID compare would be a single instruction, and it's too bad consensus is that we don't really need it.

An int is either 4 or 8 bytes, it's still going to perform better no matter how you treat the GUID. edit: bytes

> An int is either 4 or 8 bits

This perfectly summarizes the bitwise-innumeracy of the argument against UUIDs. Even without 128 bit word sizes, most UUIDs are going to be non-matches in their lower bits, assuming a random distribution. There's no computational efficiency gained in the vastly wide critical path here.

Re: Design better databases

#80

Post by fleitz »

developer2 wrote: post #67
mortehu wrote: post #35

Tip for SQL users: If you give all your ID fields unique names, e.g. by calling your field "reservation_id" instead of "id", even in the reservation table, you can do stuff like: SELECT * FROM reservation JOIN guest USING (reservation_id); By doing "USING (reservation_id)" instead of "ON reservation.id = guest.reservation_id", the field will be automatically deduplicated, so you don't have to qualify it elsewhere in…

Not really a fan. When your table name is "reservation", you're prefixing "id" with the table name ("reservation_id"), but you don't do the same for the other columns. I've never liked the inconsistency of having "reservation_id", but other columns like "name" instead of "reservation_name". Especially on longer table names where you wind up with columns like "this_really_long_table_id". All just to shorten join claus…

I once made that argument, we ended up with reservation_name, I regret I ever said anything about it.

Apparently, it made joins more clear, I seriously wonder how often people fucked that up for them to think it was a good idea to prefix every fucking column with its table name.

It's like when I see unit tests for setters and think, gee, setters seem pretty straightforward to me, how often to people fuck them up?

Post reply on HN