Nik and Michael discuss hot_standby_feedback - what it controls, whether and when read replicas should be changed to on, and other settings you can change to help too. Here are some links to things they mentioned: hot_standby_feedback https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK hot_standby_feedback: experiments on PostgreSQL 17 (by Nik) https://console.postgres.ai/shared/brief/6018f69d-8975-48e3-8259-a72c77105572 Best practices for Amazon RDS PostgreSQL replication (blog post) https://aws.amazon.com/blogs/database/best-practices-for-amazon-rds-postgresql-replication/ log_autovacuum_min_duration https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION max_standby_streaming_delay https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY WAIT FOR LSN https://www.postgresql.org/docs/19/sql-wait-for.html transaction_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TRANSACTION-TIMEOUT Hot Standby Feedback should default to on in 9.3+ https://www.postgresql.org/message-id/flat/20121130190227.GJ3957%40awork2.anarazel.de ~~~ 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: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork
Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.
Transcript
From the show. Tap a time to play from there.
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.
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...
Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...