Nikolay: Hello, hello, this is Postgres FM.

I'm Nik, PostgresAI.

And as usual, here, Michael, pgMustard.

Hi, Michael.

Michael: Hi, Nik.

Nikolay: I proposed this topic because I thought we have a gap

here.

Before we started recording, you said we have already episode

about transaction_timeout, but it's very related but it's not

the same.

So I wanted to dive deep into long-running transactions.

What do you think?

Michael: Yeah, I was surprised we didn't have an episode on it

specifically already.

We had a first ever episode, didn't we, on slow queries and I

guess long transactions as part of that.

But we hadn't done a specific episode on it.

And I guess as a long-running podcast maybe it's definitely time

to revisit.

Yeah,

Nikolay: sometimes idle in podcasting.

So I would like to challenge us and think by the end of this

recording, we record in advance as usual because some preparation

is needed, post-processing is needed, thank you for doing this

by the way.

So I would like to challenge us and think should this episode

be called long-running transactions or somehow else?

Because you already understand what I'm talking about, right?

Michael: Only because of our pre-discussion I got a good hint

from you. But is it more that the problems they cause that you

want to talk about and therefore maybe it's only a subset of

long-running transactions that are an issue.

Nikolay: So listeners who listen to this already see the decision.

Did we preserve a long-running transaction title originally or

we decided to change it?

But the problem in my head is simple.

I see in monitoring tools, in blog posts, and everywhere, like

everywhere actually, that long-running transactions are bad.

And at the same time, I remember, actually it was an interesting

moment, I had an experimental educational course where I explained

various concepts of database health, and we touched this topic,

how long-running transactions are harmful to autovacuum and I said

you just open transaction and it will be blocking autovacuum and

I said it's so easy to demonstrate let's open a couple of psql

sessions or something like yeah psql vacuum verbose and we will

see it has dead tuples but cannot clean up them because there

is a long-running transaction in progress.

And I just wrote begin and it didn't work.

It Cleaned up tuples perfectly.

I think I also tried again with begin and SELECT, doing some

select and keeping transaction
open, being in idle transaction

state according to pg_stat_activity.

And again, it didn't work.

And I looked stupid.

And the reason I look stupid is
actually because we have actually

2 problems and we constantly mix
them.

And saying long running transactions
block vacuum is wrong.

Not all of them block vacuum.

Some of them block vacuum.

And we have, in my mind, we should
improve tooling and materials

and everywhere understanding and
start distinguishing 2 problems.

1 is lock-related and 1 is vacuum-related.

We should change monitoring tools
and snippets, everything, to

hunt 2 problems separately.

Diagnose them separately.

Michael: Yeah, should we go back
to the basics though, in terms

of, maybe let's first cover the
long-running transactions that

are harmful, or like can be harmful,
and then reverse our way

backwards to the ones that aren't
and therefore trying to distinguish

between them.

Nikolay: Some people say long transactions,
which I think is

oversimplification because it may
be very long but fast, or it

may be very short but very long
running.

Michael: Wait, what does long mean
if it's not just about the

time?

Nikolay: A lot of text.

Michael: Oh, sure.

OK.

I didn't even think about that.

Nikolay: Like, I'm not a person
who tries to be pedantic.

I gave up, for example, my scientific
mentor was distinguishing

database systems and database and
saying you cannot say Postgres

is database, you must say Postgres
is database system or database

management system.

I already gave up.

You cannot fight with old people.

They will say it's a good database,
a meaning database system.

So it's okay to say long transactions.

It's not okay for my texts I write
and like products we build.

There we always pursue precision
and we say long-running transactions

right but at a very high level
if you have long-running transactions

there are 2 dangers at least actually
there are maybe a few more

but 2 main dangers are some locks
are acquired.

And as we know, like this is a
simple rule to remember, we talked

about this so many times locks
are released only at the end of

transaction.

They cannot be released before
rollback or commit, that's it.

So it means that transaction, if
it lasts long, accumulates a

lot of locks and even access share
locks can be harmful, right?

Sometimes.

Yeah,

Michael: even if the statement that acquired the lock was short,

if it's followed by statements that are slower, it's not...

Nikolay: The

Michael: lock is acquired, that's it.

Exactly.

Nikolay: The lock is held until the very end of the transaction.

It might block someone like DDL.

DDL might block other SELECTs, as we also discussed many times.

And this is a big surprise, and like this two-step effect, which

can put the whole server down, actually.

This is a big deal.

So if you just selected something and keep the transaction open

for long, you just, for example, should avoid changing schema.

Or you have a dump happening.

Very common situation.

There's a dump happening or something like initialization of

logical replica, which lasts a couple of hours, for example.

