Hello, and welcome to Postgres FM, episode number one

Pilot episode.

pilot episode yeah.

I'm Michael I'm from pgMustard and I'm joined today by

Nikolay, Samokhvalov, don't try my last name, unless
you have Russian or Ukrainian roots or Belarusian roots.

So yes, I'm Nikolay from Postgres AI.

Wonderful, looking forward to these.

Let's discuss why we do it because I felt so long that we should
have some podcasts pure podcasts without any screencasting or slides

and so on, because it's also a good format to have some discussion.

And I feel like meetups died because of course this started
to die before COVID, but COVID finished them completely.

So we, it's very hard to, to meet at some place and have some pizza,
pizza and beer, and I mean, people still do it, but only like 10 person's

come so like, it's not, it's not working well, so we need more formats.

I know many people hate on purely online
formats, but I actually actually love them.

And I think we should experiment with more and more styles for formats.

And the Postgres community needs some podcasts.

That's why we discussed with Michael and we should start Postgres FM probably.

Right.

Maybe it will die also, but who knows?

Right.

But I, I, I hope not.

Why, why do you do it?

Yeah.

Well, I, I love podcasts in general.

I love them for learning.

I find them really helpful.

I can, I can listen to them at times where I
can't watch a video or I can't attend an event.

I find them really helpful in terms of learning,
but also getting to know people in the community.

See, different guests getting to hear interesting conversations
between people that I probably wouldn't be a part of generally.

So I love them as kind of a, an insight into something that
you're not necessarily familiar with already, or a way of

keeping up with people that you don't talk to all the time.

Like that's quite a nice format as well.

But yeah, basically thought it would be interesting.

I thought I would like to listen to this.

Right.

I would like to listen to a couple of people talking about Postgres each
week or discussing something that is super interesting or controversial

or not yet decided or a common issue that people see all the time.

Something that I've just hit or might be able
to avoid hitting in future, that kind of thing.

Right, I'm the same.

I'm just trapped sometimes trying to fill gaps.

So when I walk my dog or I'm on an airplane, just, I have some
time and I want to not just some entertainment, but something

useful and podcasts is great for this kind of gaps to fill.

Right.

So, yeah.

Okay.

Let's, let's start from.

Topics we have, I think we, like in general, we will try to talk more
about performance, but maybe not only right, but definitely about Postgres

Yeah, I think always about Postgres.

I think based on what we focus on, we probably will end up
bringing performance topics more than most would, but equally

I think we're open to suggestions from other people, as
well as to what, what you'd be interested in hearing about.

So yeah, we're very open on the topics front.

My opinion about performance It was surprise to me,
but I think not everyone is interested in performance.

Not every engineer who writes SQL is interested in performance.

This is discovery I've made.

Like it's like, it feels like the most interesting part,
like, scale, scalability, performance, these kinds of topic

but I found many people, not just interested, they just need
some query to work and return proper results and that's it.

So like, I wish everyone to be interested in performance,
but, anyway let's, let's start with the first topic.

Well, I'm interested in that one, actually.

Do you find there's a pattern as to who cares and who doesn't care so much?

Or is it that they, they only care if it's below a certain
threshold or is it that they care about one thing, way more?

I think, yeah, I think we're movement towards one of the topics we
wanted to discuss and the word, the word threshold is like as good here.

So what I observed in organizations from like, small startups that
grow very fast, like several times all numbers increased several

times per year, or very large organizations with thousands of
people and hundreds of engineers, I observe a quite good pattern.

Like not good, like obvious pattern where, when a business dictates
a feature delivery, but to be very fast, like a lot of competitors

around, so requirements are very strict, so we need to move faster.

So developers mostly interested in having
features delivered like daily, for example.

Right.

So very, very fast move, very fast and under risk pressure.

They don't have time to have the best performance ever.

Right.

They don't have this Like with just don't have time.

So then they start to move like, okay, it works.

Let's go.

And, but the problem is who defines this threshold?

