Nikolay: Hello, hello, this is
PostgresFM, a podcast about Postgres.

Hi, Michael.

Sorry for Mike last time.

Michael: That's all right, Nik.

I don't even notice normally.

Nikolay: Right.

Nik is fine.

Nik is fine.

So, episode number almost 90.

I don't remember exactly.

Like, Do you remember?

You always remember.

Michael: I remember, but you told
me not to remind you anymore

until we get to Milestone.

And you're the only one that ever
brings up the episode number.

Nikolay: Really?

Michael: I never mention it.

Nikolay: Okay.

Interesting.

So we are going to talk about what?

Michael: Oh yes, so this week was
your suggestion, and it's a

cool 1.

It's going to be a new feature
in Postgres 17, hopefully.

It's committed, but you never know
for sure.

And it's transaction timeout, a
new parameter that we'll have

hopefully in the next version.

Nikolay: I can show off, it was
my idea.

But most of the work, Andrey did,
Andrey Borodin did.

So it started during our PostgresTV
hacking Postgres session.

Michael: Which are back now, right?

After a long pause.

Nikolay: Well, yes, we'll try.

Nobody knows.

It's hard to arrange usually because
to do proper hacking, you

need a lot of time.

Last time we did restart it, we
talked about this transaction

timeout and Andrei tried to write
tests because former tests

needed to be removed.

By the way, I also wanted to say
thanks to Alexander Korotkov

who committed this patch.

Honestly, I even didn't ask.

So it was good to see that it's
committed.

And so session finished after 90
minutes by timeout because we

needed to go.

Session about timeout finished.

Okay, now you're smiling.

So, transaction timeout.

It took a few years for me to understand
something is wrong.

We don't have something.

Then, last year, like a year ago,
I started thinking, it should

be.

My idea was maybe I'm very wrong
or I'm very right.

So it's so strange in 2023 to think
about this basic setting.

But honestly, the last few years,
I already suggested to everyone

to consider the transaction timeout
to be applied on application level.

In 100% of cases, I saw a very
strange look, if it was a video

meeting.

It was a very strange look, like
Postgres cannot do it?

Yes, Postgres cannot do transaction
timeout, but you do need

it and we can discuss why.

So I always say, do it always on
the application side if you

have LTP, because, you know, like
anyway, HTTP has timeout.

A server, for example, NGINX, like
30 seconds, it's normal, right?

Nobody will wait many minutes,
unless it's a specific request.

Sometimes we can wait a few minutes,
but usually we have a limit.

And Postgres doesn't have this
transaction timeout.

It has only statement timeout and
idle transaction session timeout.

But no transaction timeout.

Michael: And also, so I looked
into when we got each of these,

and statement timeout was ages
ago, like 7.3, would you believe?

And then idle in transaction session
timeout was actually more

recent than I realized---

It's 9.6, which is probably only
about 7 or 8 years ago.

And then we also got more recently
idle session timeout.

Nikolay: Which is different.

It's outside of normal work.

It's for like, let's drop the connection
if it doesn't do anything.

Michael: Yeah, but that was a recent,
like, version 14 edition.

So still only a couple of years
ago.

Nikolay: Ah, you want to say that
it's also a very, very basic

thing and only added recently?

Yeah, I agree with that.

Usually people implemented it outside
Postgres as well, to drop

connections.

Michael: I've seen less need for
that one than I have seen for

this one, but like you, I didn't,
it's one of those things that I

kind of didn't realize I needed
because statement timeout can

cover a lot of the base, like a
lot of the use cases can be just

about coped with via statement
timeout, but one or two really can't.

And I think I saw your post and
I'll link it up the mailing list

thread that I think Andre started,
but your first reply to that

included some really good examples
of when you do need this in

addition to statement timeout or
like instead of statement timeout.

So is it worth like covering a
couple of those?

Yeah.

Nikolay: Yeah, let's discuss why
we use statement timeout in

OLTP like in regular the most common
cases like web and mobile

apps.

We had an episode about that and
I think it was the very first

episode maybe.

Speaking of counting.

Michael: Yeah, we've done a few
other episodes related to this

as well.

We did one on connections, one on
zero downtime migrations.

Nikolay: No, I'm talking about
the very first episode.

