Nik and Michael discuss max_connections, especially in the context of increasing it to solve problems like migrations intermittently failing(!) Here are some links to things they mentioned: max_connections https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS Tweet about deployments vs connections issue https://x.com/brankopetric00/status/1991394329886077090 Nik tweet in response https://x.com/samokhvalov/status/1991465573684027443 Analyzing the Limits of Connection Scalability in Postgres (blog post by Andres Freund) https://www.citusdata.com/blog/2020/10/08/analyzing-connection-scalability/ Exponential Backoff And Jitter (blog post by Marc Brooker) https://aws.amazon.com/blogs/architecture/exponential-backoff-and-jitter/ ~~~ 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 PostgresFM. I don't remember the number of episodes, Michael, do you remember? Michael: No, we must be getting close to 170 though. Nikolay: Yeah, some really big number. Not as big as your max_connections, right? Michael: I like it, good segue. Nikolay: Yeah, my name is Nik, PostgresAI, and as usual here is Michael, pgMustard. Hi Michael. Michael: Hello Nik. Nikolay: So we are going to discuss really high max_connections
in general and maybe we will touch a little bit connection pooling systems a little bit, and maybe we will touch per user connections, max_connections setting because it's possible. Yeah. So in general, like is it bad idea to raise max_connections And is it a good mitigation action when you have problems? This kind of action. What's the ideal max_connections? Michael: And why do you, I think you've seen some cases recently
where people are raising it and you think it's not a good idea. So is it worth kind of jumping straight into some recent experiences? Like why is this on your mind at the moment? Nikolay: Yeah, so I proposed this topic after a tweet I saw where It was shared that there was a pattern when deployments, I guess, schema migrations or DDL were failing, but only in particular hours of every working day, or maybe every day overall. And then they realized that at the same time, during a couple
of hours, some data pipelines were running. And they just raised max_connections and that's it. Actually, I'm not sure max_connections because it doesn't say which database system it is. I just assumed it's Postgres because Postgres is default choice right now. I might be wrong. Maybe it's different databases. Michael: I took a note and it said the solution was, 1, separate connection pool for migrations, and 2, better cross-team communication.
Which I thought was interesting. But yeah, maybe separate connection pool for migrations is where you're drawing that conclusion from. Nikolay: Well, this doesn't sound as a mitigation for me. It sounds like isolation of problem. Yeah, well, I might be wrong. So again, like there are several possible options here, but I bet what happened, These pipelines produced long running transactions, right? And these long running transactions blocked some DDLs, ALTER TABLE,
add column, for example. And this DDL blocked any queries to this table. And a number of active sessions spiked and achieved, reached max_connections. If you start isolating, maybe some, actually if you start isolating, everyone will suffer anyway, right? So this idea that somehow isolation helped somehow, like if this theory is right, then separate connection pooler for migration
and pipelines doesn't help at all. Because locks are global, right? So you can, you don't lock a table only in the context of some connection pool, you lock it globally in this database, right, in this logical database. Michael: Yeah, so if it really did mitigate the issue, either it was the other part of their solution, which is the better cross-team communication, or the theory's wrong, and actually it was a case of these migrations taking up a few slots, a few connections
that weren't, that the application expected to be able Nikolay: to use. Migration needs only 1 connection, right? Michael: Yeah, true. Nikolay: So how could pipelines, data pipelines take a few connections? Yeah. I don't understand how they can exhaust the reach max_connections at all. Michael: Well, should we talk about the general case a bit more then? Nikolay: Yeah, so again, like this general case is described
and we discussed it a few times. Everyone should understand that any ALTER statement, it requires a lock, exclusive lock on the table. Even, well, there are exclusions, for example. No exclusions, actually. ALTER TABLE is a serious thing. Even if it's super fast, you still need a lock. And if you, with default settings, fail, like if your session fails to acquire this lock, it will start waiting. It will wait forever with default settings.
Default settings means a log timeout 0, statement timeout 0, transaction timeout 0, idle transaction session timeout 0. Everything 0 means forever, infinite, right? Waiting infinitely. So this means it will be waiting, waiting, waiting, but any query, including any SELECT to this table, will need to wait behind us in the line. So there's a queue forming there, waiting queue, lock queue, blocking queue.
And actually, you might see multiple queues, and it forms trees, and actually not just trees, it's forests, so it's beautiful. Like, it can look really great, like forest of blocking or locking trees, but this is eventually what accumulates a lot of active sessions, can overwhelm your server basically. Even if it's not achieving max_connections, it can reach to high CPU consumption of course and so on. But it's very common to see that max_connections is reached because
too many active sessions. And most of them are just waiting. So when you reach max_connections, any additional attempt to connect will be, we'll see an error, right? Too many connections. Yeah. Too many clients or how's it? I don't remember. Too many clients I think. Michael: Yeah. I can't remember either. I remember back in the day when a lot of people used Heroku Postgres.
This used to come up all the time because they were much stricter on how many connections they would let you have. And these days, more people on things like RDS who have much much higher default max_connections than Heroku ever has. I see it much much less that people are hitting this. I think probably causing other issues, I'm sure we'll get to that, but it's interesting that that used to come up more in various online forums? Nikolay: Yeah, so in the past, so we need to distinguish the
states of sessions or connections. Connections and sessions or backends all are synonyms in this context of Postgres. So we need to distinguish active, idle, and idle in transaction, 3 main states. And you can see states and selecting it from pg_stat_activity. So before Postgres, I think 12, 13, 14, so Michael: 5 years ago. Yeah, is this the Andres Freund work?
Nikolay: Right, right. Yeah. A couple of articles published originally in Citus blog, then in Microsoft blog, Microsoft Azure Citus there, right? So a couple of good articles, excellent articles. First researching and then discussing optimization. So usual fear was that idle connections consume memory, but articles prove that the main problem is that how snapshot, work with snapshots
is organized and idle connections, for example, if you just add 1000 idle connections, it can hurt your performance. And I demonstrated it with benchmarks easily as well. So it was quite easy to see but after, so if you have I think 14 plus, maybe 13 plus, If you have this Postgres, basically everyone right now. Michael: Oh true, all supported Postgres versions now, that's incredible. Nikolay: Because 13, we have a couple of cases where 13 is still
in production, and then talks to upgrade it, but basically right now if you are on supported version, so idle connections don't hurt performance anymore. Too much. Michael: Or anywhere near as much. Nikolay: Yeah, exactly. Yeah. So having 1000-2000 additional connections, It's not a big deal these days. And also processors improved. I really like Graviton4. It's like, it's amazing. And it handles a lot of connections, even active very well.
Much like I have my neural network trained what to expect from like 500 connections, for example on a machine with fewer than hundred vCPUs, right? I can imagine what should happen, but on new hardware, I see it much better. It handles more active sessions much better. Michael: When you say your neural network, do you mean your brain? Nikolay: Yeah, yeah, yeah, my LLM internally, yeah.
So I just, well I expected this, but wow guys, you are fine actually, You are not down. I would expect you to be down by this point, right? So we just see it in our monitoring what people experience, what their cluster experiences. And like, wow, this is when they check what hardware it is, oh it's actually Graviton4 on Amazon which is great. I'm impressed. And Postgres. And Michael: newer Postgres. Yeah, okay. But you're talking about lots and lots of active sessions rather
than idle. Nikolay: Additionally, on larger machines, RDS and Aurora RDS, they just set really high max_connections. I think this decision was made. I'm really curious how this decision was made. And It's like we see 2500, 5000... It's very common. I can already, like looking at settings, I can tell you, oh, this is not seeing the settings which start with RDS dot.
Just looking at this profile of settings, I already realize, oh, this is RDS, or this is like CloudSQL. Like there are patterns of some decisions. You know, random page cost 4 and max_connections 5000. Oh, very likely it's RDS. Michael: RDS, yeah. That's funny. Nikolay: So this gives us opportunity to observe how clusters behave with high max_connections. And old arguments don't work anymore. So old arguments were like, guys, this is bad.
You just have 2000 connections, 1900 of which are idle. Why do you do this? Because you just basically pay big tax over performance-wise. Because all the latencies, it hurts all the latencies, but now it's not so. And the only argument left in my mind is that still if number of active sessions exceeds the number of vCPUs significantly,
then it hurts performance globally and all queries are processed for much longer time, latencies increase, and none of them have chances to be finished in time, in statement timeout, which for example 30 seconds. So instead it would be better if we reach max_connections sooner and just say some clients retry later, like too many clients, but these guys would have much higher chances to complete execution
inside statement timeout. So we have limited resources, CPU and disk I/O. Yeah, to make Michael: sure I understand, are we talking about if you allow too many connections, even if most of them are going to be idle most of the time, you run the risk that a bunch of them will become active at the same time. Yeah, OK. Nikolay: Exactly. So if some wave of requests, unexpected or expected, but poorly planned, happens, if we have low max_connections, we will have
a limited number of backends which will do a job inside the statement timeout, if it's limited. If the statement timeout is limited, it's a different story. It's also a very wrong state for OLTP. We also discussed it many times. But if we have 10, 20, 50x compared to the number of vCPUs, we actually have max_connections and this wave comes. All of them try to execute, but none of them will succeed because they will all bump into the statement timeout. Or if the statement timeout is not set, they will bump into some
different timeout on HTTP level, application level, or something like 1 minute or something. They will still, like nobody, like we took all the chances from everyone. That's a problem. If we leave it, some of them will succeed. Some of them will need to retry because too many clients and we will need to fix the problem. And of course, if there is connection pooler, it's great because connection pooler will take a lot of headache from Postgres backends.
Michael: Yeah well I wanted to ask you about this actually because I think sometimes when 1 of us says connection pool I think people, or people can assume either direction right, They can assume database side or application side. And a lot of the time I see people scaling their app and having application side pooling. And That's how I see, I think, that's how a lot of the Heroku issues happened. Nikolay: A lot Michael: of the people running out of connection on Heroku because it's so easy to spin up new versions of your app and scale the
app horizontally. But each 1 comes with another 10 connections. Another 10 connections. They're using poolers, but it's application side, so there's no, they don't use anything on the database. Nikolay: Those poolers are too far from the database. And exactly this is this pattern I saw so many times. Especially, imagine e-commerce. And they have microservices, many clusters, some clusters, we see it. Okay, max_connections is high because they don't use PgBouncer.
They say, okay, we have Java application and all the connection pooling is on our side. HikariCP for example, right? There's such connection pooling. And all great, I cannot convince them to start using PgBouncer. Not bad, okay, max_connections we keep somehow high because we have a lot of idle connections. Why? Because there are many application nodes. And then before Black Friday, which happened last week, this story was many, many years ago. Before Black Friday, guys, infrastructure guys who are responsible
for application nodes decided to double capacity. This is in their hands, their decision, okay, we need to be better prepared, let's just double number of application nodes from 100 to 200, for example, or something. And nobody thought about the configuration of those poolers, So they were like 40, right? 40 connections. So it was, for example, I don't know, like 20 nodes times 40, it's 800, for example, right?
Maximum number of connections, 800. But they doubled it, it became 1, 600. I think numbers were higher in that case. And nobody talked to database team, like, it's just application nodes, let's have, they're stateless, let's add them. And this is how you suddenly see, oh, many more idle connections. The actual load didn't increase. We don't need, like, a number of active sessions didn't increase. But since capacity was doubled, the number of idle connections doubled, obviously. And you cannot solve this problem having connection pooler on
application side at all? Well, you can, but you need to somehow memorize every time you add or remove application node, you need to rethink pool configuration for all of them, to maintain the same overall number. Some of connections should be maintained, right? Michael: So you mean if we double the number of pools, we can halve how many connections each pool has? Yes. Yeah, but then it seems brittle to me to then need to remember that. Nikolay: Yeah, there are 2 numbers.
There is like minimum and maximum number. I'm talking mostly about like minimum number. These connections are open and they're maintained. It doesn't go down below that, right? So if we doubled capacity, but the actual workload didn't increase because it's not Black Friday yet, why do we open more connections to Postgres? It's not right. And a good solution here is to have an additional pooler closer to the database, where it will be configured according to database
situation, not situation with application nodes and the needs to increase the fleet. Yeah, so connection pooler helps and usually we say like, okay, if you have PgBouncer, each PgBouncer can handle 10,000 connections incoming, something like this, thousands of them. And it should open only 100 connections to the database. This depends on the number of VCPUs. We still say take number of VCPUs times some constant.
Usually it was 2, 3, 4. Now we say 10. Because we see Postgres behavior improved, hardware improved. Okay, 10. But not more. If you have only 96 connections, OK, 1000. If you have 32 VCPs, I mean, number of max_connections should be, OK, 320. It should not be 5000. I'm very curious how RDS team made the decision. Because I guess they...
I have several theories here. 1 theory is there's so many clients without pooling and this was just a need. And I'm like, okay, let's do it. Michael: It's just moving the problem, isn't it? It's instead of people, instead of people thinking, oh, what, I don't have enough connections, they're going to hit Nikolay: performance issues. Yeah, maybe they have some additional reasoning, which I don't get. I'm very curious. And another thing, maybe RDS proxy was not so good, and still not so good. We saw it in action, RDS proxy, it's a very weird proxy compared
to PgBouncer behavior. You see idle connections more. I don't remember all the details, but every time we touch it, I always say, like, why is it behaving so? So what is this? It's very strange. It's very different. There's no, like, very good multiplexing capabilities like in PgBouncer. You have 10,000 connections incoming, maximum, and you transform them to maximum 100 backends. PgBouncer is very reliable and stable to solve this task.
RDS proxy, not so much. So maybe they had issues with RDS proxy and they needed to raise max_connections because it was not solving this task somehow. Michael: Interesting theory. If anybody knows more, let Nikolay: us know. Yeah, I'm very curious. So this is my approach right now to max_connections as maximum 10x for vCPUs, ideally lower so database can breathe under heavy load. Michael: And lowers the risk, I think, is that yeah. Nikolay: Yeah, So just to be honest with you, how many resources
do you have? Do you have 1000 cores? Maybe yes. In this case, no problem. Go for it. Because it sits all the time, like no problem, no problem but then we have problem we have problem and we have huge spike and unresponsive database right system I mean database system Michael: so it's picking which like what do you want your failure case to be right do you want your failure case to be some people can't access it or Nikolay: Do you want your failure case to Michael: be some people can't access it or do you want your failure case to be nobody can do anything?
Nikolay: Yeah and also if some attempt to connect and execute a query from backend failed, backend should have a retry logic. Michael: Yes, it Nikolay: should be. Connections and query execution, both. So it should be handled there properly additionally if users immediately see there's too many clients, Okay, maybe you should additionally adjust your application code. So, to have an ideal state is retrial logic with exponential
backoff and jitter. So, this is like a more scientific approach. There is a good article, an Amazon article about this. It's not rocket science, but it's good. There is some science behind it. Exponential backoff and jitter. So it can happen quite fast, and this adds some resiliency to this system a little bit. Of course, if you have 500 max_connections and all the time all of them are busy, yeah, that will be, the users will notice this,
right? But still those who are still working, they will have more chances to complete. And this is, I think, super important. Then sit with welcoming performance cliff, basically. Yeah. But another thing which I'm curious in is how to demonstrate the problem. Naive attempts failed so far, so I don't have a good article or demonstration how this performance cliff behaves. Name it performance cliff may be wrong as well.
But I feel it's somehow like, not maybe cliff, But somehow too high max_connections increases risks to be down for everyone. And how to demonstrate it, I'm not sure. So these considerations are more theoretical. So we have many clients who listen to us, agreed. But max_connections, to change it, you need a restart. So they still see it with 5000. That's it. At the same time, I'm not super worried.
Well, I still believe we need to go down with max_connections and plan a restart and do it. So unfortunately, some companies are afraid of restarts and try to minimize it. It's another question, right? How to stop fearing restarts and do minor upgrades more often and so on. But in our effect, OK, if you have 2 max_connections, it's also good because in our monitoring, we put active session history analysis, weight event analysis in the center right now.
And it's quite great to see very colorful, beautiful graphs if we have a lot of max_connections a lot of areas with different colors it's so good it was a joke right so okay I mean if you if you have max_connections high your graphs looking better you know Michael: oh I see what you mean yeah Nikolay: this probably is not responsive right but graphs look really great Michael: okay well I'm interested in the pool like I do think we do need to justify like why should you have an extra, an extra
thing running, like it's more complexity for Pete to justify, right? It's another layer if we're saying you should use PgBouncer. And added latency, like maybe not much, but like there's a bit of extra, right? There's 1 extra hop. So I do think it is sensible to like have to demonstrate what are, like what is the advantage. Nikolay: Yeah, yeah, well, It's quite easy to demonstrate the
benefit of having a PgBouncer in between. There are certain types of workload where it will be very noticeable that the PgBouncer improves. And also if you have slow clients, it improves things, because backends are not working on transferring data and like it's offloaded to a PgBouncer. Michael: Yeah, okay, that's a good 1. Nikolay: Yeah, yeah. But also if you take a lot of fast selects and with PgBouncer,
it will be much better than without PgBouncer. Because, for example, you achieve 1 million TPS with select only pgbench, right? And PgBouncer helps Postgres to communicate and start working on next execution. So you can feel it and you can raise number of clients easier and scale better. This is a simple benchmark, actually.
Usually, in this stress load testing, we start with 1 client, 2 clients, 4 clients, and so on, a number of parameters, hyphens c and hyphen j, lowercase, in pgbench, we match them because there is interesting logic not everyone understands from beginning how these parameters are connected to each other. And then we just grow. But usually when we grow to the number of vCPUs, we go beyond
that and TPS numbers go down, latency goes up. Because, and this is ideal state for direct connection benchmark. We just scale until the peak, which matches number of VCPUs. If peak starts earlier, there is some interesting bottleneck, I bet. Some lightweight lock or something. And then if you introduce PgBouncer, this peak should shift to the right and become higher.
Yeah, that's how you can see it. Michael: Well, is that an easier way to sell it then? Instead of like trying to convince people to use it because it reduces the risk around exhaustion of resources. Actually just try and sell it on the increased performance. There's no Nikolay: problem to sell PgBouncer. Everyone wants it. Almost everyone. Michael: Well, I don't, I'm interested. Nikolay: Yeah, Yeah. Yeah. Well, I talked about that company. There I couldn't sell it. These days I see everyone understands quite easier that connection
pooling is needed. And also keep in mind that I just said a lot of very fast SELECTs. If you have long running statements, quite long, for example, 10 milliseconds or 100 milliseconds, it's quite long. It means if 100 milliseconds, it means execution 100 milliseconds, including planning time. It means that 1 backend can do only 10 QPS, TPS. It's not a lot at all. And that's it.
And communication overhead, which PgBouncer will take on its shoulders, will not help a lot in this case. It will help only if you have tons of fast SELECTs. In this case, this overhead will be comparable to execution time, right? Or maybe even higher somehow. Some millisecond SELECTs, this is what we need. And guys can tell you, you know what? We don't have so many. Depending on application, but sometimes we see average Query latency is exceeding 1 millisecond significantly.
In this case, PgBouncer will have, but not so much. Well, again, depending on the client, sometimes clients are slow, sometimes they're under control and also slow. But anyway, I just, from my experience lately, dealing with a lot of startups, including AI startups, which grow really fast. Everyone understands connection pooling is needed. Usually it's a trade-off, like if it's RDS, what to do, because RDS proxy is managed, no-brainer, but they lack good behavior,
as I said, and also they lack pause/resume, which is weird because there's blue-green deployments, but RDS proxy doesn't have pause/resume. It means that there is no 0 downtime possibility here to maintain connections, right? And PgBouncer has it, but you need to deal with it yourself. And if you go to global database in Aurora, it's like it's, it's, it sticks to, it sticks you to RDS proxy more.
Michael: So what's their blue-green deployments doing behind the scenes if it's not? Nikolay: I don't know about blue-green deployments. They do logical replication and everything. This is it. Yeah. But this switch if it's for RDS proxy, RDS proxy doesn't have pause/resume, I know it, right? Michael: Yeah, interesting. Nikolay: Well, maybe, I haven't checked a couple of months, maybe I have outdated, but I didn't see the news about pause/resume. I think the switch is still, like, interrupting current execution,
so it's near 0 downtime, not fully 0 downtime. Michael: So when you were talking, going back a bit, I'm still a bit confused, you were talking about folks not being able to demonstrate a kind of a theoretical limit of what's the added risk, why would you need to demonstrate it? Like if you're seeing everybody using a database side pool like PgBouncer already, who's it for that demonstration? Nikolay: So let's distinguish 2 things here.
First is how to demonstrate the need in connection pooling. This is a slightly different topic than reconfiguration of max_connections. Michael: Yeah, but very related, no? Nikolay: Related, but I find it easier to convince that connection pooling is needed compared to let's reduce max_connections. Michael: Okay, so people are like, we'll have a pooler, but we'll also still have 10,000 max_connections.
Interesting. Nikolay: Well, it might be just because it's painful to restart for them. That's it, honestly. Michael: Yeah. Yeah. And if most things are going through the pooler and the pooler has a lower limit, then actually they might not get near their max_connections. It's just a number, not actually how many connections they have. Nikolay: Yeah, I'm checking blue/green deployments once again, and I don't see pause/resume, and I think switchover allows connections in both environments, allows write operations on 1 cluster and
only read only on another cluster. And then you basically, You need to take care of everything yourself. This is undercooked, I think, situation. They should have pause/resume in RDS proxy or just ship PgBouncer in a managed form as some other managed platforms do, right? Yeah, true. That's it. In this case, it would be possible to achieve fully 0 downtime. And we know like we our standard criticism of blue green deployments,
that is that it's not blue green deployments because it's you lose 1 environment after switchover, which is super strange. So it's not reversible. Michael: It's not reversible, yeah. I don't think you lose it, but it's not reversible, yeah. Nikolay: Yeah, so again, I don't find problems convincing about the need of pooler, but how to, and again, it's easy to demonstrate that pooler brings benefits to certain types of workload significantly,
but how to demonstrate, and By the way, we could emulate slow clients. In this case it will be even more interesting to see how with PgBouncer Postgres behaves much better, higher TPS, healthier state, lower active sessions and so on. But when we move to the topic, OK, max_connections, forget about pooler. How can I see that lower max_connections is somehow beneficial?
Here I have a hard time right now. If anyone has ideas let's work together and create some benchmarks, demonstration tests, experiments. Michael: So maybe coming full circle, the things that triggered this for you was the discussion of it in the context of migrations and I mean specifically like schema changes and data pipeline like long maybe long running transactions maybe things that take heavy locks.
Nikolay: So long running transactions because if you acquire a lock it's released in the end so it's a long running transaction. Michael: Yeah, I was thinking on the read-only ones, at least there's, I mean there are still massive implications, but it's not as devastating unless they're combined, right, unless you have both. Nikolay: Read-only transactions also acquire locks. Michael: Yes, yeah, of course. But they're fine until they block Nikolay: a... Modification. Exclusive lock coming from ALTER TABLE cannot be acquired because
of ongoing access share lock from some ongoing SELECT, which probably isn't finished, but there is data pipelines, they love complex long transactions and some brief SELECT, which you executed in the beginning of transaction holds this access share lock till the very end of transaction. And it blocks ALTER TABLE. It's just ALTER TABLE should have low lock_timeout and re-tries. Michael: Yes, so that's the...
Nikolay: That's the approach. This is good mitigation to... Not to properly schedule something, because if not, the data pipeline can be something else. At any time, Postgres can start aggressive autovacuum process, which will be freezing your tuples to prevent transaction ID wraparound, and this will block your DDL. If it hasn't happened yet, Either it happened but you didn't notice yet, which is very common I think.
Some outage, we tried everything good. Okay, Postgres is weird. Michael: Or even not even an outage, maybe just some customers getting some slow, like things running slowly. Nikolay: Yeah, like 5 seconds or 10 seconds latency spike and we don't understand why. Or you just got lucky And it awaits you next week. So everyone must understand this topic and implement low log timeout and retries until Postgres will have work concurrently
in all operations. Which will make things interesting because concurrently means you lose the transactional behavior of DDL. So it's very interesting. The future is interesting because I think ALTER TABLE should have something like concurrently or with 3 tries but it cannot be inside transaction block in this case. So it's so interesting topic, right? Michael: Yeah, you always combine these, lock timeout and retries, and it's only just struck me that if that sounds like a lot of
work to people, and then retries can feel like a lot of work. Actually, the important part is the lock timeout. Retries, it's Nikolay: like, whoa. That's a good point. Yesterday, we opened gates for first clients for our copilot product, PostgreSQL Copilot. And we had a new client, which actually originally they came as a consulting client but use our product inside consulting and like we found a lot of issues as usual, like It's great.
And observing their situation, I think it's RDS or Aurora, I don't remember particularly, but I noticed lock timeout was very low. It was very, very low. It's so interesting, 100 milliseconds. I haven't talked to them yet. I will soon. But it's an interesting situation. Global lock timeout is very low. So I even started to think, okay, lock timeout, does it affect only DDL or row level locks as well? What do you think? Michael: Good question.
I don't know. Nikolay: Yeah, it affects all. Yeah. Row level lock as well. So if you update a row but didn't say commit, so it's like multistate on transaction. You updated the row, sit inside transaction. Another transaction just fails after 1 second. Oh, not after 1 second, after 100 milliseconds. And this makes me think, okay, something is not right here. You know, deadlock timeout is 1 second by default. Yeah. But lock timeout is 100 milliseconds.
So that lock detection never happens. Michael: Yeah, in that case. Nikolay: Yeah. How is it possible? I'm very curious how this application feels, right? And I will be talking to them soon, I hope. And I think maybe I'm just overlooking, maybe there are some settings at session level, user level. So this global maybe is overwritten. Because it's a very interesting situation to keep it so low and
global yeah but what you say basically okay forget about read-write let's just set lock timeout second or 2 seconds and maybe only for DDL right Michael: yeah that's all I'm thinking is to start with start like add lock timeouts to your, just to your migrations. And then if they start failing, because they won't always like depending on your lock timeout and depending on your like, well, Especially if you design them in a way that is not super... You know, if you're not doing full table reworks and things.
Nikolay: You are right, but I can tell you what happens next. Next pipeline runs for 10 minutes, and then it fails. And like, fuck, damn, we need to retry it whole. So retries will be long, big loops. Michael: Within the pipeline, and within hours. Nikolay: Yeah, yeah, yeah. That's why I say do retries right on. The most radical way, extreme way to do retries is inside transactions, have sub-transactions. This is the only case when I can say sub-transactions are reasonable.
Well I think some financial applications might need it as well, but in general case I tend to say Avoid sub-transactions. But if you have a complex DDL transaction, not DDL, it can be DDL, DML or something, and then inside it you want to have retries, and you cannot lose... You can, but it's too costly, you will need to retry a lot of stuff. You don't want to lose what you have so far in that transaction. OK, you can use safe points there, but you need to double check that safe points don't go too deep in terms of nesting, 64, right?
And also you need to check there are no long-running transactions that will affect the health, and you will see sub-transfers for your wait events. So in this case, you just retry, and if retry fails, you retry. If you have a lot of timeout, if your local DDL fails, you don't lose everything. You lose only until the latest save point, and then retry again. Usually it's done using PL/pgSQL, and begin, exception, when, blah, blah, and blocks. It will create sub-transactions implicitly for you.
And then you can retry there, and this becomes an exception and block. And it's good, it's good. I mean, this is a good thing to have. And in this case, retries are super local, and the whole pipeline of deployment won't fail. If everything is all right. Of course, if there is a long transaction running 2 hours, those retries probably won't last so much. Michael: Yeah, it fails anyway. Nikolay: So that's why I connected these 2 words. Michael: Oh, I definitely think a lot of timeout and retries
is great I'm just thinking as there's there is an intermediate step if people want to get a lot of the benefit without all of the work. Nikolay: Yeah well I agree at least lock timeout yeah this is already it's a safeguard from from downtime basically yeah at least partial downtime and here interesting that we want like no like shift left testing you know We want to test and find bugs earlier. Ideally, developer who developed the code finds bugs immediately, right? This is shifted to left. Here it tries, we want to shift to the right.
Because if we shifted to like testing pipelines, yeah, like it's too, it makes retry heavier, right? Michael: Yeah, these things are also, I think, on the testing front, these are the hardest things to test as well, right? Because they're dependent on other activity. Nikolay: On situation. Michael: And very few people have good test setups that have concurrent activity. You know, it's not common. Nikolay: Yeah, this is on 1 hand. On another hand, it's not rocket science at all, and you just
need to avoid 2 things. I thought about this, and you know, like in DBLab, for example, in our tool for database branching, we eventually developed methodology where we don't need background workload because it's very uncertain. 1 day it has long running transaction, another day, we should be prepared for everything. This is our approach. And we just decided to do 2 things. 1 thing is that we need lock timeout and retries.
This is 1 thing. Just to be prepared that lock cannot be acquired. Again, lock cannot be acquired sometimes because of autovacuum. Michael: Oh yeah, in anti-wraparound mode you mean? Nikolay: Yeah, and it can run, if it's a huge table, it can run hours sometimes. Michael: If it's Nikolay: throttled, it can run hours. And another thing is that we should not keep exclusive locks for long. That's it. For long means like for many seconds. They should be brief.
If you know how to acquire locks gracefully, so not waiting for long, blocking others, and with retries. And also, if you acquired lock, you don't hold it too long, for long, that's it. There are exceptions, though. There are exceptions. For example, if you create a new table and you load data to it, well, technically nobody is working with this table yet. So if you created the table, you own the lock on it, exclusive
lock. Inside the same transaction, you can load data. Why not? In this case you are breaking the rule number 2. You hold the lock too long, but it's harmless. So, but yeah, there are exclusions, but in general these 2 rules are serving great. Like they are helpful if you keep them in mind. Graceful acquisition and don't call it too long if it's very heavy, like lock, heaviest lock, exclusive lock. Michael: Sounds good.
Anything else you want to touch on before we call it a day? Nikolay: I think that's it, I hope it was helpful. Michael: Yep, nice. And I'm interested to hear if anyone gets in touch about any of this as well. Nikolay: Oh yeah, I'm curious. Maybe someone from RDS will comment on this. Why max_connections is so high by default? Michael: Yeah, it could be. Good. All right. Thanks Nikolay. Take care. You too.
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...