7 ms·

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. Furt

by Grimm1 5y ago

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 yourself a headache. In fact it's like a big bullet point near the top of the docs page. "MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously." I'm not entirely sure why buzz around "developer learns basic knowledge" has this on the front page.

Nextgrid 5y ago
> I'm not entirely sure why buzz around "developer learns basic knowledge" has this on the front page. The problem is that in the old days, not knowing about indexes left you with an underperforming system or downtime. But in The Cloud™ it leaves you with an unreasonably huge bill and that somehow as an industry we're accepting this as normal.
- aspenmayer 5y ago
  Using money to solve business problems is good business sense, but only if that’s the best way to spend that money. I agree with you that the status quo is normal, but nonsensical.
- heisenbit 5y ago
  Yes we really should not accept this. The ability to impose limits on spending is key to control an enterprise. Whole security certification guacamole is based on having established controls. But where the bit hits the fan control is absent.
- Grimm1 5y ago
  Which really is a head scratcher. You'd figure especially as a startup seeing a 5k oopsie isn't really as acceptable. Mistakes do happen and I don't mean any shade to this particular person (they'll never make this mistake again) but as an industry the aggregate consequence of this is you have a lot of waste and stupid choices that then have to be cleaned up when more knowledgeable (read highly paid) people are introduced later on. They'll have to clean up the mess which causes real business consequences that, and I've personally seen this, will directly impact bottom line and have no quick or easy solution to wiggle out of. Maybe it's acceptable for products like this because the balance between good engineering and company health probably aren't as cut and clear but stuff like this always makes me sad because it's such low hanging fruit, it doesn't require any real effort, just basic curiosity around your job.
  - xupybd 5y ago
    The best technical people aren't always the best to start a business. The goal is to make money not have perfect code.
    - Nextgrid 5y ago
      If someone comes to me and tries to sell me a service that can leave me with an infinite bill I'd look at them funny and walk away. But that's just me and maybe I just don't get it and I'm not "startuping" right.
    - Grimm1 5y ago
      Cool, and if this was a case of bike shedding over something that hadn't just cost that early stage startup 5 grand I'd agree with you. However regurgitating a platitude that everyone, including myself, learned when we tried getting our first business off the ground doesn't add much value here. Had this been a Database with 10million rows it would have cost them 50k, and this is incredibly basic programming knowledge. Basic proficiency is a far cry from worrying about best technical talent and not a particularly egregious ask.
      - xupybd 5y ago
- williamdclt 5y ago
  > But in The Cloud™ it leaves you with an unreasonably huge bill and that somehow as an industry we're accepting this as normal. No. Nobody finds that "normal", that's just untrue. It's even the _whole_ subject of this blogpost: the bill was not normal. I don't disagree that some people are overrelying on cloud services, but that didn't become normality, it's still a beginner's mistake
  - Aeolun 5y ago
    > it's still a beginner's mistake Absolutely, but previously that wouldn’t cost you €5000 extra.
cerved 5y ago
they were using some kind of foreign keyless MySQLish whatever thing
yashap 5y ago
Seriously. Like, every junior dev has to learn DB indexing basics sometime, and apparently the other of this blog post just did. But really can’t understand why this article is getting voted to the top of HN.
- azeirah 5y ago
  What I gained from the article wasn't that the dev was unaware of indices, it's that he didn't realise indices were missing due to how planetscale's database disallows foreign keys. I never worked with a database that doesn't have foreign keys and it's not unthinkable to forget when you do for the first time, that foreign keys were what created indexes for you automatically. A little bit of planning could have prevented that though :/
pjscott 5y ago
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.
- williamdclt 5y ago
  If we weren't using underwhelming ORM DSLs, I'd love to use/write a github bot that automatically runs EXPLAIN ANALYZE on queries updated in a PR and post the query plan!
  - Aeolun 5y ago
    You can do that still. Just a bit more work to evaluate the ORM layer first.
