Mistakes Beginners Make When Working with Databases • Hacker News Board

Re: Mistakes Beginners Make When Working with Databases

#41

Post by hobs » Wed, Jun 08, 2016, 4:20 PM UTC

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…

Also feel free to use the "bottom" of the keyspace instead of zero to start your keys if your database uses signed integers. (MSSQL I am staring at you)

Re: Mistakes Beginners Make When Working with Databases

#42

Post by lloyd-christmas » Wed, Jun 08, 2016, 4:20 PM UTC

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 not directly screen-by-screen design, one example I always see is that people default to creating an Address table. Many apps never store more than one address per user. If it's one-to-one, why force a join? (I think this is more of an overlap of both of our views though, and it popped into my head because Address tables are a pet-peeve)

Re: Mistakes Beginners Make When Working with Databases

#43

Post by KurtMueller » Wed, Jun 08, 2016, 4:20 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…

I think the advice on shying away from db normalization is a terrible idea for beginners. If anything, they should try to stick to creating db tables in 3rd normal form until they learn when and where to bend those rules.

Re: Mistakes Beginners Make When Working with Databases

#44

Post by drawkbox » Wed, Jun 08, 2016, 4:21 PM UTC

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 made where this has any impact at all.

- Some databases like Oracle you need a sequence to even do autoumbering, huge pain

- Autonumbering is a pain when having to replicate across environments or when you start getting multiple databases and clusters

- Numeric ids for important data is not exposed, prevents easily incrementing for next/previous (other ways to do this but this is one)

- Many databases have a UUID field or field optimized for unique ids/guids/uuids now

- If you were a piece of data wouldn't you want to be unique? All your data are unique snowflakes with UUIDs. On a serious note, this can help to identify data across all types and not just in the same table.

Re: Mistakes Beginners Make When Working with Databases

#45

Post by mathattack » Wed, Jun 08, 2016, 4:21 PM UTC

prashnts wrote: post #38

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…

But, aren't they frontend developers, and not the Backend developers?

It helps to have a broad view so you can plan and design accordingly. Back end developers should understand how people use the system, even if they're not going to own the designs. Similarly, to be great programmers, front end developers need an understanding of what goes on underneath the hood, even if it's not their day job. (I understand it's all part of the learning curve) The extreme of this is Joel's [0] exhortation that every programmer should learn C. It's a different context, but the same principle.

[0] http://www.joelonsoftware.com/articles/CollegeAdvice.html

Re: Mistakes Beginners Make When Working with Databases

#46

Post by emilong » Wed, Jun 08, 2016, 4:22 PM UTC

One way to generate UUIDs in a more index-friendly way, at least in Postgres, is to use uuid-ossp's uuid_generate_v1mc() function. They end up sequential, so maybe not the best if you need total unpredictability, but the lower order bits should be reasonably random.

h/t to Wayne E. Seguin for his article http://www.starkandwayne.com/blog/uuid-primary-keys-in-postg...

Docs here: https://www.postgresql.org/docs/9.4/static/uuid-ossp.html

Re: Mistakes Beginners Make When Working with Databases

#47

Post by hinkley » Wed, Jun 08, 2016, 4:22 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…

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 thinks they know what 'unique' 'random' mean but can't actually be trusted with that much responsibility (it's okay, most of us can't. I once stopped someone in the 11th hour from shipping a mutually authenticated SSL application that could only generate 256 unique AES session keys, due to a seeding bug. That was scary)

Re: Mistakes Beginners Make When Working with Databases

#48

Post by _RPM » Wed, Jun 08, 2016, 4:23 PM UTC

Facebook has ~2 billion users, and if they use 64 bit primary keys, theres no way they will reach that number (2^63) - 1

Re: Mistakes Beginners Make When Working with Databases

#49

Post by PaulHoule » Wed, Jun 08, 2016, 4:23 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…

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.

Re: Mistakes Beginners Make When Working with Databases

#50

Post by Jweb_Guru » Wed, Jun 08, 2016, 4:23 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…

Timestamp + server id can be uniquely guaranteed per server without coordination, so yes, it works fine :P Also, modern computers have monotonic clocks available and can use things like PTP.