public inbox for [email protected]
help / color / mirror / Atom feedGuidance on Calculating max_locks_per_transaction for a Highly Partitioned Environment
2+ messages / 2 participants
[nested] [flat]
* Guidance on Calculating max_locks_per_transaction for a Highly Partitioned Environment
@ 2026-06-29 17:55 Vivek Gadge <[email protected]>
2026-06-30 16:47 ` Re: Guidance on Calculating max_locks_per_transaction for a Highly Partitioned Environment Ron Johnson <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Vivek Gadge @ 2026-06-29 17:55 UTC (permalink / raw)
To: pgsql-general
Hello Team,
I am looking for guidance on determining an appropriate value for the
max_locks_per_transaction parameter in our PostgreSQL 17.6 production
environment.
Environment has the following characteristics:
PostgreSQL Version: 17.6
max_connections = 1500
Daily partitioning strategy
Large OLTP workload with transactions that may access multiple
partitions
Occasionally, we encounter the following error:
ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
We understand that the shared lock table is sized based on
max_locks_per_transaction, max_connections, and max_prepared_transactions.
However, we have not found any guidance on how to estimate an appropriate
value for environments with a large number of partitions and indexes.
We would appreciate guidance on the following:
Is there a recommended methodology or formula for estimating
max_locks_per_transaction.
Our objective is to determine an appropriate value based on workload
characteristics rather than increasing the parameter through trial and
error.
Thanks
Vivek Gadge.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Guidance on Calculating max_locks_per_transaction for a Highly Partitioned Environment
2026-06-29 17:55 Guidance on Calculating max_locks_per_transaction for a Highly Partitioned Environment Vivek Gadge <[email protected]>
@ 2026-06-30 16:47 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Ron Johnson @ 2026-06-30 16:47 UTC (permalink / raw)
To: pgsql-general
On Tue, Jun 30, 2026 at 9:26 AM Vivek Gadge <[email protected]> wrote:
> Hello Team,
>
> I am looking for guidance on determining an appropriate value for the
> max_locks_per_transaction parameter in our PostgreSQL 17.6 production
> environment.
>
> Environment has the following characteristics:
>
> PostgreSQL Version: 17.6
> max_connections = 1500
>
That's HIGH. If you have that many concurrent connections, then you really
need a connection pooler.
> Is there a recommended methodology or formula for estimating
> max_locks_per_transaction.
>
> Our objective is to determine an appropriate value based on workload
> characteristics rather than increasing the parameter through trial and
> error.
>
The memory structures handling locks per transaction are really light. I
set the value to 15000 (where average connection count is around 30) and
then worry about something else.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-06-30 16:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-29 17:55 Guidance on Calculating max_locks_per_transaction for a Highly Partitioned Environment Vivek Gadge <[email protected]>
2026-06-30 16:47 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox