Postgres FM

Nik and Michael are joined by Sai Srirampur to discuss ClickHouse's new managed Postgres service.
 
Here are some links to things they mentioned: 

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

~~~

Postgres FM is produced by:

With credit to:
  • Jessie Draws for the elephant artwork

Creators and Guests

Host
Michael Christofides
Founder of pgMustard
Host
Nikolay Samokhvalov
Founder of Postgres AI
Guest
Sai Srirampur
Postgres @ ClickHouse, Co-founder PeerDB, ex Citus Data, Azure Postgres

What is Postgres FM?

A weekly podcast about all things PostgreSQL

Nik: Hello, hello.

This is Postgres.FM.

My name is Nik, PostgresAI.

And as usual with me, Michael pgMustard.

Hi, Michael.

Michael: Hello, Nik.

Nik: I proposed to discuss hot_standby_feedback.

And that's interesting because
I have 4 customers with this problem

right now, different on different
platforms, right?

Yeah.

On Supabase on RDS and something
else.

It's very interesting how problems
sometimes are very silent

until suddenly they come from multiple
directions.

Right.

I don't know why it's happening,
right, but it's just happening.

Michael: All 4, are they all suffering
from the same direction?

Yeah.

Okay, interesting.

Nik: So all of them are complaining
about read replicas lagging.

And it's all because platforms,
most platforms keep hot_standby_feedback

off by default, like Postgres
itself has it off by default.

While my honest opinion, like strong
opinion, I will say, is

that you cannot have read replicas
with off.

For general OLTP case with many
users, And I say this after a

lot of experience.

I built 3 social networks with
each 1 of them achieved many million

users and 1 plus DAU daily active
users.

And we needed to scale reads of
course, right?

So we needed read replicas.

1st 1 I built in 2006.

And also I helped many companies
who also needed to scale reads.

My honest opinion, you cannot have
read replica with hot_standby_feedback

off, just because
it will start lagging.

But this is how it is.

All people need to rediscover it
because defaults are...

This is our favorite topic, right?

Defaults are terrible in many aspects.

Michael: Well, yeah, I'm actually
not sure I fully agree on this

1 yet.

Maybe I will by the end.

Nik: You are not alone.

Michael: Yeah, I'm not sure.

It's a really difficult 1, I think,
this 1 in terms of like risks

versus, yeah, when things bite
you.

But let's, let's, maybe let's go
back to basics.

Like what's the…

Nik: Yeah, yeah, yeah.

So we should discuss, and I just
threw it in the, at the very

beginning to set my goal.

I'm going to discuss it in detail
and explain my way of thinking.

But just separately, I'm just wondering,
are there people who

really scaled reads on really large
web or mobile apps and had

it off because I just don't understand
how it's, how it can be

possible unless you, you are okay.

Your application is okay with lagging
replicas.

Sometimes it's so if you serve
traffic to some users and some

stale information, like lagging
some seconds or maybe minutes

is fine.

I can imagine that you basically
show some news for example or

blog posts or something.

But in general, modern applications
like social media applications,

e-commerce and so on Where you
want to scale reads to asynchronous

replicas, of course we talk about
asynchronous replicas here.

Then we do need hot_standby_feedback
to be on.

Anyway, let's start from the basics.

We had several episodes where we
covered basics already.

I just wanted to have a quick recap.

1st of all, my favorite thing,
if some people don't realize,

there are hidden columns, xmin,
xmax.

xmin is a transaction ID which
gave birth to this tuple.

Tuple is a row version, right?

And there is a concept of xmin horizon,
and we had a separate

episode about it, which defines
global for whole that database.

Global horizon, which says this
is the oldest transaction ID that

defines data snapshot, which is
still needed to some transactions,

some clients.

Right.

And it means, And this is global,
a single value basically.

There are many values coming from
different sources, but horizon

is absolute minimum of all of them.

And it defines the garbage collection
behavior, vacuum behavior,

right?

Because when DELETE or UPDATE or
canceled INSERT happens, dead

tuples are produced, right?

And the job is not done fully.

The remaining of the job is garbage
collection.

Dead tuple must be deleted later.

And if this dead tuple belongs
to a transaction, which is newer

than our xmin horizon, Vacuum cannot
delete it.

And this is simple logic.

It's global.

Some people think it's maybe per
table, but it's not.

It's global.

Michael: Yeah.

And this is all very simple on
a single node.

Just if you're only worried about
having a primary database or

maybe only an HA replica, no, it's
nice and simple, right?

Like we just hold on to the very
versions that might be needed

by the oldest.

Query or whatever the process might
need it and then once that's

finished.

We do the cleanup or vacuum does
it right

Nik: And here we need to dive into
some details.

So yeah, let's 1st think about
only the primary and single node

situation where usually people
start.

You have, there are 5 reasons,
5 sources and our monitoring covers

them.

And we discussed it in the xmin horizon
episode.

5 reasons to block xmin horizon.

Normally if all transactions are
fast, they finish promptly,

no replication is used, neither
logical nor physical, and you

don't use prepared transactions.

In this case, xmin horizon flows
naturally, and you can calculate

age of xmin horizon, meaning that
how many transaction IDs, real

transaction IDs passed since this
horizon and ideally it's very

low.

Say thousands is quite low.

10, 000 is quite low.

1 million it's already noticeable.

Right.

And we know, we all know that 1
billion is already basically

0.5 of capacity we have because
if the whole capacity is 2.1

billion roughly, right?

We have 4-byte integer transaction
IDs, and 0.5 of that space

is our future, 0.5 of it is our
past, and we need freezing vacuum.

1 of its jobs is to freeze tuples
saying that don't look at this

xmin, it looks like from future,
but it's from the past, right?

And if we don't freeze during 2.1,
transaction ID, if our xmin horizon

is that old, age of it is that
old.

We can use function age, actually,
right?

In this case, it means that we're
going to be in big trouble

and database will be not accessible
because transaction ID wraparound

will happen.

This is the biggest danger actually
of xmin horizon being blocked

for a long time.

And In the case of single node
of the primary, everything is

relatively simple, but I noticed
many people don't realize nuances

here.

Remember this year I started saying
that the warning, let's avoid

long running transactions is quite
inaccurate.

Right.

Remember this, we're talking about
this.

And I noticed actually, even AI
is making a lot of mistakes.

For example, with GPT-6 Astra,
I decided to renew graphics in

PGSimCity and also make it
more entertainable and have some

bad scenarios to be reproduced
and so you could see what happens.

And it wrote a very simple thing
and I see it quite often.

It said somebody typed BEGIN and
left transaction open for a

day and we're in trouble.

But it's not simple BEGIN is not
a trouble.

Because by default we have read
committed transaction isolation.

Which means that after each statement
snapshot is free to go.

Basically we don't block xmin for
ever.

So if we think about transaction
at default transaction isolation

level, if it consists of multiple
statements, while each statement

lasts, of course we need that snapshot
and we block xmin horizon.

We need to work with that data.

Some SELECT is running on some
huge table.

Yeah, it lasts for long and we
need that data.

But once this SELECT finishes,
Even if transaction is still open,

that snapshot is not needed.

And basically we allow xmin horizon
to shift.

And Simple BEGIN doesn't hold any
problem, any snapshot at all,

right?

At default isolation level.

So yeah, it's very inaccurate and
that's why saying, oh, long

running transactions are dangerous.

It's too rough.

It's not a precise statement because
not every long running transaction

is.

Yes.

Michael: Some are of course, but
not quite all right.

There are lots of cases where they're
not.

Yeah, sure.

Nik: Right.

Of course, we don't talk about
locks here because there are 2

topics, locks and xmin horizon
blockage, 2 dangers of long-running

transactions.

So we need to distinguish read
committed transactions and transactions

running at higher isolation level,
repeatable read and serializable.

And repeatable read is pretty common
because this is how pg_dump

works.

So every time you run pg_dump, even
if it's not doing, of course

it's doing something always, right,
but it captures snapshot

in the very beginning of transaction,
And even if you dump a

lot of tables, you select from
them, the pg_dump selects from

them, it shifts to the next table.

Snapshot is still the same as it
was defined at the very beginning

of transaction.

It means if you start a transaction
at repeatable read, that's

enough to be harmful.

You just say START TRANSACTION
or BEGIN at repeatable isolation

level.

I always forget syntax.

You don't need even to run any
queries inside it.

It's already harmful.

It holds.

Michael: You say harmful, it starts
to hold the xmin horizon,

right?

It affects, yeah.

Short holds are not expected, right?

The reason it's being held is for
a good reason.