And at the same time, we have deployment trying to change the

DDL, trying to change schema, right?

And yeah, this is a very dangerous situation because if you don't

have lock_timeout, retries, we talked about it as well a lot.

In these cases can cause downtime.

So lock acquisition plus holding these locks for long.

This is a danger number 1.

And danger number 2 is how MVCC in Postgres is organized.

MVCC is a multi-version concurrency control for like new folks.

I know we have new folks all the time and specifically vacuum

problem.

So to clean up dead tuples promptly, server should be in good

state.

And if you keep the transaction long, in some cases, not in all,

as we will discuss now, your long-running transaction might block,

not might block, it will block.

If, for example, you inserted a row and just keep the transaction

open, This is a real transaction.

It has real transaction ID, right?

This is definitely will block vacuum and it will need to skip

the tuples which became dead recently after your transaction

started.

Because, and this is global, this is biggest surprise here for

people, as I see, to realize this is global.

You insert it to 1 table and keep the transaction open.

But vacuum cannot clean any table.

It can clean only dead tuples which became dead before, because

xmax, this is a hidden Column in every row, you can SELECT xmax

from any table.

Xmax for dead rows is filled with a transaction which deleted

this row, with UPDATE or DELETE, it deleted this row version.

And if this xmax is in the past compared to your transaction

ID, which holds this snapshot, right?

This will be possible to delete, but if it's later, Postgres

thinks maybe your transaction will need it.

Michael: Yeah, it could still, if that transaction wants to read

that row, let's say the 1 that got updated or deleted, it needs

to return the old version.

Yeah.

According to its snapshot, so it's still needed, or theoretically,

still needed.

It's a feature, right?

Nikolay: It's a feature, but also we need to think about transaction

isolation levels here.

Michael: True.

Nikolay: Because by default, so if you have a transaction ID,

so basically in pg_stat_activity, you can see the column

backend_xmin, which defines this horizon that your transaction needs,

this snapshot, it defines this snapshot.

And if you have repeatable read, which is not repeatable read,

it's already higher isolation level.

It's more like it's stricter.

It's snapshot isolation, in fact.

And it's not default, but it's used during things like dumping.

pg_dump will use a repeatable read.

It opens transaction in a repeatable read.

And also logical replication initialization.

And sometimes in multiple threads, multiple connections.

So in this case, this snapshot needs to be held because this

transaction which is running in repeatable read needs this snapshot

for consistency.

All Queries will need to read from this very snapshot.

So all tables you read during dumping must be from the same moment,

right?

While in traditional, like, default transaction isolation, which

is read committed, it's shifting.

Snapshot is shifting, so it's not being held.

And if you, for example, open transaction, SELECT from 1 table,

SELECT from another table, SELECT from another table, you will

see that this backend_xmin is shifting, right?

Which means that our horizon is shifting and we're not blocking

a vacuum like we could block if we are dumping or just using

transaction at repeatable rate.

Which means that not all, not all transaction, long-running transactions

are harmful to vacuum.

Right?

Does it make sense?

Michael: Yeah, at least from a Bloat perspective.

So the, actually, probably from everything in terms of vacuum,

I was thinking there might be an exception around like transaction

ID wraparound, for example, if we had a very long running transaction,

but if it didn't have a transaction ID, then actually that's

not gonna be an issue for a transaction
ID wraparound either.

Nikolay: That's an important distinction
because if we inserted

or we ran XID current function,
or there's a new version, I'm

constantly forgetting, I'm old.

So you have allocated real XID.

It's 1 story, but if you're just
reading and you don't have,

your transaction doesn't have allocated
XID, it has only basically,

there is concept of virtual XID,
which is optimization to save

space because we have only 4 byte
transaction IDs, right?

So it's like, it's not a lot these
days.

And if you, like most of your queries,
most of your transactions

are reading transactions, it would
be not wise to give them,

to consume from this space, right?

Yeah.

So there is optimization with virtual
transaction ID and purely

reading transactions, they only
consume from that space, right?

Virtual transaction IDs.

So we don't have it and which means
that you need to distinguish

these topics and my point is that
not all long-running transactions

are harmful.

But at the same time, if you're
just reading, but this reading

itself is still happening, you
are reading from a huge table,

it lasts long, definitely backend_xmin
will be like some value

not changing in pg_stat_activity.

In this transaction, reading transaction
and read committed,

it will be harmful.

So I'm talking only about cases
when we read something And we

either read something else constantly,
small reads, right?

Or we read and sit in idle transaction
state, not doing anything

and not closing this transaction.

Michael: Yeah.

Are you talking, is this a bit
of an edge case?

Like who's actually doing this
a lot?

Like when does it happen?

Nikolay: What exactly?

