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

121–130 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#121

Post by balls187 »

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…

> UUID primary keys aren't needed for big keyspaces

Agreed. UUID's are great for distributed/eventually consistent dbs. However, the article gives the primary reason the reason given to prefer UUID was specifically for key exhaustion.

Which, BIGINT is a much simpler solution.

Re: Mistakes Beginners Make When Working with Databases

#122

Post by treebeard901 »

JoeAltmaier wrote: post #37
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.

Denial isn't the same as argument. The definition of a UUID pretty much means they are. Enough bits and the chances of collision are less than that of a superintelligence spontaneously evolving in your morning coffee and hacking your computer. And its past time amateurs stopped thinking they've come up with the perfect UUID (timestamp + server id! Perfect! Until I set my clock back, or repurpose the server) and just…

In my experience this is only true if the UUID is generated by the database system in a controlled manner. It is not uncommon to have a unique identifier be used as a key in a database that comes from an external source. Consider an environment where you have a database that stores data for a monitoring system. So you decide to use the machine ID (a UUID) as a key. In your design you fail to take into account sys admins that do not clone a VM the correct way, so you wind up with two different machines with the same supposedly unique identifier. Sure, if you have a unique constraint on the key, you will find out about it pretty quickly, but it is just one example.

Re: Mistakes Beginners Make When Working with Databases

#123

Post by wvenable »

This passable advice at a scale that probably 99.999% of people will never need. I've worked on very popular systems, very old systems, and very popular and very old systems and I've never been at this scale.

When you're not at some massively huge scale, this advice is mostly terrible. You should do exactly the opposite.

Re: Mistakes Beginners Make When Working with Databases

#124

Post by balls187 »

aianus wrote: post #109
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?).

Poor index performance effects all your queries, regardless of scale.

Better more practical advice might be: if you don't need to use a X as a PK, don't use a X as a PK.

Where X can be either UUID or BIGINT.

If you don't expect your table to scale past a billion rows, INT is more than fine.

Re: Mistakes Beginners Make When Working with Databases

#125

Post by r00fus »

Over-normalization - one enterprise design pattern I've seen is to take fields that have basic values (which may have more attributes than just text) and create a field_values table which can be reused across objects. The key is to have a composite natural key like "field_name, value_name" so you can reuse across parent tables without creating seperate value tables for common things like status, phase, etc.

It allows more configurability as well - you could have a UI to control/audit all such values and allow for easy external mapping.

Re: Mistakes Beginners Make When Working with Databases

#126

Post by developer2 »

lloyd-christmas wrote: post #42
mathattack wrote: post #3

Great post. I find that many front end developers start with the User Design first, ignoring how the data should be stored or accessed. While user centricity is a great value, if you worry about screens needed rather than data needed, you cause trouble down the road. I've found that a data-first mental model is much more scalable and supportable. Many times the details of the databases get abstracted away, and perfor…

> I find that many front end developers start with the User Design first, ignoring how the data should be stored or accessed This sentence seems contradictory. If you start with user design, you then know how the data will be accessed which allows you to store it with purpose. Obviously this shouldn't be at the expense of proper database design, but how your app functions defines how you should store your data. While…

>> how your app functions defines how you should store your data

I think this is the fallacy the parent you are replying to is attacking. Your data should stand apart from your application. Applications change over time. Many databases also serve multiple applications (web, mobile, api, stats, etc.). Your data store should make sense on its own without being tied to an application's design. If you build your database according to your application, then making changes to the application can be difficult or require refactoring the database to match the new application specs. If you instead build your database to make sense standalone, it's up to each application to use it appropriately.

Re: Mistakes Beginners Make When Working with Databases

#127

Post by ryanbrunner »

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…

The problem is there's very few examples of real-world data that actually matches a primary key - i.e. is guaranteed to be unique and never changes. I've been burned by this so many times. In reality, everything needs to at least have some capability to change.

Re: Mistakes Beginners Make When Working with Databases

#128

Post by treebeard901 »

balls187 wrote: post #121
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…

> UUID primary keys aren't needed for big keyspaces Agreed. UUID's are great for distributed/eventually consistent dbs. However, the article gives the primary reason the reason given to prefer UUID was specifically for key exhaustion. Which, BIGINT is a much simpler solution.

The main problem I have seen with UUIDs from a performance standpoint has to do with indexing and ordering. To start with ordering, it is inherently a NP-complete problem. UUID can add a good bit of over head here since it's less computationally expensive to order an integer than it is to order something like a unique identifier. This is also why UUIDs tend to increase fragmentation with indexes, since indexing can be so closely tied to ordering. SQL Server has made some improvements here with the newsequentialid() function, so maybe it's less of a problem moving forward. To contradict myself for a second, perhaps the move to more distributed systems and service models over the long term increases the need for the move from INTs to UUIDs. Better to architect for the future now and save yourself some pain down the road. It will be interesting to see how that plays out.

Re: Mistakes Beginners Make When Working with Databases

#129

Post by lordnacho »

mercer wrote: post #115
JoeAltmaier wrote: post #37

Earlier quoted context omitted.

Denial isn't the same as argument. The definition of a UUID pretty much means they are. Enough bits and the chances of collision are less than that of a superintelligence spontaneously evolving in your morning coffee and hacking your computer. And its past time amateurs stopped thinking they've come up with the perfect UUID (timestamp + server id! Perfect! Until I set my clock back, or repurpose the server) and just…

Could you elaborate on that? I use UUID's at the moment, trusting that they'll be unique, but in part because I don't properly understand them, I feel a little anxiety about the chance of a collision. I sort of assumed they already used timestamps as a basis already, but apparently they do not. Is it just a matter of it being highly improbably of generating the same UUID twice? I'm assuming yes on gut feeling, but I…

If you write a sufficiently long series of random digits, it becomes increasingly likely that your number is the only use of that number anywhere in the universe. That's the crux of it.

I'm on a train now, but I think there's also a timestamp element to it somewhere, making it less likely that a restart of the random number generator will cause you to have a collision.

Re: Mistakes Beginners Make When Working with Databases

#130

Post by imtringued »

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…

Then you'll end up with a lot of inconvenient compound keys.
Post reply on HN