If you're doing a dump, you want
probably everything, not just

consistency, but you want the data
at that point in time, and

if it's changed, then you want,
like you still need those, you

can't have those rows being cleaned
up.

Nik: So you don't need to pay…
IM – This is how consistency is

defined, right?

TG –

Michael: Yeah, yeah.

IM

Nik: – Because if you read from
2 tables from different points

of time, this is inconsistent dump.

So we don't want that.

That's why we need a repeatable read.

Michael: But you say it's harmful.

It just means we're holding on
to a bit of extra data maybe bloating

some indexes a bit like there's
a an amount of that we should

always expect when we're running
Postgres right like it there's

a certain amount of that will always
be natural we try and minimize

it and we try not to let it get
excessive.

But then if that goes on for too
long, it gets into that boundary

where it is maybe excessive or
causing other issues.

Is that what we're trying to say?

So like harmful, yes, but like
it, it scales with the amount

of time or the maybe like time
multiplied by the amount of churn

in that time.

Nik: Yeah.

Yeah.

Yeah.

Database.

I usually say you need like, it's
really hard to understand age

and number of transactions, although
it's more representative

metric in this context.

If you say like long transaction
running like 3 hours during

busy time on like at high traffic
is not the same as during weekend

with low traffic, right?

Because it might be even nothing
happened during 3 hours at weekend.

Sometimes we see some applications
with very acute spikes of

traffic, right?

But, but so it's, there is no like
very clear connection between

time and I, I name it XID consumption
or XID growth rate.

So how fast real transaction ID
grows because this is what defines

this xmin horizon age.

And 1 of the contributors by the
way is savepoints, sub-transactions.

If you use them a lot in 1 transaction,
you consume many real

transaction IDs, because every
savepoint gets allocation of

XID, real XID.

So this is 1 of the dangers I identified,
that savepoints lead

to higher consumption of XIDs.

So yeah, and yeah, if it, of course,
like if we block a vacuum,

it also, we need to be very accurate
with language here because

I realized also when we say vacuum
is blocked, some people think

it's blocked, it like fails.

I even saw, so I have a new tool
to be released soon called PGBS

detector, which is going to, and
we started using it internally

to identify bullshit in our write-ups
and I saw RDS article from

2019 and this was the only article
talking about hot_standby_feedback

from RDS.

Nothing else.

So I tried to understand why they
keep default off and what's

the reasoning behind it.

So I saw that in that article,
this like very confusing statement,

like vacuum is blocked and then
error like canceled statement,

like it was vacuum statement canceled.

No, when we say xmin horizon blockage
leads to affects vacuum

negatively, It's not like vacuum
fails or cannot work at all.

It works partially.

It cleans up the dead tuples which
became dead before xmin horizon.

And it just keeps and reports it
in the logs, tuples which it

cannot clean as dead, already dead
but cannot be cleaned up yet.

I don't remember.

It cannot be removed, cannot be
deleted.

I don't remember exactly.

But this is what's useful to see
because you can see for each

table, a vacuum visited, autovacuum
visited, you can see what

is the damage for this.

You can also run VACUUM VERBOSE
and VERBOSE will report such

tuples which are dead, but cannot
be deleted because it's xmin horizon.

It actually also reports xmin horizon.

It just prints the transaction
ID and what's the age of it, which

is very useful as well.

So that's why autovacuum logging
should be enabled.

And maybe not 0, which means all
occurrences of autovacuum,

but maybe at least above a few
seconds, 10 seconds or 1 second,

if you're concerned about some
very often executions, which are

very fast.

So anyway, autovacuum logs are
quite useful in this area.

What's next?

Michael: I think we've not really
even discussed what hot_stand

by_feedback does.

We've talked about the retention
of dead tuples while like something's

happening, but hot_standby_feedback
comes in because it's something

happening not on the primary, right?

Like it's

Nik: something happening on your
replica.

Yeah, it's conflicts.

So if you think how replication
works, physical replication,

it replays WAL like it was recovery.

It was built on top of recovery
from crashes, right?

And the hot_standby_feedback is
just constantly replaying WAL

like it was a recovery.

That's why a function to check
is it primary or standby is called

pg_is_in_recovery, which is confusing.

Right?

And it replays.

WAL, good.

But what if we have some query
ongoing, selecting from a huge

table, for example, reporting,
right?

