Nik and Michael discuss long-running transactions, including when they're harmless, when they cause issues, and how to mitigate those issues. Here are some links to things they mentioned: transaction_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TRANSACTION-TIMEOUT Our episode on transaction_timeout https://postgres.fm/episodes/transaction_timeout Our episode on slow queries (which was also our first ever episode!) https://postgres.fm/episodes/slow-queries-and-slow-transactions Our episode on locks https://postgres.fm/episodes/locks lock_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT Transaction Isolation levels https://www.postgresql.org/docs/current/transaction-iso.html pg_current_xact_id_if_assigned() https://pgpedia.info/p/pg_current_xact_id_if_assigned.html Monitor xmin horizon to prevent XID/MultiXID wraparound and high bloat (how-to guide by Nik) https://postgres.ai/docs/postgres-howtos/performance-optimization/monitoring/how-to-monitor-xmin-horizon idle_replication_slot_timeout https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-IDLE-REPLICATION-SLOT-TIMEOUT PREPARE TRANSACTION https://www.postgresql.org/docs/current/sql-prepare-transaction.html log_autovacuum_min_duration https://www.postgresql.org/docs/devel/runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful statement_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT idle_in_transaction_session_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT lock_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT ~~~ 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.
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.
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...