derekdahmer 5y ago
I've been using relational databases for web apps for my entire career and probably would have made this same mistake if using PlanetScale for the first time. The author had two misunderstandings: 1) An index isn't created automatically 2) You get billed for the number of rows scanned, not the number of rows returned Even if I noticed #1, I probably wouldn't have guessed at #2 for the same reason as the author.
ampersandy 5y ago
> I'm not entirely sure why buzz around "developer learns basic knowledge" has this on the front page. Because it's a well written, humble account of learning from a mistake then using it as an opportunity to teach others to help them avoid the same mistake. If anyone leads a team, I hope they might learn from this approach, rather than just bashing on people and implying they don't deserve any attention because they made a mistake a more experienced developer might have dodged.
- Grimm1 5y ago
  Personally I find it trite but whatever floats your boat.
  - deleted 5y ago
    [deleted]
AnotherGoodName 5y ago
You'd be surprised and frustrated. If you ever see someone say "We hired Oracle consultants and they are miracle workers" or "NoSQL is sooo much faster than SQL" you can be pretty sure they missed databases 101 and the requirement to add indexes.
scottlamb 5y ago
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 might have even remembered to say they add an implicit index. But it's super easy to miss that you're depending on a side effect like that until you see the slow query (or, in this case, high bill). When you're programming, how many compiler errors do you see a day? (For me, easily dozens, likely hundreds.) Do you think each one indicates a serious gap in your knowledge? Along these lines: imposter syndrome is a common problem in our industry. One way it can manifest is junior engineers can thinking they're bad programmers when they repeatedly see walls of compiler errors. I think it'd help a lot to show them a ballpark of how often senior engineers see the same thing. [1] I know that when I'm actively writing new code (especially in languages that deliberately produce errors at compile time rather than runtime), I see dozens and dozens of errors during a normal day. I don't think this is a sign I'm a bad programmer. I think it just means I'm moving fast and trusting the compiler to point out the problems it can find rather than wasting time and headspace on finding them myself. I pay more attention to potential errors that I know won't get caught automatically and particularly to ones that can have serious consequences. 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. [1] This is something I wish I'd done while at Google. They have the raw data for this with their cloud-based work trees (with FUSE) and cloud-based builds. I think the hardest part would be to classify when someone is actively developing new code, but it seems doable.
- Grimm1 5y ago
  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.
watt 5y ago
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.

by Grimm1 5y ago

Nextgrid 5y ago

> I'm not entirely sure why buzz around "developer learns basic knowledge" has this on the front page. The problem is that in the old days, not knowing about indexes left you with an underperforming system or downtime. But in The Cloud™ it leaves you with an unreasonably huge bill and that somehow as an industry we're accepting this as normal.

aspenmayer 5y ago
Using money to solve business problems is good business sense, but only if that’s the best way to spend that money. I agree with you that the status quo is normal, but nonsensical.
heisenbit 5y ago
Yes we really should not accept this. The ability to impose limits on spending is key to control an enterprise. Whole security certification guacamole is based on having established controls. But where the bit hits the fan control is absent.
Grimm1 5y ago
Which really is a head scratcher. You'd figure especially as a startup seeing a 5k oopsie isn't really as acceptable. Mistakes do happen and I don't mean any shade to this particular person (they'll never make this mistake again) but as an industry the aggregate consequence of this is you have a lot of waste and stupid choices that then have to be cleaned up when more knowledgeable (read highly paid) people are introduced later on. They'll have to clean up the mess which causes real business consequences that, and I've personally seen this, will directly impact bottom line and have no quick or easy solution to wiggle out of. Maybe it's acceptable for products like this because the balance between good engineering and company health probably aren't as cut and clear but stuff like this always makes me sad because it's such low hanging fruit, it doesn't require any real effort, just basic curiosity around your job.
- xupybd 5y ago
  The best technical people aren't always the best to start a business. The goal is to make money not have perfect code.
  - Nextgrid 5y ago
    If someone comes to me and tries to sell me a service that can leave me with an infinite bill I'd look at them funny and walk away. But that's just me and maybe I just don't get it and I'm not "startuping" right.
  - Grimm1 5y ago
    Cool, and if this was a case of bike shedding over something that hadn't just cost that early stage startup 5 grand I'd agree with you. However regurgitating a platitude that everyone, including myself, learned when we tried getting our first business off the ground doesn't add much value here. Had this been a Database with 10million rows it would have cost them 50k, and this is incredibly basic programming knowledge. Basic proficiency is a far cry from worrying about best technical talent and not a particularly egregious ask.
    - xupybd 5y ago