It's pretty common.

Let's offload heavy reporting queries
to read replicas.

If it's running there and WAL has
instruction to clean up dead

tuples which on the primary already
dead but we still reading

them.

This is 1 of conflicts and this
is snapshot conflict of replication

conflicts with a query or query
or transaction conflicts with

this WAL data right It cannot be
replayed.

So depending on the type of physical
standby, 1 of 2 settings

are used to decide what to do.

So what to do is obvious.

Replication is paused.

It cannot be replayed.

This is default option.

So we wait until the end of this
transaction.

Michael: And then we continue replaying.

Nik: We wait until xmin horizon
shifts.

If it shifts, we can replay it.

So you can see it manually in pg_stat_
activity backend_xmin

column.

And also it's, yeah, So backend_
xmin is for each backend, you

can see this local horizon for
each backend and minimum of them

is our horizon for this node.

It waits, it waits not forever.

There is a setting I believe it's
called max_standby_streaming_

delay.

Yes,

Michael: there's max_standby_streaming_
delay and max_standby_

archive_delay.

Nik: I will never remember all
GUCs.

Yeah.

So yeah, there are 2, 1 for streaming,
1 for WAL replay.

Replicas which are consuming WALs
from archive, like S3 archive

or something, according to restore_
command.

And, but more common because they
have smaller lags is streaming

replication, which connects to
the primary, unless we use cascade

replication, and just streams the
WALs and applies them.

And this is when we use max_standby_
streaming_delay.

By default, I think it's 30 seconds.

It waits a maximum of 30 seconds.

After this, Postgres says replication
is more important and just

cancels the query.

And the 1st thing that happens,
people don't like queries being

canceled, right?

Or important reports.

Michael: Especially sometimes long
ones, like if it's a long

reporting query, you've already
waited 10 minutes for it.

Nik: Right.

And that's very visible because
reporting usually is close to

decision makers, So they start
complaining very fast.

Like, why report was not delivered?

Okay, we have conflicts.

Canceled.

Okay, let's increase this to 3
minutes, 30 minutes, 3 hours a

day.

And it's okay.

Yeah.

At extreme, you can set minus 1.

And so replication will wait until,

Michael: yeah.

So what happens now?

And this whole time, I think it's
really important to stress,

this whole time anything newer
than that conflict is not getting

replayed on the replica.

So no, any new inserts, any updates,
any deletes, nothing's coming

through

Nik: until that query finishes.

Yeah, it's global.

Horizon was global.

Replication is just single process
actually.

You can see a startup process in
top or ps.

And this is what applies changes.

By the way, changes might be received
already because the replication

has stages.

They can be flushed to local pg_wal
directory, but they just

cannot be applied because somebody
needs very old, all the data,

which we already want to clean
up.

Right.

So what happens next?

Reports work.

For example, we set 3 hours, All
reports last no more than 1

hour.

We are good.

But then, usually what happens,
some users started to complain.

Stale data.

And if your load balancer is not
smart enough, If you have a

smart load balancer, by the way,
you mentioned Postgres 19 has

a wait for LSN feature, right?

We probably should talk about it
separately.

I have a lot to talk about this.

But smart load balancing understands
that some replica is lagging

and stops using it.

Just because we don't want to deliver
stale data to our clients.

But if you have pretty basic load
balancing and some either queries

or transactions went to read replica,
It takes some time because

usually who suffers is not decision
makers like management of

a company wanting reports, but
some users.

And they also usually not every
user is willing to waste energy

to report problems, right?

It takes some time usually, that's
a problem.

Michael: It takes time and also
you have to rule out other issues

1st, right?

Like you have to make sure it's
not on your side.

Like there's just because something
seems like it's not working

doesn't you don't automatically
assume it's a service.

Nik: Yeah And what I'm describing
is a very common situation,

which I went through in 2006 or
7 and just thought it's solved.

But due to defaults, especially
defaults on managed Postgres

platforms, which keep hot_standby_feedback
off, This is what's happening.

So people increase max_standby_
streaming_delay and then they

just say, okay, we have replication
lags.

And they start thinking why, because
it's not obvious why.

It's not really not obvious, because
yeah, it's just lags and

so on.

Michael: But so should we switch,
should we move to then why

we now have hot_standby_feedback
and what it does?

Nik: So hot_standby_feedback reports
xmin horizon observed on

