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.