Let 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 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.
Hey, 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.
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.