replica to the primary.

So the primary can involve it in
calculation of the final xmin horizon

used by Vacuum, deciding what can
be cleaned safely, what still

cannot be cleaned.

So if you had 1 hour query on the
primary, which led to bloat

sometimes, right, because xmin
horizon again, global, it

was bloat.

And then you offloaded to read
replica without hot_standby_feedback,

you have lags up to 1 hour because
your queries are 1 hour.

And it's terrible lag, right?

Or when you switch to hot_standby_feedback
to on, you have the problem

like the horizon being reported
and you have the similar situation

as it was executed on the primary
itself.

Matthew 14.

Michael: And I think the critical
thing to mention is the chance

of you then getting conflicts are
massively reduced because the

primary won't have cleaned up those
dead tuples and therefore

won't send those conflicts through
to the replica until it's

finished what it was doing.

So it avoids that case we were
just talking about in most cases.

Nik: That's why lag doesn't happen.

So conflict leads to lag.

Conflict leads to 2 things.

1st, lag of replication, and when
it's too much lag, then cancelling

the source of the problem.

Query.

Right?

So, don't block xmin horizon Progress.

So, you are right.

I like that you used reduce number
of conflicts because it doesn't.

Yeah.

For example, we talked about the
conflict when we need to clean

up, but tuples still needed there,
Like this is a snapshot conflict,

but there might be also a conflict.

When we change schema, we need
ACCESS EXCLUSIVE lock.

And the mistake is to keep this
ACCESS EXCLUSIVE lock for long.

And on the primary, it leads to
many problems.

We know like some selects even
will wait.

It's ACCESS EXCLUSIVE lock, blocks
even selects.

Schema change.

So if you open transaction, edit
a column very briefly, and then

start to read something from somewhere.

You keep the lock until very end
of transactions.

We discussed many times lock cannot
be released midway.

It's released only at the end.

If you do something else, this
lock is held and nobody can work

with that table.

But on the replica also, if you
keep this lock on the primary.

WAL comes saying lock and again,
lag will happen.

And hot_standby_feedback doesn't
solve this at all.

This should be clear.

So yeah, you're very good warning.

I liked it.

It's not solving fully.

Even more, I learned it only recently.

So if you usually use standard
replicas, these days I used streaming

replication plus replication slots.

Replication slots were created
later than streaming replication.

It's like additional level.

And you can use streaming replication
without slots.

Usually people think replication
slots are protection from WALs

being deleted from the primary.

The replica lags too much, it cannot
converge anymore, right?

It's solved usually if you have
good backups in S3, object storage,

you can configure restore_command
on the replica and it will

just take WALs from there, no
problem.

So slots can be like mitigated.

Also slots are convenient for observability.

But what I also learned, imagine
we have streaming replication

without slot.

Slots have xmin horizon reporting
right in pg_replication_slots,

you can see it, xmin, column.

But if you don't have slot, streaming
replication, It works all

good.

But then some network issue, replica
disconnected.

On the primary, for example, vacuum
deleted tuples, reconnection

happened, vacuum already deleted
tuples and WAL is delivered,

tuples are deleted.

And despite hot_standby_feedback
being on, we have situation like

it was off.

This was an interesting finding
for me last week when I dived

into this topic deeper, using our
new tool for experiments, reproduced

a lot of failure scenarios, including
this 1.

So it's interesting.

It means that slots are useful
in many different cases also,

like in sensors, right?

So yeah, hot_standby_feedback
on drastically reduce the chance

that you will have lag on standby.

And this is what we want for the
replicas because we want them

to be up to date as much as possible.

Michael: But it also does 1 other
thing that I think is quite

important.

It should also drastically reduce
the number of cancellations

you get if you have these long
running queries.

Let's say longer than 30 seconds
or whatever you set that timing

to, it should reduce those getting
cancelled because the conflicts

aren't going through.

Because you're not getting conflicts
in the

Nik: 1st place, right?

Right.

Cancellation is a result of delay,
which is a result of conflict.

So conflict, delay or replication
lag, and then cancellation

when it's too high.

Value of lag is too high.

So that's why I think read replicas
should have hot_standby_feedback

on and reasoning that it will be
worse because they will lead

to bloat.

This is just how Postgres works.

It was usually people start with
all queries going to primary,

then they offload queries to replica.