Michael: Like a small read and
it doesn't get committed.

Nikolay: Oh it happens all the
time.

The number 1 reason is humans.

So they...

Michael: Like opening an editor
or something.

Nikolay: Yeah.

In psql or even in more cases some
graphic interface, right?

They begin some production, read
something and then they all

maybe change something also, right?

More like usually when you are
reading you are not opening transaction.

Like when you're just exploring
stuff, you run some queries to

understand the state of database.

Usually you don't use begin-commit
transaction block.

But when you try to write, sometimes
you do.

And abandoned transaction from
humans, so common.

It's very common.

So just abandoned, forgot to close.

In some cases, this is called turning off auto-commit, right?

So you, in some editors, you can say, OK, I don't like auto-commit,

I will turn it off.

Then you start working but not committing, and then this is abandoned

transaction, so it doesn't do anything.

And if it holds this backend_xmin in this activity, this is

what starts blocking autovacuum.

And in first maybe few hours you won't notice.

Michael: So yes, I understand the dangerous case.

I'm talking about the non-dangerous case.

The opening a transaction, begin, then a SELECT.

You talked about just doing a read query, not doing an UPDATE.

When would that happen?

Nikolay: In the same situation, it might happen as well.

I'm just like, I was trying to understand all the possible cases.

In this case, it's not.

Michael: Well, I think, yeah, but also, I just don't think it

happens in the same way.

Like, I understand doing begin, UPDATE, and then you've got the

option to ROLLBACK if you're scared or you're traumatized maybe

from doing an UPDATE once and then seeing you're expecting 1

row updated and then you see 30,000 rows updated or something

like that maybe You start using transactions explicitly so you

can ROLLBACK to...

Nikolay: I get what you mean, yeah.

Michael: But who's doing that for SELECT queries?

Like I don't...

I don't know.

Nikolay: Our purpose is to be complete, right?

To cover all possible...

Michael: No, I understand.

So this is why I'm asking, Is this an edge case?

Nikolay: Yeah, usually I think if I have a bunch of SELECTs and

I combine them to transaction, I would probably use repeatable

read, so all of them are consistent.

That's why I combine them, right?

This is like, basically like pg_dump does, right?

If I don't, if I use transaction, but I don't use repeatable

read, why do I use transaction in the first place?

It's just a bunch of SELECTs.

Maybe to...

Just like what comes to my mind.

Maybe to make sure schema won't be changed.

To block DDL.

Michael: Or to deal with the pooler maybe?

Like to make sure you stay with the same.

Nikolay: Yeah, maybe there might be some cases and yeah.

Another very common situation when we see, so there's this term,

xmin horizon, right?

You can see it's in some places
in source code, you won't be

able to see it, I think you won't
be able to see it in documentation.

I checked long ago, I think it's
very like unofficial term.

You can see it in some blog posts,
but again, I feel it's not

fully understood topic, right?

So what is xmin horizon?

And I was trying to understand
like all aspects of it, how to

properly measure.

And my question is like, why is
not properly measured by monitoring

tools?

Because it's not.

You like check all popular monitoring
tools, excluding PostgreSQL

monitoring tool, right?

And you won't be able to see like,
they like, they talk, okay,

there is a long running transaction
lasting like this amount

of time.

Okay, have you excluded transactions
which are not harmful or

you included them?

What's the purpose of this chart?

Are we talking about danger related
to locking or are we talking

about danger related to vacuuming?

Unclear.

My point is, if we start distinguishing,
if we start thinking

about xmin horizon, we need to
understand what it is, and how

to measure it, how to properly
understand it.

So I came up with the idea, we
have 4 sources.

First is a pg_stat_activity,
backend_xmin, we just select

the oldest.

Actually, you can use function
age to backend_xmin, which will

give you difference, just like
integer difference between your

current function.

Yeah, this function should understand
that we have wraparound

of the space, right?

So it will give you a number of
transactions, the agent in terms

of number of transactions, not
in terms of seconds.

The second very, very common situation,
super common.

You created a logical slot and
either haven't started using it

or abandoned or it's lagging.

So if you check the pg_replication
slots, it also has a column

that's called xmin there.

And you also can use age.

So it's another very popular reason
to have something lagging.

And we need to distinguish logical
replication and physical replication.

Because as I remember, if you have
physical replication slot

but haven't started using it, I
think it's not harmful.

I might be mistaken.

Logical replication slots are definitely
harmful.

Unused logical replication slots
are definitely harmful.

Michael: Did this change in a recent
version where I think there

might be some new setting where
by default they expire after

a certain age of not being used.

Nikolay: Yeah, there is such setting,
it's just a protection

mechanism and I think we should
talk about protection measures

