Michael: Hello and welcome to PostgresFM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard, and I'm joined as usual by
Nik, founder of PostgresAI.
Hey, Nik.
Nikolay: Hi, Michael. Michael: And we also have Radim Marek with us, who is a consultant
and Postgres enthusiast, whose BoringSQL site we have mentioned
many times on the show.
Welcome, Radim.
Radim: Hello guys.
Thank you for having me.
Nikolay: Thank you for coming.
Michael: Yeah, absolutely.
So we wanted to talk about a newish project you have kicked off
called RegreSQL about regression testing SQL queries or SQL
queries.
Very cool.
It got quite a lot of uptick on Hacker News, a lively discussion
on there.
Congrats.
But also, I'd be interested in discussing with you about how
you got into the topic, like regression testing in general.
Where would you like to start?
Radim: Okay so that's the tough topic to start with.
So effectively it wasn't my project or it didn't start it as
my project it actually has started as a complementary project
to a book called The Art of Postgres, which I still believe is
one of the best things you can do to learn how to work with Postgres.
Nikolay: Dimitri, right?
Radim: Yes, Dimitri.
And I'm trying to get in touch with him forever, like last 4
years, But I'm not able, so maybe if he hears this podcast that
I will have a chance to talk to him.
So I did start with the project because I was kind of a bit worried
about how people deal with the SQL queries in their code.
Because usually it's either generated by ORM and you don't see
it or it was a string hard-coded in my case Go file so you can
see it in TypeScript files.
And all the testing around this one originally kind of bothered
me because you have an infrastructure which was difficult to
start, set up, and everything.
And I said there must be a simple way.
Something that actually running a simple query doesn't take minutes
or seconds to do all the infrastructure tiered up, tiered down.
And I found RegreSQL that is doing this job.
And I tried to fix the project.
And fast forward, I think 4 years, because it's actually quite
a long time ago.
I found another use cases because I'm working on some educational
products and I needed to test whatever users are hitting the
metrics that I'm interested.
And then I kind of figure out, yes, this is actually where it
can come in because it was comparing whatever the output the
correctness of the query and that was only step away because
then I realized okay every single incident is based on increased
time on SQL queries and that was just a you know small step to
go from something that is predictable because time is unpredictable.
I got into something predictable and this is where I started.
So I started committing, I started adjusting it.
And right now I'm actually working on a version 2.0, which is
a big update in terms of functionality and in terms of developer
experience.
Nikolay: But how do you approach the testing?
So I understand the tool.
For regression testing, it's super important these days, especially
because there are no more excuses not to have 100% coverage of
tests and proper coverage, not just like good-looking coverage.
And performance testing in CI, and even before your AI assistant
commits and pushes code, that should have happened before probably.
How do you approach the fact that we need a lot of data to have
proper plans?
Radim: Okay, so this is effectively a never-ending topic, how
even developers can get to something which is representative
of their database.
I actually call this 1, I think that happened somewhere on Hacker
News with somebody from Supabase.
We came up to a topic which was called, it works on my database.
That's the same issue.
But to answer your question, as I said, initially I naively thought
I can do a time, But time is all over the place and that wasn't
working.
So the only way how to approach this 1 is buffers and cost.
Nikolay: Let's pause here because I think it's super important.
I think industry doesn't understand it yet.
We chatted about buffers with Michael 2 years.
Finally, Postgres 18 has it by default and explain and analyze,
this is great, like absolutely great.
So, industry doesn't realize how important it is to shift focus
from timing to buffers still, right?
Why timing is not enough?
Why is it not so good when you have to control performance.
What's your point of view on this?
Radim: Okay, so timing.
Timing is a perfect metric because this is what you see if you
come in the middle of incident.
Timing is actually the easiest to spot metric.
Queries are timing out, Hopefully all of us have, you know, statement
timeouts, transaction timeouts, everything in place.
Nikolay: And humans need the good timing.
This is our final goal, right?
Radim: Exactly.
But the problem is, A, if you want something fast, explain alone
doesn't give you timing because that's just, you know, what the
planner thinks.
And EXPLAIN ANALYZE can't give you something that is predictable.
So if you want to do a regression testing, You can't rely if
you are running on, I don't know, underutilized AMD machine or
ARM machine.
Or, you know, everybody these days, we have Apple Silicon Macs
and they are super fast.
Their I/O is fast.
So timing is generally, that's the-
Nikolay: Doesn't work in CI.
Radim: It doesn't, yes.
Well, it doesn't even work in production because you will get
a big spread of queries.
You still have outliers.
1 customer can hit 10,000 rows.
1 customer hits 5 rows.
How do you...
Nikolay: What's the locking weight on locking acquisition as
well?
Yeah, yeah.
That's great.
Yeah.
I think it's even
Michael: worse in CI because flaky tests are a nightmare.
So even if, for example, we accepted double the time, if we said
like fail this test if it's double the time and that worked 99%
of runs, when it fails but for a bad reason, when it fails, it's
picked the same plan, no indexing changes, nothing has actually
changed, but there was just some something else happening on
the machine at that moment, and it took, it kind of blitzed past
its p99, we got that 1 time it was slow, and the test fails,
that's not a flaky test, that's a test that sometimes fails when
it shouldn't, like false positives.
And those are always the nightmare tests.
Nikolay: Before we proceed to the solution, we all agree, buffers,
right?
Still like let's pause maybe, I have a crazy idea, I'm thinking
for several years about this.
Maybe it's time to implement it.
Probably it's a better topic for our hacking sessions with Kirk
and Andrey we have usually on Wednesdays live.
But your opinion, I'm very interested to hear your opinion.
What if we still keep analyzing timing, but imagine, you know,
wait events, right?
Which usually are used in aggregated form, like how many sessions,
aggregate, and so on.
What if that analysis would be brought to explain and analyze
to understand how this timing was spent exactly.
Was it heavyweight lock acquisition waiting?
Or I/O was too slow this time?
If in this case, wouldn't it help to extract the, like to structure
the whole latency, to split it to pieces, and in CI context,
extract pieces which are like unpredictable, okay, this doesn't
matter, okay, waiting time, it doesn't matter, waiting on lock
acquisition.
But here we see a lot of actual CPU non-waiting, which usually
is null, right?
But there is a trick there.
But anyway, wouldn't it help to keep in the, like, still keep
timing as main metric for regression, testing in CI for performance?
Radim: I believe in theory that would work, but for me the complexity
behind this is already implemented on the cost and buffer level.
So I'm not sure if the benefit would actually be having better
metrics.
Because what I found out is, and we can get to the discussion
is, you know, buffers are fixed.
If you have a predictable data set that you start with, and you
have a ideal state scenario.
I think that's the important ideal state because production is
never in ideal state.
You will have a bloat, you will have a, so, you know, my regression
testing is never- Always changing.
Exactly, everything is changing, so it depends.
But if you want to do a regression testing, you just need a matrix,
1 or 2 of them, which are predictable.
Buffers are always predictable.
Nikolay: Yeah, I'm just, I'm so so interesting because you came
and talked what I was like shouting, shouting on every event
basically.
Buffers, I even have t-shirt with buff, explain what is buffers,
where buffers is bold.
By the
Michael: way, it can depend a little bit, unfortunately.
It depends what you mean by buffers, but like if you include
planning buffers, which are sometimes reported, like there are
states that, like for example, if for example, autovacuum just
ran, you might end up with a different, if analyze's just run,
you might end up with a different number of planning buffers
than if it hasn't just run or if it hasn't run recently.
So there are like slight variations.
So yeah, it might not be perfect.
But cost is an interesting 1, for sure.
Nikolay: So, okay, what I'm going to do, I will postpone my comments
about why not only buffers, and maybe let's return to timing,
and we will discuss maybe later, but let's agree with you, like
buffers is the thing that doesn't depend on the state so much.
So we just check hits and reads of shared buffers, also temporary,
like everything.
It's I.O.
Database is all about I.O.
Right?
But still the question remains how like your approach, like you
need a lot of data to reproduce the executor behavior so you
will see proper number of buffers, right?
You cannot do it with database sizes small.
Radim: Yes, that's the part.
It works on my database because developers they will start with
hundreds.
Cardinality of the data is going to be very small.
And I haven't seen a single company that wouldn't have a problem
with how to get a kind of representative set of data to developers
machines.
Well, I know there are attempts, but like if you take an average
software team, they usually have huge issues on this one.
So my issue, and that's actually part of the reaction to Michael's
comment, is I do always have to start with a clean slate.
So for me, this is not a production, so I have to count that
the vacuum ran and the statistics are up to date.
The original solution, which I described, fixtures, they are
the obvious solution, so you know I will always have to improve
the ways how to improve the fixtures.
But the way how to do it, and this is actually my roadmap towards
version 2.0, is to do a snapshot building.
You've probably seen, well you actually seen because you have
discussed it, that instant database clones.
That article wasn't a coincidence, it was actually for me need
how to get a fast start state for the regression testing, and
I needed something that works.
This is why I had to dig, and then I actually, same as you guys,
I was surprised it's already there, you know, because it wasn't
something I was aware actually exists in version 18, but suddenly
everything was done.
So it's not for production use, which you mentioned correctly,
because there are still issues.
But if you fix a easy to get starting point, if you imagine you
have a predictable build, which actually is coming, it's now
already committed, but it's coming for version 2.0 as part of
the developer experience.
And you have a fresh statistics, you can run the query.
And if your configuration, Postgres configuration is actually
a very interesting part because you can change the cost, how
it's calculated but you will always get the same buffers.
Exactly.
Nikolay: If you talk about hits plus reads probably the distribution
will be different.
Also hits might be hitting the same buffer multiple times.
So there are nuances there.
Radim: But the total, it's like always the same.
So, and you Nik, you said it, we all of us came from a spindle
disk.
So you remember times where you try to hit the control C on the
query and it was just flushing WAL files on a disk.
And we can say that I/O is cheap these days, but it's not.
It's still the only metric that you have to do.
And I/O, whatever there's a contention, will affect time.
So for me, certainly buffers is number 1.
So there is nothing more predictable.
Cost is very close.
And I actually did a, I haven't done like a scientific research
on the topic, but if you take the 1 version of Postgres and you
run through different architectures and same settings, the variance
is surprisingly low.
Not all
Nikolay: settings.
There is a specific set of settings because you can have much
less memory, shared buffers can be very small, you just need
to set proper effective cache size, proper all the planner settings
and work memory.
We have an article about it.
And then I agree with you, it will be the same planner behavior
for the same version.
That's it.
It's great.
It's magic.
You can have Raspberry Pi and repeat the haver of huge like 800
core machine.
It's insane.
Radim: Exactly.
This is what I did.
I actually took my old Raspberry Pi, I took Hetzner, I took a
GCP machine And the difference was something around 2%.
So right now I have a threshold of 10% by default, but I'm looking
forward to actually make it configurable.
That's great.
And let's let people decide.
But and then the answer to all your initial questions is I don't
have to care about timing because in production, if you would
have 1000 transactions per second, I/O will get saturated, you
know, And that will naturally raise the timing.
So if you care about buffers, you don't have to care about timing,
because timing is just an extrapolation of problems that happened
before actually the query returns.
Nikolay: Some people say, like, let's do proper full-fledged
benchmarking just to study behavior of planner for a few queries.
It's wrong idea because you have so many moving parts around.
You just narrow down to single plan, single session, right?
And focus on what matters.
But here, like my...
I have...
Before I had that question.
So you mentioned create database in Postgres 18, you mean strategy,
right?
When you can copy-on-write clone and when you have create database
with clones, template 1 database, but not in regular form, but
basically very fast because of copy-on-write.
Does it mean you use ZFS or something, Btrfs or what?
Radim: Well, it depends what you want.
I'm not forcing people to use anything.
I'm just offering an option if you want fast because you know,
another part of frustration was that everything in your integration
test that touches the database is slow.
Yeah.
And the way how people normally use it, it's slow, but you know,
running most of the queries it's like literally milliseconds.
Nikolay: Yeah, yeah.
And we have new 1 branching, we have other copy-on-write branching,
so that's great.
And here, if you compare, do you know about DBLab or not?
Radim: Yes, I know.
Nikolay: Yeah, so I'm comparing the various approaches and this
approach, like when we have multiple logical databases and physical,
is great because this is exactly what we try to achieve with
the DBLab, but not many people realize what you just said.
I hope with this episode more people will realize.
And the thing is that if you allocate this machine, you can have
a huge database.
This wonderful Postgres 18 feature, if You use ZFS, we don't
care about time anymore, almost.
So we focus on buffers.
Postgres doesn't know which file system we have.
And then you can achieve constant price for many, many tests
in parallel.
Unlike Neon or Aurora, thin cloning, you need to pay.
If you run 10 tests in parallel, for example, you can have 10
ideas from LLM, you need to pay 10 times more for compute.
Yes, storage is solved, but compute is not.
Here, create database, 10 databases in parallel, it's good.
The only thing compared to DBLab is that it's not possible, you
cannot have a major upgrade inside a clone and have independent
versions in parallel.
But Maybe some more differences, but in general, it's great that
it comes to Postgres naturally.
This feature is a great feature.
I hope it will get traction in CI environments.
Radim: I think it's actually great to have this natively.
I'm not saying it's perfect.
It's just an option.
Nobody is forced into a third-party solutions, everybody will
pick whatever they want and, you know, I'm not against Neon or
anything, I think you evaluate your requirements and you say
what's the best approach you want to take.
Nikolay: Well with Neon it's also the same, like you can, you
just pay for compute but also you can study planning and behavior,
put it into CI.
The thing is that you need to focus on buffers because cold startup
will be different, right?
But we don't care anymore if you focus on buffers.
That's the magic, right?
That's great.
And cost, also cost.
Because this is what says how planner things.
The only problem is there are some queries which are out of this
methodology.
For example, when people who deal with RLS, they know this problem,
right?
If you have a query with counting million rows and there is a
hidden filter involving current setting, it's going to be executed
for each row And it's not buffers.
It's pure CPU time.
But these are exclusions, right?
We have only limited number of them, right?
Radim: Exactly.
I would say before hitting that problem, that is 99% of problem
we can solve much easier.
Nikolay: I agree.
And still you can troubleshoot it on this machine because getting
those million rows is much faster if you have this copy-on-write
and your methodology.
I mean, what you said, it doesn't mean you should stop thinking
about timing, right?
You still can think about timing, right?
Radim: You can, it will hit you.
That's what's going to hit you in production at the end of the
day, because this is what the user perceives when they render
the page waiting for the output or something, but it's not predictable.
Yeah, The
Nikolay: problem is we gave this tool, DBLab, to many hundreds
of engineers and noticed, I think thousands already, and noticed
that some people expect that it's full reproduction of production
behavior.
They say, why my Index is being created 3 times longer than in
production.
Well because quite different environment, right?
So there is some adjustment needed in in a mindset to test like
this, right?
Well, okay enough about thing calling and branching.
Let's think about tooling.
Michael: Yeah, I had a question going back to cost tolerance.
You mentioned allowing for 10% higher costs, and that confused
me slightly because I was thinking on CI we'd always be running
on the same, effectively a clone of the same Database static
state you mentioned why would the cost why do we need to allow
for 10% higher costs potentially
Radim: that was initially my naive protection effectively so
before I did the benchmarking I said you know 10% literally I
just there was no measuring I just said 10% sounds reasonable.
When I did measuring, I found, as we discussed, you know, there
is slight variance, but it's not actually, I think on average
was below 2%.
Nikolay: I would like to see details here because I think there
should be 0 difference.
Let's talk, I'm going to follow up with you on this because there
are some cases which I don't know, I'm super interested to understand.
And
Radim: yeah.
I will just, you know, just to finish the topic because, you
know, I did extensive testing in terms of establishing it I haven't
dig into a reason because you know some were our machine some
were very you know Raspberry Pis so that wasn't comparable and
that was the where the difference came from Some queries were
0.
Nikolay: But Planner doesn't know about hardware.
It doesn't know anything about hardware.
Radim: We actually had this discussion on the Prague Postgres
meetup 2 months ago, that it will be actually very interesting
to go into a differences between ARM and Intel processors.
What actually is, if there is some default somewhere, I have,
for example, seen that I/O startup time, again, it doesn't involve
buffers, but the I/O
Startup times on the cold I/O
Are much slower on ARM in general.
I believe there might be something else.
Nikolay: Different code paths.
Exactly.
Right, right.
This I can imagine, yeah.
But it's not about hardware, just code paths.
The architecture might matter.
Yeah, that's interesting.
Yeah.
What's also interesting here, I think we touched an interesting
topic.
Could zoom in there.
It's because it's super interesting.
When they say 10%, I think it's because in CI, you need to define
some thresholds to define where, when it's failed, when it's
passed, right?
And it's when you work manually, you have some like thresholds
in your mind, you don't realize, but then you need to code it
basically.
And this is a challenge, right?
Radim: Exactly.
This is where it started from.
And this is actually where I hit a wall with the original release
of the tool because people didn't understand, you know, you said
it, people didn't understand buffers, why they matter so much.
People didn't understand why something is changing.
And I had to start thinking again.
And this is, you know, I think if you would go to original blog
post, now there is a notice that I had to take the fixtures out
of the test because people were kind of linking it together and
they were getting different data sets and they didn't use it
correctly.
My fault was that I didn't specify properly that that was the
issue with the blog post.
But this is why the version 2.0 is actually heading in the direction
because we have touched on the topic.
You can have a multiple snapshots and those snapshots will give
you a different experience but the same baseline on a given snapshot
should be stable or more or less stable over time.
And then it depends because your business will change, business
requirements will change.
Then for example, yesterday I committed a change which is a selective
update.
So you can literally link a change in baseline to a commit so
you can say and blame what actually was that kind of historical
trend change on a query.
So this kind of adds another dimension because suddenly you don't
have a good blame only who broke something.
You actually can trace it back to how did it affect the performance.
And this was actually on one of the posts, Michael, you had on
your blog about this optimization from 7 milliseconds to something
you hit the index scan.
And that was actually a big inspiration because I said, you know,
this is actually very important because the reason that query
does 7 milliseconds, that doesn't matter.
But if you save so many buffers, you should be able to trace
it back even on the positive outcome.
So at 1 point, there can be a layer which will take all those
optimizations.
And this is where we will get to very interesting topic and that's
the topic in this AI.
Nikolay: Right, experiments, right?
Radim: Experiment, well, I wouldn't call it experiments.
I call it set reality because I believe most people-
Nikolay: I mean, code is written by AI only right now, right?
Radim: Yes.
Yeah,
Nikolay: yeah.
And how do we control it, right?
So as I said, we need not only 100% coverage, but it should be
real tests should be inside, right?
Including performance, I agree.
Yeah.
Radim: So, yeah, we had a discussion with Michael in London during
a PGDay.
If you have a context which AI don't have at 1 point, you can
then see regular SQL actually in a very different light, because
it knows the query, it knows the baseline, it has a snapshot,
and it has a context, which is the schema.
Nikolay: Exactly.
And statistics.
Radim: Exactly.
You have statistics.
So you have suddenly much more data, which you need.
And I had to, you know, all of us, all of us have to think about
how AI is going to change our work.
I'm thinking
Nikolay: every day since 2017.
Radim: Exactly.
I have a weekly session which I do and you know, I'm trying to
digest the news and do this 1.
But there are things that just confirmed that this is the right
direction because the joke is 84% of developers use some sort
of assisted coding or the vibe code or whatever, we all do it.
Yes.
But the same 84% of people, they don't write SQL queries anymore.
Nikolay: Yes.
Radim: If they were bad in writing SQL queries, you know how
a LLM SQL queries look without the context.
I actually found it, you know, this is the deformation you have
when you look into a, how Planner works.
I found it very funny because it doesn't differentiate whatever
you're running Postgres 12, 14, 18, it will mix all that advice,
it kind of reads the blog post which is just you something wrong
and people will just go with it.
And if you put this 1 in context of a 10 times more pull request,
Who's going to review it?
And now you see where all this coming through because if you
don't have time to review it, if you don't have the human aspect
of reviewing it, and if you don't have the DBA knowledge, I think
the lost DBA knowledge is actually kind of key here.
You need something that will govern that a your tests are fine,
you can write unit tests, integration tests on a couple rows,
but how do you do it if you have to return 5,000 rows?
This is very difficult And this is what RegreSQL actually
was doing ever since start.
You can take a 5,000 rows and compare them.
And it can suggest you, you know, order is wrong or maybe, you
know, there is a wrong field and it will notice it.
So actually that comes somewhere between unit test and integration
test, it can measure or control that your data and your contract
is still valid.
And this is where I believe the direction of the tool is actually
having a big future.
Nikolay: That's absolutely great.
I just like everything you say makes total sense, absolute sense.
Yeah.
That's great that we like think in the same direction.
That's all I can say.
Radim: Well, thank you.
But I would say, you know, this is, if you have experience dealing
with production system, this is what you will effectively arrive
to.
Yeah.
Because you have to generalize, you have to find underlying issues.
Nikolay: We need guardrails and protection, automated protection
at a larger scale.
We cannot say, oh, let's not, let's prepare to run those pipelines,
right?
Because they're expensive and long.
We need to make them cheap and fast, radically, right?
So we can run like 50 of them, right?
And check all the things and throw away all hallucinated stuff,
right?
This is this is the way yeah This is the way And then comes to
in many details including for example There are some hard problems
which come from production side not from development side.
For example plan flips You know like and then it's different
so but it's a different problem if we split these problems anyway,
I agree the most danger to performance right now is the AI.
But it's also great.
But, Like, I mean, it's wild west right now, right?
So we need some practices and tools to be established.
Radim: But just to kind of put it in contrast, I actually believe
most issues are actually very simple because most of the incident
they start, you know, I'm not sure if I'm the only 1, but most
incidents you see are literally simple, missing index.
Or somebody rewrites index without understanding how it should
be rewritten.
And you know, they obviously verify it locally.
It works fine because they have a small database but they don't
notice that the query exploded 100 times in terms of buffers
retrieved and that will have an impact.
Well, 100 times is easy, that usually you can survive it.
Nikolay: Well, important point here also I noticed if you say
1000 buffers people don't understand you.
If you say 8 megabytes they start understanding how much it is,
right?
So you just multiply by block size And this is magic.
So simple magic, you know.
Radim: It is.
That's, and you know, this is, I don't want to sound as a promotion.
I'm building like it's called labs.
So, you know, we both have labs of some sort.
Nikolay: For experimentation, it's right.
Yeah.
Radim: Because for me, and you mentioned it before, developers
want same environment as production and they don't understand
why it's different.
And how many of us, I think 3 of us, we are lucky because we
had access to a really busy production environments and we know
how it looks like.
But if you take an average developer, during their career, they
will never actually touch environment that has more than 10 TPS
and maybe gigabyte of data, they don't simply see it.
This is why, you know, if you trace the BoringSQL block, everything
comes down to, this is fine on your database, but it will eat
your life on production because it won't work.
Nikolay: Yes, I compare it to bicycle.
It's hard to be DBA because you need to learn, but where to learn?
If you don't have access to terabyte scale and heavy workloads,
if the only practice you have is production, it's basically learning
bicycle, how to ride bicycle on highway.
Some people survived and they are called DBA now.
Others they say the database is slow, it's dangerous, don't touch
it.
If you have an environment where you can safely make mistakes,
I saw magic when people have access to terabyte scale clones
and they start to do really crazy stuff and realize it's wrong,
but nobody affected.
And this is how learning is actually works, you know, it works.
So yeah, you're basically 100% resonating in my mind what you
do.
Radim: And then take whatever you do, because I'm actually following
your post on LinkedIn about self-driving Postgres.
Now you take, people won't write SQL queries.
They will not, let's forget that it's a regression testing framework,
they will not have this experience and they will never hit issue
that Postgres would be badly tuned.
Yeah.
That suddenly increases the gap and sure, there will be AI which
will help but you still need a manual or human operator at 1
point or somebody who will be able to diagnose right when wrong.
Nikolay: Because real testing shifted to production that's why.
And we just need to shift it left again.
Yeah.
Radim: Exactly.
So I think it's all about clarity and giving people, sure, there
will be, I think we will never have 10 times more DBAs.
We will have probably 10 times less DBAs.
But we need to make sure how the new people will actually learn
those skills and how they will learn them predictably without
that fear, without that, you know, being scared and who knows
what will happen in 5 to 10 years.
Nikolay: Yeah, I agree.
I agree.
And also it's very resonant to what happened to code and containers.
Containers is the way to have reproducible tests.
It's standard, right?
We just need the same for data.
And copy-on-write is the key.
But also methodologies should be adjusted, buffers.
So everything is so.
And I agree, DBAs is like sysadmins.
It's in the past.
People need to learn Postgres who build stuff and know how to
operate bigger databases coming from various angles.
I have front-end engineers who already start understanding things.
Or Supabase, Supabase is a great example.
He attracted a lot of front-end engineers to learn SQL and so
on, right?
That's great, I think.
Radim: And that's the boring part, you know, I have to make the
joke because, you know, for me literally the direction where
I'm going is those boring things are with us for 40 years, they
will be with us for quite some time.
And they just work, those skills are reusable, no matter if you've
read them in LLM or not.
Somebody needs to understand them.
Nikolay: Yeah, yeah.
So can you explain what your tool does?
So assuming we understood this, that we need to focus on buffers
during optimization and establish baselines focusing on buffers
and cost.
And we managed to achieve the same data using this feature in
Postgres 18.
What does your tool do and who should use it?
Radim: Okay, So the main goal of the tool is to find all your
SQL queries and help you in some way to write a plan because
you know queries need parameters, it needs to have a way, So
you need to write a fixtures or provide your dump database, anonymized
database.
That's your choice.
I'm not actually forcing you in anything.
You have to get it to predictable state.
That's your snapshot.
Then you run it on your queries and based on your plan, each
query can have 1 to n plans.
That means what's the variation.
So you can have LLM to generate you plans, you know, using random
values.
You can have a fuzzy generated plans and that means every single
plan will execute that query once.
What the tool does, it captures the expected, the original tool
did it also, it captures the data.
So what you have is right now it's a JSON with all the data
that it produced.
This is your baseline And that's the contract for correctness
of the queries.
Then you have the functionality for baseline.
So right now the version 1.0 does EXPLAIN only, but version 2.0
will have a full EXPLAIN ANALYZE because that's what I need for
buffers.
And it records that data.
Nikolay: Yeah.
What about other settings, modern settings like memory and serialize?
Radim: That is interesting because again, this is the part of
the 2.0 roadmap because snapshot can't be consistent.
You have to have a data, you have to have a now even configuration.
So I didn't, I'm not saving all the configuration, all the metadata
about where it's running and how it's running, but I'm actually
saving this as part of the snapshot.
So if something changes, like if you change a configuration,
what's the cost of sequential scan, it will warn you.
You change something and I will probably give you wrong results.
So you still have to interpret the data.
At 1 point you arrive to a scenario where you have a good Commit
and you can distribute the snapshots for testing.
So ideally, you can have a couple of versions of the snapshots.
You can have a small snapshot, which will have 100 megabytes,
200 megabytes.
You can have a mid-size 5 gigabyte snapshot, which you can use.
And then you can have a large snapshot, 50 gigabytes or something.
And then every time you run test, you will get your report, what
actually has changed.
And that change is based on the cost of the buffers.
Nikolay: Yeah, that's great.
And you capture also version, I think, right?
And settings.
Yes.
And some like statistics and structure of table, which indexes
we have and so on, right?
We're basically in the same, like, met at some point, it resonates
a lot, but you come from development more, I come from production
more.
And in production, what also matters often, including buffers,
for example, if you think about index-only scans, heap fetches
matters a lot, right?
And this is the state of production which can be bad or good.
And capturing from production the state, some statistics might
make sense as well.
Do you see somehow it could play or not?
For example, when last time this table was vacuumed, how much
dead tuples it has or how much bloat accumulated, something like
these things, you know, or it's outside of your scope?
Radim: I spend so much time thinking about this topic.
And if I would ask, if you would ask LLM, that would say, yes,
that's your natural direction.
But I actually proactively said no to this because this is a
different domain.
You know, you have to guarantee what's broken on the development
side.
You have to make sure that contract, which is also a performance
or buffers, is the same or better and you track it, but you can't
replace this in production.
Because in production you can have a latency, you can have a
replica, which run in different availability zones, you can have
a network issues, You can have, you know, so many issues that
can happen in production.
And there are tools that I effectively, I can't compete with.
And I think that wouldn't be, that wouldn't be my goal.
And because my angle is education effectively.
For me, it stops with CI.
Nikolay: Yeah, it again resonates 100%, because what you do,
you say, let's split the system into pieces and study each piece
separately.
And when we understand each piece, like, behavior fully, then
we can go and increase complexity.
And this, that's why you shouldn't use pgbench to study plans,
right?
Because pgbench is
Radim: very cool
Nikolay: to test the whole system.
And also that's why you shouldn't bring too much from production.
Study your system first, how it works, and then let's go to production,
understanding our system and knowing what it's capable of already.
Then we bring complexity.
This is like one of the oldest scientific methods, René Descartes,
right?
Let's split the pieces, study each piece, and then composition
to have whole understanding.
Without this split, it's super hard and you always have too many
moving pieces, right?
So I like this, but still I'm from production, so I care about
the state of production, you know, it's different.
Yeah.
Cool.
Michael: I think we're talking about different things.
They were, I really liked that.
This is about regression testing.
This is about not breaking things that like ourselves through
development.
This is not breaking correctness, whether that's a human changing
a query or an LLM chatbot changing a query.
So correctness is like primary importance, that makes sense.
And ideally not affecting performance, but we can't measure production
performance in test, in development.
So all we can do is get a proxy for it.
I don't think we can anticipate every single potential performance
issue in development that could hit production.
Nikolay: We've produced really difficult problems.
Yeah, with point-in-time recovery, sometimes you can reproduce
really hard problems at macro level.
Michael: Reproduction is different from anticipation though.
Anticipating, so to get it into your regression test you would
have to anticipate every possible issue and I think personally
I really like the direction of let's do 1 thing at a time, let's
tackle the common things, let's
Nikolay: look at the costing.
I'm not
Michael: arguing with the
Nikolay: whole concept, I'm just saying that also is possible,
it's just much, much heavier, much more expensive, time consuming.
In development we need to move faster to write a lot and Check
dozens of ideas at the same time like in parallel.
This is what we need right and this is perfect approach So the
Michael: last the last thing I wanted to say is I've personally
I personally think that This hasn't been a topic that's been
particularly of interest to people for many years.
I've expected it to be like I thought it should be.
I've worked at a company previously that made tools in this area
that weren't very successful and I was actually pleasantly surprised
by how much positivity and interest there was in on the Hacker
News discussion and in your in your revamp of this tool.
So for me, it's actually feels like people are ready for this
topic for some reason.
I don't know what changed or what.
Nikolay: What is writing there?
Who knows?
Michael: Do you think that's what it is, Radim, from your perspective?
Radim: I think this goes to, there's not 1 answer.
First is going back 1 topic, how many percent of incidents you
deal are really production-based?
Like no matter if it's human or LLM, you know, I'll be just guessing.
90% of all incidents are change management that didn't go well
and something happened.
So this is why the regression testing actually when you Microsoft
you can't measure but if you take a snapshot something which
is now working you should actually do something that is measurable.
You measure if you're improving on performance.
So it's you know sure contract is important but actually if you
start with a 0.0, you can technically say we on average go up
and down in terms of performance.
This is very easy to measure.
And you know this 1 for example, you know how many times you
double guess when you upgrade from Postgres 17 to 18, what's
the impact?
You can suddenly put a number and if you somehow put all your
queries and do that number, you can put that number into a bucket.
And the second 1 is no matter what you do, I was asking myself,
you know, why nobody thought about this 1 before?
I actually don't understand why Nobody did this because it's
predictable.
The metrics were there, but nobody did it.
And from my experience and the frustration I had was all the
incidents were very easily avoidable because the metrics were
not in the 10% margin, they were in 1000%.
Every single incident started with 1000% difference.
Nikolay: I have also 1 question about this tooling.
So how do you capture queries to test?
Because for example, if we have ORM, it produces various queries.
We can see pg_stat_statements, normalized queries in production,
but for each query we might have multiple plans depending on
parameters.
So how to find those queries?
I have my own methodology.
It's actually described in some articles.
We could collaborate maybe a little bit on it, but what's your
approach?
Radim: This is the difficult part.
I'm not going to lie.
I come from Go background, and if you take, ORMs are not that
popular in Go, I would say.
For a framework like SQLX and things like this, 1 day they encourage
you to write SQL queries by hand.
And this is actually where it started.
So, effectively, my first commits were about supporting multiple
queries in 1 file.
So I have a different base.
There are even frameworks like SQL C, I think that generates
code based on the queries or vice versa.
But that would keep the target market very slow.
So the obviously next step, and that's the support of the different
frameworks.
So I think in my article, I did a SQL alchemy.
That was 1 that actually allows you to save the queries.
And this, if you remember, we talked about the plans.
And plans are that variation of arguments.
Nikolay: Right.
Radim: So you see, you have an artificially generated snapshot,
which is fixed, and then you need to generate the plans.
And plans variation, if you write 1 plan, your query testing
is not going to be this 1.
So you need to generate, and this is actually where LLM can help
you.
Because suddenly you have a tool which can create the variation,
create even hallucinate data.
And you will be testing queries with a, I would actually be quite
happy to have hallucinated parameters because it will show you
whatever something is changing or not.
Nikolay: It's a proactive approach actually, because my approach
is to take everything from production because we deal with really
large systems and we need to understand when we, for example,
perform major upgrade, there are no plan flips, so planner or
plan flips are positive.
But what you say, like, let's use LLM to generate parameters
which probably will happen next month, right?
Like some edge cases we haven't met before.
I like this a lot as well.
Yeah.
Radim: Yeah.
So you have to use what you are afraid of.
So if we are afraid of randomized queries, then why not use it?
And effectively said, yeah, I need a variation of thousand plans.
Nikolay: Yes, again, like the problem is like, again, I have
slightly different opinion here because I'm coming from production
experience.
I would say we can generate like thousands and thousands of various
cases, maybe 1,000,000 queries to test, but it will increase the
longevity and the price we pay for CI and testing, right?
So we need probably to prioritize some queries.
How to prioritize?
We need to study production.
No?
Radim: Exactly.
Okay.
You need to understand your business requirements, you need to
understand your outliers, you need to understand your smallest
customers, you need to understand your biggest customers, you
need to understand the patterns which are happening.
Nikolay: That's also an interesting comment, because I was thinking
about like this tooling, Oh, by the way, parameters right now
is just an idea, it's not implemented yet, a collection of...
Radim: No, that's already...
The queries are already parameterized.
Yeah, I understand the
Nikolay: picture, but if I have already a large 10TB database
with a complex workload and I want to start using your tool,
will it help me to create a set of queries, example queries,
which will be tested?
Radim: Queries we have to extract from somewhere.
So no, I would say answer is no, because right now I assume you
have a queries or way how to capture the queries, either by having
them in the SQL files or by being able to have a CI task that
will generate those SQL files from your ORM.
Nikolay: Yeah, I would like to have an additional call with you
to discuss because I have an approach maybe we could connect the
dots here and see if it can land because this methodology probably
works with your tool naturally, you know, how to inject this
approach to an existing big project.
We deal mostly with projects already starting, hitting walls,
they come to us for help.
So I understand completely how to start from scratch, but when
you have something already, there it can be difficult.
Another thing I wanted to point out, what I hear, you say smaller,
medium-sized, bigger.
Sometimes we have only 1 database and that's it.
Some systems like SaaS, which is installed only once.
But sometimes we develop software which has many installations,
small, big ones.
So it's interesting.
I think the approach when you define some reference databases,
this works better when we have multiple installations of various
sizes?
Is it what you think?
Radim: Yes, as I said, there's no universal answer how to generate.
And many people actually wrote me about fixtures being completely
wrong.
But what I'm trying to get past is I don't care about the features.
I care about the way how to generate a snapshot.
And if you have the same cardinality and same data, you can then
measure correctness.
So obviously the investment in a tool like RegreSQL is actually,
it's a big 1.
You have to start using it and you will get dividends of months
on.
You might get some dividends as you go, but actually the true
value will only go.
So the developer experience and then whole life cycle, that's
maybe a version 3.0, because you know, there needs to be much
more support.
So the tool itself is not important as much, it's the whole experience
in the process because methodology, exactly.
Nikolay: So what I'm actually interested in, like let me share
like to be honest with you and what I have in my mind listening
to you.
For example, GitLab, they use our approach.
Many engineers say it's great and we have case study publishers.
Everything is great.
But listening to you, I realize actually we always cared about
the biggest GitLab.com installation which is huge, multiple,
it's huge.
We focused on solving that problem.
We solved it, not fully, but solved how to test queries there.
It can be proved always.
But now I'm thinking, actually, GitLab has hundreds of thousands
of other installations and they have reference.
We actually should care about that as well.
So it's interesting because we could generate different sizes
of database and cover it by testing them as well.
So it's interesting because I was always thinking about the biggest
1, you know, because usually problems usually there.
Right.
Radim: Exactly.
You hear about the biggest ones.
This is where, you know, the production breaks, the incident
starts, the query, which was fine.
It sounds similar, but it's a very different problem.
But you need to find a some sort of boundary sometimes you can't
solve all the issues and maybe you need to find a different solution
Nikolay: right right right yeah so it's
Michael: just out of interest Nik what would you expect to break
on a smaller instance?
Is it that it's just like a different shape, for example, on
GitLab.com's main instance, maybe there's tons of 1 type of object
and in a different customer's installation there's a different
distribution and therefore plans are different?
Or is it like...
Nikolay: I can tell you.
So what we think about, like, we have various kinds of testing,
like, red data testing, It's like ultimate final testing with
basically production clones, which only a few people can have
access.
But also, you cannot download this database due to size and also
restrictions, PII and so on.
But what if you need to care about multiple installations in
this case, the direction DBLab is moving in is having full-fledged
database branching Git-like, but also cloning between multiple
setups.
And We already run on MacBook.
So I'm thinking if you care about smaller databases as well,
developers could basically download reference database and iterate
much faster because I hate how CI is organized right now.
If AI is hallucinating, in production if I hit it, it's an absolute
nightmare.
But hitting even in GitLab CI or GitHub Actions, hitting problems
there, it's already too late for me.
I want to iterate much faster.
I want all the tests to be run before push, git push, right?
In this case, if we have a DBLab of a smaller size, you download
it and like don't hunt only for the biggest case, medium size,
small size.
I have like a terabyte or so here.
I can have a good example database on my laptop and iterate much
faster and before push I already have much better code, you know.
Michael: Yeah, so catch more issues earlier and only catch the...
Nikolay: Shift left what you can shift left.
Michael: Yeah, that makes sense.
Nikolay: Okay.
So it's saying let's test earlier.
Ideally, developers should test.
Usually they don't have time, but AI, I have no excuses to AI.
AI should test everything.
I pay for it, right?
Tokens, not time, right?
That's it.
I think it's a perfect time to think about all levels, and if
you can shift left to your laptop to your machine, you should
do it.
So future DBLab versions will be able to clone data between,
to pass snapshots between 2 DBLabs.
You can have it on your machine and just extract and then do
something, extract back and share with your colleagues some tests
and so on.
Yeah, anyway, I'm still in agenda all the time because it's actually
my topic.
I'm so glad you came actually.
Radim: There's actually so many topics and I think we can go
for another hour because you know, it's like the support.
I think last night I pushed was the same 1.
You take the snapshot, you run it against migration, either a
SQL file or command.
So you can actually trigger a third-party system and then test
the same just to test the migration how it looks like and You
can start there so many angles on this topic alone.
Nikolay: Oh, yes
Radim: You know, I don't say I have answers for everything, but
I just feel like I
Nikolay: hit a topic.
Radim: Let's have
Nikolay: part 2 about testing of schema changes.
Let's have part 2 just about it, because it's a big topic.
This is where, as you said, change management, this is where
danger comes from usually in instance, right?
So let's just discuss it separately because I think it's worth
discussing.
Michael: Radim, is there anything on the regression testing side
that you wish we had asked about or that you wanted to mention
before we wrap up?
Radim: I think we have covered most of the things.
Nikolay: Where to start for people?
Radim: Where to start for people?
Nikolay: You know, first steps.
Radim: Follow, first step, follow the project.
I think that's the first good step.
I will definitely make it public and do some, you know, sessions
around the version 2.0 because this is where the core developer
experience will come and I will actually need feedback on that.
So I'm actually open to any feedback.
You can email me at any point or open a GitHub issue.
I would be actually grateful to see that.
But I would say pick your 10 queries.
Don't start with anything.
Find a way to pick 10 queries which you believe are, maybe not
small, I would say representative of something that's breaking.
Try with them, try to create a plans, try to try the tool, how
it works, what might be the flow, and try to live with it.
And then have a rule that every single handwritten or LLM written
query needs to be covered.
If you start with that, you will start seeing value in a couple
of weeks, a couple of months, because you will have that check
mark, everything is fine.
And then in future, hopefully all the features and the developer
flow will support that you will get much more values and you
will actually get your time back in terms of safer production.
Nikolay: Confidence level builds up.
Yeah, I mean, a small set of queries, but it's a good idea to
understand which queries matter most based on some production
or something.
And you have a website boringsql.com so this is where you have
blog and so on and the project you're talking about is RegreSQL,
right?
Radim: Exactly.
Yeah, great, great.
Cool.
Right now it's not featured, I'm preparing a section.
Nikolay: You have a huge roadmap I guess.
Radim: You can't even imagine and this is actually where I loved
it.
Nikolay: I can.
Yeah, that's great, that's great.
And the project is in Go, so that's great.
Yeah, good, good, I think, finishing words, right?
Unless Michael has something.
Michael: No, I'll link to everything in the in the show notes
if anybody needs anything that we've mentioned and yeah thanks
so much for joining us Radim it's been great.
Radim: Yeah thank you it was very interesting and I loved talking
because you know it's an important topic.
Nikolay: Thank you for coming.
Radim: Okay, thank you guys.