Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Spending $5k to learn how database indexes work

briananglin.me

Post reply on HN
Print view View original

81–90 of 209 posts

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

#81

Post by marcinzm »

mike_hock wrote: post #78
dreyfan wrote: post #17

Don’t use DB providers that charge for rows/data scanned. Use Amazon RDS or Google Cloud SQL or just install it yourself on a VM. Pay for CPU, memory, and storage instead.

Rent metal and run your own MySQL/Postgres/... One insert every 3 seconds. Could run that off a 10 year old laptop.

The time spent setting it up and managing it and then having to deal with backups/environment clones/access control/scaling limitations/etc. outweighs the savings for almost any company paying US wages. Especially since you'd need metal for everything and not just the db due to network latency.

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

#82

Post by Nextgrid »

Buttons840 wrote: post #59
Nextgrid wrote: post #42

Earlier quoted context omitted.

I have no problems with a developer doing a 5k oopsie with things like card processing or an area that has a legitimate potential for direct monetary losses (such as payment processing where a bug could allow customers to order goods without actually paying). I have a problem with whoever looked at and said "nah, let's go with a cloud-based solution that charges per-query and gives us an essentially infinite financia…

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 this "cloud" didn't save them. But at least with an on-prem Postgres the worst they'd have is significantly reduced performance*, where as here they had a 5k bill.

*actually the price/performance ratio for bare-metal servers is so good that a 100$/month server would probably take their unindexed query and work totally fine (as a side effect of the entire working dataset being in the kernel's RAM-based IO cache).

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

#83

Post by radu_floricica »

flippant wrote: post #76
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…

What are you using this for?

www.couriermanager.com - SaaS for courier companies, basically. The new server is the common pool instance - I have others for dedicated clients.

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

#84

Post by pjscott »

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…

One of the best database habits I've ever developed is to run EXPLAIN on every query that I expect to run repeatedly, then sanity-check the output. It's very little effort, and has prevented so much hassle.

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

#85

Post by brasetvik »

anglinb wrote: post #75
brasetvik wrote: post #55

Earlier quoted context omitted.

Kudos for being open about your mistakes. Could you share a little bit about what your thought process was in general when picking a database technology? You call out "easy migrations and database branches" outweighing other quirks, so some pros and cons weighing must've happened :) Is it easy, for example, to test things in your dev environment with realistic amounts of data, and to get an understanding of how the q…

Thanks! The decision making process was pretty unsophisticated tbh. Basically I spent the last couple years working at GitHub as a security engineer and had been pretty comfortable with MySQL so wanted to stick with that. I had heard from our database team how annoying migrations were and I had previously locked a postgres database multiple times in production trying to deploy a migration, so MySQL + safe migrations…

Thanks for the background :)

Lock behaviour of migrations are indeed very important to be aware of.

Braintree (which are heavy Postgres users) have a pretty good post on that - https://medium.com/paypal-tech/postgresql-at-scale-database-...

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

#86

Post by Grimm1 »

scottlamb wrote: post #80
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…

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 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. Or and this would be worse, a lack of caring. (Which is not what I think happened here though)

I won't respond to your imposter syndrome bit I don't really think it's relevant to my point.

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

#87

Post by Nextgrid »

marcinzm wrote: post #81
mike_hock wrote: post #78

Earlier quoted context omitted.

Rent metal and run your own MySQL/Postgres/... One insert every 3 seconds. Could run that off a 10 year old laptop.

The time spent setting it up and managing it and then having to deal with backups/environment clones/access control/scaling limitations/etc. outweighs the savings for almost any company paying US wages. Especially since you'd need metal for everything and not just the db due to network latency.

Every cloud-hosted startup I've consulted for had a full-time devops guy wrangling Terraform and YAML files. The cloud requires an equivalent time investment.

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

#88

Post by Nextgrid »

Spooky23 wrote: post #26
cperciva wrote: post #6

Earlier quoted context omitted.

It's not quite so crazy when you phrase it as $0.15 for reading a million items from the database .

25 years ago i built and ran a biggish database system that supported a reservation system. Even given the limitations of the time (RDBMS cost, 9GB disks, Sun kit, etc), our cost of good sold for that type of workload was exponentially less. (At scale) Today, I could probably run that company off my MacBook Pro and have room to spare. That said, the rationale for choosing this technology is cute: “After seeing a ton…

I have seen so much over-engineered startup shit costing thousands in AWS fees that could run faster on my laptop.

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

#89

Post by scottlamb »

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…

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 do it). Another would be watching for abrupt changes in the operations the billing is based on.

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

#90

Post by amenod »

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.

Post reply on HN