Mistakes Beginners Make When Working with Databases • Hacker News Board

Re: Mistakes Beginners Make When Working with Databases

#11

Post by epalmer » Wed, Jun 08, 2016, 3:58 PM UTC

knucklesandwich wrote: post #8

IMO normalizing up front is rarely ever a mistake. The primary advantage of normalization is it makes your schema amenable to changes in requirements. What if category ends up needing a description, and an id for a permalink-able page with links to each post in a category, etc.? Normalize up front and then denormalize where you need to when the requirements crystalize and your focus becomes responsiveness.

Only as a general rule: I have found that write once read mostly tables (history tables) are great candidates for denormalizing. State tables (read & write) usually benefit from 3rd form normalization.

Re: Mistakes Beginners Make When Working with Databases

#12

Post by ma1069 » Wed, Jun 08, 2016, 3:59 PM UTC

mjw_byrne wrote: post #4

Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have. I think this isn't a question of ints being a…

I had the same thought... And not just about point 3. All of these seems complex points with no absolute answer, as not every database really needs to scale up to several terabytes... Sometimes the reported "advices" might be useful, sometimes they might just unnecessarily complicate things...

Re: Mistakes Beginners Make When Working with Databases

#13

Post by perezdev » Wed, Jun 08, 2016, 4:00 PM UTC

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.

Re: Mistakes Beginners Make When Working with Databases

#14

Post by isp » Wed, Jun 08, 2016, 4:02 PM UTC

These specifically apply to Postgres, and don't necessarily generalise to all databases.

One example: counter-example to #4. Oracle database (since 11g) has a "fast add column" feature - which allows adding a non-NULL column with a default value to an arbitrarily-large existing table, without a "rewrite" of the table. (Behind the scenes, the default value is stored as metadata - and the default value is then read from the metadata for preexisting rows, rather than updating each and every row in the table.)

Re: Mistakes Beginners Make When Working with Databases

#15

Post by nsxwolf » Wed, Jun 08, 2016, 4:02 PM UTC

I recommend reading the article about pagination linked in the article. I came away from that thinking the LIMIT ... OFFSET approach is the best one to use in most circumstances. It's certainly a good starting point - you can monitor how well it suits your application over time.

Consistency of results may be an issue, but any scheme you use is either going to show inconsistency when you move between pages, or it's going to lie to you. That's the reality of concurrent modification.

Re: Mistakes Beginners Make When Working with Databases

#16

Post by endymi0n » Wed, Jun 08, 2016, 4:03 PM UTC

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 they often use 64bit integer keys for a lot of entities, see Graph API or Adwords). Will wreak havoc on insert and join performance and index size. 64 bit is more than you'll ever need even for the most serious application.

- Default values on NULL columns: Okay advice, but a pretty random issue

- Going away from normalization is optimization that's mostly premature and regarding the drawbacks should only be done with utmost care and to resolve specific performance problems, not as a general approach.

Overall, pretty mediocre advice.

Re: Mistakes Beginners Make When Working with Databases

#17

Post by _quick_q » Wed, Jun 08, 2016, 4:03 PM UTC

acveilleux wrote: post #7

mjw_byrne wrote: post #4

Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have. I think this isn't a question of ints being a…

This was probably the weakest suggestion in the article. That said, the default SERIAL type in postgres is 32-bit signed integer so it can be surprising when you exceed 2B items.

That's why BIG SERIAL exists. :-)

Re: Mistakes Beginners Make When Working with Databases

#18

Post by Someone1234 » Wed, Jun 08, 2016, 4:04 PM UTC

Illniyar wrote: post #6

mjw_byrne wrote: post #4

Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have. I think this isn't a question of ints being a…

With scale its not about key exhustation, its the ability to be able to create a key in multiple servers without having to consult a central location.

Indeed, which the article fails to even explain.

I'm legitimately not sure the author understands why large distributed systems utilise UIDs so just made up a reason that sounded good to them.

I haven't used a non-64 bit database in, let's say ten years, so making design decisions because a 32 bit int might be exhausted is rather dated advice at best.

Re: Mistakes Beginners Make When Working with Databases

#19

Post by stevecalifornia » Wed, Jun 08, 2016, 4:07 PM UTC

"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 sequential, auto-incrementing integer for your clustered index column and make another second column that is your primary key that is your UUID.

This article feels like the blind leading the blind.

Re: Mistakes Beginners Make When Working with Databases

#20

Post by andysinclair » Wed, Jun 08, 2016, 4:07 PM UTC

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 building but having a good idea of the data structure should be a "before thought" rather than a after-thought.