Hacker News Reader

"Hey Infrastucture Finance Boss, we need a new database."

"Thats cool, how much per month should we budget?"

"$1.50 per 10 million rows inspected"

"What? How much is that per month?"

"TBC, and we won't know until we start using it"

"RDS is $x per month so you're sticking with that"

Errors get expensive, but DDoS attacks could literally kill your business.

Basically if you forget to query over an index and incur a full table scan, things are going to get very expensive, and it'll probably be a surprise since devs aren't going to bother checking charges run up.

This is sort of wait nails people with DynamoDB pricing. If you mess up your query, you end up scanning through tons of data, racking up the cost.

imo at least with dynamodb, its pretty straightforward what any given call is capable of doing, and you can get back capacity usage on every request to track. with these mysql/postgres compatible apis the actual query is abstracted as is its cost.

Yeah the pricing model for SQL based queries is not optimal at all considering you don't control how the query optimizer will use the indexes, and (not trying to assign malice) they aren't very incentivized to improve it.

Their CEO mentioned that they’re working on an updated pricing model to address this issue, in another HN thread: https://news.ycombinator.com/item?id=29133026

Does MySQL (or PlanetScale) provide a way to input a query and get a lower/upper bound on the number of rows the query will "inspect" when run?

MySQL’s “explain <query>” does exactly what you described and better. It shows indexes used, exact number of rows scanned etc. It’s the first thing I do when I suspect a query taking time. It’s a pity that not too many backend engineers know about it now a days.

That only explains the particular query plan chosen for the query at that time. This might vary depending on the table statistics, query parameter or moon phase if you're unlucky. It is a very important tool, but doesn't solve the problem here that in the end the database is in control and decides how many rows to read, not you.

>*might vary depending on the table statistics...

This is a good point. You can control for some variables (like query parameters), via testing, however as table statistics change, so does query planning. For instance, your table gets bigger (or smaller) and MySQL chooses a different query plan.

There's almost no way to completely predict pricing in all but the simplest of apps. Overall, the pricing model seems untenable.

> exact number of rows scanned etc

I'm reading MySQL docs [1] and I can't figure out which value in the example output corresponds to the "exact number of rows scanned".

> It’s a pity that not too many backend engineers know about it now a days.

I use EXPLAIN with PostgreSQL when needed but mostly focus on the execution time, not the number of rows scanned (reading a large row could be orders of magnitude slower than reading a row in a JOIN table of two integers in a row based storage engine).

[1] https://dev.mysql.com/doc/refman/8.0/en/explain.html

Let's just say that MySQL docs are not super helpful when you want to quickly refer to something.

Here's[1] an explain output that I ran just now. The row labeled "rows" is the actual number of rows scanned which as you can guess is bad because it's doing a full able scan.

I completely ignore the actual execution time and focus mostly on two things 1) indexes used; 2) number of rows scanned. If rows scanned is high it means I need to do something about it now even if the absolute query time is low. Either an index is missing or someone in the application code is querying the wrong way (random example, using a "like" on a full table).

[1] https://imgur.com/a/0Pj52QA

Yes, through `EXPLAIN`.

Their FAQ [1] clarifies this somewhat:

> Every time a query retrieves a row from the database, it is counted as a row read. Every time a row is written to the database, it is counted as a row written.

[1] https://planetscale.com/pricing (FAQ at bottom of the page)

> Reads: Retrieving or inspecting rows during a query or mutation of any kind to your PlanetScale databases

That's what the detailed pricing page says, which is somewhat different. The pricing is actually per row inspected, not per row returned to the client. So in aggregations you'd pay for every single row you aggregate. So I'd argue that the FAQ is actually misleading.

So does that make an accidental full table scan an expensive operation from a billing standpoint?

Like others say, the end user doesn’t have a lot of control over how the query planner does its magic. They do have control over what goes into and out of the system though.

That being said things like Google BigQuery bill by compute time and data examined so I guess that works as long as the price per “unit” is on the order of millicents or something.

Oof. Well that's not good.