Nik and Michael are joined by Radim Marek from boringSQL to talk about RegreSQL, a regression testing tool for SQL queries they forked and improved recently. Here are some links to things they mentioned: Radim Marek https://postgres.fm/people/radim-marek boringSQL https://boringsql.com RegreSQL: Regression Testing for PostgreSQL Queries (blog post by Radim) https://boringsql.com/posts/regresql-testing-queries Discussion on Hacker News https://news.ycombinator.com/item?id=45924619 Radim’s fork of RegreSQL on GitHub https://github.com/boringSQL/regresql Original RegreSQL on GitHub (by Dimitri Fontaine) https://github.com/dimitri/regresql The Art of PostgreSQL (book) https://theartofpostgresql.com How to make the non-production Postgres planner behave like in production (how-to post by Nik) https://postgres.ai/docs/postgres-howtos/performance-optimization/query-tuning/how-to-imitate-production-planner Just because you’re getting an index scan, doesn't mean you can’t do better! (Blog post by Michael) https://www.pgmustard.com/blog/index-scan-doesnt-mean-its-fast boringSQL Labs https://labs.boringsql.com ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
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.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...