separately.

Michael: Yes, okay, cool.

Nikolay: I'm just trying to say
that physical and logical replications,

replication basically streams,
they are slightly different here.

Definitely if you have logical,
if it's unused, it's harmful.

If it's lagging, it's also harmful.

Michael: Makes sense.

Nikolay: It starts harming you
right when it's created.

So you don't want logical replication
slot to be unused or lagging

too much.

Physical slightly different, and
I think, if I'm not mistaking,

if you just create a slot but nothing
connected to it, it won't

hold xmin horizon.

Moreover, if you have physical
replication, but this replication

is not using hot standby feedback,
on replica it's set to off,

which is actually not common.

Usually people prefer to set it
to on.

Hot standby feedback sends, for
physical application, it sends

to the primary the information
which xmin horizon is needed on

replica, right, on standby.

If it's not sending, it's better,
right, because we don't care.

But this will give you, like, physical
replica which will start

lagging every time it has a long-running
transaction on its own.

While hot standby feedback will
protect replication lag, but if

there is a long-running transaction
on replica, it will report

to the primary, and we will have
the same effect as we had the

long-running transaction on our
primary, which explains why this

dilemma makes it really hard to
have long-running transactions

for analytical purposes on regular
physical nodes, right?

And you cannot offload it to replicas.

Only if you can afford replicas
which are lagging for hours,

for example.

Anyway, we have logical and physical
replication which can also

affect us.

And we have fourth component, which
is prepared transactions.

There is a pg_prepared_xacts transactions.

System view you can see also I
don't remember column name maybe

transaction or something, I don't
remember.

But you can also get, if you use
2PC, right, prepared transactions,

in this case you have them, right?

In this case some of them might
be affecting, they may affect

xmin horizon.

So I found 4 sources for our analysis
to define which, so we

dig information from all of them,
take ages And then just take

the greatest of ages.

This is such function greatest,
right?

And this will be our true age of
our xmin horizon.

What is big, what is small?

This is hard to understand.

When I think about it, what is
big?

Is 1,000 too much or 1 million
too much or 1 billion too much?

Billion is definitely too much
because we, we, our, our, yeah,

yeah, yeah.

We have 2.1 is basically our, the
whole capacity for XID wrap

around, right.

But we also, there's also MultiXact
mechanism additionally,

but let's not go there.

Billion is definitely too much.

Is 1,000,000 too much?

In heavily loaded systems, I like
to think about it like thinking

about what tables we have and how
many rows they have.

And how many rows we consider,
how many dead tuples.

So dead row versions, how many
of them we consider too much.

Usually we prefer for OLTP heavy
loaded systems, we prefer

to make autovacuum be very aggressive.

Default settings for autovacuum,
vacuum scale factor, autovacuum

analyze scale factor, they are like 10 and

20% is on 2 common default numbers.

It's too much.

If we have 10 hundred billion rows,
10% is way too much.

So we usually set, okay, like 1%
so 0.01 or even half percent.

Even sometimes even go even lower
and make it delete the tuples

more often now you already have
some hint. Okay, you think I have

like 10 million row table and I
have 1% scale factor. It means

that 100,000 rows is when the
autovacuum should start cleaning

up.

And then, I think, okay, xmin horizon
is lagging that much.

Okay.

Is it lagging 1,000?

It's okay.

We don't care.

Our basically actual horizon when
autovacuum starts, we expect

work from it, 100,000, roughly.

But the problem is that we have
different tables, small, big,

right?

So we need to think about core
tables and like nature of our

database to think how much of xmin horizon,
like xmin h is too

much for us, right?

Michael: I think there's 2, you're
talking almost like a bottoms-up

approach, right?

Working out how much bloat we can
tolerate.

Nikolay: But yeah, how much dead
tuples we can tolerate is not

yet bloat.

As we remember, we allow too many
dead tuples so autovacuum is

converter of the tuples to bloat,
it will come and convert our

millions of dead tuples to bloat,
but it's not bloat yet.

At least in my terminology, I wish
we had official terminology

for bloat.

Like for dead tuples it's clear,
but for bloat, we don't have

it as well.

Michael: The more dead tuples we
leave around, the worse bloat

issues can get, like in terms of
like fragmented indexes for

example, in terms of hot updates,
if we've got dead tuples that

we

Nikolay: can't remove.

Hot updates might benefit from
bloat because they have space.

Michael: No, because it's not space
yet if you can't reclaim

it.

If it's not being processed by
the network.

Nikolay: But then eventually a
vacuum will happen.

Michael: Eventually.

And it

Nikolay: will leave a lot of gaps
and these gaps might be reused.

Michael: Maybe, yeah.

Nikolay: There are opposite direction
of effects here.

