Michael: Hello and welcome to Postgres fm, a weekly show about all things Postgres qr.
I am Michael, founder of PG Mustard.
This is Michael who's Nikolay founder of Postgres ai.
Hey, Nilay, what are we talking about?
Nikolay: Hello.
Let's talk about query optimization, like second step, third step after you identified.
Maybe third step, right?
First step is you found some queries which behave not well.
Second step, you found some optimization idea and what's next
Michael: Yeah, exactly.
How do we, know whether it be safe to make the change that we are thinking about, whether it'll make a difference, that kind of thing.
Nikolay: Yep.
And this is one of the topics our listeners.
Michael: Yeah, exactly.
I think they phrased it quite nicely.
It was like a, 1 0 2, I think they said like the American class, like the, in terms of university courses, right?
Is that how, how it works?
1 0 1 is the intro and then 1 0 2 is the next step along
Nikolay: per person who can explain this, but Yeah.
I, I saw 1 0 1 is everywhere.
Right, Right.
So, where to start?
Let's first Make a step back and think about how we ified our optimization idea as usual.
I have strong opinion here because I see.
a usual approach is either to rely on experience or to test a right on production because the problem is usual problem is that a
non-production environments, they have different data, different size of database everything is different, sometimes different hardware,
but actually hardware is less important, much less important.
We can Tune our, pause this on a weak hardware to behave in terms of the planned choices to behave is exactly like on production.
It's possible, but the data, statistics and planner settings, of course, these things are the most important.
So question is how you vilify your ideas.
how do you do it?
And the answer.
Great.
So
Michael: Well, I'm interested.
Yeah.
Well, so I think you've probably gone really advanced there already.
Talking about how hardware is is less important.
I think the first lesson is that testing in a similar setup as possible is really useful.
And maybe there's some exceptions to that, but by similar, I think the, the first thing I always try and encourage
people to do is make sure the quantity of data is similar.
So, and I guess we're testing two things, right?
The first thing we want to test is, for the single query that I was trying to optimize or for the single problem I was trying to solve?
Does this solve that problem?
And then there's a secondary level, Does it introduce other problems or does it work well amongst the whole load of the system?
Nikolay: anything?
Right, Right, right.
But right, So we want to, we want our non-production Postgres behave.
Ideally exactly in the same manner as production would do.
It actually production can change.
Yesterday, it chose one plan.
Today, there's no guarantee it chooses the same plan.
And this is a quite well known in pogo.
And unfortunately, POGS doesn't provide a good way to solve this problem.
I mean, to freeze the blind choice and avoid a sudden plant flips for critical queries.
but it's our topic.
This, this our topic.
Just I, I mentioned it just to, to give the feeling that the planner behavior is not stable.
So achieving the same planner behavior on non-production environment, it's quite a challenge, right?
But there, there are specific tricks that can help us.
For example, of course, ideally we want the same.
because the same data will mean produce, statistic content is the same.
So we are fine.
There are trick to export, import produce, statistic content, even if you don't have data.
There are some not very popular, but interesting extensions.
I think they came from Japan.
I don't remember names, but We will provide links.
It's possible to export a st.
I, this approach have limitations.
It's similar to hypothetical indexes partitions.
Very interesting approach.
And sometimes it's good, but what if we want like, true behavior So executor really executes our plans that we can see the behavior of pos for our query.
In this case, we, we want the same data.
Of course there are several levels.
How we can be close to the same.
We can just take it as this.
On physical level.
This is the absolutely good approach.
We, we just take, grab the same page of data.
If we have managed s we just cl it.
Most of manage services allowed to clone.
It takes time, it costs money, but it'll give us the same data.
And so we can see behavior there, but sometimes we cannot copy on physical.
So we can only copy on logical level, but as you said, the numbers of rows, for example, will be the same.
So we're good,
Michael: A problem, not really in the topic of performance, but a problem I see quite a few customers
coming across with that is the kind of access to data.
So giving developers access to production data can be problematic from a
privacy point of view.
Okay.
What do
Nikolay: very problematic, and in my opinion, it's not about there are two problems that access of arbitrary
developer and company to production can bring us.
First problem is security and second problem, I think it's more important, even more important, security we can.
Ask them to sign something to do security trainings, a lot of things.
But second problem is more interesting.
Developers want to develop, so they add a lot of instability if we allow developers to go to production.
, it can introduce challenges in terms of stability because we want to have established process of development, testing and deployment.
So we don't want developers to go and, and check, for example, Oh, I have a great index idea.
I will create it right on production.
Primary note and I will check.
It's not good.
So, Right.
But security is also a concern.
but even if you don't talk about security, which is a very big concern if we need to use logical copy of a
production, for example question is, will plan be the.
Row counts are the same, right?
Statistics should be the same.
And for example, we took the same, even same hardware and we put the same positive settings to configuration.
Question is does it guarantee that the planner will behave in exactly the same manner?
Michael: So the, the place my head's going to is using analyze and it sampling.
Even when you run analyze, you need to, it gets samples of tables, right?
So maybe that's not the only way.
It could be different, but that's, One it could
Nikolay: I agree.
Yeah.
Some, some instability there, but also if you have a lot of bloat, for example, the real pages is different.
So on your new note, on your, on your logical clone rail pages will be smaller.
And this definitely this, this directly affects the planner behavior.
So it can choose different plan, so we can see different access notes and plan not, not access notes, execution notes, and, and plan.
When you.
Production and this logical replica, unfortunately, and there is not, I don't know, solution to this problem, but it's quite interesting problem.
So
Michael: And actually not just bloat.
Also, I guess insert order, right?
Like
Nikolay: Physical distribution?
Yeah.
yeah.
Well this you you mean, You mean buffer numbers probably different.
This is this, I understand.
For example, we need to read a thousand rows.
We, we had thousand of buffer res and hits on our source, but we have only hundred, or we have, we have like much fewer hits and res on our
replica because more compact storage, for example, of these rows.
this is possible, right?
But question is, will, structure of the plan will be the same, and that's where unfortunately we cannot g.
Usually it's very close, but unfortunately real pages is different and it directly affects the plan behavior.
Michael: But we are, we are talking about edge cases, right?
Not the majority of the time.
If we're talking about simple LTP type queries, does it use the index, does it not?
The kind of things people are probably worried most about.
They're likely to be easily replicated once you have a similar like sample of data.
Nikolay: Right, right.
I agree and sometimes, for example, we might have multiple production environments and they are slightly different.
So things can be very interesting in various cases, but at least we can be as close as possible if we.
Aim to do it.
We, we say, Okay, we have a billion rows in a table.
Let's have a billion rows in a table in our production environment, non-production environments, and then we can see the plans.
Now, back to the topic.
If we have a plan if we have a query and the plan, and now we want to ensure that it's a good idea and we need
to approve it, of course direct comparison before, and.
It's already good, right?
So we have one query and we see that our change, for example, we create an index or we do something else, we change
query somehow we directly see that IO numbers improved.
So conclusion is, let's do it.
But as you mentioned in the very beginning, of course there are some cases when our change can affect other querie.
And the question is how to find it holistically, how to test it holistically.
And this is unsolved problem.
First of all, let's consider one case when it's, it can be possible.
I had it and I wrote an article about it.
Uh, We will provide it.
So it's like an interesting case when optimization of index led to.
All updates being worse in terms of execution?
Why?
Idea was simple.
For example, we have some, select, it's quite slow and it's maybe not slow, but we see that it uses some index, which we can reduce in size significantly.
Adding a where clause we add a where clause, everything fine.
this select has better performance because index size is smaller and, and so on, because now it's partial smaller, right?
But then we deploy it and, and suddenly see that latency of all updates on, on average reduced.
Why?
Because we lost HOT updates because adding some column to.
Index definition, even it's in, in the wear cloud, leads to different behavior of updates because HOT updates, heap only
tuple updates, they are possible only if no indexes have column name in their definitions that calm, which we are changing.
Right.
So if we change some column, usual case, for example, updated at a timestamp, we usually tend to change it when we do updates, right?
Because it's the semantics of this column.
But if we have an index on it, no such updates can be hip on the top it, hip on the top.
Updates, they are much faster and regular updates because during these updates POGS doesn't touch all indexes.
It doesn't need to change all indexes.
But with regular updates, POGS needs to update each index and this, this is called index amplification.
Index, right?
Amplification issue.
One of those which Uber h ighlighted in their article, so losing hat updates may be painful, so
Michael: Especially on a heavily loaded system for sure.
The, the other, the thing, the place I am starting to see some progress on in terms of tooling is people even
being aware that some of these things are problems.
So the, it's PG added an index advisor.
In the last year or two.
And one of the things that it points out is an estimate of right performance overhead.
So not only, not only can indexes be a problem in terms of preventing hot updates, they also add right overhead if a new rose, for example.
Nikolay: yeah, any index.
If you create one more index, definitely you add overhead.
Definitely.
And we can actually estimate, we can measure.
And this is interesting and this definitely it's possible to improve tooling.
Definitely.
I don't see best tools here.
I made couple of attempts to create tools.
One of the.
ideas was to create a benchmarking tool which will be used in, in any change for any change related to database.
It, it was called Nancy Bot.
And the idea was we will replay workload before and after our change.
We will collect all metrics, for example, from pist, statements from logs, auto, explain everything, and then we will
provide the direct comparison Before and after, and we see exactly which queries degraded and which queries improved.
And this was great idea.
We had even had a good interface.
And so, but then I realized that practically it's not possible to run full-fledged benchmarks every time developers does
any change because it's like, it just economically doesn't.
It's too expensive.
You need separate machine or couple of machines, sometimes several in, in, in a batch.
But sometimes we ran several experiments, like, what if we do this, this, this, Like we have 10 options and we executed them.
Then questions arise.
Like we, we executed them in parallel not sequentially because sequentially is too slow.
If, if each experiment takes 10 minutes.
You want to execute parallel, but then questions arise.
Okay.
But what about are all virtual machines the same or there are some deviations, for example, you one of them got worse discourse, CPU or something.
So we
had to
Michael: neighbor.
Yeah.
Nikolay: Right, right, right, right.
So, We added a lot of logic, like micro benchmarks to ensure that like some baseline is met and so on, and so on and so on.
And the complexity grew.
But the main problem was you just need to pay a lot to do such experiments.
Some campaigns can, can afford it, Also the, one of the biggest problems was how to replay workload.
There was p play and replay.
Go.
I, I saw the other, maybe yesterday I saw the article from Frank Pa, Pa Yuy about how to run pji replay for yuy or p play go actually for yoga.
But, but collecting queries is also a challenge.
And I did it several times.
And so the bottom line.
It's quite complex.
It's possible to do it.
Some companies actually implemented something, but it's very hard to create a universal tool here.
But still there is need here, there is need.
we want to cover all changes with some testing, right?
But the good news here is that we don't need full-fledge.
CL we we can do in a shy environment.
We can focus on IO metrics and run many experiments on just one machine.
This is what we do with Database Lab and think loans and database branching.
and it's possible.
So we can have think loans.
Of course they have different timing because different file system, for example is SS or, or anything else.
BTS is also an option.
And then you run multiple experiments right on one machine and you don't care about timing.
You care about plan structures and.
Are you numbers and you can verify many things.
So question.
Next question is what to verify.
Okay.
We have our query.
We want to optimize.
We found some index.
We ed that query is improved.
How to check other queries?
Well, some new tool link needs to be built here, right?
And I think it consists of two areas.
First area is a reaction.
We need to reactive approach.
We want to grab the content of psal statements periodically from production and have some regression testing.
We can reduce the scope.
If we we can pass queries or already normalized queries.
using PG query library from Lucas Fit.
Right to identify which tables are involved, and if we consider optimization for a particular table, we can find only those
queries from top hundred or top 500 by total time by calls.
Somebody needs to decide what is the most important metric and which queries are most critical for our application, but we
can find those queries which potentially might be affected.
Of course, we can also.
Think about triggers and various dependencies, foreigners, and so on.
So this is tricky.
But anyway, we can find it automatically and using Think loan, using single connection sequentially.
We can find, we can test many queries, but the problem will be how to find parameters, which, which parameters to use for.
Queries and this is unsolved problem.
I already mentioned that it's, this is big problem how, which parameters to test
Michael: In, in reality, what do you see?
Like in reality, what I see people doing, I think it's probably at a smaller scale to you, is that they reason about the potential to.
Effects talk, discuss it amongst the team.
During the PR process, during their discussions, then they monitor, well, they, then they deploy to production and monitor.
Maybe they provision a little bit
Nikolay: Purely reactive approach, right?
So we, we deploy and see, yeah, this is possible.
First of all, of course, code review helps.
And having a bunch of very experienced positive experts helps a lot, right?
They can say, Oh, you know what?
This is good idea, but what will happen?
What other head from it?
Let's think about it.
Of course, we can manually analyze and try to predict, and if you have experienced 10 plus years, It'll work quite well
unless you are very tired, very busy have days off and so on.
So I, I still think about fully automatic, automatic approach, but it's, yeah, it's possible and we can check metrics and have some alerts.
For example, if updates are very important for us, if the ratio of hot updates.
After our release, it can be somehow flagged, alerted, and so on.
This is good approach.
Yes, but it's purely reactive.
Right?
And actually most projects even they don't even don't have this, even this.
So I agree this could be a good step towards it.
But back to fully automated fashion, which queries to check, we need to check queries from production for regression testing.
And that's possible.
And we actually in some, in a couple of cases, we already have something like, For with our best lap angel everything is done in c I c.
and the user users see the difference and potential degradation before we deploy it.
It's, it's very good, but additionally we need to think about absolutely new queries.
For example, if it's a new feature, new feature branch and it introduces some new queries or Rewrite or for
existing queries, production doesn't have these queries.
So second part of it is even more challenging.
We need to test absolutely new queries here.
And I, I'm just explaining our thoughts we move to in this direction with database lab and.
, I'm excited to, to let in future we will have fully automated testing of all things related to databases, even for those who are not experts at all.
And when experts are involved, they have a lot of artifacts metrics to analyze and understand much quicker what to
do, how to fix it so every, everyone can move faster.
With better quality and so on.
so right now without this tool idea is right, we need to have code review and some every database schema change.
We need to test the change itself, but also think about how it'll affect workload.
Michael: Yeah.
And I guess first step, a lot of, some people don't even check about the locks involved, right?
They, that's, that's like, I guess that maybe that's 1 0 1 than 1 0 2, but making sure that it's, it's not a blocking change.
I know this has got a lot better in more
Nikolay: Well, right.
This is, I I even don't discuss it considering it's too trivial.
Right.
So like creating should have concurrently and updates should be in batches.
Yeah.
And this is actually, we solve the with database lab, we have fully automated verification that no exclusive logs.
So last more than specified number of seconds.
Well, for example, one second, or, Yeah.
This is possible to fully automate for the change itself.
we spent some time understanding how to test the change itself and quite already solved it.
We can put it to your C C I C D, but now question is, I, I think it's a bigger question.
For example, some developer created something but forgot, index, how to see that it'll be better if we deploy.
And how to see it without involving very, very expensive and very experienced DBAs every time, right?
So we should involve them only if we have a hard question, not a simple question like forgotten limit or index which degrades our other queries.
Michael: Yeah.
And I was You mentioned PG Replay Go.
That's my, the old, the company I used to work at Go Cardless and I, I think that's how they reason.
Nikolay: right.
Yeah.
Michael: So I think that's how I remember it being used most.
It was big changes, it was risky things.
It was things that people already knew were potentially
Nikolay: All.
Example, a big, functionality.
We deployed big functionality, or we had some de factoring during several months.
Right,
Michael: large migration, that
Nikolay: Right.
Right.
Well, in my opinion benchmarks with multiple sessions should be applied very rarely when something big changes, either like
big new release happens or migration to new , major s version or change of infrastructure, change of operational system.
It happens couple of times per quarter maximum usually, right?
Even in
that much less normally.
It doesn't depend on the size of company actually, because anyway.
It's very expensive change usually.
And it's a good, it's a good that we need benchmarks, full-fledged benchmarks.
So not every day, but application changes might happen every day, sometimes several times per day.
And they might change some small part of database.
Good news on the small part of database, right?
We don't perform huge refactoring every day.
So if we change only one table, two table.
We should be able to test it in more light manner.
This is why I'm so big fan of think learning and I'm so excited to see others.
Also think about database branching, new database they say about it, like they say, you can test a lot of things in C I C D.
It's, it'll be cheap.
Actually, AOR has, as we discussed, Avor has thin loans, but it's not cheap.
You need to pay for each loan for compute power separately.
I, I hope new one
Michael: Interest.
Nikolay: will be different running on one machine and you pay for single machine, but run many, many tests at the same time on one machine.
Otherwise it doesn't scale.
Cause you sometimes need the dozens of.
That's happening in parallel, if you have big organization, it can be very costy, so you will turn it off and you
will be without testing, returning to bed times again.
But the advice right now is yes have database reviews, try to automate some things.
Learn your pta ptat user tables.
Right, because it shows you how many updates and hard updates you have some type statistics.
It's very good to, understand it.
I, I'm not, I don't agree with a data doc approach.
If you go to database dashboard in Data Doc, they have a lot of improvements lately over the last year or two.
But if you check what they name, they present it as, Tap statistics?
No, No.
Throughput is tps.
Qps.
This is throughput.
Our global throughput for our data is not, but tap statistics is important, but it should be in details, but it's off topic anyway.
Understanding when you change something for a table understanding Apple statistics from that user tables, it's a good thing to have.
And you, can see, Oh, I lost updates.
Oops.
Right.
Michael: How would your like advice change if we're talking about smaller teams?
If we're talking, let's say like you're a six person startup, you've got a decent amount of data, but not necessarily, you know, not billions
of rows and Maybe the team is currently prioritizing moving fast, and if, if they have a few problems that they have to fix, that's okay.
Is there any changes in this?
Like, because this feels like a very safe approach, like the kind of tooling we've discussed seems very very much
optimized for, try to avoid a problem at, at almost any cost.
Nikolay: Well, yeah, good question.
First of all, I would, anyway, change mindset to use buffers instead of timing anyway.
Even, even if you are small, because it will save you so much.
Like of course there is such thing as a premature optimization when you try to optimize too early, too much.
But still if you know that some table, you will use it as a, like in the queue pattern.
Insert, update and delete, delete, insert, update, delete.
It'll belo at a lot of you need some experiment Anyway, This experiment by the way, can still be single user, single session
experiment in shared environment because you don't actually need to use bench and utilize all CPUs to, You don't need bug.
Some kind of background workload, unless this workload also deals with your table.
What you do need is to think about the future, like what will happen one and two years with this table, how big it'll be, and run some benchmark
with one session to fill it and then to run one more session to next.
You run to update it heavily and so on, and see What blood will be and do, Do you have good indexes here or no?
Like if you focus on buffers, you will quickly, This mindset change is very important because you will start comparing
like, like in your, in your tool pitch master to you compare, for example, rose buffers, buffer numbers to get one row.
We, we deal with thousand buffers.
It doesn't feel good at all.
So, and if in one year it will be even worse.
probably, we should prepare for better growth right here in this area.
So we need to test it.
We need to test our queries and see what will happen if we, if we have more data, for example,
Michael: Yeah, I think if I, The only other thing I would add is I think I see kind of a couple of extreme failure cases here.
I see the failure case where people have not really started adding many indexes at all.
They almost have no indexes, and then they start to scale and they have every problem.
Nikolay: column, right?
Yeah,
Michael: Or Yeah, exactly.
Or the other extreme where they've added an index every time they've seen a problem.
Or Yeah.
I, I, I've not seen an every column case I've heard about them.
But definitely, you know, every time they see a potential for a multi column index, they add one.
So a single table on even a table of six columns could have 20 indexes on it.
Different orders, different.
So I've seen both of those failure cases, and I guess if you know you're in one of those, Then it kind of changes your approach quite a lot.
If you're in the, we already have too many indexes, maybe you need to be a bit aware of that and be looking at reducing that.
If you know you're in the case of we don't have enough, maybe you've got a little bit more leeway to not have to worry about as much impact of adding
Nikolay: You need to understand your workload to, to know what to test, and like this is true, but.
Change of mindset to buffers and just experiment a lot.
This is a good thing, like experiment, experiment, experiment.
And you, you, you will see and you then you will probably start if, if you're, everything is based on human decisions on like based on gut, right?
In this case, At some point you will start skipping some checks because you already know that it's, it's, it's okay, but sometimes you'll be wrong.
Anyway, this is a bad thing,
Michael: Yeah, in fact, the buffers thing came up.
I just, the other day I saw a really interesting blog post from a company that moved from RDS for Postgres to Aurora for Postgres, and.
They, in my opinion, must have not had enough indexes.
They must have ha been doing too much IO cuz their costs went maybe tripled or something according to the graphs.
And it was really interesting to see that and see that they thought this simple migration would be straightforward, but actually because
they hadn't optimized for buffers, they hadn't reduced, you know, being quite efficient on the IO front hit them with a big bill
Nikolay: Yeah.
On a raw optimization it's not about performance, it's about money as well.
Right.
And I, I, ayo centric optimization is, it's the best thing there because if you focus on reduction of Ayo, I don't remember.
It's about, is it about rights or reads?
I remember something different.
Was there this detail I already forgot.
But either rice or risk can, if you have them a.
You need to pay for at Aurora.
So you definitely want to reduce and you want your buffer pool to have better efficiency.
a ratio in terms of heat rate versus res and just you can check your queries and optimize them and so on.
And, and if, for example, indeed, if you, if your index added more, for example, it broke all your hot updates.
So hot, hot updates ratio became zero.
This is what happened with me a couple of times.
In this case, you'll start paying more , so you optimize one query, but not only.
degraded.
you have worse performance for other queries, but you're also paying more.
It's not good.
So my advice is, is know your workload.
Learn it from p statements, for example, right?
Focus on buffers.
Not timing one.
Optimizing timing is final, our final goal for optimization, but it should not be used during the process of optimization.
It should be only like checked in the beginning and the end.
But, but in inside of the process optimization, we should be focused on buffers and final experiment a lot and to experiment a lot.
You.
Just be ready to loan and reset a lot and so on.
That's why I think Nik is good and as I've said, Aurora has think cloning so you can run additional load and test it there, then stop it.
I guess they have also like per minute or even per second billing ass.
In general has it two notes.
So they are per second.
So it's can be very efficiently.
You, we will need to wait some time, like minutes to provision a node, but then at least you can quickly stop it.
Right?
But pure thing close, which are I, I, I name them local Think loans.
So we have one DM and many CL inside it.
So we pay it for only for one dm.
It's the.
For experimenting, so having such a platform for experimenting is a good, it's a good thing.
Anyway, general device has experimented a lot.
Michael: Yeah.
Sounds great.
Anything else you wanted to add?
Nikolay: Now as usual, subscribe, like, share, provide feedback.
Feedback is very important.
Share is also very important in your social networks, working groups like disc, Telegram, what you use.
Please share and bring us more ideas.
Actually, we have a line, have a queue of ideas anyway, but we want.
Michael: Yeah, exactly.
They don't, it's not just always request comes in and we do the exact topic.
Sometimes it's gives us an idea for a slightly different one, or we put two together and that makes a good episode, that kind of thing.
So
we'll see.
Nikolay: exactly.
Okay.
Michael: much everybody.
Take care.
Nikolay: Bye.
Bye.