Spending $5k to learn how database indexes work • Hacker News Board

Re: Spending $5k to learn how database indexes work

#91

Post by fabian2k » Sat, Nov 06, 2021, 8:46 PM UTC

Nextgrid wrote: post #82

Buttons840 wrote: post #59

Earlier quoted context omitted.

It's not so clear cut. What's the cost of losing the entire on-prem database? Do you trust a company who hired a developer who didn't know about indexes to hire a rock solid DBA? And how much does that DBA cost?

> What's the cost of losing the entire on-prem database? Backing up an on-prem DB doesn't require specialist DBA knowledge. Basic UNIX skills are enough. Not to mention, since you're not in the cloud, bandwidth or efficiency is not a concern - feel free to rsync your entire DB off to a backup server every 5 minutes. > to hire a rock solid DBA? And how much does that DBA cost? They didn't have a DBA here either, and t…

Rsyncing the database won't work in many cases, this doesn't ensure your backup is consistent. That is really, really dangerous advice, especially as you might not notice this if you test the process while the database is idle.

For Postgres you either use the pg-dump command and backup the dump or you setup WAL archiving and save base backups and the WAL files as they are created.

This isn't rocket science, but you really should read the manual at least once before doing this. Just copying the files is not the right way to backup a database (unless you really know what you're doing and are ensuring consistency in some other ways).

Re: Spending $5k to learn how database indexes work

#92

Post by Nextgrid » Sat, Nov 06, 2021, 8:49 PM UTC

Grimm1 wrote: post #86

scottlamb wrote: post #80

Earlier quoted context omitted.

Good for you. But I think you're being uncharitable by failing to distinguish between "concept I didn't understand" and "thing I forgot to consider until I saw the problem it caused". The title also suggests the former, but I think the author is being a bit humble by underplaying his existing knowledge. Likely he actually did know what indexes are before; if you asked him to detail how MySQL foreign keys work he migh…

No you've missed my point, the author seemingly didn't know that ForeignKeys applied indexes by default in MySql. It's not "Concept I didn't understand", clearly they're capable of understanding because they did after they ran into the issue. It's about not having had basic knowledge to begin with. But he didn't see compiler errors, he caused monetary cost to his employer. When I deploy something that unintentionally…

> When I deploy something that unintentionally causes a large monetary bill to my employer, then yes I do believe that indicates a gap in knowledge so I don't in anyway believe I'm being uncharitable.

It depends, if you've been given a loaded footgun it's not entirely your fault when it inevitably goes off.

Let's go back to your "compiler errors" scenario, and let's say someone decided that the company should be using a cloud-based compiler that happens to charge per error. I wouldn't blame developers for falling into a trap that challenges all known assumptions.

The problem is that there is a DB that charges insane amounts of money per row processed with no upper limit and that someone actually thought it was a good idea to use it.

Re: Spending $5k to learn how database indexes work

#93

Post by Nextgrid » Sat, Nov 06, 2021, 8:51 PM UTC

amenod wrote: post #90

radu_floricica wrote: post #54

I'm just leaving this here: https://www.hetzner.com/dedicated-rootserver/ax161/configura... Draw that nice red slide all the way to the right. No, it's not storage. Yeah, it's actually affordable. Yeah, that was a sexual sound you just made. You do have to be prepared to know some basic sysadmin, or pay somebody to do it for you. My newest server has about 60 cores and half a tera of ram. Surprisingly, it's not uber…

...and Hetzner just started offering their services in the US a few days ago. (EDIT: not affiliated) If you do something stupid with your code at least you won't go bankrupt, only your service will be slower.

With the performance of these servers you have a huge margin for stupidity before you even notice any slowdowns.

Re: Spending $5k to learn how database indexes work

#94

Post by Nextgrid » Sat, Nov 06, 2021, 8:53 PM UTC

fabian2k wrote: post #91

Nextgrid wrote: post #82

