4 ms·
I don’t understand how you can test Postgres performance in CI or on a developer laptop. Until your tables are large and varied enough, Postgres can ignore inde
by jitl 11mo ago
I don’t understand how you can test Postgres performance in CI or on a developer laptop. Until your tables are large and varied enough, Postgres can ignore indexes and prefer full table scans because it’s faster. Plans depend on statistics so your test data generator better output rows with the same distributions as you get in prod. Unless you have a large and representative load of concurrent queries, Postgres and filesystem caching can optimize around a single query shape, masking issues that will shit the bed in prod.
Example: I wrote a recursive query that provided a huge reduction in queries issued from our app for a traversal. Worked beautifully in local, and halved p95 in our dogfood environment. Yippee! In prod? The query always timed out after 60 seconds, even though it had the same query plan as dogfood env and staging. Sad trombone noises.
For query semantics regression testing, we just write tests in our normal test framework and run them in CI like any other test. Test data setup works like any other test, and we can call some of our millions of lines of app code to help out with repetitive tasks. To prevent cross-test leakage we wrap each test in a BEGIN..ROLLBACK, and transform inner use of transactions to save points in our db client layer. I’d like to add libeatmydata to speed things up further but haven’t done so yet.
- radimm 11mo agoLet me be clear - this is just baseline testing that's only valid for similar data patterns, same cardinality, and so on. Plus it's currently using EXPLAIN, so we're working with estimates, not actual execution data. But from my experience, this is low-hanging fruit for catching regressions that could break your application without you noticing. The performance work you're describing is the real nitty-gritty database management - the deep optimization work that truly makes or breaks an app. For that there's no silver bullet.
- jitl 11mo agoFor regression testing I struggle to see how a new SQL only test runner improves over a project’s existing testing systems. I think any project using a database should have integration tests that combine testing application logic with their database access queries. Your system does some explain checking but to me it feels tenuous if it’s worth the complexity required to dump all the queries my app may perform to text / YAML and adding yet another test runner in CI. I think one interesting angle would be to integrate your assertions directly into Postgres as a plugin. That way, you can spy on every query issued in any test framework, dump the text automatically, and prove additional assertions about the query plan index use perf etc, regardless of the user’s primary language. That would also get you out of needing to build 1000 different integrations for all the programming language / ORM combos in the universe. If you don’t want to do a Postgres plugin you could also do this as a Postgres wire protocol proxy.
- radimm 11mo agoHey, thank you for follow-up. Really appreciated. This is why I decided to go out and show RegreSQL. You're absolutely right that in an ideal world, every project would have solid integration tests covering their database access queries. That's the goal everyone should aim for. Unfortunately there again and again cases where developers are afraid of SQL and databases and it's not uncommon to see very simple things sending applications to crawl. As for the second point, that's a direction I would love to get. Whatever it can be directly, as part of open source or service is something to be decided - but tracking the trend data there would provide a lot of value indeed.
- isaacvando 11mo agoAt work we have anonymized dumps of our production data that we use for local development. This is extremely helpful as it allows testing things locally that would be completely unrealistic to setup otherwise. Your whole dataset might not be able to fit on a dev machine, but I'd guess in most cases you could create a subset of it that would be large enough to be useful for local performance work.
- jitl 11mo agoDo you use an off the shelf tool for anonymization or did you write something yourself?
- isaacvando 11mo agoWe using Tonic.ai. Although I am interested in writing something myself; I suspect there are a lot of ways it could be improved such as generating the anonymized version more quickly.