It's just a mistake to expect that
those queries won't affect

vacuum behavior.

That's it.

Of course, you can live with off.

Actually, 2 points here.

1st point is quite interesting.

So we want to minimize damage of
long-running transactions which

block xmin horizon progress, and
how can we do it?

We can set transaction_timeout,
both on primary and all read

replicas.

For example, if we set it to 3
hours, that's it.

No more 3 hours of damage.

But as we discussed, damage is
relative.

3 hours, we don't know how many
transactions happened, right?

I think actually it would make
sense, it might make sense to

have all timeouts.

We have transaction_timeout, we
have statement_timeout, and

idle_in_transaction_session_timeout.

transaction_timeout limits whole
transaction, which is good.

But I think it might make sense
either to have all these 3 measured

in not in milliseconds or seconds,
but in a transaction count.

Imagine transaction_timeout, but
count of transactions, right?

Or adjust hot_standby_feedback so
it would be not like on and

off, but some threshold after which
we cancel.

So there is definitely room for
improvement here.

And if you look, my AI told me
other database systems ship settings

for replicas.

Postgres ships 2 extremes, on and
off.

But with transaction_timeout, at
least measured in seconds, right?

It's indirect, but it's already
good enough in many cases, which

was implemented with Postgres 17.

So on old Postgres it's not available.

But it's good enough.

My recommendation, on and 3 hours
transaction_timeout.

So nobody could leave transaction
open which blocks xmin horizon

progress for a very long time.

But you need to do it on the primary
as well, right?

Because who knows what happens
there.

It's not no difference here.

Michael: I think the big difference
is people not offloading

just like OLTP traffic to a replica.

I think it's people think that
I've got a replica, I can send

my reporting or analytics queries
there or I can give a data

team access to that and it doesn't
matter, it protects the primary.

I think the key learning from this
setting is That's not true.

You have to be, it can still affect
the primary and therefore

you have to factor that in on almost
like an architectural level.

Should you even be running those
reporting queries there if it

on an OLTP, like an important OLTP
system or should you not do

that?

I think it raises those important
questions.

Nik: Yeah.

For reporting replicas, maybe you
should keep it off, but the

consequences are very often replication
lags.

And I even say that it makes like
basically this replication

replica, like single user, like
you take around very long query,

all other users like suffer and
cannot use it anymore.

Like they say, oh, it's very old
data.

We need fresh data.

So like you become very like expensive
user, but maybe it's fine

in some cases, like maybe it's
better to do that and then catch

up.

I don't know, but it's quite expensive.

It's quite expensive to have a
whole node for reports, which,

and this node is lagging and catches
up.

I don't know.

Michael: Like it's, but some, some
people do like shipping it

to like ClickHouse.

We did whole episodes on this,
didn't we?

And I think there's some interesting
alternatives.

Nik: But this is not really a replica,
it's an analytical replica,

right?

Or if you have different storage,
there are some, not Postgres,

but some alternative to Postgres,
which stores data differently

and executes queries much faster.

In this case, maybe hot_standby_feedback
on is still good because

negative effect to vacuum will
be low, right?

I can imagine some cases where
off is good.

For example, if it's a delayed
replica.

Some people keep 8 hours, 12 hours
delayed replica, which replays

WAL with delay 10, 12 hours to
be able to very quickly restore

point-in-time recovery, to perform
point-in-time recovery.

I think this recipe is quite outdated
because now we have snapshots

and if even with lazy load, it's
quite fast to provision multi-terabyte

databases from cloud snapshots.

But if you use like delayed replica,
of course you don't want

hot_standby_feedback.

I actually think it's not possible
to use it there because hot_stand

by_feedback works only with streaming
replication, right?

So with archive replicas which
work using restore_command, replaying

WALs from archive or from some place.

hot_standby_feedback cannot be applied
there.

And you cannot see them in replication
slots, so these replicas

are invisible.

And this is how delayed replicas
should work.

And in this case, you'll be dealing
with the same mechanics,

but defined by max_standby, not
streaming, but what the other

alternative?

Shipping.

Archive delay, max_standby_archive_
delay.

Right.

So I think this is, this covers
quite well this topic.

I think Postgres has opportunity
to, to improve things drastically,

like for better control.

I wish like I had a capability
to define the damage, not in seconds,

but precise, like maximum, like
100000 transaction IDs, yeah.