Michael: But the point I was trying
to get to was often people

are monitoring this in order to
prevent transaction ID wraparound

so the top-down approach could
be how many how much time okay

1,000,000 might not be that bad in
terms of getting towards 2,000,000,000,

but if 2,000,000,000 could accumulate
within an hour or 2, if you've

got like a ridiculously, so actually
you might want an earlier

alert or a lower threshold because
as soon as you find out that

it's going above a healthy number
or above like a not normal

number for your system you've only
got, you're on a stopwatch,

you've only got 2 hours, you've
got to fix this problem.

So it might be that you're thinking
in terms of top down as well.

Nikolay: Yeah, for example if I
see age of this is 100 million

it's already wow, it's a lot, It's
already like emergency.

You

Michael: might only, yeah, exactly.

You might only have a couple of
hours at that point.

Nikolay: Yeah.

Speed is interesting.

I wish we had also good, again,
monitoring systems, like proper

XID consumption, XID growth
rate metric to be covered

properly.

If you think, okay, this is an xmin
horizon, some numbers, okay,

I know it's transaction, but you're
right, we like, it's hard

to map it to real time.

But here I see, okay, we consume
2 billion transactions per week.

It's quite a loaded database, in
fact, right?

Real transaction ideas.

In this case, I see peaks when
there are busy hours, I understand

my seed growth rate.

And this is helpful for me.

I understand, okay, this is, I
can already start playing with

this.

I think we could go even deeper.

I just don't see proper analysis
tools in this area developed.

Like this is like my, like thoughts
and from people I work with

and talk to, and I see huge potential
here.

So it could be much better visualized,
presented for analysis.

Michael: And as you say, separated
out in terms of things that

are risky in terms of locks and
things that are risky in terms

of...

Nikolay: Oh yeah, locks are completely
different.

Michael: I'm just going to keep
saying block.

Yeah.

But that's what I mean, you're
talking about different alerts,

different charts, different...

Yes.

Nikolay: So I would have alert,
and we actually do have alerts

for xmin horizon based on understanding
this particular system

with its growth rate and like table
sizes, right?

And the behavior of vacuum, autovacuum.

And we set it, for example, I think
100 million is a huge number.

We should set it lower and already
start investigating what blocks

our xmin horizon.

And this is the right direction
to understand.

And we also can check logs if you
enable autovacuum.

We usually aim to enable, if not
all, but at least above 1 second

autovacuum processing.

So log_autovacuum_min_duration,
1 second.

Sometimes we set to 0.

There is some risk that you might
see a lot of and have observer

effect, but usually like at least
1 second is beneficial.

So you start Again, I think all
default was 10 minutes.

It's ridiculously huge.

Like you don't see a lot.

And I think it was, it was decreased
recently.

Right?

Oh, cool.

Yeah.

So what I'm talking about here,
if you have a logging for autovacuum

behavior, it reports like xmin horizon,
it reports it, right?

Transaction ID was this, xmin horizon
was that, it's this number

behind.

So you might extract information
from those logs.

And also it reports how many dead
tuples it would remove, but

couldn't because xmin horizon is
blocking.

So autovacuum messages and logs
are extremely helpful for this

type of risk analysis.

Michael: I think it might be just,
I think it's still 10 minutes.

Nikolay: Maybe, maybe.

Michael: Wow.

All right.

Nikolay: So this is about xmin and the vacuum behavior xmin horizon

and this concept again Like I think documentation could be Fair

that these concepts exist Maybe like 1 should propose To define

this, because everyone suffers from bloat.

And I think, in my opinion, everyone needs to understand, working

with Postgres deeply enough, it needs to understand the concept

of xmin horizon to start distinguishing it from long-running

transactions, right and The second problem is like first problem

first problem historically lock acquisition what to say about

it, so it's Yeah,

Michael: I think we should point people.

I think we did a good episode on that specifically.

But yeah, maybe that's a good excuse to switch over to mitigations

though, because...

Nikolay: Mitigation.

Yeah, 1 point I wanted to mention, there are some exotic problems

also, which might happen from long-running transactions.

Michael: Oh yeah, sure.

Nikolay: I point to my sub transactions Post explaining 4 problems

with sub transactions and 1 of them which happened to GitLab

And we had episode I think about it, right?

So it was the most interesting.

It was happening when you have long-running transactions.

I think when you actually hold this xmin horizon on the primary,

hold snapshot, or maybe locks.

This is a question actually I need to do.

I have some homework to do to return to that material and understand

what of 2 problems happened there.

So long-running transactions on the primary and high TPS replica

started to suffer at some point because of sub-transactions.

Once you close long-running transactions on the primary, suffering

ends.

