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

101–110 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#101

Post by kbenson »

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.

Yes. Any time you let a non-associated record be displayed by a user session that is not linked to that record and should not have access to it, that's not a problem of easily guessable keys, that's a problem of not securing data by checking access rights.

Re: Mistakes Beginners Make When Working with Databases

#102

Post by l8again »

A bane of our existence, when working with dbs, is that we always conflate storage with query optimization. When we talk about databases, we unconsciously think of both concerns. This is traditionally instilled in us as that makes the most sense starting out, but almost always what ends up happening is your dataset needs to be shared among multiple applications, each with its own optimization requirements. And that's when things start to go haywire. At some definition of scale, a better design is to have a storage layer that is not really concerned with query optimization, and allow different applications to design their own query systems. Said another way, even when you are working in RDBMS's, it's good to acknowledge storage and query as two separate concerns.

Re: Mistakes Beginners Make When Working with Databases

#103

Post by mercer »

davidw wrote: post #95
mercer wrote: post #93

Earlier quoted context omitted.

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.

wouldn't filtering by date field be good enough, generally?

Either way, once I realized that I use my own little todo-app constantly, a server-based implementation became necessary. I'm working on a Horizon/RethinkDB version right now which uses UUID's by default. But if that were not the case I would've probably gone for simple incrementing ID's.

Re: Mistakes Beginners Make When Working with Databases

#104

Post by davidw »

mercer wrote: post #99
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…

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

Here's what happens. Perhaps it has been fixed now:

* New record is created, with new ID.

* Record is deleted.

* Database is shut down (cleanly).

* Database is started again.

* New record is created. It gets the old ID, because it's just looking at the current max(ID) or something like that.

Re: Mistakes Beginners Make When Working with Databases

#105

Post by davidw »

mercer wrote: post #103
davidw wrote: post #95

Earlier quoted context omitted.

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.

wouldn't filtering by date field be good enough, generally? Either way, once I realized that I use my own little todo-app constantly, a server-based implementation became necessary. I'm working on a Horizon/RethinkDB version right now which uses UUID's by default. But if that were not the case I would've probably gone for simple incrementing ID's.

Dates and distributed systems... is something you need to be careful with. Anyone have a good link summing this up?

https://aphyr.com/posts/299-the-trouble-with-timestamps isn't bad.

Re: Mistakes Beginners Make When Working with Databases

#106

Post by endymi0n »

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…

Clarifying my original reply here:

UUIDs absolutely have their place - both the problems of distributed ID generation (mostly in remote clients / apps) and avoiding information leakage (-> German Tank Problem) are very real world examples where using them is a sensible thing.

However, my point was that using them as a default choice has a lot of drawbacks. All over our data model, we've got like 60-70 entities with serial IDs and about 2 with UUID columns.

Also, don't underestimate how fast PostgreSQL can deal you out from a sequence via nextval(), should you really go down that route. But yes, if the database is more than a millisecond away, UUIDs definitely have their time and place.

Re: Mistakes Beginners Make When Working with Databases

#107

Post by mercer »

davidw wrote: post #81
sshykes wrote: post #78

Earlier quoted context omitted.

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.

In which cases would this be a German tank problem for you? I can think of apps where you want investors or something, but I can't help but wonder if they'd be savvy enough to check for that.

I do know that I try to make my invoices to clients a bit more impressive, because I don't send many of them and they most definitely do notice if they get invoice #3 in May. Main problem is that in my country the rules for invoices are both murky and stringent, so I'm pretty much limited to a 0000 format.

Re: Mistakes Beginners Make When Working with Databases

#108

Post by davidw »

mercer wrote: post #107
davidw wrote: post #81

Earlier quoted context omitted.

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.

In which cases would this be a German tank problem for you? I can think of apps where you want investors or something, but I can't help but wonder if they'd be savvy enough to check for that. I do know that I try to make my invoices to clients a bit more impressive, because I don't send many of them and they most definitely do notice if they get invoice #3 in May. Main problem is that in my country the rules for invo…

Well if you have an 'order #', your competition can make an order a week and see how many other orders you've gotten in the meantime.

> in my country the rules for invoices are both murky and stringent,

I sympathize, as the rules were like that in Italy, where I lived for a long time.

Re: Mistakes Beginners Make When Working with Databases

#109

Post by aianus »

stevecalifornia wrote: post #19

"Use UUIDs instead of integer PKs" is 95% of the time a HORRIBLE idea. As a rule of thumb, if you are a beginner, you should NEVER use a UUID instead of an integer PK. If you know the definition and the ins-and-outs of 'clustered index', 'index fragmentation' and 'page split' then feel free to use a UUID if you see fit-- otherwise, please don't. If you are a beginner and for some reason have to have a UUID: Use a seq…

> "Use UUIDs instead of integer PKs" is 95% of the time a HORRIBLE idea. As a rule of thumb, if you are a beginner, you should NEVER use a UUID instead of an integer PK.

Can you enlighten us as to why? Seems to me if you're not at the scale where you see the benefits of UUIDs you're also not at the scale to see the drawbacks either (bigger, slower indices?).

Re: Mistakes Beginners Make When Working with Databases

#110

Post by vectorpush »

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.

> IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security.

IMO, explicitly prohibiting unauthorized access to an API endpoint is a basic security tenant, not a "holistic" one. if iterating through an API's integer key sequence results in unauthorized access to data, replacing the integers with UUIDs only masks the problem and I'd say is a classic example of how relying on obscurity for security can be a pernicious mistake, especially for a novice developer.

Post reply on HN