Mistakes Beginners Make When Working with Databases • Hacker News Board

Re: Mistakes Beginners Make When Working with Databases

#181

Post by TheCoelacanth » Wed, Jun 08, 2016, 7:11 PM UTC

Jweb_Guru wrote: post #96

mikeash wrote: post #67

Earlier quoted context omitted.

Version 1 UUIDs are server ID (MAC address) plus timestamp. Your overall proposal is already included in "UUIDs." They don't fit into 8 bytes, though, so if you want to squeeze them down you'd have to come up with your own scheme. If you do use this scheme, be careful that your timestamp is sufficiently granular that it's not possible to generate two identical IDs in quick succession....

Granted re: UUIDs. There are definitely tradeoffs here (time precision / inserts per second [in practice you'll just end up throttling or massaging the timestamp if you do have insufficient granularity] vs. number of supported partitions vs. ability to efficiently route a path to the key vs. fast reservation ability vs. key size, etc., etc.). I was referring to the notion of a global 128-bit pseudorandom number as th…

Of the five UUID versions, only version 4 is pseudorandom. The other four versions are completely non-random.

Re: Mistakes Beginners Make When Working with Databases

#182

Post by insulanian » Wed, Jun 08, 2016, 7:11 PM UTC

drawkbox wrote: post #44

Since there is some UUID hate here, some missing pros to using them: - No round trip for generating keys, data can be sent in with an existing or new uuid without having to hit the autonumber/keymaster, removes a single point of failure for a small fee on each row. Storage is cheap so 16-byte uuid is not a deal-breaker, the benefit is speed and horizontal scalability. Optimized read-only tables and/or caching can be…

You're optimizing for write, but the performance gain is negligible in comparison to the performance loss due to index fragmentation and slow joins.

Also, on "storage is cheap": http://www.sqlskills.com/blogs/kimberly/disk-space-is-cheap/

Re: Mistakes Beginners Make When Working with Databases

#183

Post by teraflop » Wed, Jun 08, 2016, 7:12 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.

Typically, filesystems are designed and tuned around the assumption that most of your data is stored in relatively large files. For instance, a typical ext4 filesystem uses 256 bytes per inode and rounds each data block up to a multiple of 4096 bytes.

So if you want to store lots and lots of tiny objects, it's very inefficient to store them each in a separate file. At that point, you could design your own compound data format, making sure it efficiently supports all the different operations you might want to do... or you could just use a database which has already solved the problem for you.

Re: Mistakes Beginners Make When Working with Databases

#184

Post by Spidler » Wed, Jun 08, 2016, 7:13 PM UTC

treebeard901 wrote: post #128

balls187 wrote: post #121

Earlier quoted context omitted.

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

UUID internally is a 128 bit integer. Cheap as hell to sort.

Re: Mistakes Beginners Make When Working with Databases

#185

Post by developer2 » Wed, Jun 08, 2016, 7:17 PM UTC

lloyd-christmas wrote: post #177

mathattack wrote: post #163

Earlier quoted context omitted.

While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist in the future. Reminds me of the cynical saying for data warehouses, "Data in, but never out." :-) What I have seen frequently enough is myopic…

> The assumptions behind this then gets built into the code base This logic shouldn't be in the code base. A query should be isolated from the application logic, as I think we can all agree on. A change in the database should only require changing the query/procedure. Your business logic shouldn't be dependent on the internal workings of the query, just on it's input/output which shouldn't need changing. Adding a fea…

Nice reply, first time I've wished to upvote more than once. The context you provide is something I don't think of enough when I am presenting my view. It could be misinterpreted that I'm advocating designing databases that are designed in some horribly archaic structure that is difficult for applications to work with.

All I mean is that a database should make sense 100% on its own. Your database schema should represent what makes sense for your data, not what your application wants to see. I've never seen a case where designing the database first results in a poorly optimized application codebase. If your database-first design results in terrible access patterns for applications, then your database just wasn't properly designed in the first place. Your data should have a sensible structure on its own without catering specifically to application specs. If your structure really is sensible, no application should have a difficult time manipulating the data within it.

Re: Mistakes Beginners Make When Working with Databases

#186

Post by tomg » Wed, Jun 08, 2016, 7:17 PM UTC

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…

LIMIT / OFFSET should be avoided from day one for a realtime app. It's not too difficult to build a future proof solution, and in my experience you run into problems rather quickly if you're building a realtime system (user generated content especially).

LIMIT / OFFSET problems:

- There are portability issues with some other kinds of datastores and can be a mess to keep caches consistent (removing an item from one page requires invalidating every page set after it)

- It makes caching at the request / cdn level more complicated.

- Any update to the data while the user is paginating results in duplicate or missing items (especially noticeable with any kind of infinite scroll).

You can instead use something like ?object_id=123&page_size=50 to get the next 50 results after item with id=123 (assuming some default order, you could also pass in an order param). To keep your client code clean, you can return a pagination object with the response so you don't need the client to figure out the id of the last object and build the url.

Re: Mistakes Beginners Make When Working with Databases

#187

Post by insulanian » Wed, Jun 08, 2016, 7:22 PM UTC

andysinclair wrote: post #20

I'm suprised by this point: "the database tends to be an after-thought in application design". Maybe it's a generational thing (I'm 40+), but I tend to always start with thinking about the data structures and design in conjunction with the UI design. Getting a solid database design in place early in the development cycle is critical to building a solid, stable system- you can of course alter the database as you start…

Oh yes! Database is the source of truth and often outlives the application on top of it.

Also, functional programming teaches to first think about the data structures and then about the processes/operations.

Re: Mistakes Beginners Make When Working with Databases

#188

Post by davidw » Wed, Jun 08, 2016, 7:24 PM UTC

mercer wrote: post #175

pietroalbini wrote: post #114

Earlier quoted context omitted.

That'a bug reported in 2003 and not yet fixed. https://bugs.mysql.com/bug.php?id=199

Oh wow, that's definitely good to know. I do quite a number of migrations and so far this hasn't bitten me, but it could.

Migrating from Mysql to Postgres might be a good migration :-)

Re: Mistakes Beginners Make When Working with Databases

#189

Post by mathattack » Wed, Jun 08, 2016, 7:33 PM UTC

lloyd-christmas wrote: post #177

mathattack wrote: post #163

Earlier quoted context omitted.

While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist in the future. Reminds me of the cynical saying for data warehouses, "Data in, but never out." :-) What I have seen frequently enough is myopic…

> The assumptions behind this then gets built into the code base This logic shouldn't be in the code base. A query should be isolated from the application logic, as I think we can all agree on. A change in the database should only require changing the query/procedure. Your business logic shouldn't be dependent on the internal workings of the query, just on it's input/output which shouldn't need changing. Adding a fea…

> And even if you fix the database, you are missing the historical hierarchies

I'm not following this one. Are you referring to an audit trail?

No - meaning if update the database schema, data will be missing that wasn't collected properly the first time. (If you didn't think you needed customer hierarchies, you didn't create them as customers came in)

I hear you on avoiding over-generalizing. That creates problems too.

Re: Mistakes Beginners Make When Working with Databases

#190

Post by treebeard901 » Wed, Jun 08, 2016, 8:09 PM UTC

Spidler wrote: post #184

treebeard901 wrote: post #128

Earlier quoted context omitted.

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

UUID internally is a 128 bit integer. Cheap as hell to sort.

To be fair, most of my experience with UUID/GUID is with SQL Server, and in that case it's stored as a 16-byte binary. Character string format: '6F9619FF-8B86-D011-B42D-00C04FC964FF' Binary format: 0xff19966f868b11d0b42d00c04fc964ff