Like where is the minimum and who checks it?

How, how to check it.

This it's usually a problem.

So sometimes you observe very good performance when you develop the
feature, but when you deploy it, it's not good or you deploy it also good.

But one year later, Normally detach this code, it left
unoptimized you have more data and in the query degrades

.
So my thereshold, I have my threshold.

I wrote an article about it.

So it's.

At every webpage should be faster than like 200, 300 milliseconds.

One second as an absolute maximum.

And since we consider page or API request, of course, and since
every page or API request may consist of multiple SQL queries.

It may have zero.

But sometimes we have dozens of queries.

It's not good.

Sometimes we have like a loop with queries.

Of course it's not good, but in general it means like, like if we have the
requirement for a webpage to be not, not longer than one second, it needs to

that general requirement for SQL to be not longer than dozens of milliseconds.

And also my article also describes where this
number comes from, where does it come from?

A human perception.

It's like 200 milliseconds, a reaction of any human plus minus 15, maybe.

So if you press some button, you expect the reaction
below 200 milliseconds better 100 milliseconds.

Yeah, I think I've heard, is it below a
hundred milliseconds it feels instantaneous.

Anything above that we can perceive, we
perceive some delay, even if it's not instant.

Yeah.

So I know exactly where you're coming from on, that makes a lot of sense.

So I guess the question is do you see a lot of companies that
generally have way worse than that performance and still don't care.

Of course.

Well, yes, yes, yes.

A lot.

If users don't complain, oftentimes we just don't care.

Like, we have some functionality it's working, but we have
so many things to create, to compete with others, right.

To, to expand, to grow.

So, if users don't complain, sometimes you see some pagers
or API requests are above one second in duration and nobody.

You can see it and from logs actually, you can check.

If you have, for example, log_min_duration_statement
longer than one second, for example.

And here we can discuss percentiles, right?

So not only every query should be below one second or
below a hundred milliseconds, we say 99% should be below.

Right?

So any big projects should talk in these terms, but sometimes we have
a lot of slow queries and nobody cares until big feedback loop works.

Big feedback loop is when a lot of users started complaining.

And management understands.

It's a big problem.

Or churn right?

Like you hear some users complain, but you realize that
it's the ones that are churning the other one's complaining.

They're the ones that are,

well, yeah,

know, it's real dollars that are being affected or, you know,
e-commerce maybe your conversion rate on the pages that are slower

E-commerce knows how to measure every, every
second of downtime or at least minute of downtime.

So yes.

And degradation is tricky in terms of how to
measure it in dollars, but also maybe it's possible.

I think it's possible.

Again, if you check slow, slow query log, usually a lot of things are, I
mean, in the logical logic company or a large project, a lot of bad things.

And if you have a luxury.

To get a few weeks for optimization that is always
in large project to fill them with optimization.

Right.

But yeah, it's, it's a decision from management
let's optimize and be prepared for further growth.

Yeah.

So I've seen some exceptions that I guess, I think you'll
probably I've read your post and I think it's great.

I think you've probably accounting for them cause they
probably fall a little bit into the analytical workloads,

but some applications almost do analytics as OLTP.

So they might be an analytics product.

And if they let you set a few filters and it takes a few seconds to
load, sometimes that's acceptable from a user, but that seems like

the, they seem like the exceptions where the user understands that
it's doing something complicated or data heavy behind the scenes.

And if they, if they're waiting for that on a one-off
basis, and it's only a few seconds, they seem okay with it.

But yeah, I think that's probably only at the exceptions.

Some complex search.

It may, it might, might be okay.

But usually the question is what will happen in a few years when we
have, when we will have a lot of data, much more data than today.

If today we have 10 terabytes in a few years, we might have 50 terabytes.

So what will happen?

So if it takes three seconds today, it
may be already above 10 seconds in future.

And of course, well, people usually are okay to wait a second or
two during different search from my opinion, but still not good.

We should optimize.

And the examples I've seen with, you mentioned
people scaling and having problems there.