So this is exotic already, exotic problems where I need to understand

is it lock-related or snapshot-related.

Michael: I also think replicas are an interesting thing in general

in terms of long running transactions.

So long running transactions on the primary and whether they

affect the replica and then also long running transactions on

the replicas and even when they can affect the primary.

Nikolay: ...a replica if hot standby feedback

is turned on, as we discussed.

Exactly.

Can report to...

They will report to primary, can have same effect.

Michael: Yeah.

So...

So I think that's not always something people are aware of.

But yeah, in terms of mitigations though, it feels like timeouts

for our friends in a lot of these cases is that, and monitoring,

we've brought up monitoring a lot, right?

Monitoring, alerting, even in extreme
cases, killing certain

problematic long-running transactions.

Nikolay: What did you want

Michael: to talk about in terms
of mitigations?

Nikolay: So, yeah, of course if
you can get rid of all the long

running transactions, both dangers
don't affect your database

health, which is great.

But sometimes we need long-running
transactions, sometimes we

need to accept some lag in replication
slots.

By the way, what caused me to start
distinguishing something

is not right.

We talk about long-running transactions,
but the same problem,

vacuum being blocked.

vacuum not fully blocked, you understand,
so you cannot DELETE

some data.

It's not only about long-running
transactions, it's also about

slots, it's also about prepared
transactions.

What's happening?

Okay, prepared transactions, they
are at least transactions,

but slots, right, or replication,
like anyway.

hot standby feedback.

Okay, hot standby feedback is also
about transactions that just

happen on standby, but just unused
logical slot.

So it's a wrong explanation of
some problem.

Problem is xmin horizon is frozen
it's not progressing right?

Michael: Not progressing yeah.

Nikolay: Maybe it's progressing
but too slow also might happen.

Michael: Yeah so if it's progressing
slowly though it's just

blocked at a new point, right?

Like it's...

Nikolay: Yeah, if it's progressing
too slow, your horizon of

XID, not horizon, but actual XID
is already too far.

So basically what we should look
at is Age of xmin horizon.

Age of the old xmin needed.

Yeah, this is what we, all we need.

And if it's too far in the past,
this is a problem.

And the reasons of this, as I said,
4 reasons.

And mitigation is simple.

Don't allow it to be too far in
the past.

How?

First of all, in OLTP I'm like
very highly recommend to be very

aggressive in time with timeout
settings globally.

I know documentation says that
it's like it's controversial and

so on like I know like If HTTP
server setting can afford 30 seconds

or 60 seconds as maximum for OLTP requests, why do we, database,

cannot afford at least the same
for all regular traffic.

Of course, for CREATE INDEX CONCURRENTLY,
REINDEX CONCURRENTLY,

we need to set it to 0.

What to set?

statement_timeout and 2 other things,
right?

If you think about transaction, it might be like something is

running, I don't transaction, something is running statement,

I don't transaction.

So it's either statement or I don't transaction and there is

a whole transaction.

So basically we have whole and 2 phases, like they switch after

each other, right?

It means we can limit the whole transaction or statement or item

transaction state.

And for all 3, we have setting, right?

And transaction_timeout.

Michael: Only since recently though, only since version 7.

Nikolay: We had an episode about transaction_timeout because

it was my idea.

I was super surprised.

I was walking around thinking, why it doesn't exist?

Like, maybe I don't understand Postgres enough after 15 years

of experience.

Then I told it to Andrei Borodin and he implemented.

Great.

In Postgres 17 it exists.

So in all new Postgres we have transaction_timeout so we can

finally limit the whole thing because before we could limit only

statement and idle_in_transaction_session_timeout.

Yes.

And if you have a series of brief statements and brief waiting

periods between statements, you could have hour or day-long transaction

easily.

Now you can limit the whole thing.

Limiting the whole thing is helpful.

Unfortunately, we cannot limit what we actually would like to

limit is xmin horizon.

Maybe we should have, I just, I just came, this idea came to

my mind just now.

Honestly, I need that info.

We could have like probably like xmin horizon maximum, after which

we start abandoning, like we're canceling transaction, abandoning

prepared transactions, like deleting them, and maybe killing

some application.

Yeah, yeah.

Yeah, why not?

Like 100 million protection, ultimate protection mechanism.

And in this case, you don't need to map what's happening with

your like server in terms of seconds and actual problem, like

how many transactions you don't want to lag, right, for your

garbage collection.

Garbage collection is a vacuum, right?

So maybe we should have those settings, maybe too much settings

at the same time, but it will be flexible.

I would say 100 million is my maximum, or 10 million is my maximum,

regardless of cost.

Michael: Even if Postgres shipped
with conservative defaults

there, it would be fine.

