With any new product there will be tradeoffs and rough edges but the positives, like easy migrations and database branches have definitely outweighed any difficulties.
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 queries will execute, etc? These seem somewhat basic, and would've probably caught this (also kinda basic, sorry :) problem early. (As in discovering "why is this query that should be a few ms with an index lookup taking so long?" early on)
The migration workflow is really cool, basically when we create a PR we branch our production database and apply any migrations that are included in that PR and then that branch is used in our per-PR review environment. (Just Heroku Review Apps), then when we merge the PR, we also merge the deploy request in PlanetScale. Database branching is a super powerful concept once you've leaned into it.
We don't really do any sort of load testing in a dev environment. We have one customer who is also a co-founder of our company so we just deploy whatever changes we're unsure about (after automated testing) to his application and see what happens. If anything looks off in Grafana we'll make take a look but it's usually "good enough" or "totally broken", very rarely do we take time to make something 25% better if it already works. The time to fix vs speed of shipping features tradeoff doesn't make sense for us.
In this specific case, the query was taking place in a background job so 10ms - 500ms didn't really matter to us so we didn't really measure the timing, if we had we may have noticed it was slow but kinda a testament to PlanetScale that we didn't even notice ;)
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-...