Sometimes these startups, they bring on a customer who has three
four times more data than all of their other customers combined.

You know, when they're in a, an early phase, that's not that unusual.

And if they haven't tested sometimes well performance
drops off a cliff or something goes badly wrong.

We've seen that a few times.

So it's yeah.

Super interesting to think of like planning ahead, but most companies
don't seem to because, as you say that it, it has to be a focus on

feature delivery and, you know, investors need updates and customers
want certain things delivered in certain timeframes, that kind of thing.

Yeah, well

So what can we do?

What can we do to help people?

Well, first, first thing to define some, some thresholds,
as you said, maybe it can be defined the form of life.

If some salaries are involved and they have
some methodologies to control uptime and so on.

So similar approach can be applied here.

Like we can say we have good quality if, for example,
99% of all, queries are below a hundred milliseconds.

We define it a form of SLO service level
objective, and we start monitoring it.

We start having alerts.

If it goes down, we.

Also second thing we perform from time to time, like at least
once per quarter, we perform analysis of current situation.

And also we try to predict future.

So like some capacity planning.

Are we okay with numbers growing with what we observe or we
predict our thresholds to be broken already in next quarter.

So this is like usual approach to growing project.

And then we should of course go down and perform query analysis and
optimization and analyzing whole workload, but it's a called different topic.

Probably we should discuss it separately,

Yeah, well, and the slow query log is a great way of
getting started right the log_min_duration_statement.

If people don't have that turned on, that feels
really sensible for pretty much everybody.

I've seen startups that don't do any monitoring yet.

They don't do any performance monitoring.

So the idea of even getting the 99th percentile might be a stretch.

But if you, if you just start logging the worst queries.

Yeah, I saw this.

So, so what I described is for a larger organization.

So like, like it's, it's already a sound
like some bureaucracy involved, right?

So some processes, but it works in the larger organization that we
need to do it a little bit more complex process to be established.

But then if it's a small startup, a few engineers only I saw this.

CTO with couple of engineers, very small startup, by the way,
it was very successful and sold recently to very large company.

But in the beginning, like three or four years ago, I saw
them a CTO having every query that went to the Postgres

log because of log_min_duration_statement, 500 milliseconds
or so it was sent to Slack and immediate reaction.

So they try to achieve zero, zero events for this in the beginning.

It's fine.

But this process doesn't scale well, at some point you will
be overwhelmed of course, but in the beginning it's good,

it's like you'll react to every occurrence of slow query.

That's great.

Anything else you wanted to cover on this one?

Well, not at this point.

I think that it's enough about slow queries.

Like maybe we can talk about slow transactions because
it's quite different topic related, but different.

Yeah, go for it.

So we, we hear like, maybe like you discussed analytical
workloads, but it looks like we mostly discuss OLTP worloads for.

It means like web and mobile apps.

So if we talk about transactions, we should understand like that there is
a query, transaction and sessions three levels and to execute the query.

You need to have a transaction, even if you don't define
it, it will be defined automatically a single query.

Like it's like implicit transaction.

Anyway I hate Ruby developers when they say I will
run this database migration without transaction.

It's not possible.

Right.

And this disabled DDL transaction it's wrong.

You cannot disable transactions in Postgres,
but, but still this like a weird term it's used.

So you, you need to open transaction, but you
cannot open transaction if you don't open a session.

Right.

So like it's like one inside another and the problem with
transactions long transactions is that they there are two problems.

First, if you acquired some exclusive lock and keep it, it's always
kept until the very end of transaction, either commit or rollback.

So you need to stop otherwise you still keeping it.

So at that means, so you can block out.

And the second problem is auto vacuum.

If you keep transaction open, even if it's read only transaction.

And sometimes even if it's on a replica on a standby
server, if hostel the, that is on a, it means that

autovacuum cannot delete freshly dead tuples right.

So we, block some autovacuum work at least
partially, but the first problem is the most.

Like it can be seen immediately if you keep long transaction
and you can, you can have a storm of locking issues.

