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

Mistakes Beginners Make When Working with Databases

craigkerstiens.com

Post reply on HN
Print view View original

91–100 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#91

Post by mgranda »

drumdance wrote: post #74
Practicality wrote: post #54

Earlier quoted context omitted.

Agreed. And the reason here for using UUIDs is not accurate. UUIDs are for having multiple systems (or components) create IDs and having a reasonable expectation for uniqueness, hence the Universal Unique part. It grinds my gears when people sneer at integers when they perform optimally or nearly optimally on the majority of use cases.

Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?" IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security. This relates to the "sometimes security by obscurity is okay" post from yesterday.

Can you link to this post? This is a topic that interests me and I'm on mobile :(

Re: Mistakes Beginners Make When Working with Databases

#92

Post by kogus »

dragonwriter wrote: post #66
endymi0n wrote: post #16

Pretty weak, partly terrible advice. - Storing images and blobs: granted, usually not a good idea - Limit/offset will take you a veeeery long way until you have to think about stuff like deep paging. And however you try to tackle that, if the stuff you paginate needs ordering, it's simply a hard problem and not a mistake. - UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale (and even t…

> UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale UUID primary keys aren't needed for big keyspaces, they are needed when you need multiple processes (especially a flexible number of multiple processes) to generate unique IDs independently of each other and the central database, which will eventually get stored in the central database. This can be important at much smaller than Goog…

A special case where UUID keys are extremely helpful is when you have to convert your data from one system to another - customer "105" can be different in different databases, but customer "7C823BE3-CB5B-4E34-BE8D-4B5A71945D3F" is truly unique. This takes dealing with foreign keys from "nightmare" to "non-issue".

Re: Mistakes Beginners Make When Working with Databases

#93

Post by mercer »

dragonwriter wrote: post #66
endymi0n wrote: post #16

Pretty weak, partly terrible advice. - Storing images and blobs: granted, usually not a good idea - Limit/offset will take you a veeeery long way until you have to think about stuff like deep paging. And however you try to tackle that, if the stuff you paginate needs ordering, it's simply a hard problem and not a mistake. - UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale (and even t…

> UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale UUID primary keys aren't needed for big keyspaces, they are needed when you need multiple processes (especially a flexible number of multiple processes) to generate unique IDs independently of each other and the central database, which will eventually get stored in the central database. This can be important at much smaller than Goog…

Indeed. I recently built a todo app inspired by a particular paper-based methodology (FVP) to scratch a personal itch, and it was a web app that uses localStorage. It was much easier to use UUID keys for the todos than to store/retrieve the max ID every time the app was opened.

That's basically the 'hello world' of web apps example where UUID's make sense already.

Re: Mistakes Beginners Make When Working with Databases

#94

Post by bcg1 »

I really have to take issue with the normalization point. Modern databases have optimizers that operate under the assumption that your schema is normalized. When you try to get clever and do optimizations yourself, you can very easily trick the optimizer into doing the wrong thing, and actually make things worse. I would argue that de-normalizing is much more of a beginner mistake that over-normalizing.

Re: Mistakes Beginners Make When Working with Databases

#95

Post by davidw »

mercer wrote: post #93
dragonwriter wrote: post #66

Earlier quoted context omitted.

> UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale UUID primary keys aren't needed for big keyspaces, they are needed when you need multiple processes (especially a flexible number of multiple processes) to generate unique IDs independently of each other and the central database, which will eventually get stored in the central database. This can be important at much smaller than Goog…

Indeed. I recently built a todo app inspired by a particular paper-based methodology (FVP) to scratch a personal itch, and it was a web app that uses localStorage. It was much easier to use UUID keys for the todos than to store/retrieve the max ID every time the app was opened. That's basically the 'hello world' of web apps example where UUID's make sense already.

If you have a lot of data to synchronize, you probably still want some kind of 'version vector' thing, which in the simple case with a centralized server, is an integer 'change counter' (I forget the exact term for it) that will let you fetch only the updated/new items. You'd still use a UUID for your ID though.

Re: Mistakes Beginners Make When Working with Databases

#96

Post by Jweb_Guru »

mikeash wrote: post #67
Jweb_Guru wrote: post #35

Earlier quoted context omitted.

UUIDs don't avoid all id collision issues, and it's far from final. They'll prevent you from using BRIN indexes effectively, for example. Timestamp plus unique server id is an arguably much better approach; on top of everything else, timestamps are actually useful, and they fit in 8 bytes.

Version 1 UUIDs are server ID (MAC address) plus timestamp. Your overall proposal is already included in "UUIDs." They don't fit into 8 bytes, though, so if you want to squeeze them down you'd have to come up with your own scheme. If you do use this scheme, be careful that your timestamp is sufficiently granular that it's not possible to generate two identical IDs in quick succession....

Granted re: UUIDs. There are definitely tradeoffs here (time precision / inserts per second [in practice you'll just end up throttling or massaging the timestamp if you do have insufficient granularity] vs. number of supported partitions vs. ability to efficiently route a path to the key vs. fast reservation ability vs. key size, etc., etc.). I was referring to the notion of a global 128-bit pseudorandom number as the final solution to everything... things are rarely that straightforward.

Re: Mistakes Beginners Make When Working with Databases

#97

Post by endymi0n »

Someone1234 wrote: post #57
craigkerstiens wrote: post #53

Earlier quoted context omitted.

Original author here, what I'm proposing is an ARRAY[VARCHAR]. Postgres has a native array type, different from manually mangling an array.

Fair enough. Still an ugly hack, and you're still over stuffing a column. It does avoid some issues with string passing which is nice, but that's just about all it solves.

On top, arrays in PG don't support foreign key constraints and in this way make your model weaker. We do use them too, but in well defined, pretty rare circumstances.

Re: Mistakes Beginners Make When Working with Databases

#98

Post by parenthephobia »

mgranda wrote: post #91
drumdance wrote: post #74

Earlier quoted context omitted.

Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?" IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security. This relates to the "sometimes security by obscurity is okay" post from yesterday.

Can you link to this post? This is a topic that interests me and I'm on mobile :(

https://news.ycombinator.com/item?id=11854576

Re: Mistakes Beginners Make When Working with Databases

#99

Post by mercer »

davidw wrote: post #72
dragonwriter wrote: post #66

Earlier quoted context omitted.

> UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale UUID primary keys aren't needed for big keyspaces, they are needed when you need multiple processes (especially a flexible number of multiple processes) to generate unique IDs independently of each other and the central database, which will eventually get stored in the central database. This can be important at much smaller than Goog…

Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, wh…

Wait, what? MySQL reuses them? Can you elaborate on that?

Re: Mistakes Beginners Make When Working with Databases

#100

Post by exceptione »

PaulHoule wrote: post #49
endymi0n wrote: post #16

Pretty weak, partly terrible advice. - Storing images and blobs: granted, usually not a good idea - Limit/offset will take you a veeeery long way until you have to think about stuff like deep paging. And however you try to tackle that, if the stuff you paginate needs ordering, it's simply a hard problem and not a mistake. - UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale (and even t…

Years ago I built an image server that served up millions of tiny images (1k or so) and found mysql did a pretty good job for that, avoiding the file system overhead which would overwise be awful.

How did MySQL help here? It uses the filesystem too...

I can imagine that you want to hold these images in RAM to bypass the filesystem. Maybe MySQl did that for you, but that sounds like a heavy middleman.

Post reply on HN