And if it happens, if somebody
exceeds it, I want to cancel that

1.

And for reporting, the most standard
approach for large databases,

I agree with you, it remains another
database system and you

need to create some pipeline, like
a logical replication pipeline

to ship data to ClickHouse or anything
else like Snowflake.

Michael: Yeah.

Yeah.

We could monitor for that.

Like you can do that.

You can build that yourself, right?

If you monitor for things holding
back the xmin horizon and you

can check the age in like transaction
IDs.

So that is that's already possible
today right but we just have

to do it ourselves rather than
a configuration parameter.

Nik: Yeah so yeah you are talking
about some automation which

will be outside Postgres but it
will demonstrate all 5 reasons

for xmin horizon being blocked,
identify them and then cancel

them.

Then 5.

Michael: Yeah.

Nik: Yeah.

You like you're talking about self-driving
ideas, but it shouldn't

be in Postgres in my opinion.

This is, this is the wrong example.

This logic is too basic, too fundamental
to be, it's possible

to implement outside.

Definitely.

I remember actually many times
I implemented like something in

pg_cron or regular cron, like
canceling long running transactions,

which are like offensive.

Looking at backend_xmin
and so on.

Yeah, we did it actually.

And this is like duct taping, right?

Michael: Yeah, sure.

Nik: This is not, but I'm talking
like, I just feel, I feel the

need in Postgres itself could be
improved, maybe some listener

should propose some patches.

Michael: I think it would be tough
to, I think this is a really

tough 1 because it's a genuine
trade off.

I don't feel like there's a right
solution and it's going to

depend on what you're using your
replica for and maybe there's

like a case that 95% of people
want and like therefore we should

just change it.

But it feels to me like some people
do prefer the trade-off of

having it on and some people prefer
the trade-off of having it

off.

And it's maybe tricky, gonna be
hard to find something everybody's

actually happy with.

Nik: Yeah, actually, I think for,
yeah, this is, yeah, I think

to leave the choice on the user
who provisions read replica,

it is also a good idea.

By the way, I've read, I saw many
hackers proposed to switch

default in Postgres itself to on.

There was such opinion, very strong
1, multiple, very well-known

hackers proposed it.

But then it was a discussion, it
was opposition to it.

And by the way, at that time,
transaction_timeout didn't exist.

So maybe now it should be reconsidered.

Yeah.

It was before Postgres 17.

And the reason I remember, which
like hits in my mind brightly,

that it was named that problems
with lags and conflicts canceled,

canceled like lags and canceled
queries on replica.

Yes.

Very obvious to you.

And it's better to discover what's
happening, understand and

then make decision based on your
situation and experience rather

than you don't notice or bloat.

Bloat on

Michael: the primary building up
slowly.

Nik: But at the same time, the
same problem exists on the primary

itself.

Absolutely the same, right?

Michael: And I think you can make
the opposite argument.

Bloat is quieter.

The reason you don't notice bloat
is because maybe it's not as

bad.

Nik: Yeah, we should control bloat
anyway.

We should control bloat anyway,
right?

And on the primary it happens exactly
in the same mechanism,

so why should we make different
approach, apply different approach

to replica.

And bloat control is a whole topic
anyway, right?

If you want to improve that experience,
it should be done on

the primary 1st.

Michael: And if we care about that
from a Postgres tuning perspective,

we should probably be changing
the defaults of the various auto

vacuum settings 1st.

Nik: You know what, I like how
I'm pulling you into discussion

and hackers.

You should think and write your
opinion next time.

Michael: Yeah, I'll try and get
braver.

Nik: Yeah, it's a rabbit hole,
I know.

Yeah.

Michael: Anyway, this was great
and helpful.

And I think I agree with you that
it feels like the vast majority

of OLTP replicas where you're just
offloading a huge number of

really quick queries, hot_stand
by_feedback On makes so much more

sense to me than Off.

But I can't agree that like all
read replicas should have it

off.

Like I do think there are these
people using it, probably still

it's a good architectural choice
as these analytics replicas

and I think you're right that it
makes sense to keep those off

and it's nice that you can do it
on a per replica basis like

it doesn't have to be the same
for all of your replicas which

is cool.

Yeah.

Good.

Awesome.

Anything else?

Nik: No.

Just don't let your replica to
lag too often.

Michael: Nice 1, Nik.

Thanks so much.