So this, this means like ideally transactions
should be also below one second, right?

Oh, interesting.

Right because otherwise, imagine you acquired the lock in the very
beginning of a transaction and someone also trying to acquire the lock.

So you updated the row and someone else tries to all day to the same row.

And this happens in the beginning of your transaction.

If you keep it longer than one second, you may
block this session, for longer then one second.

So our previous topic will be broken as well.

Right.

So this is a similar thing.

And that's why I also say when you're split, worked into batches, try
to find some batch size that will allow you not to exceed one second.

Roughly.

Exactly because of this, right?

Awesome.

So what going back to the folks that don't have much of this setup
at all, and what would you recommend them logging or monitoring?

Yeah, well every morning throwing should
have monitoring for a long transactions.

And I see most of monitoring fails to do.

I just fails like they don't have it.

They don't report what, like we cannot, we open
monitoring and we can not answer the simple question.

What is the longest transaction?

What's the duration right now?

Is it five minutes?

Is it one hour?

We are interested in this.

We are even just, we don't discuss autovacuum and the xmin horizon here.

Just this simple question.

And I think this is first step.

You should have it in any monitoring.

If it is, if it's missing, you should add it.

And then you should have alerts again, like soft or hard alerts.

Like if some transaction is running a longer than 10 minutes
already not good like, like it's always this bad situation.

Of course, sometimes you should exclude some transactions.

For example, vacuum can run longer.

But regular transactions should be under control in OLTP, we
cannot allow somebody just opened transaction and keep it forever.

Yeah, so, I know you're focusing on the first part of this, but the
second part does feel worth mentioning in terms of avoiding transaction

ID wraparound it, like, I know, I know some small companies shouldn't
get anywhere close to it at least for a long, long time, but we've

seen big companies over the last few years, get tripped up by that.

And it feels like maybe they might not have
been monitoring for this kind of thing.

Even at these large company.

With massive scale.

Right, right, right.

One of consequences of keeping transaction
very long is you block autovacuum as well.

And that can not delete freshly dead tuples if you do it,
sometimes people open transaction and keep it for several days.

If you're modifying queries or transactions are coming
at very high rate, it's it can be a problem as well.

But I observe usually already every monitoring, has it like I'm less
concerned here because I had just see, for example, Datadog has.

Others also implemented exactly because of this very painful
experience from Mailchimp and Sentry before like several years ago.

It's a good that those guys, by the way, blogged about it.

It's that's visibility.

Yeah.

Blogging is very, very important.

So, I guess, even in my mind, I don't have very good model
for monitoring ideal model because we should distinguish

various types of transactions modifying, which acquired lock.

And also they have real transaction ID
or don't only do transactions on standby.

And like different things, right?

So, so maybe we should have couple of
monitorings aimed, to different purposes.

One is for locking issues and other is for autovacuum issues . By the way,
we can discuss different things, not transaction duration, but xmin horizon.

And only then we care where.

From our transaction on our primary or
from replication slot or somewhere else.

So, so maybe we should have different things,
two monitoring charts, metrics, right?

The other thing that interplays with this that I think I've heard you
speaking about before are timeouts, so to protect against some of these

Yeah.

I recently had one company, quite a really big startup.

And they experienced a lot of issues because of lack of timeouts involved.

And once again, we saw the problem that Postgres.

It doesn't have very important handle.

Actually somebody should develop it.

Maybe I should develop it but I'm not a hacker anymore.

First, what do we have, if we talk about like
session transaction, and query statement.

So three levels, we can limit statements using statement timeout

but we cannot limit transaction.

There is no such way in Postgres at all.

That's it.

Can we can, we do session timeouts.

That's

I think it's, it's usually, if it comes
through pgbouncer, we can limit idle sessions.

So we can say drop connection.

If we like, I don't remember the concrete
names out of top of my head, but right.

But it's, I think this is possible, but session is less a problem.

This is not a big problem.

