It's pretty terrible how poorly developers test their database queries. This looks like a great step in the right direction. I think how the ORM story in RegreSQL develops is crucial. The SQLAlchemy integration looks interesting, but at the same time super specific. There are a million ways to generate SQL statements and ORMs are just one of them. A question that comes to mind is how will you handle interactive transactions? I'd say most complexity in queries comes from the back-and-forth between database and server. Is that out of scope?
Would also be fun if you could support PGLite [0], that's what I've been using to write "unit" tests connected to a "real" database.
Interesting. Perf regression can happen locally but they mostly happen in prod when data change in volume or in shape, can this run safely on a prod db?
Looks really well thought out and I will be testing it for sure!
I'm wondering how I would be able to regression-test functions in my project (pgflow [0]) - it tracks a graph of state machines modeled in few tables. State is mutated only by calling few exposed SQL functions (task queue worker does it).
Given I can't enforce everything I need with check constraints and I try to avoid triggers if possible, I opted for only using exposed SQL API [1] for setting up state in my pgTAP tests.
It is imperative and harder to maintain, like scripts you described in the article, but really my only option, as I want to have maximum confidence level.
Does RegreSQL support some kind of init scripts or I would need to wire it myself and just run RegreSQL after the proper state is set? Would lose the "run once and get report on everything" benefit then :-(
Nice! However I would actually advocate for fixtures in application code. I’ve seen too much drift otherwise. And creating “scale” is also easy, just add a for loop :). No programming in yaml needed. As an added benefit you can use the same fixtures for your end to end tests!
So it would be nice if RegreSQL would support fixture hooks for those who like this route.
Not quite the same workflow, but in Percona Toolkit there's a tool called pt-upgrade which can provide similar information. See the use-case doc section on "reference results to host" comparisons: https://docs.percona.com/percona-toolkit/pt-upgrade.html
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.
At 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.
12 comments
[ 2.9 ms ] story [ 36.4 ms ] threadWould also be fun if you could support PGLite [0], that's what I've been using to write "unit" tests connected to a "real" database.
[0] https://pglite.dev/
I'm wondering how I would be able to regression-test functions in my project (pgflow [0]) - it tracks a graph of state machines modeled in few tables. State is mutated only by calling few exposed SQL functions (task queue worker does it).
Given I can't enforce everything I need with check constraints and I try to avoid triggers if possible, I opted for only using exposed SQL API [1] for setting up state in my pgTAP tests.
It is imperative and harder to maintain, like scripts you described in the article, but really my only option, as I want to have maximum confidence level.
Does RegreSQL support some kind of init scripts or I would need to wire it myself and just run RegreSQL after the proper state is set? Would lose the "run once and get report on everything" benefit then :-(
[0] https://pgflow.dev/ [1] https://github.com/pgflow-dev/pgflow/blob/main/pkgs/core/sup...
So it would be nice if RegreSQL would support fixture hooks for those who like this route.
[0] https://sqlc.dev/
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.