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

71–80 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#71

Post by masklinn »

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…

> 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).

It also keeps them coherent, if you store images on a filesystem but metadata in a database, since they don't share transactional contexts you will eventually end up in an inconsistent state ("dead" files without metadata, or live metadata missing the corresponding image data).

Re: Mistakes Beginners Make When Working with Databases

#72

Post by davidw »

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…

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, where I did have a say in things, used UUID's and Postgres.

Re: Mistakes Beginners Make When Working with Databases

#73

Post by jameshart »

And once you've got over these initial issues you can move on to the Mistakes People Who've Figured Out a Thing or Two About Databases Make:

- storing their images externally, but forgetting to apply a consistent backup strategy to their image data

- Database is getting big, query returns lots of records, so just adding pagination. Forgetting that the user really doesn't actually want to page through results at all. The correct answer was probably to add proper faceted filters and full text search.

- using UUID keys everywhere, then making the mistake of mixing up UUID keys from one table with UUIDs from another one.

- Using nullable columns as a tool for schema change, but not deciding what it actually means for a particular column in a row to contain a NULL.

- Thinking that they will get away with just storing a chunk of structured data like an array in a particular column value because from the application point of view it's really just one blob of data anyway. In general I give a structured datatype like that two days before someone is writing a query that digs into the inner structure of it.

Re: Mistakes Beginners Make When Working with Databases

#74

Post by drumdance »

Practicality wrote: post #54
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…

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.

Re: Mistakes Beginners Make When Working with Databases

#75

Post by [deleted] »

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…

[deleted]

Re: Mistakes Beginners Make When Working with Databases

#76

Post by ivanhoe »

perezdev wrote: post #13

Number 2 is kind of funny: >>The unfortunate part is: pagination is quite complex, and there isn’t a one-size-fits-all solution. Probably not. But the example he gave is a one size fits most. I've used it on a dozen different projects and have never had performance issue.

Problem is actually any ORDER BY operation on a big table, but often it can be fixed with a bit of index magic... on small enough DB (and that's like 99% of web projects) you don't really need to care about this, ever...

Re: Mistakes Beginners Make When Working with Databases

#77

Post by olavgg »

Practicality wrote: post #54
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…

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.

You can also use ZooKeeper to increment the id for distributed systems. If we're talking about a lot of inserts per second, you can increment by a thousand.

For example system-A gets id range for 1-1000, system-B gets id range for 1001-2000 and so on.

Re: Mistakes Beginners Make When Working with Databases

#78

Post by sshykes »

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.

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.

Re: Mistakes Beginners Make When Working with Databases

#80

Post by sshykes »

_RPM wrote: post #51
hinkley wrote: post #47

Earlier quoted context omitted.

If you can avoid exposing those ID's then 64 bits should do fine with no problems. But it turns out JavaScript can only handle ~54 bit integers, so if you try to randomize your IDs to prevent people scanning your data, you'll be in for a rough patch making sure you always treat IDs as strings. At least with UUID you pretty much have to treat it as a string. You also avoid being the next developer in a long line who t…

The common pattern I've seen is to send the 64 bit integer back as a string. So it would be a "numeric string"

100% agree with this method. Math calculations are never really used with IDs in my experience.
Post reply on HN