I would, I would prefer to have a way to
limit transaction duration, but there is no

But if we, if we kill the session that kills the transaction.

Right.

But I guess if it's only, only have it's idle.

Yeah.

Interesting.

But like if transactions are small, session for example can be
very long and then you establish connection yesterday and continue

working politely like very small, transaction, very brief.

Why should we kill you?

We can limit statement and we can limit breaks
between statements and side transaction.

It's called idle_in_transaction_session_timeout, a very long name.

So we can limit statement, limit breaks pauses between statements
and everyone should do it actually, I think any OLTP should start

with, like, we had the discussion in Twitter sometime ago and I'm
big fan of global default, very limiting for all OLTP project to

those who need to extend they can do it in session or for user,
but in OLTP, I prefer, like to see statement timeout, 30 seconds

idle_in_transaction_session_timeout also like 30 seconds, sometimes even 15.

Imagine that transaction, which consists of like a chain of very small,
very brief statements with small pauses between you don't break any timeout

settings in this case and your transaction can last hours and I saw it.

Yeah,

it's bad.

It's bad.

point.

Yeah.

And it seems actually potentially very tricky.

When do you see people using multi query transactions?

So like, let's say the, the rails app that we were talking about

Well, with long transaction we have two risks again, like locking
issues, so you can block others and it can be very painful.

And autovacuum . So what I saw people don't understand this simple idea
that locks are released in the very end and they split work into batches,

but these batches are inside one transaction block and it's, it's awful.

Like we perform small updates.

Everything is fine.

But transactional lasts one hour.

And those updates that we are in the very beginning
locks that's a shame that those updates are still held.

So you have a lot of blocked concurrent commits and
they have those like victim queries they can, can

be, of course can reach statement timeout and fail.

So we see degradation in best case a statement.

Timeout will save us to have a, like a
chain reaction, but it's still not good.

This is why.

Yeah.

That's why I think by default in OLTP transaction duration should
be limited, and I saw people implementing these on application side.

Which is of course like a weak limitation, because you might have multiple
application called parts if sometimes in different, different languages.

So, and still somebody can connect to using some tool and so on.

And so like, I would rather see it on
the Postgres side, but doesn't exist yet.

Yeah.

And just to go back to something you said earlier.

So if you set quite an aggressive one, let's say even if it's
30 seconds as a timeout if we're doing a big data migration,

or if we, if we need to do a big schema change or add an
added large index, we might need to set that the okay, cool.

I'm guessing if we create an index concurrently

you should do statement amounts set to zero.

Right?

Yup.

Right.

But it's, there's a, another topic there.

We probably should discuss it next time about lock timeout setting
. Let's keep it outside of today's discussion so yeah, exactly.

This is a good point.

Wonderful.

I think we've done alright there.

What I like about it actually like sometimes people
say we don't want to have timeout because it's painful.

You reach it, you fail.

Well, if you have 15 seconds and then you fail, you see it immediately.

Then you said, like, if you have 30 minutes, for example,
and you fail after 30 minutes, damage already there.

And you also like feedback loop is huge.

This is painful, but small statement timeouts are not that painful.

People see them, people adjust settings and go, that's

That's a really good point.

I'd not considered that, that the smaller it is the less painful it is.

Of course the user doesn't see what they're expecting to see, and there's
a problem, but in the grand scheme of things, it's a much smaller

problem than you would have had if it had been a minutes or, or longer.

Yeah.

Great point.

Right.

Small, timeouts are good in terms of user experience,
because it's like fail fast and, and adjust that's it.

Right.

Awesome.

Is there anything else you wanted to cover on that?

maybe that's it for, for first step pilot episode, right?

Yeah.

Let's see.

Let's see what people will tell us in Twitter where, where we can.

Get the feedback.

I'll put some links in our, in the show notes so you can find us yeah,
please let us know what you think, what you want to be discussed.

Any questions you have be really welcome.

Thank you, Ni see you next week.

Good.

See you.

Bye bye.