What was it?

Michael: Yeah, slow queries.

Nikolay: Slow queries, yes.

And this is about statement amount,
basically.

But people don't do SQL queries.

I mean, end users, web and mobile
app users, they don't do SQL

queries.

So they work at a higher level, HTTP
requests.

So why do we need statement timeout
at all?

Why?

What is the problem we are solving?

I have this honest question because
in my opinion, when we think

about what we try to solve with
statement timeout, it's not the

right tool usually.

For example, again, users work
at a high level and we know some

intermediate software settings,
middleware, which is usually

written in some Python, Java, Ruby,
anything.

These application servers and also
an HTTP server, like maybe front

end server, like an NGINX or something,
and VoIP, right?

They usually have some limits to
drop connections which last

too long, if a server is taking too
long to respond, or communication

taking too long, usually like 30
or 60 seconds.

This is what users will get if
something which should take 100

milliseconds or less, takes 30
seconds.

It's time to admit there is a problem
and tell the user that,

like, for 502 gateway timeout,
it's usually what NGINX was returning.

And then we, back to database,
we think, OK, we need to limit

as well.

But what exactly do we need to limit?

This is the question.

Is the statement timeout the right
tool for that?

Michael: I do think there's a like,
a lot of cases where it's

enough.

So if, for example, we're trying
to, in that case, let's say,

for example, the users or like
the app has already given up,

but the database carries on working.

Yeah, that's doing pointless kind
of, that's pointless work.

So avoiding pointless work would
be great.

But there are other cases too, where
even if the app didn't have

a timeout, is it okay for a few
users of a system to be using

all the resources when, you know,
like hogging those resources

by running some really, really
slow or really, really heavy queries.

Is that an acceptable trade-off
or do you want to limit that?

So I think there are other slightly
different cases.

Right, but

Nikolay: Work is not...

Statements are too small.

It's too atomic a piece, right?

Michael: Well, I think there's...

Before we move on to why transaction
timeout, there's also one

other case which I think or maybe
maybe this makes the point

perfectly. These long-running transactions,
even if they're a single

statement transaction, they can
block internal processes like

autovacuum and cause issues that we've,
I mean, we've spent multiple

episodes talking about the kind
of issues that long-running statements

could cause, never mind long-running
multi-statement transactions.

Nikolay: No, no, no, no, not statements
block it.

Transactions.

Michael: But as you've said multiple
times, you can't have a

statement without a transaction.

So like a long-running statement
is enough, but you're right

that...

Nikolay: You can have a statement
without transactions for example

creating an index concurrently. Three transactions
I mean, you're okay

without a transaction completely.

No, no dirty reads.

Michael: Exactly right,

Nikolay: Right, right.

So yeah, but like we want
to apply the right tool.

It means that it should suit for
all edge and corner cases.

And I'm saying statement timeout
is for a different hole to close.

Michael: You gave a really great
example that completely sold

me in that email thread I mentioned.

Nikolay: Email?

Michael: Yes.

And that was a case I've seen actually
quite recently, which

was...

...taratatata.

Nikolay: Yeah, I call it...

It's like from a machine gun.

Yeah, but - statements coming from
a machine gun.

Brief statements with brief pause.

Michael: And somebody setting a
transaction at the beginning,

so in the case I saw, it was deliberately
taking out, like doing

begin, then doing multiple updates
or like upsets in this case,

and then only at the end committing
it.

So any one of those statements was
really short, but because they

were doing tens of thousands or
even hundreds of thousands, I

think, in this case, the transaction
as a whole was longer than

you'd probably want on a OLTP
system.

So in that case, statement timeout
wouldn't have helped, or at

least any sane statement timeout
wouldn't have cancelled that.

But a transaction timeout...

Nikolay: An idle transaction session
timeout wouldn't cancel

this transaction because the breaks
are also short.

Michael: Yeah, it's not idle, exactly.

Nikolay: It's like from a machine
gun.

It can be select plus 1, by the
way, if it's wrapped in a transaction

block.

Select plus 1, this anti-pattern,
which...

Michael: Like n plus 1.

Nikolay: 0, sorry.

Yes, yes.

Yeah, yeah.

