[HN Gopher] RegreSQL: Regression Testing for PostgreSQL Queries
___________________________________________________________________
RegreSQL: Regression Testing for PostgreSQL Queries
Author : radimm
Score : 143 points
Date : 2025-11-14 07:10 UTC (15 hours ago)
(HTM) web link (boringsql.com)
(TXT) w3m dump (boringsql.com)
| WilcoKruijer wrote:
| 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.
|
| [0] https://pglite.dev/
| radimm wrote:
| OP here - I do agree some of the problems that come with
| SQL/ORM queries are pretty horrendous and that's exactly where
| I would like RegreSQL going. For now I can't promise the
| particular direction, but comments like this are the reason why
| I pushed myself to release it and keep it beyond my own
| playground. Thank you!
| jci wrote:
| My goto for this lately has been ephemeralpg [0] and pgTAP [1].
| It's been pretty great
|
| [0] https://github.com/eradman/ephemeralpg [1]
| https://github.com/theory/pgtap
| mrasong wrote:
| Just found out about pglite, this library is insanely cool. You
| can even run Postgres right in the browser.
| jillesvangurp wrote:
| It's an area where people get conflicted between unit testing
| in isolation and integration testing. The compromise between
| those two approaches tend to be slow, flaky, and merely provide
| the illusion of code coverage which you shoot for with unit
| testing while not being quite realistic enough to push it
| towards the side of being a proper integration test. I've never
| liked tests like that and mostly consider them a typically
| gigantic waste of time. Code coverage is meaningless for
| integration tests.
|
| I deal with a lot of complex querying logic with mostly
| Elasticsearch. My appproach is to either unit test or
| integration test and just skip everything in between. With
| queries what I care about is that they work under realistic
| scenarios against the same version of Elasticsearch that we use
| in production. Creating test fixtures is expensive. So tests
| for read only querying shares the same test fixtures. That
| speeds things up. I don't care about separately testing simple
| crud operations because most of my scenarios trigger plenty of
| those. A unit test for that has no value to me. Unit testing
| whether my API can send queries to some fake Elasticsearch,
| empty Elasticsearch, etc. has limited value to me. There's some
| limited value in knowing the queries I'm sending are
| syntactically correct. But you get that for free with a proper
| integration test not failing. The unit test is redundant if you
| have a proper integration test. And a unit test without a
| proper integration test provided little practical value.
|
| What I actually do care about is all the complicated dashboard
| and other queries that make lot of assumptions about how data
| is structured, what fields are there, how they are indexed,
| whether they can be null, blank, or have invalid values, etc.
| work as intended. That kind of calls for an integration test.
| Anything trivial enough that a unit test would be good enough
| doesn't tend to need a lot of testing. Any scenario that
| touches enough of that stuff, kind of covers most of that.
|
| I put a lot of effort in ensuring that my integration tests can
| run quickly, concurrently, and don't interact with each other
| (data randomization). That allows me to get away with not
| deleting a lot of data between tests and gets me a lot of
| realism for free because real users don't have an empty system
| completely to themselves. So having a lot of tests running
| against a busy system is integration testing gold. I have close
| to 300 full API integration tests running in around 30 seconds
| on my laptop. Close enough to unit testing performance that I
| run them many times per day.
|
| The same approach applies to database testing. Probably more so
| because all the interesting bugs usually relate to constraints,
| transactionality, database locks, etc. If you have flaky tests
| because of that, it might actually be because your database
| layer has some issues with the notion of users not being polite
| enough to queue up one by one.
|
| This is not for everyone, I realize. Do what works for you.
| I've butted heads with people over this more than a few times.
| But in my company (of which I'm the CTO), we unit test
| functions, small classes, regular expressions, parsing logic,
| etc. We integration test systems and APIs. Testing individual
| queries without the rest of the system is hard and pointless.
| Test the API that triggers the query. That 30 second
| performance for test runs is something I spent a lot of time on
| getting. It means we can do major changes without fear. If
| tests pass, our users should be fine.
| sgarland wrote:
| > It's pretty terrible how poorly developers test their
| database queries.
|
| Yes. This becomes especially obvious when you rewrite ORM
| garbage for something complicated, and are told that they can't
| accept it, because they're not sure how to test it.
| h1fra wrote:
| 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?
| null_deref wrote:
| Yeah my question exactly, another one from me would be will the
| best practice be to run it periodically?
| radimm wrote:
| The primary direction is to make RegreSQL part of CI/CD
| pipelines. In theory in can be run against production DB, but I
| believe it needs much more work to provide real value there.
| Thank you for the comment!
| jelder wrote:
| Underrated point.
|
| Looking for performance issues on a machine with different
| baseline IO and CPU load, buffer state, query plans,
| cardinality, etc. is just theater and will lead to a false
| sense of security. RegreSQL is approaching a stateful problem
| as if it were stateless and deterministic. A linter like
| https://squawkhq.com is a good partial solution but only
| addresses DDL problems.
|
| RegreSQL would be better served by focusing only on the aspects
| of correctness that tools like SQLx and sqlc fundamentally
| cannot address. This is a real need that too few tools try to
| address.
| mbvisti wrote:
| I have nothing to add but this looks cool! Will definitely check
| it out :)
| jumski wrote:
| 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 :-(
|
| [0] https://pgflow.dev/ [1] https://github.com/pgflow-
| dev/pgflow/blob/main/pkgs/core/sup...
| mickeyp wrote:
| IMO, you should not avoid triggers if it helps prevent
| invariants in your database. That is what they are especially
| good at preventing.
|
| You can instruct postgres to raise exceptions using the same
| error code that constraints use: that way your clients do not
| need to know the difference.
| jumski wrote:
| Good point! For the SQL functions I mentioned, I'm
| comfortable without triggers - all mutations go through
| functions (no direct table access), and only start_flow is
| user-fac ing.
|
| That said, there ARE other places that would benefit from
| triggers (aggregate counts). I've avoided them because
| they're hot paths and I was worried about perf impact - relyi
| ng on pgTAP coverage instead.
|
| Your defense-in-depth argument is solid though. I should
| revisit this and benchmark whether the safety is worth the
| perf cost. Something like RegreSQL would come in handy
| radimm wrote:
| At this point it supports initialization through the fixtures
| systems (like inline SQL or SQL files). At the moment they have
| fixed order, which might lead to some limitations, but I'm
| already thinking about some pre/post test setup hooks and full
| schema handling as well (for full schema reloads).
|
| Plus I have whole set of other requirements where RegreSQL
| suddenly seems to be a good solution.
|
| And without sounding cliche - Thank you for the comment! This
| is exactly why I forced myself to go public and get this level
| of feedback.
| jumski wrote:
| No cliche at all - I'm in the same boat, showing my stuff
| online was way out of my comfort zone!
|
| I was postponing proper, dedicated performance testing for
| some time and would really love to up my game in that regard.
|
| I'm very happy with pgTAP approach of running stuff in
| transaction and rolling them back after the test - how this
| works in RegreSQL?
|
| Would love to provide feedback and test the hooks when you
| will be working on them. I'm mostly interested in performance
| testing and my use case would be to run them on CI and
| compare to previous metrics stored somewhere in order to fail
| CI when performance regressions are introduced.
|
| Happy to connect, got contact info in my profile.
| radimm wrote:
| For now only fixtures support transaction as cleanup
| options, but that's a good point that tested queries might
| also modify the queries.
|
| I will definitely reach out, just give me bit of time to
| mentally recover from the exposure and got some meet ups
| where I promised to deliver some presentations and they
| will consume a lot of my spare free time.
| jumski wrote:
| no rush, take your time and enjoy the fame :-)
| tinodb wrote:
| 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.
| radimm wrote:
| It's not unreasonable view - noted, will add to my list. Thank
| you!
| jitl wrote:
| To get prod scale out of a for loop I'm gonna need a few hours
| of iterations :-(
| andy_ppp wrote:
| This looks great, any plans for MySQL support (or a similar
| project), the legacy system I'm working on could really do with
| this!
| radimm wrote:
| I'm obviously biased. Adding MySQL support is not that
| difficult but maintenance is (and ultimately PostgreSQL is
| better way forward (half joking :))
|
| With current feature set it's something I have already
| considered but still undecided.
| esafak wrote:
| Are there interfaces that we can use to implement support for
| other databases?
| evanelias wrote:
| 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
| StarlaAtNight wrote:
| Wonder if the YAML fixtures drew inspiration from dbt's unit
| tests: https://docs.getdbt.com/docs/build/unit-tests#unit-
| testing-a...
| ForHackernews wrote:
| PgTAP is only mentioned offhandedly at the end of this article,
| but it's an excellent mature tool for unit-testing your database:
| https://pgtap.org/
| radimm wrote:
| OP here - I'm going to follow up with the separate article on
| pgTap. But the goals of both tools is slightly different in my
| mind.
| aranw wrote:
| How well will this work with something like sqlc [0]? sqlc has
| some custom syntax around the sql files specific to the library
|
| [0] https://sqlc.dev/
| radimm wrote:
| For now the syntax is not fully compatible - but my goal is to
| add https://github.com/boringSQL/queries (library behind SQL
| files parsing) to better align on it. It's definitely on my
| radar
| jitl wrote:
| 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 wrote:
| 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 that there's no silver bullet.
| isaacvando wrote:
| 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.
___________________________________________________________________
(page generated 2025-11-14 23:01 UTC)