Thread

1. Guidance on Calculating max_locks_per_transaction for a Highly Partitioned Environment

   Vivek Gadge <vvkgadge56@gmail.com> — 2026-06-29T17:55:59Z

   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.
   

2. Re: Guidance on Calculating max_locks_per_transaction for a Highly Partitioned Environment

   Ron Johnson <ronljohnsonjr@gmail.com> — 2026-06-30T16:47:11Z

   On Tue, Jun 30, 2026 at 9:26 AM Vivek Gadge <vvkgadge56@gmail.com> 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!