I mean, it's actually a terrible
name, but the idea is that it's

n selects in a loop.

So, we didn't notice that they
have a loop.

It can be short updates with the
idea that we want to make them

short because it's better, you
know, like to...

But if it's wrapped into a transaction,
you cannot release any

locks until the very end of the
transaction.

This is the rule.

All locks are released only if it's
a commit or rollback.

That's it.

And so it's a bad idea to split
into batches, updates or deletes.

Insert is almost never, I think,
an issue.

It makes sense to split.

An insert should be a single massive
insert, usually.

Like copy or insert.

But if it's updates and deletes,
people know there's a rule to

split, then they put it in a single
transaction and what's happening

here.

What do we do?

We're accumulating a lot of logs
and not releasing them.

So there are only 2 reasons, the
big reasons I see.

Well, the source of the transition
is 1 reason, but there are 2 problems

which are very bright.

They scream let's have transaction
timeout.

They did it for many years and
I always say, okay, this is a

problem we must solve on the application
side.

We cannot do it in PostgreSQL at
all.

Surprise.

Well, we can use pg-cron and put
maybe some, I call it terminator

snippet, working with pg_stat_activity,
checking the exact start, like

transaction start timestamp, and
then terminating.

So terminator script.

This name is derived from pg_terminate_backend.

So was it renamed or no?

I don't remember.

pg_start_backup was
renamed.

It's about naming and I support
it.

Well, I'm having 3 in my speech,
sorry.

So, pg_start_backup was renamed to
pg_basebackup start.

So the same should happen with
pg_terminate_backend, I think.

Now, pg_terminate_backend should be
pg_backend_terminate.

So, this terminate script is also
like a workaround.

It should be some simple solution.

Let's not allow too long-running
transactions.

Because we keep locks, this is
problem number 1 we just discussed.

And second problem you mentioned,
keeping xmin horizon frozen

basically, not shifting.

Which means that all, like xmin
horizon is the hidden xmin value

of a tuple which is the oldest
in the system and we usually cannot...

Autovacuum usually comes and deletes
it at that time, we delete

it but we cannot delete it if there
is some transaction which

still can read from this because
xmin is like current time not

past, not the past for this transaction
which means that if we

keep our transaction we block this
xmin frozen and that means

that Autovacuum cannot delete dead
tuples, more and more of them,

freshly dead tuples, I call them.

Or maybe it can be improved this
name.

So these 2 problems, locks and
xmin horizon frozen.

Locks are not released and they
both can hit and a lot of people

can notice and you can even be
down down server can be down in

some cases and you cannot limit
it inclusion in Postgres 16 I

was so happy to realize I'm not
mad.

It's obvious and I think it was
discussed maybe, so shouldn't

discuss many times, but we just
went ahead, many things to Andrej,

just went ahead and coded it.

Then many strange cases started
to appear.

For example, sub-transactions or
conflict with statement_timeout,

which should fire first, for example.

Michael: That's a good point.

Do you want to talk a tour about
in this future, like once Postgres

17 is out, if you're designing
a new OLTP system, are you even

using, like, do you normally use
global statement timeouts?

And if you did in the past would
you now replace that or would

you use both with different settings
how would you

Nikolay: do it?

Good question, good question.

So first of all in documentation
we can read that setting statement

timeout globally is not a good
idea and I think it's a very bad

idea to write this in documentation
because we discussed, even

in HTTP server, application server,
they have their own timeouts

database is usually the most like...

Data intensive work is usually
in database unless we talk about

some other stuff happening now
outside of web and mobile apps,

right?

I mean AI stuff like in GPU and
so on.

So we want to limit in all OLTP systems,
we definitely want to limit

our work to 30 seconds maybe.

It's a good limit.

This article and our very first
episode is about basics, which

are so like 30 years, like 40 years
since the beginning of the

Internet.

People don't like to wait more
than a second, in a normal case.

It means that we don't want to
limit globally and be protected.

Only specific users which need
it, always can change it, unless

it's restricted.

So you just set statement timeout
or anything and go.

Of course, if DDL is needed to
be executed, for example, create

index concurrently, of course,
you also should remove this limit.

But global setting is protection,
it should be there for OLTP

case.

And Postgres is like, OLTP is the
main case.