Earlier quoted context omitted.

> What's the cost of losing the entire on-prem database? Backing up an on-prem DB doesn't require specialist DBA knowledge. Basic UNIX skills are enough. Not to mention, since you're not in the cloud, bandwidth or efficiency is not a concern - feel free to rsync your entire DB off to a backup server every 5 minutes. > to hire a rock solid DBA? And how much does that DBA cost? They didn't have a DBA here either, and t…

Rsyncing the database won't work in many cases, this doesn't ensure your backup is consistent. That is really, really dangerous advice, especially as you might not notice this if you test the process while the database is idle. For Postgres you either use the pg-dump command and backup the dump or you setup WAL archiving and save base backups and the WAL files as they are created. This isn't rocket science, but you r…

I am not saying that rsync or cp is the right way to backup a DB, I was just giving a very crude example. I absolutely agree with the issues you're raising.

However, I'd still take recovering a DB that has been backed up by rsync/cp over a DB that's not been backed up at all. If you really can't be bothered to do it the right way, you're still better off doing something than running with no backups at all.

Re: Spending $5k to learn how database indexes work

#95

Post by Grimm1 » Sat, Nov 06, 2021, 8:54 PM UTC

scottlamb wrote: post #89

Grimm1 wrote: post #86

Earlier quoted context omitted.

No you've missed my point, the author seemingly didn't know that ForeignKeys applied indexes by default in MySql. It's not "Concept I didn't understand", clearly they're capable of understanding because they did after they ran into the issue. It's about not having had basic knowledge to begin with. But he didn't see compiler errors, he caused monetary cost to his employer. When I deploy something that unintentionally…

I think you didn't read through to this part of my comment: > I think the most important thing the author learned is that failing to add an index can cost this much money before you notice. > Ideally the author and/or the vendor will also brainstorm ways to make these errors obvious before the high bill. Load testing with realistic data is one way (though people talk about load testing a lot more than they actually d…

No I did, but since I disagree with your earlier point about how much existing knowledge they have it kind of by default means I disagree with what they took away from this incident.

It's also highly speculative so like I'm not going to go back and forth on it.

Needing a vendor to hand hold your likely highly paid dev seems like a bad fix to me.

Also not having an index isn't an error it can be a valid choice based on your situation and query load which is why people should know the situations when they're needed.

I think people should simply be better. A lot of people don't like hearing that though so usually I keep it to my private chats where people seem more willing to cop to that fact.

I know we disagree, I know you're going to continue disagreeing, I know I don't want to have the conversation.

Re: Spending $5k to learn how database indexes work

#96

Post by watt » Sat, Nov 06, 2021, 8:58 PM UTC

Grimm1 wrote: post #14

I feel like indexes are a pretty fundamental type of DB knowledge. In fact I'd say it's table stakes knowledge you should have if you're working with them. Further more, knowing that ForeignKeys typically apply an index to that column is also in my head basic knowledge. I'm sorry you got burnt, and congrats on learning a lesson, but you could have gotten the same knowledge by ever googling MySql ForeignKeys and saved…

You are absolutely missing the point. The point is not about indexes or full table scans, but it's a about cloud providers who will charge you for every row "inspected" and how a full table scan might cost you $0.15 and it would add up. It's not about slow performance which you can diagnose and fix, it's about getting an unexpected $5k bill, which you can't fix.

And in the end, if the cloud provider wants to charge you for rows "inspected", this can't be buried in small print. That's unacceptable!

The billing must come with up-front, red capital letters warning, and must come with alerts when your bill is unexpectedly little high (higher than expected, not just 10x or 100x higher). It must automatically shut down the process, requiring the customer confirm they want proceed, that you actually want to spend all that money. And it must be on the cloud provider to detect billing anomalies and fully own them in case it goes the wrong way. This is the cloud "bill of rights" we need.

Re: Spending $5k to learn how database indexes work

#97