williamdclt 5y ago
> But in The Cloud™ it leaves you with an unreasonably huge bill and that somehow as an industry we're accepting this as normal. No. Nobody finds that "normal", that's just untrue. It's even the _whole_ subject of this blogpost: the bill was not normal. I don't disagree that some people are overrelying on cloud services, but that didn't become normality, it's still a beginner's mistake
- Aeolun 5y ago
  > it's still a beginner's mistake Absolutely, but previously that wouldn’t cost you €5000 extra.

cerved 5y ago

they were using some kind of foreign keyless MySQLish whatever thing

yashap 5y ago

Seriously. Like, every junior dev has to learn DB indexing basics sometime, and apparently the other of this blog post just did. But really can’t understand why this article is getting voted to the top of HN.

azeirah 5y ago
What I gained from the article wasn't that the dev was unaware of indices, it's that he didn't realise indices were missing due to how planetscale's database disallows foreign keys. I never worked with a database that doesn't have foreign keys and it's not unthinkable to forget when you do for the first time, that foreign keys were what created indexes for you automatically. A little bit of planning could have prevented that though :/

pjscott 5y ago

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.

williamdclt 5y ago
If we weren't using underwhelming ORM DSLs, I'd love to use/write a github bot that automatically runs EXPLAIN ANALYZE on queries updated in a PR and post the query plan!
- Aeolun 5y ago
  You can do that still. Just a bit more work to evaluate the ORM layer first.

derekdahmer 5y ago

I've been using relational databases for web apps for my entire career and probably would have made this same mistake if using PlanetScale for the first time. The author had two misunderstandings: 1) An index isn't created automatically 2) You get billed for the number of rows scanned, not the number of rows returned Even if I noticed #1, I probably wouldn't have guessed at #2 for the same reason as the author.

ampersandy 5y ago

> I'm not entirely sure why buzz around "developer learns basic knowledge" has this on the front page. Because it's a well written, humble account of learning from a mistake then using it as an opportunity to teach others to help them avoid the same mistake. If anyone leads a team, I hope they might learn from this approach, rather than just bashing on people and implying they don't deserve any attention because they made a mistake a more experienced developer might have dodged.

Grimm1 5y ago
Personally I find it trite but whatever floats your boat.
- deleted 5y ago
  [deleted]

AnotherGoodName 5y ago

You'd be surprised and frustrated. If you ever see someone say "We hired Oracle consultants and they are miracle workers" or "NoSQL is sooo much faster than SQL" you can be pretty sure they missed databases 101 and the requirement to add indexes.

scottlamb 5y ago

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 might have even remembered to say they add an implicit index. But it's super easy to miss that you're depending on a side effect like that until you see the slow query (or, in this case, high bill). When you're programming, how many compiler errors do you see a day? (For me, easily dozens, likely hundreds.) Do you think each one indicates a serious gap in your knowledge? Along these lines: imposter syndrome is a common problem in our industry. One way it can manifest is junior engineers can thinking they're bad programmers when they repeatedly see walls of compiler errors. I think it'd help a lot to show them a ballpark of how often senior engineers see the same thing. [1] I know that when I'm actively writing new code (especially in languages that deliberately produce errors at compile time rather than runtime), I see dozens and dozens of errors during a normal day. I don't think this is a sign I'm a bad programmer. I think it just means I'm moving fast and trusting the compiler to point out the problems it can find rather than wasting time and headspace on finding them myself. I pay more attention to potential errors that I know won't get caught automatically and particularly to ones that can have serious consequences. 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. [1] This is something I wish I'd done while at Google. They have the raw data for this with their cloud-based work trees (with FUSE) and cloud-based builds. I think the hardest part would be to classify when someone is actively developing new code, but it seems doable.

Grimm1 5y ago
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.

watt 5y ago

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.