We don't usually think about analytical
queries.

So it means that I always recommend
setting this and documentation

is having bad advice in this case.

So 30 seconds, my recommendation.

Sometimes 15, it's better.

Michael: Oh, wow.

Nikolay: Yeah, well, even 10 maybe.

It depends.

It raises the bar requirement for
queries that this is about

quality actually.

So if your query is poorly designed
or you forgot to create a proper

index something like a bad plan,
be killed.

But now with transaction timeout,
I would say statement timeout

might be not needed at all.

Michael: Interesting.

I wondered if you were going to
say that.

Nikolay: So again, my logic is
applied at a higher level.

This is about each feature.

If we go down, in both cases, database
specific, Postgres specific

cases I mentioned, they also apply
to a transaction level at

higher levels.

We don't care about statement and
breaks between statements.

Michael: And do you think like
same order of magnitude?

Like you mentioned 30, I've seen
60 seconds quite often mentioned

as a sensible default, so 30 seconds
not too different.

Would you go similar, like for
any reason to double it or like

increase it, like add a little
bit of buffer there or just similar

number?

Nikolay: No, no, no, Why buffer?

Set it to 30, solve all problems
and then consider going even

down.

And if we talk about statement
timeout, why at all?

Like we need it, like, okay, we
put it, but what if we have transaction

timeout 30 seconds, for example,
which statement timeout would

I use?

30 seconds maybe as well.

But it's already solved.

Michael: I don't think there's
any, that wouldn't make sense

to me.

But I was wondering if you might
say you know statement timeout

30 seconds transaction timeout,
60 seconds and

Nikolay: Transaction? I don't... transaction
session timeout, 60 seconds.

But when transaction timeout? What?

Session timeout, you mean?

Michael: No, no, no, transaction.
So, transactions can consist

of multiple statements, right?

Right. So, there's no need to have
a statement timeout that is

bigger or equal to the transaction
timeout.

Nikolay: It doesn't make sense.

Michael: So the only thing that
could make sense would be a statement

timeout that's less than the transaction
timeout.

Nikolay: Right.

Michael: But it seems pretty neat.

Yeah.

Nikolay: But again, like if you
take transaction timeout, which

is already quite good, restrictive,
30 or 60 seconds, then I

think statement amount...

And I don't see logical to go down.

Okay, it's about resources.

If you go down, maybe you save
some resources by killing some statement

earlier than the transaction.

But it's not the main reason to
have transaction command for

me.

Well, of course. Maybe it's actually
super important.

Maybe I just forgot that we use
it.

Okay, I don't know.

I saw so many incidents where even
statement timeout was not achieved,

but resources were spent fully.

So I don't know.

I mean, we have very restrictive
statement timeout, very, very

low.

And still CPU 100% and everything
is down because a lot of work

is happening.

So statement timeout doesn't protect
you, even 15 seconds.

I mean, to start protecting, maybe
I would like to have it 1

second.

In this case, I think it will start
protecting.

Michael: Interesting.

Nikolay: But 30 seconds is too high.

Michael: Yeah, it's a tricky one,
right?

Because yeah, I think there's a
growing, and I know you mentioned

analytical isn't our primary case,
but there's a growing trend

that I'm seeing of kind of hybrid.

I've had it called HTAP databases
where people don't want to

spin up a second analytical, like
a warehouse or something, and they're

trying to run some small analytical
queries on their like OLTP

database, which means you do get
these kind of the odd several

second query or at least hundreds
of milliseconds running

on the same database.

So I can see how it could get tricky
for some folks.

But I think that the 30 seconds
and the higher feels like a sane

starting point, as you mentioned.

And if you start to see some errors
from those, look into them.

And if you don't, look into what
your slowest queries are and

whether you can reduce it.

Nikolay: In case of HTAP, I will
just have another database user

specifically for long-running
queries. I will adjust this setting

maybe setting it to a couple or maybe
5 minutes and allowing to

last longer, that's it.

Michael: Yeah, that's a good point
that I don't know if we mentioned

yet that these can be set at the
role level, at a session level,

and globally.

Nikolay: Well, I never tried, but
statement timeout definitely

can be set at user level.

I think lock_timeout should
work as well.