Even if it was like 100,000,000 as
a default, it would be fine

for like cases, wouldn't it?

Nikolay: I'm curious, maybe this
idea was discussed, maybe it's

not a good idea.

From my mind, it just came.

It's interesting.

If you have opinion listening to
us, leave this opinion as a

comment under YouTube or somewhere
like on Twitter or LinkedIn,

I don't know.

Anyway, this is interesting.

But what we can do, we can protect
with transaction_timeout,

statement_timeout, idle_in_transaction_timeout.

What else?

We should, we must actually, monitor
lags of replication slots,

especially logical replication,
but physical as well if we use

hot standby feedback.

And we should not allow lagging
too much.

And finally, we should be very
careful with 2PC and abandoned

prepared transactions.

That's it.

So only these 4 areas.

Michael: Yeah, when you say monitoring,
you mean alerting as

well, right?

Nikolay: Yeah, so if some replication
slot is unused, logical

replication slot, or it's lagging
too much, Again, this is interesting.

It will be also either in bytes
or in seconds.

We should think about seconds,
how much, how we need to map it

to transactions, understanding
our seed growth rate.

So there are different metrics
in play here.

So to understand health properly,
it takes time.

And if you have cattle, not pets,
so you should have simple rules.

If some slot is lagging too much,
By the way, slot lagging too

much, it's in bytes, right?

We cannot limit it in seconds of
lag.

There are bytes, seconds, and then
XID growth, XID like

number of transactions.

So 3 metrics, There is a triangle
here.

Interesting.

Anyway, this is to protect from
affecting vacuum.

You can also reactively just monitor
what the vacuum reports

in logs.

And it will start to complain clearly,
right?

So you can set threshold based
on logs and if you have some good

log analysis system, you could
set up alerts from there.

And as for logs, for me, it's very
completely different topic.

Like yeah, long running transactions
contribute to it, but problems

might happen very fast.

Unlike xmin horizon being blocked,
you might not notice it at

all.

Even you, okay, you ran, you blocked
it for many, many hours.

Maybe it was not, maybe it was
needed at just one time.

It won't affect you so much.

The negative effects, they are
postponed and they grow like it's

not a cliff.

It's like it's slow degradation.

Unlike situation with locks, with
locks it might be very fast.

So if you have, as I said, this
classic example is you have the

weakest heavyweight lock possible,
accessory lock on the table.

You block DDL, DDL blocks other
SELECTs, boom.

What to do here?

We need to use proper DDL deployments.

We need a low lock_timeout and
retries.

This is number one thing I would
recommend.

And number two thing is to craft
schema changes properly to avoid

long running exclusive locks, first
of all, but also accessory

locks as well at some point.

They are not harmful unless you
have conflicting sessions and

to detect conflicts sooner is helpful.

Another measure which is actually
a common problem with Postgres,

if we have a vacuum which blocks
us, vacuum always blocks us

when we do DDL.

We cannot acquire access exclusive
lock on the table to change

its schema if vacuum is happening.

But fortunately in most cases vacuum
is running in normal state

and it will kill itself after I
think a second.

Michael: Do you mean autovacuum
or do you mean anti-wraparound?

Autovacuum.

Nikolay: I'm talking about autovacuum, good correction.

But if it's running in transaction
ID wraparound prevention mode,

which happens often if you have
high XID growth, related topics,

right?

If we'll like insert a lot, not
in batches, but single rows,

for example, we consume XID so
much.

Save points can contribute to XID
consumption and XID growth,

XID growth rates a lot.

In this case, we probably have
a problem like if XID growth

is high, autovacuum transaction
ID wraparound happens more

often.

It might happen, for example, every
two days in a very busy systems.

In this case, it happens every
two days, but we aim to have deployments

five times per day.

We are like very rapidly developed
startup.

In this case, this might clash.

Like you have an autovacuum, transaction
ID wraparound prevention, and

you try to deploy DDL.

And you, even if you have retries,
you must have low lock_timeout

and retries.

You try to deploy it, but it runs
on your terabyte size table

and blocks you.

This is super annoying.

You cannot deploy something you
need to deploy right now.

What to do about it?

Question is open.

Michael: Well, you've talked a
lot about partitioning, so the

tables are smaller and the vacuums
are quicker.

But I actually also think, at the
time I thought it was a good

thing, but I thought the threshold
where an anti-wraparound vacuum

kicks in is relatively low in the
grand scheme of things in terms

of the risk.

I would seriously consider, if
it's happening every 2 days, that

seems like a bad smell to me.

That feels like something that
shouldn't be.

Like, let's make sure normal vacuum
happens enough so that it

doesn't have to run in anti-wraparound
mode.

That would be my.

