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

81–90 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#81

Post by davidw »

sshykes wrote: post #78
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.

Vehemently disagree. What you are proposing is everything wrong with security-by-obscurity. In this case the security hole of /user/123 just needs to be properly locked down. That is all.

I mostly use integers (but see my comment elsewhere). There is most definitely a german tank problem involved in them, though:

https://en.wikipedia.org/wiki/German_tank_problem

For most things I do, it's not a concern, but it is something to keep in mind.

Re: Mistakes Beginners Make When Working with Databases

#82

Post by rwmj »

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.

I think it very much depends on specifics. Years and years ago I stored emails as BLOBs in an Informix database for a large webmail system. That turned out to be a bad idea and we ended up migrating (very painfully) millions of emails to a file server.

However I don't think it's universally true this would always be bad. It depends on the speed at which you can insert and fetch blobs from your database, the speed of your NFS server, whether your database or file server can be replicated, and the problems with data integrity you're definitely going to have when not everything is known by the database.

No substitute for careful analysis of individual cases.

Re: Mistakes Beginners Make When Working with Databases

#84

Post by sbuttgereit »

Craig Kerstiens's advice/writings is usually pretty sound, but I think he makes a fairly significant mistake in providing the advice he does here: over generalization. I appreciate he's talking about advice to beginners, but even so I think he needs to set context in his examples. Missing the context of possible solutions is probably the biggest mistake that anyone makes when making their choices in this (and many other) regard(s).

None of the things mentioned are necessarily bad depending on context. For example, if I have a billion people per second visiting my website, yeah, storing the site images in PostgreSQL alongside the rest of the data probably isn't a great idea. But, if I am building an ERP system for the SMB market, it may be a fine idea given the degrees of concurrency I can expect, the cost of server equipment, and the advantages of keeping related data (binary or otherwise) together. The trade-offs are different. Simply put, it depends.

I think this post could be improved with some clarification and I do think a tip sheet for beginner's is a good idea coming from someone that does have good advice for PostgreSQL... it just needs to be less generally prescriptive and more instructive about how to think about the given advice.

Re: Mistakes Beginners Make When Working with Databases

#85

Post by horshod »

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.

I have faced this issue in my web application. My solution is to use a UUID wherever the ID will be exposed to the user (only one place in my application) and use an integer ID everywhere else. Although this does mean 2 IDs need to be generated and stored. The other solution is to never allow access to users without a log in.

Re: Mistakes Beginners Make When Working with Databases

#86

Post by ReedRothchild »

Mister_Snuggles wrote: post #58
ReedRothchild wrote: post #33

Not sure about point 1 as a blanket statement... many times external services are not allowed and therefore aren't accessible. How is S3 storing them? Probably in a DB. In some DB and application frameworks, IO issues with image data can and have been streamlined. If not, better model design might help. Putting them in S3 limits what you can do with the images. For basic application images, yeah that doesn't belong i…

This advice really only applies to public web sites. PostgreSQL could be used as the backend for, as an example, an internal inventory tracking application. Something like this could allow users to upload a generic photo of the item in inventory. Putting these in S3 or a CDN makes no sense for an internal application. And, of course, "internal application" doesn't necessarily mean a web application. It could be a fat…

I agree with your "better advice"... Even if it's a public facing app, having them stored in the DB makes it much easier to restore, replicate, etc... Not to mention allowing fine grained authorization rules. Any time I have worked on a system (web app, fat-client, etc) where images, which are really data, are separate from the image metadata, overtime, a colossal mess occurs. Orphaned files without metadata, metadata without files.

Even in your public web app, you also might have other back-end systems that cannot access S3, but can access your application services or DB (yikes). There are definitely many cases where external providers like S3 is the better approach, but not all. Also, you don't have a performance problem until you have a performance problem.

Re: Mistakes Beginners Make When Working with Databases

#88

Post by jameshart »

ashmud wrote: post #24
Illniyar wrote: post #6

Earlier quoted context omitted.

With scale its not about key exhustation, its the ability to be able to create a key in multiple servers without having to consult a central location.

For processing data types without quality natural unique keys, being able to generate a unique key beforehand is very useful. I've written code both ways: inserting to get an auto-incremented integer, and generating a UUID, and the UUID approach was much easier to write and maintain.

I'm going to make a bold claim: no datatype has a quality natural unique key.

I am struggling to think of a counterexample. Maybe chemical elements? Where the natural key is of course the atomic number, not the chemical symbol...

Before you suggest 'zipcodes' or 'states', consider that zipcodes are not in any sense natural, and anyway, like state codes, are so US-centric that they don't belong as top level elements in most real database schemas.

Re: Mistakes Beginners Make When Working with Databases

#89

Post by kbenson »

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…

Depending on your configuration and needs, using a method to generate primary keys in a way unique to each system might still yield better performance. For example, in MySQL you can use auto_increment_increment and auto_increment_offset to force one server use insert using even integers and one odd, or any combination you need. It does eat bits from the key size though. Changing it later can be a pain, but it possible if you think it through. I don't doubt something like this is possible in Postgres as well.

Re: Mistakes Beginners Make When Working with Databases

#90

Post by olavgg »

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.

Post reply on HN