Worth checking by the way.

Michael: I thought the doc said
that, but I was checking the...

Because this is the development
doc.

Nikolay: Maybe even it's...

It should be

Michael: in the

Nikolay: testing tests maybe.

Maybe it's...

So I'm quite sure it should work,
yeah.

So you...

Anyway, we talk about statement
timeout right now.

We don't have lock_timeout
until 17.

So statement timeout can be set
for different users differently.

And I also had a case when people
had very low global statement

timeout, but then adjusted for people,
for human connections,

they adjusted it to set to 0.

Michael: How do you feel about
that?

Nikolay: I feel this is dangerous.

It's like someone can just run
something then drink some coffee

and then go for some call.

It's kind of dangerous.

By the

Michael: way, I've got bad news
for you.

The warning seems to have been
copied over to transaction timeout

documentation as well.

And it says in the docs, in the
new docs, setting transaction

timeout in postgresql.conf is not
recommended because it would

affect all sessions.

Nikolay: Yeah, actually it's a
good point.

I need to raise this again.

I don't agree with this.

It's a bad, very bad statement.

Michael: There's also 1 other note
in here that I had forgotten.

I guess it's only a minor note,
but prepared transactions are

not subject to this timeout.

Thought worth mentioning.

Nikolay: Yeah, it's so.

Michael: So, yeah.

Is there anything else?

I guess the story for how this
came about is pretty interesting.

The thread seems quite long, to
me at least.

Nikolay: As usual, a lot of unpredictable
problems and then people

seem to agree and then I think
okay, not 4.17.

But it was a good surprise last
week, right?

Or a couple of weeks ago.

Yeah, I just wanted to say, if
our world survives, right?

For folks who are listening to
this in 2025 or later, just start

thinking about limiting at transaction
level first and then go

down.

This is the main advice, but for
others, limit the application

side and limit statement amount
and idle transaction session

timeout, of course, both are good
to have limited to below a minute

maybe, at least 1 minute.

I also saw people are afraid of
an idle transaction session

timeout being low.

I don't understand this.

Put it very low, very low.

Like if someone is not doing anything,
bye bye.

OK, maybe for humans, you can limit
it to 1 or 2 minutes but

still you're probably holding some
very important exclusive locks

and blocking someone, it's not a
good idea so either commit or

do something already, right or

Michael: yeah the default
is no limit like there's absolutely

no limit at all.

So, any...

Yeah, even if you're really...

Even if you are concerned, what
would an hour limit look like?

Several hour limit look like?

Even that's going to be better
than none at all.

So yeah, I like that advice for
people to be checking.

Do you even have any of these limits
set?

Because the default is not to.

And even on a lot of cloud providers,
they don't set

Nikolay: defaults for you.

IGOR MINAROVICH-SHMAROVICH Yeah,
Nginx, if you work on web or

mobile apps, Nginx, for example,
has a default proxy timeout limit

of 60 seconds.

This is a good starting point to
think about it.

If 99% of your database work is
to serve HTTP requests, at least

60 seconds should be there.

And go down to details.

Also, one more thing.

Maybe some folks will be inspired.

I'm not a good drawer of some pictures,
but imagine if we had

a schema in documentation showing
the relationships between statement

and breaks between statement and
some big transaction and timeouts

will be visualized.

So for better understanding, this
is transaction timeout, between

them there are multiple statements,
they are also limited and

breaks are limited.

And also outside we have a session
timeout.

So idle session, how is it called?

Michael: Idle session timeout.

We have both.

Nikolay: No, session timeout, what
is it?

I mean-

Michael: We have idle in transaction
session timeout and we have

idle session timeout.

Nikolay: Idle session timeout.

This is what...

Like, it's outside of a transaction
also have limited.

So all periods of workflow are
covered now.

And to visualize this would be a
great idea.

Documentation already has a couple
of pictures.

It would be good picture to have.

I

Michael: think you'd be in the
first 10 still though if you got

this one in.

Nikolay: Right exactly, so maybe
someone will be inspired.

Good.

Michael: Nice.

Yeah.

Thanks so much Nikolay.

Nikolay: Catch you next week.

Bye.

Catch you

Michael: next week.

Nikolay: Bye.