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

141–150 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#141

Post by PeterisP »

exceptione wrote: post #100
PaulHoule wrote: post #49

Earlier quoted context omitted.

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.

"bypassing the filesystem" in this case means not having a million of tiny files, which often hurts performance.

If you'd have non-DB system for that (e.g. any game with a lot of tiny images), then you would generally want to use spritesheets, archives or some other approach to store many of these images in a single file instead of having each of them be a separate entry in the filesystem.

Re: Mistakes Beginners Make When Working with Databases

#142

Post by collyw »

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…

The reason given in the article was scaling. At that scale you probably don't want beginners designing your database for you.

Re: Mistakes Beginners Make When Working with Databases

#143

Post by combatentropy »

andysinclair wrote: post #20

I'm suprised by this point: "the database tends to be an after-thought in application design". Maybe it's a generational thing (I'm 40+), but I tend to always start with thinking about the data structures and design in conjunction with the UI design. Getting a solid database design in place early in the development cycle is critical to building a solid, stable system- you can of course alter the database as you start…

It was of all things a database book that advised me to begin with the user interface. It's counterintuitive because, isn't the database the foundation of the interface?

Don't get me wrong. I don't roll an application into production until I've gone back and forth over both the interface and the database, until both satisfy me, the database is well-normalized, and so on. But when the first line of anything has yet to be written, I think I save a few iterations by first sketching out the fields and behavior of the user interface.

Re: Mistakes Beginners Make When Working with Databases

#144

Post by brianwawok »

olavgg wrote: post #90
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…

Storing images / blobs in a database is fine! https://sqlite.org/intern-v-extern-blob.html I just recently built a e-commerce site that stores the images in the database. But be careful, some ORM's will automatically fetch that blob together with the rest of your object.

So if you do this on a large site, what % of your DB load is going to be serving images?

If you put your images behind a CDN, you solve 98% of that.. but you still have a thundering herd problem if your cache gets cold and a bunch of people want to see a non-CDNed iamge.

IMHO it is way way way easier to just throw the image up on google cloud storage (or s3), and store the URL in your database. Or better yet, derive the path from the ID so nothing to store. You can handle millions of hits per second with no DB load.

Re: Mistakes Beginners Make When Working with Databases

#145

Post by collyw »

muxxa wrote: post #113
davidw wrote: post #72

Earlier quoted context omitted.

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…

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

That's what unique keys are for usually.

Re: Mistakes Beginners Make When Working with Databases

#146

Post by Clubber »

muxxa wrote: post #113
davidw wrote: post #72

Earlier quoted context omitted.

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…

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

That's called a natural key and I only use it for logins in user tables. I haven't found anything else that doesn't break down.

Re: Mistakes Beginners Make When Working with Databases

#147

Post by molecule »

calebsurfs wrote: post #62

As a beginner using MySQL I was expecting not using innodb_file_per_table or generally learning db configuration to be on the list. It's a huge pain when your little web server runs out of disk space because innodb is eating it all.

Good advice, but that's specific to MySQL and its InnoDB engine, while the article is about general relational databases.

Re: Mistakes Beginners Make When Working with Databases

#148

Post by naasking »

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.

> 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?"

You're misidentifying the actual security problem. Using URLs in this manner requires cryptographically secure random numbers, or my preferred method is to HMAC the URL to sign it's protected parameters. I actually wrote a small library for .NET called Clavis to demonstrate this idea [1]. The MAC acts as the cryptographically secure identifier needed to make the URL unguessable.

[1] https://higherlogics-trac.sourcerepo.com/higherlogics_clavis...

Re: Mistakes Beginners Make When Working with Databases

#149

Post by Clubber »

Someone1234 wrote: post #10

The first point is strange, borderline wrong. What they really MEAN is don't store images in your general purpose database, in particular as long strings. There are however databases with first party support for image storage, which is useful because now you can store the image and metadata about the image together (as well re-using existing solutions like replication, authentication, etc). Additionally their "soluti…

I've stored images both ways and both have their issues. To declare one is superior over others in all circumstances as the OP did is short sighted. The one thing I would recommend is once you pick one, stick to it for consistency's sake.

Re: Mistakes Beginners Make When Working with Databases

#150

Post by olavgg »

brianwawok wrote: post #144
olavgg wrote: post #90

Earlier quoted context omitted.

Storing images / blobs in a database is fine! https://sqlite.org/intern-v-extern-blob.html I just recently built a e-commerce site that stores the images in the database. But be careful, some ORM's will automatically fetch that blob together with the rest of your object.

So if you do this on a large site, what % of your DB load is going to be serving images? If you put your images behind a CDN, you solve 98% of that.. but you still have a thundering herd problem if your cache gets cold and a bunch of people want to see a non-CDNed iamge. IMHO it is way way way easier to just throw the image up on google cloud storage (or s3), and store the URL in your database. Or better yet, derive…

I use Varnish, so it is never a performance issue.
Post reply on HN