Mistakes Beginners Make When Working with Databases • Hacker News Board

Re: Mistakes Beginners Make When Working with Databases

#111

Post by sbuttgereit » Wed, Jun 08, 2016, 5:13 PM UTC

sbuttgereit wrote: post #84

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 ot…

Actually, thinking about it, I think the biggest database beginner mistake I see is simply not coming to grips with what the data actually means. The project I'm working on right now is a do-over project that exists because the original project team didn't take the time to really understand why they were doing the project, why the project mattered to the client; they simply saw, "it needs to do 'x', it needs to do 'y'". The client, in turn, simply took it for granted that the project team understood problem. At the end: it resulted in irrecoverably bad data collection.

If you fail to understand the meaning in data and just try to jam values into some persistence store to get through some transaction someone told you to code up, you'll likely make decisions for the future that you don't even know will come yet. Each table, each value, expresses some idea, some piece of information that, without even considering the application functionality, has meaning in the context of the rest of the data you're capturing. If you respect that relationship of ideas, you'll know whether or not normalization or de-normalization makes sense, you will more likely have a flexible information architecture rather than a brittle one.

So there's my beginner's advice: really understand what and why you are stuffing data into a database in the first place. Don't loose sight of the larger context. Conceptualize the information as information. Then figure out how the technology facilitates (or doesn't) the expression of that information with the greatest clarity.

Re: Mistakes Beginners Make When Working with Databases

#112

Post by yardie » Wed, Jun 08, 2016, 5:13 PM UTC

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?

You can also do the same in certain versions SQL Server. Change the ID field from auto-increment enabled to disabled. Add and delete a few fields. Then re-enable auto increment. Now, normally the server should start new IDs at the last position. Normally being the operative word.

Re: Mistakes Beginners Make When Working with Databases

#113

Post by muxxa » Wed, Jun 08, 2016, 5:16 PM UTC

davidw wrote: post #72

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…

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 that the database is much more comprehensible, e.g. foreign keys are legible without having to join back to the primary table. The relational model for data is pretty cool and breaks down when e.g. UNION doesn't work if 2 rows differ only in an arbitrary integer primary key.

Re: Mistakes Beginners Make When Working with Databases

#114

Post by pietroalbini » Wed, Jun 08, 2016, 5:16 PM UTC

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?

That'a bug reported in 2003 and not yet fixed.

https://bugs.mysql.com/bug.php?id=199

Re: Mistakes Beginners Make When Working with Databases

#115

Post by mercer » Wed, Jun 08, 2016, 5:16 PM UTC

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…

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 highly distrust that body part these days.

(Edit: I realize I could go look this up myself, but 1) I like explaining things so perhaps you do too, and 2) I'm hoping perhaps at the very least you can point me to a good resource to properly understand this topic)

Re: Mistakes Beginners Make When Working with Databases

#116

Post by davidw » Wed, Jun 08, 2016, 5:18 PM UTC

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 that a lot of people end up screwing that up and picking something that doesn't work well as a primary key. Also, just using an integer is often a great way to get started without thinking about things so much. That works for a lot of web apps.

Re: Mistakes Beginners Make When Working with Databases

#117

Post by qaq » Wed, Jun 08, 2016, 5:20 PM UTC

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.

For majority of projects limit offset is fine. Some of the linked advice on alternatives is way more horrible like Cursors WTF? this will not scale well.

Re: Mistakes Beginners Make When Working with Databases

#118

Post by billmalarky » Wed, Jun 08, 2016, 5:20 PM UTC

davidw wrote: post #104

mercer wrote: post #99

Earlier quoted context omitted.

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.

How old was the version of mysql? Even years ago I remember mysql stored the auto increment value separately.

Re: Mistakes Beginners Make When Working with Databases

#119

Post by barkingdog » Wed, Jun 08, 2016, 5:24 PM UTC

Hmm, going to say a hard no on naively preferring UUIDs. It's a little bit of extra work to go with the instagram style approach [0] of using many logical postgres shards with a custom id function that implements twitter snowflake style IDs, but it's an elegant, scalable and operationally simple solution. I am very fond of it.

[0] http://rob.conery.io/2014/05/29/a-better-id-generator-for-po...

Re: Mistakes Beginners Make When Working with Databases

#120

Post by pmelendez » Wed, Jun 08, 2016, 5:24 PM UTC

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.

If you have a great amount of tiny images then you are relying in how fast is the filesystem to localize the image. Chances are that depending on the index you are using MySQL could do a better job (this depends on the storage engine but usually is one file for the table and another ones for indexes)