Nikolay: Yes.

I'm not against this.

Like, it's good that it's happening
often.

And freezing is good.

Like, once.

So partitioning is helpful not
only because tables are smaller,

but more pages are all frozen and
we don't touch them anymore

because they are like our archive.

This is super, it makes things
much more efficient compared to

huge unpartitioned table where
anytime page can receive new tuple

and it's not all frozen anymore
in visibility map, right?

Visibility map has 2 bits, all
frozen and all visible.

And boom, we need to take care
of, autovacuum needs to take care

of this page again.

So the problem is, for me, is not
that it's too often happening.

The problem is like this threshold.

I would sometimes, so it's okay
to have it low and make it very

frequent.

Good partitioning is great, but
sometimes I wish it would be

easier to raise this bar temporarily
while I'm doing my changes

and then raise it back.

But as far as I remember, I might
be mistaken here.

Last time I checked this maybe
a couple of years ago, this topic.

It's not so easy.

You cannot change it globally so
easy.

It requires restart, I think.

Maybe I'm mistaken again.

And the table level, there is also
some caveat.

I don't remember which, but I didn't
see a good way to...

Okay, For each deployment, we want...

And also interesting, you know,
like, you kill that vacuum which

runs in transaction ID wrap around,
and if you don't acquire

log on the table fast enough, it will kick in again.

Especially, like, it might kick in if you have many workers and

nap time is low, so it's an interesting problem as well.

So raising this was not easy, as I remember.

So I came up with 2 ideas.

The first idea is if this is deployment, which is big deployment,

we have like major upgrade or something, because it's a problem

during major upgrades.

We had it during major upgrades when not a vacuum running transaction

ID wrap around was a problem for us or some major changes.

If it's a rare change, we probably should forecast when the vacuum

will start freezing.

And do it before.

Michael: I like that.

Nikolay: Yes, yes.

Do it before.

Another thing is that probably we should just sometimes kill

it ourselves.

But if you want to kill it, you need to do it properly.

You need to issue attempt to acquire lock on your table to change

and kill it immediately.

Kill it to vacuum and your attempt to acquire a lock is successful

and then good luck to try to prevent transaction ID wrap around

while we're doing our job.

This is like...

Michael: Wait, is that so it doesn't start again?

Nikolay: Yeah, yeah.

So I...

Yeah, okay.

I won't.

That's it.

So the lock is mine and I'm doing my job.

It's like, it feels slightly dirty, right?

But it's, there's no, there is no harm if you also monitor XID

and MultiXID or up around risks.

If you know, you're only at 10% of capacity, 200 million, it's

fine to go to 300, 400 million during like even big deployment.

We will be fine afterwards, right?

But this feels dirty, honestly, to do this trick.

I did this trick manually a few times in my career because I

needed to finish deployment and I knew that we are not in danger

zone.

So yeah, Yeah,

Michael: that makes loads of sense to me.

My preference, I think, still would be avoid it happening as

often as that and actually trying to make sure things got frozen

before it was needed.

So it really is a fail safe rather than a normal like thing that

happens in normal operation.

But yeah, super interesting.

We started the episode talking about what we should call it and

I think maybe we should end it like with a, what do you think

now, having talked about it?

Nikolay: Oh.

Yeah, but we try to keep titles very short, right?

We didn't have xmin horizon episode.

I tend to think this should be called xmin horizon episode, because

this is where we spend time most and it's less about locks. It's

less like and I think long running transaction is a wrong concept

to chase

Michael: Yeah, let's call it xmin horizon because I think that's

gonna

Nikolay: be, but my concern is that people think in terms of long-running

transactions and...

Michael: That's fine.

I think long-running transactions is interesting and I actually

don't mind that there's this edge case of long-running transactions

that aren't harmful.

Nikolay: Let's keep long-running transactions because it's easier

to understand what we're talking about.

This is what people expect.

Let's keep the name long running transactions.

Explain horizon. This concept is still, it's not fully clear in

my head.

Like I it's clear, but I want it to become official in documentation.

Maybe it should be explained in documentation.

Michael: And then that opens us up so we can still do an episode

on that at some point.

Nikolay: Yeah.

Maybe I missed some parts of it, how to properly monitor.

I know some people who develop observability tooling listen to

our episodes.

Michael: Oh cool.

Nikolay: Yeah, I hope it will be improved.

Michael: Nice.

Nikolay: It's like clarity.

And I thank you for listening to this.

I feel I went quite deep.

We went quite deep in this episode.

Oh, I personally

Michael: enjoyed it.

Yeah, I hope everyone else did too.

Great, it was

Nikolay: great to chat to you as usual.

Michael: Likewise, Nik, take care and see you next time.