Post by Grimm1 » Sat, Nov 06, 2021, 8:59 PM UTC

Nextgrid wrote: post #92

Grimm1 wrote: post #86

Earlier quoted context omitted.

No you've missed my point, the author seemingly didn't know that ForeignKeys applied indexes by default in MySql. It's not "Concept I didn't understand", clearly they're capable of understanding because they did after they ran into the issue. It's about not having had basic knowledge to begin with. But he didn't see compiler errors, he caused monetary cost to his employer. When I deploy something that unintentionally…

> When I deploy something that unintentionally causes a large monetary bill to my employer, then yes I do believe that indicates a gap in knowledge so I don't in anyway believe I'm being uncharitable. It depends, if you've been given a loaded footgun it's not entirely your fault when it inevitably goes off. Let's go back to your "compiler errors" scenario, and let's say someone decided that the company should be usin…

First its not my "compiler errors" scenario it's the person who initially replied to me. Sure whatever, I don't think I ever insinuated I thought that was a good idea, it runs in parallel with the issue I have.

Re: Spending $5k to learn how database indexes work

#98

Post by scottlamb » Sat, Nov 06, 2021, 9:00 PM UTC

Grimm1 wrote: post #95

scottlamb wrote: post #89

Earlier quoted context omitted.

I think you didn't read through to this part of my comment: > I think the most important thing the author learned is that failing to add an index can cost this much money before you notice. > Ideally the author and/or the vendor will also brainstorm ways to make these errors obvious before the high bill. Load testing with realistic data is one way (though people talk about load testing a lot more than they actually d…

No I did, but since I disagree with your earlier point about how much existing knowledge they have it kind of by default means I disagree with what they took away from this incident. It's also highly speculative so like I'm not going to go back and forth on it. Needing a vendor to hand hold your likely highly paid dev seems like a bad fix to me. Also not having an index isn't an error it can be a valid choice based o…

> I know we disagree, I know you're going to continue disagreeing, I know I don't want to have the conversation.

Please consider not chiming in on the next article like this then. I think your attitude of (paraphrasing) "no good programmer would have made the costly mistake you shared, and articles about it aren't worthwhile" is super harmful to our industry. It's the polar opposite of the blameless postmortem approach I'm fond of.

Re: Spending $5k to learn how database indexes work

#99

Post by tristor » Sat, Nov 06, 2021, 9:01 PM UTC

Nextgrid wrote: post #92

Grimm1 wrote: post #86

Earlier quoted context omitted.

No you've missed my point, the author seemingly didn't know that ForeignKeys applied indexes by default in MySql. It's not "Concept I didn't understand", clearly they're capable of understanding because they did after they ran into the issue. It's about not having had basic knowledge to begin with. But he didn't see compiler errors, he caused monetary cost to his employer. When I deploy something that unintentionally…

> When I deploy something that unintentionally causes a large monetary bill to my employer, then yes I do believe that indicates a gap in knowledge so I don't in anyway believe I'm being uncharitable. It depends, if you've been given a loaded footgun it's not entirely your fault when it inevitably goes off. Let's go back to your "compiler errors" scenario, and let's say someone decided that the company should be usin…

I am somewhat shocked to find that an RDBMS is considered a “loaded footgun” in 2021. Perhaps grandparent isn’t the most charitable in their interpretation, but I am in full agreement. It continues to astound me how little about the basics of databases most developers know, and how strongly resistant they are to trying to learn.

Re: Spending $5k to learn how database indexes work

#100

Post by [deleted] » Sat, Nov 06, 2021, 9:02 PM UTC

Nextgrid wrote: post #93

amenod wrote: post #90

Earlier quoted context omitted.

...and Hetzner just started offering their services in the US a few days ago. (EDIT: not affiliated) If you do something stupid with your code at least you won't go bankrupt, only your service will be slower.

With the performance of these servers you have a huge margin for stupidity before you even notice any slowdowns.

[deleted]