public inbox for [email protected]
help / color / mirror / Atom feedRe: Shared hash table allocations
12+ messages / 3 participants
[nested] [flat]
* Re: Shared hash table allocations
@ 2026-03-31 21:25 Heikki Linnakangas <[email protected]>
2026-04-02 10:24 ` Re: Shared hash table allocations Matthias van de Meent <[email protected]>
2026-04-02 12:55 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Heikki Linnakangas @ 2026-03-31 21:25 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On 31/03/2026 01:02, Heikki Linnakangas wrote:
> I wonder if we should change the defaults somehow. In usual
> configurations, people are currently getting much more lock space than
> you'd expect based on max_connections and max_locks_per_transaction, and
> after these patches, they'll get much fewer locks. It might be prudent
> bump up the default max_locks_per_transaction setting so that you'd get
> roughly the same amount of locks in the default configuration.
I did some testing of the memory usage and how removing the wiggle room
affects the number of locks you can acquire. Attached are the test
procedures I used, and proposed patches. The patches are new, designed
to just change the parameters of the hash tables and shmem calculations
with no other changes. They don't include the refactorings we've
discussed so far in this thread. My plan is to commit these new patches
first, and those other refactorings after that. Once these new patches
are committed, the refactorings won't materially change the overall
memory usage or how it's divided between different hash tables, all
those effects are in these new patches.
master: With the default configuration on master, the attached test
procedure can take 14927 locks before hitting "out of shared memory"
error. At that point, all the "wiggle room" is assigned for the LOCK
hash table. A different scenario could make the PROCLOCK hash table
consume all the wiggle room instead, but I believe running out of LOCK
space is more common, and I don't think it changes the big picture
anyway if you hit the ceiling with PROCLOCK instead.
0001: While looking at this, I noticed that we add 10% "safety margin"
to the shmem calculations in predicate.c, but we had already marked the
predicate.c hash tables as HASH_FIXED_SIZE so they were never able to
make use of the safety margin. Oops. The extra memory was available for
the lock.c hash tables, though. After removing that bogus 10% safety
margin from predicate.c, memory usage was reduced by 200 kB, but the
number of locks you could take went down from 14927 to 14159.
0002: As the next step, I also removed the 10% safety margin from
lock.c. That reduced memory usage by another 320 kB, and the number of
locks went down from 14159 to 12815.
0003: After those changes, there's only little extra memory sloshing
around that's not accounted for any data structure. ipci.c reserves a
constant 100 kB, but that's pretty much it. However, there's still
flexibility between the LOCK and the PROCLOCK hash tables. The PROCLOCK
hash table is estimated to be 2x the size of the LOCK table, but when
it's not, the space can get assigned to the LOCK table instead. In patch
0003 I removed that flexibility by marking them both with
HASH_FIXED_SIZE, and making init_size equal to max_size. That also stops
the hash tables from using any of the other remaining wiggle room,
making them truly fixed-size. This doesn't change the overall shared
memory allocated, but the number of locks that the test procedure could
acquire went down from 12815 to 8767, mostly because it cannot "steal"
space from PROCLOCK anymore.
0004: To buy back that lock manager space in common out-of-the box
situations, I propose to bump up the default for
max_locks_per_transactions from 64 to 128. That increases memory usage
again by 3216 kB, making it 2696 kB higher than on master (remember that
the previous changes reduced memory usage). The number of locks you can
take after that is 17535, which more than on master (14927).
Increasing the default won't affect users who have already set
max_locks_per_transaction to a non-default value. They will see that the
number of locks they can acquire with their existing configuration will
be reduced, again because of the lost wiggle room and flexibility
between LOCK and PROCLOCK. Not sure if we could or should do something
about that. Probably best to just document in the release notes that if
you had raised increase max_locks_per_transaction, you might need to
raise it further to be able to accommodate the same amount of locks as
before.
Here's all that in table form:
| Patch | Shmem (kB) | Locks |
| --------------------------------------+------------|-------|
| master | 153560 | 14927 |
| 0001: remove 10% from predicate.c | 153360 | 14159 |
| 0002: remove 10% from lock.c | 153040 | 12815 |
| 0003: Make lock.c tables fixed size | 153040 | 8767 |
| 0004: max_locks_per_transactions=128 | 156256 | 17535 |
This increase in memory usage is not great, but it's not that big in the
grand scheme of things. I think it's well worth, and better than the
sloppy scheme we have today.
Any thoughts, objections?
- Heikki
Attachments:
[application/sql] shmem-test.sql (2.1K, 2-shmem-test.sql)
download
[text/x-patch] v3-0001-Remove-bogus-safety-margin-from-predicate.c-shmem.patch (1.6K, 3-v3-0001-Remove-bogus-safety-margin-from-predicate.c-shmem.patch)
download | inline diff:
From 8f55a325a4bfeeeb87ec2582c7d30d8a20d0abe9 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Tue, 31 Mar 2026 12:06:44 +0300
Subject: [PATCH v3 1/4] Remove bogus "safety margin" from predicate.c shmem
estimates
The 10% safety margin was copy-pasted from lock.c when the predicate
locking code was originally added. However, we later (commit
7c797e7194) added the HASH_FIXED_SIZE flag to the hash tables, which
means that they cannot actually use the safety margin that we're
calculating for them.
The extra memory was mainly used by the main lock manager, which is
the only shmem hash table of non-trivial size that does not use the
HASH_FIXED_SIZE flag. If we wanted to have more space for the lock
manager, we should reserve it directly in lock.c. As this patch
stands, the lock manager will just have less memory available than
before.
---
src/backend/storage/lmgr/predicate.c | 6 ------
1 file changed, 6 deletions(-)
diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c
index ae0e96aee5f..efa47ec1684 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -1383,12 +1383,6 @@ PredicateLockShmemSize(void)
size = add_size(size, hash_estimate_size(max_predicate_locks,
sizeof(PREDICATELOCK)));
- /*
- * Since NPREDICATELOCKTARGETENTS is only an estimate, add 10% safety
- * margin.
- */
- size = add_size(size, size / 10);
-
/* transaction list */
max_serializable_xacts = (MaxBackends + max_prepared_xacts) * 10;
size = add_size(size, PredXactListDataSize);
--
2.47.3
[text/x-patch] v3-0002-Remove-10-safety-marging-from-lock-hash-table-est.patch (818B, 4-v3-0002-Remove-10-safety-marging-from-lock-hash-table-est.patch)
download | inline diff:
From 53e0a614fdfaa42003841b4776cc4aab72f11982 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Tue, 31 Mar 2026 23:14:36 +0300
Subject: [PATCH v3 2/4] Remove 10% safety marging from lock hash table
estimates
---
src/backend/storage/lmgr/lock.c | 5 -----
1 file changed, 5 deletions(-)
diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index 234643e4dd7..2159de9015a 100644
--- a/src/backend/storage/lmgr/lock.c
+++ b/src/backend/storage/lmgr/lock.c
@@ -3778,11 +3778,6 @@ LockManagerShmemSize(void)
max_table_size *= 2;
size = add_size(size, hash_estimate_size(max_table_size, sizeof(PROCLOCK)));
- /*
- * Since NLOCKENTS is only an estimate, add 10% safety margin.
- */
- size = add_size(size, size / 10);
-
return size;
}
--
2.47.3
[text/x-patch] v3-0003-Make-the-lock-hash-tables-fixed-sized.patch (2.1K, 5-v3-0003-Make-the-lock-hash-tables-fixed-sized.patch)
download | inline diff:
From edc25ff76b77244880120803ca0f495fa734a02c Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Tue, 31 Mar 2026 18:39:03 +0300
Subject: [PATCH v3 3/4] Make the lock hash tables fixed-sized
---
src/backend/storage/lmgr/lock.c | 19 +++++++++----------
1 file changed, 9 insertions(+), 10 deletions(-)
diff --git a/src/backend/storage/lmgr/lock.c b/src/backend/storage/lmgr/lock.c
index 2159de9015a..ac7c5ed0604 100644
--- a/src/backend/storage/lmgr/lock.c
+++ b/src/backend/storage/lmgr/lock.c
@@ -445,16 +445,14 @@ void
LockManagerShmemInit(void)
{
HASHCTL info;
- int64 init_table_size,
- max_table_size;
+ int64 max_table_size;
bool found;
/*
- * Compute init/max size to request for lock hashtables. Note these
- * calculations must agree with LockManagerShmemSize!
+ * Compute sizes for lock hashtables. Note that these calculations must
+ * agree with LockManagerShmemSize!
*/
max_table_size = NLOCKENTS();
- init_table_size = max_table_size / 2;
/*
* Allocate hash table for LOCK structs. This stores per-locked-object
@@ -465,14 +463,14 @@ LockManagerShmemInit(void)
info.num_partitions = NUM_LOCK_PARTITIONS;
LockMethodLockHash = ShmemInitHash("LOCK hash",
- init_table_size,
+ max_table_size,
max_table_size,
&info,
- HASH_ELEM | HASH_BLOBS | HASH_PARTITION);
+ HASH_ELEM | HASH_BLOBS |
+ HASH_PARTITION | HASH_FIXED_SIZE);
/* Assume an average of 2 holders per lock */
max_table_size *= 2;
- init_table_size *= 2;
/*
* Allocate hash table for PROCLOCK structs. This stores
@@ -484,10 +482,11 @@ LockManagerShmemInit(void)
info.num_partitions = NUM_LOCK_PARTITIONS;
LockMethodProcLockHash = ShmemInitHash("PROCLOCK hash",
- init_table_size,
+ max_table_size,
max_table_size,
&info,
- HASH_ELEM | HASH_FUNCTION | HASH_PARTITION);
+ HASH_ELEM | HASH_FUNCTION |
+ HASH_FIXED_SIZE | HASH_PARTITION);
/*
* Allocate fast-path structures.
--
2.47.3
[text/x-patch] v3-0004-Change-default-max_locks_per_transactions-128.patch (4.1K, 6-v3-0004-Change-default-max_locks_per_transactions-128.patch)
download | inline diff:
From 692c0727d84d534a2575e0442c96e4df3752a7b4 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Wed, 1 Apr 2026 00:09:35 +0300
Subject: [PATCH v3 4/4] Change default max_locks_per_transactions=128
---
doc/src/sgml/config.sgml | 2 +-
src/backend/utils/init/postinit.c | 2 +-
src/backend/utils/misc/guc_parameters.dat | 2 +-
src/backend/utils/misc/postgresql.conf.sample | 2 +-
src/bin/pg_resetwal/pg_resetwal.c | 4 ++--
5 files changed, 6 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 229f41353eb..20706b56158 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -11470,7 +11470,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:$libdir'
can lock more objects as long as the locks of all transactions
fit in the lock table. This is <emphasis>not</emphasis> the number of
rows that can be locked; that value is unlimited. The default,
- 64, has historically proven sufficient, but you might need to
+ 128, has historically proven sufficient, but you might need to
raise this value if you have queries that touch many different
tables in a single transaction, e.g., query of a parent table with
many children. This parameter can only be set at server start.
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 577ef5effbb..783a7400464 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -593,7 +593,7 @@ InitializeFastPathLocks(void)
* value at FP_LOCK_GROUPS_PER_BACKEND_MAX and insist the value is at
* least 1.
*
- * The default max_locks_per_transaction = 64 means 4 groups by default.
+ * The default max_locks_per_transaction = 128 means 8 groups by default.
*/
FastPathLockGroupsPerBackend =
Max(Min(pg_nextpower2_32(max_locks_per_xact) / FP_LOCK_SLOTS_PER_GROUP,
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 0a862693fcd..a3e0dda34af 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -1979,7 +1979,7 @@
short_desc => 'Sets the maximum number of locks per transaction.',
long_desc => 'The shared lock table is sized on the assumption that at most "max_locks_per_transaction" objects per server process or prepared transaction will need to be locked at any one time.',
variable => 'max_locks_per_xact',
- boot_val => '64',
+ boot_val => '128',
min => '10',
max => 'INT_MAX',
},
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index cf15597385b..6e376d85d61 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -856,7 +856,7 @@
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
-#max_locks_per_transaction = 64 # min 10
+#max_locks_per_transaction = 128 # min 10
# (change requires restart)
#max_pred_locks_per_transaction = 64 # min 10
# (change requires restart)
diff --git a/src/bin/pg_resetwal/pg_resetwal.c b/src/bin/pg_resetwal/pg_resetwal.c
index ab766c34d4b..44f2b446e5d 100644
--- a/src/bin/pg_resetwal/pg_resetwal.c
+++ b/src/bin/pg_resetwal/pg_resetwal.c
@@ -722,7 +722,7 @@ GuessControlValues(void)
ControlFile.max_wal_senders = 10;
ControlFile.max_worker_processes = 8;
ControlFile.max_prepared_xacts = 0;
- ControlFile.max_locks_per_xact = 64;
+ ControlFile.max_locks_per_xact = 128;
ControlFile.maxAlign = MAXIMUM_ALIGNOF;
ControlFile.floatFormat = FLOATFORMAT_VALUE;
@@ -931,7 +931,7 @@ RewriteControlFile(void)
ControlFile.max_wal_senders = 10;
ControlFile.max_worker_processes = 8;
ControlFile.max_prepared_xacts = 0;
- ControlFile.max_locks_per_xact = 64;
+ ControlFile.max_locks_per_xact = 128;
/* The control file gets flushed here. */
update_controlfile(".", &ControlFile, true);
--
2.47.3
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
@ 2026-04-02 10:24 ` Matthias van de Meent <[email protected]>
2026-04-02 11:52 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Matthias van de Meent @ 2026-04-02 10:24 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On Tue, 31 Mar 2026 at 23:25, Heikki Linnakangas <[email protected]> wrote:
>
> On 31/03/2026 01:02, Heikki Linnakangas wrote:
> > I wonder if we should change the defaults somehow. In usual
> > configurations, people are currently getting much more lock space than
> > you'd expect based on max_connections and max_locks_per_transaction, and
> > after these patches, they'll get much fewer locks. It might be prudent
> > bump up the default max_locks_per_transaction setting so that you'd get
> > roughly the same amount of locks in the default configuration.
>
> master: With the default configuration on master, the attached test
> procedure can take 14927 locks before hitting "out of shared memory"
> error. At that point, all the "wiggle room" is assigned for the LOCK
> hash table. A different scenario could make the PROCLOCK hash table
> consume all the wiggle room instead, but I believe running out of LOCK
> space is more common, and I don't think it changes the big picture
> anyway if you hit the ceiling with PROCLOCK instead.
>
> 0001: [...]
LGTM
> 0002: As the next step, I also removed the 10% safety margin from
> lock.c. That reduced memory usage by another 320 kB, and the number of
> locks went down from 14159 to 12815.
LGTM
> 0003: In patch 0003 I removed that flexibility by marking them both with
> HASH_FIXED_SIZE, and making init_size equal to max_size. That also stops
> the hash tables from using any of the other remaining wiggle room,
> making them truly fixed-size.
I think this patch finally gave me a good reason why PROCLOCK would've
needed to be allocated with double the sizes of LOCK:
LOCK is (was) initialized with only 50% of its max capacity. If
PROCLOCK was initialized with the same parameters and all spare shmem
is then allocated to other processes, then backends wouldn't be able
to safely use max_locks_per_transaction. To guarantee no OOMs when all
backends use max_locks_per_transaction, PROCLOCK's size must be
doubled to make sure PROCLOCK has sufficient space. (The same isn't
usually an issue for LOCK, because it's very likely backends will
operate on the same tables, and thus will be able to share most of the
LOCK structs.)
Now that LOCK is fully allocated, I think the size doubling can be
removed, or possibly parameterized for those that need it.
> 0004: To buy back that lock manager space in common out-of-the box
> situations, I propose to bump up the default for
> max_locks_per_transactions from 64 to 128. [...]
> The number of locks you can
> take after that is 17535, which more than on master (14927).
Note that this is for one backend; with current sizing you could lock
the same 17535 locks in at least one more backend.
Patch LGTM.
> Any thoughts, objections?
Overall, I'm +1 on this change. I do have some general comments
though, at least in part based on discussions in the hackers discord
last year[0]:
1.) We'll need to clearly advertise the changed, more strict behaviour
of the heavy-weight locking system in the release notes.
2.) (Related) We probably should make it easier for DBAs to monitor
lock counts now that we enforce the limit more strictly. This could
take the form of (optional) logging that alerts when a session exceeds
some threshold number of locks in a transaction (e.g. 100% and 200% of
max_locks_per_transaction), or as a metric in
pg_stat_{activity,databases} as total locks taken/max number of locks
taken in a transaction.
3.) (Related) We should probably parameterize the LOCK-to-PROCLOCK
ratio. LOCK is large, and especially on systems with high values of
max_connections (where the additional LOCKs will go unused) the
overhead of carrying all those additional LOCKs would go up to 50% of
the added memory usage (LOCK at 152+24=176B, PROCLOCK at
2*(64+24B)=176B). It'd be nice if we could avoid allocating that
memory.
Kind regards,
Matthias van de Meent
Databricks (https://www.databricks.com)
[0] starting at
https://discord.com/channels/1258108670710124574/1266090488415654032/1442879718285119518
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 10:24 ` Re: Shared hash table allocations Matthias van de Meent <[email protected]>
@ 2026-04-02 11:52 ` Heikki Linnakangas <[email protected]>
2026-04-02 13:47 ` Re: Shared hash table allocations Matthias van de Meent <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Heikki Linnakangas @ 2026-04-02 11:52 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On 02/04/2026 13:24, Matthias van de Meent wrote:
> On Tue, 31 Mar 2026 at 23:25, Heikki Linnakangas <[email protected]> wrote:
>>
>> 0003: In patch 0003 I removed that flexibility by marking them both with
>> HASH_FIXED_SIZE, and making init_size equal to max_size. That also stops
>> the hash tables from using any of the other remaining wiggle room,
>> making them truly fixed-size.
>
> I think this patch finally gave me a good reason why PROCLOCK would've
> needed to be allocated with double the sizes of LOCK:
>
> LOCK is (was) initialized with only 50% of its max capacity. If
> PROCLOCK was initialized with the same parameters and all spare shmem
> is then allocated to other processes, then backends wouldn't be able
> to safely use max_locks_per_transaction. To guarantee no OOMs when all
> backends use max_locks_per_transaction, PROCLOCK's size must be
> doubled to make sure PROCLOCK has sufficient space. (The same isn't
> usually an issue for LOCK, because it's very likely backends will
> operate on the same tables, and thus will be able to share most of the
> LOCK structs.)
Hmm, I don't know if that makes sense. It can happen that you have a lot
of backends acquiring the same, smaller set of locks, growing PROCLOCK
so that it uses up all the available wiggle room, and LOCK can never
grow from its initial size, 1/2 * max_locks_per_transactions *
MaxBackends. If the workload then changes so that every backend tries to
acquire exactly max_locks_per_transactions locks, but this time each
lock is on a different object, you will run out of shared memory at 1/2
the size of what you expected.
The opposite can't happen, because PROCLOCK is always at least as large
as LOCK. It doesn't matter what you set PROCLOCK's initial size to, it
will grow together with LOCK, and you will not run out of shared memory
before PROCLOCK has grown up to max_locks_per_transactions * MaxBackends
anyway.
> Now that LOCK is fully allocated, I think the size doubling can be
> removed, or possibly parameterized for those that need it.
I don't think that follows. The 2x factor is pretty arbitrary, but it's
still a fair assumption that many backends will be acquiring locks on
the same objects so you need more space in PROCLOCK than in LOCK.
I don't know how true that assumption is. It feels right for OLTP
applications. But the situation where I've hit max_locks_per_transaction
is when I've tried to create one table with thousands or partitions. Or
rather, when I try to *drop* that table. In that situation, there's just
one transaction acquiring all the locks, so the PROCLOCK / LOCK ratio is 1.
We could parameterize it, but I feel that's probably overkill and
exposing too much detail to users. At the end of the day, if you hit the
limit, you just bump up max_locks_per_transactions. If there are two
settings, it's more complicated; which one do you change? You probably
don't mind wasting the few MB of memory that you could gain by carefully
tuning the LOCK / PROCLOCK factor.
- Heikki
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 10:24 ` Re: Shared hash table allocations Matthias van de Meent <[email protected]>
2026-04-02 11:52 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
@ 2026-04-02 13:47 ` Matthias van de Meent <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Matthias van de Meent @ 2026-04-02 13:47 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On Thu, 2 Apr 2026 at 13:52, Heikki Linnakangas <[email protected]> wrote:
>
> On 02/04/2026 13:24, Matthias van de Meent wrote:
> > On Tue, 31 Mar 2026 at 23:25, Heikki Linnakangas <[email protected]> wrote:
> >>
> >> 0003: In patch 0003 I removed that flexibility by marking them both with
> >> HASH_FIXED_SIZE, and making init_size equal to max_size. That also stops
> >> the hash tables from using any of the other remaining wiggle room,
> >> making them truly fixed-size.
> >
> > I think this patch finally gave me a good reason why PROCLOCK would've
> > needed to be allocated with double the sizes of LOCK:
> >
> > LOCK is (was) initialized with only 50% of its max capacity. If
> > PROCLOCK was initialized with the same parameters and all spare shmem
> > is then allocated to other processes, then backends wouldn't be able
> > to safely use max_locks_per_transaction. To guarantee no OOMs when all
> > backends use max_locks_per_transaction, PROCLOCK's size must be
> > doubled to make sure PROCLOCK has sufficient space. (The same isn't
> > usually an issue for LOCK, because it's very likely backends will
> > operate on the same tables, and thus will be able to share most of the
> > LOCK structs.)
>
> Hmm, I don't know if that makes sense.
Code and mailing history indicate it's not the reason, but there is no
other sane reason why PROCLOCK would *not* be sized to
max_locks_per_transaction * MaxBackends. At least with this reasoning
the minimum size is exactly that.
> It can happen that you have a lot
> of backends acquiring the same, smaller set of locks, growing PROCLOCK
> so that it uses up all the available wiggle room, and LOCK can never
> grow from its initial size, 1/2 * max_locks_per_transactions *
> MaxBackends. If the workload then changes so that every backend tries to
> acquire exactly max_locks_per_transactions locks, but this time each
> lock is on a different object, you will run out of shared memory at 1/2
> the size of what you expected.
>
> The opposite can't happen, because PROCLOCK is always at least as large
> as LOCK. It doesn't matter what you set PROCLOCK's initial size to, it
> will grow together with LOCK, and you will not run out of shared memory
> before PROCLOCK has grown up to max_locks_per_transactions * MaxBackends
> anyway.
>
> > Now that LOCK is fully allocated, I think the size doubling can be
> > removed, or possibly parameterized for those that need it.
>
> I don't think that follows. The 2x factor is pretty arbitrary, but it's
> still a fair assumption that many backends will be acquiring locks on
> the same objects so you need more space in PROCLOCK than in LOCK.
I agree that we'll *probably* have more PROCLOCKs in use than LOCKs.
But max_locks_per_transaction (MLPT) to me indicates that it is an
indicator of the maximum number of locks taken by a transaction, and
transaction locks have a 1:1 correspondence with PROCLOCKs (as long as
we ignore fast-path locking).
Adjusting that value by an arbitrary factor does not many any sense.
The user configured a value X, so we should use that value X.
Possibly there could be adjustments we need to make to give ourself
some breathing room (it's not uncommon to overallocate by a constant
factor to allow evict-after-insert patterns in caches), but I can't
explain a blanket doubling of usage "because we have a hunch LOCK
usage will be lower than PROCLOCK usage" when the user specified a
value that would/should map 1:1 against PROCLOCKs scaling as anything
other than plainly wasting memory.
> I don't know how true that assumption is. It feels right for OLTP
> applications. But the situation where I've hit max_locks_per_transaction
> is when I've tried to create one table with thousands or partitions. Or
> rather, when I try to *drop* that table. In that situation, there's just
> one transaction acquiring all the locks, so the PROCLOCK / LOCK ratio is 1.
> We could parameterize it, but I feel that's probably overkill and
> exposing too much detail to users. At the end of the day, if you hit the
> limit, you just bump up max_locks_per_transactions.
Or, if it's for DROP, you could use a phased dropping scheme, where
you spread the operation across many transactions by dropping a subset
of the partitions in each transaction. It takes more careful execution
and more time, but it allows you to avoid hitting the limits and
starving other backends of lock slots, and avoids requiring postmaster
restarts.
> If there are two
> settings, it's more complicated; which one do you change? You probably
> don't mind wasting the few MB of memory that you could gain by carefully
> tuning the LOCK / PROCLOCK factor.
Yes, that would be more complicated, but we have similar factors
elsewhere (hash_mem_multiplier, various costs, weights). We wouldn't
even have to use a factor, we could just as well use a new, more
direct `max_unique_locks_per_transaction`, which we'd use to scale the
LOCK hash.
Note that with our current default settings we're spending 11kiB (= 64
* (64+24)) per backend on what I would consider oversized PROCLOCK
allocations. With MLPT=128, that doubles to 22kiB per backend. Every
50 max_backends, that'd be ~1.1MB of shared memory allocated in excess
of user's requested configuration.
Kind regards,
Matthias van de Meent
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
@ 2026-04-02 12:55 ` Ashutosh Bapat <[email protected]>
2026-04-02 14:14 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Ashutosh Bapat @ 2026-04-02 12:55 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On Wed, Apr 1, 2026 at 2:55 AM Heikki Linnakangas <[email protected]> wrote:
>
> On 31/03/2026 01:02, Heikki Linnakangas wrote:
> > I wonder if we should change the defaults somehow. In usual
> > configurations, people are currently getting much more lock space than
> > you'd expect based on max_connections and max_locks_per_transaction, and
> > after these patches, they'll get much fewer locks. It might be prudent
> > bump up the default max_locks_per_transaction setting so that you'd get
> > roughly the same amount of locks in the default configuration.
>
> I did some testing of the memory usage and how removing the wiggle room
> affects the number of locks you can acquire. Attached are the test
> procedures I used, and proposed patches. The patches are new, designed
> to just change the parameters of the hash tables and shmem calculations
> with no other changes. They don't include the refactorings we've
> discussed so far in this thread. My plan is to commit these new patches
> first, and those other refactorings after that. Once these new patches
> are committed, the refactorings won't materially change the overall
> memory usage or how it's divided between different hash tables, all
> those effects are in these new patches.
>
>
> master: With the default configuration on master, the attached test
> procedure can take 14927 locks before hitting "out of shared memory"
> error. At that point, all the "wiggle room" is assigned for the LOCK
> hash table. A different scenario could make the PROCLOCK hash table
> consume all the wiggle room instead, but I believe running out of LOCK
> space is more common, and I don't think it changes the big picture
> anyway if you hit the ceiling with PROCLOCK instead.
>
> 0001: While looking at this, I noticed that we add 10% "safety margin"
> to the shmem calculations in predicate.c, but we had already marked the
> predicate.c hash tables as HASH_FIXED_SIZE so they were never able to
> make use of the safety margin. Oops. The extra memory was available for
> the lock.c hash tables, though. After removing that bogus 10% safety
> margin from predicate.c, memory usage was reduced by 200 kB, but the
> number of locks you could take went down from 14927 to 14159.
>
> 0002: As the next step, I also removed the 10% safety margin from
> lock.c. That reduced memory usage by another 320 kB, and the number of
> locks went down from 14159 to 12815.
>
> 0003: After those changes, there's only little extra memory sloshing
> around that's not accounted for any data structure. ipci.c reserves a
> constant 100 kB, but that's pretty much it. However, there's still
> flexibility between the LOCK and the PROCLOCK hash tables. The PROCLOCK
> hash table is estimated to be 2x the size of the LOCK table, but when
> it's not, the space can get assigned to the LOCK table instead. In patch
> 0003 I removed that flexibility by marking them both with
> HASH_FIXED_SIZE, and making init_size equal to max_size. That also stops
> the hash tables from using any of the other remaining wiggle room,
> making them truly fixed-size. This doesn't change the overall shared
> memory allocated, but the number of locks that the test procedure could
> acquire went down from 12815 to 8767, mostly because it cannot "steal"
> space from PROCLOCK anymore.
>
> 0004: To buy back that lock manager space in common out-of-the box
> situations, I propose to bump up the default for
> max_locks_per_transactions from 64 to 128. That increases memory usage
> again by 3216 kB, making it 2696 kB higher than on master (remember that
> the previous changes reduced memory usage). The number of locks you can
> take after that is 17535, which more than on master (14927).
>
> Increasing the default won't affect users who have already set
> max_locks_per_transaction to a non-default value. They will see that the
> number of locks they can acquire with their existing configuration will
> be reduced, again because of the lost wiggle room and flexibility
> between LOCK and PROCLOCK. Not sure if we could or should do something
> about that. Probably best to just document in the release notes that if
> you had raised increase max_locks_per_transaction, you might need to
> raise it further to be able to accommodate the same amount of locks as
> before.
>
> Here's all that in table form:
>
> | Patch | Shmem (kB) | Locks |
> | --------------------------------------+------------|-------|
> | master | 153560 | 14927 |
> | 0001: remove 10% from predicate.c | 153360 | 14159 |
> | 0002: remove 10% from lock.c | 153040 | 12815 |
> | 0003: Make lock.c tables fixed size | 153040 | 8767 |
> | 0004: max_locks_per_transactions=128 | 156256 | 17535 |
>
> This increase in memory usage is not great, but it's not that big in the
> grand scheme of things. I think it's well worth, and better than the
> sloppy scheme we have today.
>
> Any thoughts, objections?
When we "allocate" shared memory, we are just allocating space on
systems which use mmap. The memory gets allocated only when it is
touched. The wiggle room as a whole is never touched during
initialization. Those pages get allocated when wiggle room is used -
i.e. when the entries beyond initial number are allocated. By
allocating maximal hash tables, I was worried that we will allocate
more memory than required. But that's not true since a 4K memory page
fits only 50-60 entries - far less than the default configuration
permits. Most of the memory for the hash table will be allocated as
the entries as used.
The second hazard of increasing hash table size is the hash table
access becomes slower as it becomes sparse [1]. I don't think it shows
up in performance but maybe worth trying a trivial pgbench run, just
to make sure that default performance doesn't regress.
The increase in memory usage is 3MB, which is fine usually. I mean, we
didn't hear any complaints when we increased the default size of the
shared buffer pool - this is much less than that. But why do you want
to double the max_locks_per_transaction? I first thought it's because
the hash table size is anyway a power of 2. But then the size of the
hash table is actually max_locks_per_transaction * (number of backends
+ number of prepared transactions). What we want is the default
max_locks_per_transaction such that 14927 locks are allowed. Playing
with max_locks_per_transaction using your script 109 seems to be the
number which will give us 14951 locks. It looks (and is) an odd
number. If we are worried about memory increase, that's the number we
should use as default and then write a long paragraph about why we
chose such an odd-looking number :D.
I think we should highlight the change in default in the release notes
though. The users which use default configuration will notice an
increase in the memory. If they are using a custom value, they will
think of bumping it up. Can we give them some ballpark % by which they
should increase their max_locks_per_transaction? E.g. double the
number or something?
I looked at the places where max_locks_per_transaction is used. I
don't see any place that needs code updates other than the ones in the
patch.
LGTM, overall.
[1] https://ashutoshpg.blogspot.com/2025/07/efficiency-of-sparse-hash-table.html
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 12:55 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
@ 2026-04-02 14:14 ` Heikki Linnakangas <[email protected]>
2026-04-02 14:52 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 16:13 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Heikki Linnakangas @ 2026-04-02 14:14 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On 02/04/2026 15:55, Ashutosh Bapat wrote:
> When we "allocate" shared memory, we are just allocating space on
> systems which use mmap. The memory gets allocated only when it is
> touched. The wiggle room as a whole is never touched during
> initialization. Those pages get allocated when wiggle room is used -
> i.e. when the entries beyond initial number are allocated. By
> allocating maximal hash tables, I was worried that we will allocate
> more memory than required. But that's not true since a 4K memory page
> fits only 50-60 entries - far less than the default configuration
> permits. Most of the memory for the hash table will be allocated as
> the entries as used.
Hmm, that's a good point about untouched memory not being allocated. I
think it's fine, though.
With small changes on top of the the earlier refactorings from this
thread, we could stop pre-allocating all the elements when a shared
memory hash table is created, and have ShmemHashAlloc() allocate them on
the fly, but instead of doing them as anonymous allocations like we do
with ShmemAlloc() today, the allocations could come from the
pre-allocated region dedicated to the hash table. You'd still get the
same determinism and visibility in pg_shmem_allocations, but you could
avoid actually touching the pages until they're needed. Not sure it's
worth the trouble.
> The second hazard of increasing hash table size is the hash table
> access becomes slower as it becomes sparse [1]. I don't think it shows
> up in performance but maybe worth trying a trivial pgbench run, just
> to make sure that default performance doesn't regress.
Interesting, but yeah I don't think that's going to be measurable. I did
some quick testing with a test function that just locks and unlocks
relations:
PG_FUNCTION_INFO_V1(test_lock_bench);
Datum
test_lock_bench(PG_FUNCTION_ARGS)
{
int32 num_distinct_locks = PG_GETARG_INT32(0);
int32 num_acquires = PG_GETARG_INT32(1);
LOCKMODE lockmode = AccessExclusiveLock;
#define FIRST_RELID 1000000000
for (int32 i = 0; i < num_acquires; i++)
{
Oid relid = FIRST_RELID + i % num_distinct_locks;
if (i >= num_distinct_locks)
UnlockRelationOid(relid, lockmode);
if (!ConditionalLockRelationOid(relid, lockmode))
{
elog(LOG, "could not acquire lock, iteration %d", i);
break;
}
}
PG_RETURN_VOID();
}
With test_lock_bench(1, 5000000), I don't see any meaningful difference,
i.e. it's within 1-2 %, with anything from max_locks_per_transactions=10
to max_locks_per_transactions=128.
With more distinct locks involved, the caching effects might be bigger,
and maybe you'd see a difference because of more or less collisions.
Spot testing some values on my laptop, I don't see anything that would
worry me though.
> The increase in memory usage is 3MB, which is fine usually. I mean, we
> didn't hear any complaints when we increased the default size of the
> shared buffer pool - this is much less than that. But why do you want
> to double the max_locks_per_transaction? I first thought it's because
> the hash table size is anyway a power of 2. But then the size of the
> hash table is actually max_locks_per_transaction * (number of backends
> + number of prepared transactions). What we want is the default
> max_locks_per_transaction such that 14927 locks are allowed. Playing
> with max_locks_per_transaction using your script 109 seems to be the
> number which will give us 14951 locks. It looks (and is) an odd
> number. If we are worried about memory increase, that's the number we
> should use as default and then write a long paragraph about why we
> chose such an odd-looking number :D.
My first thought was actually to set max_locks_per_transaction=100,
making it a nice round number :-). But then the neighboring default of
max_pred_locks_per_transaction=64 looks weird. We could reduce it
max_pred_locks_per_transaction=50 to make it fit in. But it feels a
little arbitrary to change just for aesthetic reasons.
> I think we should highlight the change in default in the release notes
> though. The users which use default configuration will notice an
> increase in the memory. If they are using a custom value, they will
> think of bumping it up. Can we give them some ballpark % by which they
> should increase their max_locks_per_transaction? E.g. double the
> number or something?
I don't think people who are using the defaults will notice. I'm worried
about the people who have set max_locks_per_transactions manually, and
now effectively get less lock space for the same setting. Yeah, doubling
the previous value is a good rule of thumb.
- Heikki
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 12:55 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 14:14 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
@ 2026-04-02 14:52 ` Ashutosh Bapat <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Ashutosh Bapat @ 2026-04-02 14:52 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On Thu, Apr 2, 2026 at 7:44 PM Heikki Linnakangas <[email protected]> wrote:
>
> On 02/04/2026 15:55, Ashutosh Bapat wrote:
> > When we "allocate" shared memory, we are just allocating space on
> > systems which use mmap. The memory gets allocated only when it is
> > touched. The wiggle room as a whole is never touched during
> > initialization. Those pages get allocated when wiggle room is used -
> > i.e. when the entries beyond initial number are allocated. By
> > allocating maximal hash tables, I was worried that we will allocate
> > more memory than required. But that's not true since a 4K memory page
> > fits only 50-60 entries - far less than the default configuration
> > permits. Most of the memory for the hash table will be allocated as
> > the entries as used.
>
> Hmm, that's a good point about untouched memory not being allocated. I
> think it's fine, though.
>
> With small changes on top of the the earlier refactorings from this
> thread, we could stop pre-allocating all the elements when a shared
> memory hash table is created, and have ShmemHashAlloc() allocate them on
> the fly, but instead of doing them as anonymous allocations like we do
> with ShmemAlloc() today, the allocations could come from the
> pre-allocated region dedicated to the hash table. You'd still get the
> same determinism and visibility in pg_shmem_allocations, but you could
> avoid actually touching the pages until they're needed. Not sure it's
> worth the trouble.
share hash table refactoring + shared memory structure refactoring +
resizable structures, we should be able to get resizable shared hash
tables as well. But that's not required immediately. I feel large hash
tables like buffer hash table, lock hash tables can benefit from this
kind of thing.
>
> > The second hazard of increasing hash table size is the hash table
> > access becomes slower as it becomes sparse [1]. I don't think it shows
> > up in performance but maybe worth trying a trivial pgbench run, just
> > to make sure that default performance doesn't regress.
>
> Interesting, but yeah I don't think that's going to be measurable. I did
> some quick testing with a test function that just locks and unlocks
> relations:
>
> PG_FUNCTION_INFO_V1(test_lock_bench);
> Datum
> test_lock_bench(PG_FUNCTION_ARGS)
> {
> int32 num_distinct_locks = PG_GETARG_INT32(0);
> int32 num_acquires = PG_GETARG_INT32(1);
>
> LOCKMODE lockmode = AccessExclusiveLock;
>
> #define FIRST_RELID 1000000000
>
> for (int32 i = 0; i < num_acquires; i++)
> {
> Oid relid = FIRST_RELID + i % num_distinct_locks;
>
> if (i >= num_distinct_locks)
> UnlockRelationOid(relid, lockmode);
>
> if (!ConditionalLockRelationOid(relid, lockmode))
> {
> elog(LOG, "could not acquire lock, iteration %d", i);
> break;
> }
> }
>
> PG_RETURN_VOID();
> }
>
> With test_lock_bench(1, 5000000), I don't see any meaningful difference,
> i.e. it's within 1-2 %, with anything from max_locks_per_transactions=10
> to max_locks_per_transactions=128.
>
> With more distinct locks involved, the caching effects might be bigger,
> and maybe you'd see a difference because of more or less collisions.
> Spot testing some values on my laptop, I don't see anything that would
> worry me though.
Great. This agrees with my experiments with sparse buffer lookup table.
>
> > The increase in memory usage is 3MB, which is fine usually. I mean, we
> > didn't hear any complaints when we increased the default size of the
> > shared buffer pool - this is much less than that. But why do you want
> > to double the max_locks_per_transaction? I first thought it's because
> > the hash table size is anyway a power of 2. But then the size of the
> > hash table is actually max_locks_per_transaction * (number of backends
> > + number of prepared transactions). What we want is the default
> > max_locks_per_transaction such that 14927 locks are allowed. Playing
> > with max_locks_per_transaction using your script 109 seems to be the
> > number which will give us 14951 locks. It looks (and is) an odd
> > number. If we are worried about memory increase, that's the number we
> > should use as default and then write a long paragraph about why we
> > chose such an odd-looking number :D.
>
> My first thought was actually to set max_locks_per_transaction=100,
> making it a nice round number :-). But then the neighboring default of
> max_pred_locks_per_transaction=64 looks weird. We could reduce it
> max_pred_locks_per_transaction=50 to make it fit in. But it feels a
> little arbitrary to change just for aesthetic reasons.
+1. Let's keep it 128 and see if there are complaints. We can set it
to 100 or 109 if the complaints look serious.
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 12:55 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 14:14 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
@ 2026-04-02 16:13 ` Ashutosh Bapat <[email protected]>
2026-04-02 16:44 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Ashutosh Bapat @ 2026-04-02 16:13 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On Thu, Apr 2, 2026 at 7:44 PM Heikki Linnakangas <[email protected]> wrote:
>
> > I think we should highlight the change in default in the release notes
> > though. The users which use default configuration will notice an
> > increase in the memory. If they are using a custom value, they will
> > think of bumping it up. Can we give them some ballpark % by which they
> > should increase their max_locks_per_transaction? E.g. double the
> > number or something?
>
> I don't think people who are using the defaults will notice. I'm worried
> about the people who have set max_locks_per_transactions manually, and
> now effectively get less lock space for the same setting. Yeah, doubling
> the previous value is a good rule of thumb.
Users who have set max_locks_per_transaction to a non-default value
but have tuned their application to respect those limits are safe even
after this change, since their lock hash table never used wiggle room.
Those users who weren't careful to respect those limits will need to
bump their setting. I think the release notes should "nudge" all the
users who use non-default max_locks_per_transaction to increase it if
they see "out of memory" errors. I don't think it should provide a
blanket advise to double their locks
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 12:55 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 14:14 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 16:13 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
@ 2026-04-02 16:44 ` Heikki Linnakangas <[email protected]>
2026-04-03 13:03 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Heikki Linnakangas @ 2026-04-02 16:44 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On 02/04/2026 19:13, Ashutosh Bapat wrote:
> On Thu, Apr 2, 2026 at 7:44 PM Heikki Linnakangas <[email protected]> wrote:
>>
>>> I think we should highlight the change in default in the release notes
>>> though. The users which use default configuration will notice an
>>> increase in the memory. If they are using a custom value, they will
>>> think of bumping it up. Can we give them some ballpark % by which they
>>> should increase their max_locks_per_transaction? E.g. double the
>>> number or something?
>>
>> I don't think people who are using the defaults will notice. I'm worried
>> about the people who have set max_locks_per_transactions manually, and
>> now effectively get less lock space for the same setting. Yeah, doubling
>> the previous value is a good rule of thumb.
>
> Users who have set max_locks_per_transaction to a non-default value
> but have tuned their application to respect those limits are safe even
> after this change, since their lock hash table never used wiggle room.
> Those users who weren't careful to respect those limits will need to
> bump their setting.
That's technically true, but in practice it's very hard for someone to
carefully tune the setting. It's difficult to know how many locks a
particular set of queries take. In practice what people do is they bump
up the setting if the get the "out of shared memory" error, until the
error goes away. If you do the tuning that way, it's quite possible that
you are relying the "wiggle room" without realizing it.
> I think the release notes should "nudge" all the
> users who use non-default max_locks_per_transaction to increase it if
> they see "out of memory" errors. I don't think it should provide a
> blanket advise to double their locks
How about:
"If you had previously set max_locks_per_transaction, you might need to
set it to a higher value in v19 to avoid "out of shared memory" errors.
If you are unsure what to set it to and don't mind the increased memory
usage, you can double the value to ensure that you can acquire at least
as many locks as before"
TODO: do some more calculations and testing of how exactly the doubling
rule works with different values. Is it guaranteed to be enough in all
cases?
- Heikki
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 12:55 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 14:14 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 16:13 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 16:44 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
@ 2026-04-03 13:03 ` Ashutosh Bapat <[email protected]>
2026-04-03 17:32 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Ashutosh Bapat @ 2026-04-03 13:03 UTC (permalink / raw)
To: Heikki Linnakangas <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>
On Thu, Apr 2, 2026 at 10:15 PM Heikki Linnakangas <[email protected]> wrote:
>
> On 02/04/2026 19:13, Ashutosh Bapat wrote:
> > On Thu, Apr 2, 2026 at 7:44 PM Heikki Linnakangas <[email protected]> wrote:
> >>
> >>> I think we should highlight the change in default in the release notes
> >>> though. The users which use default configuration will notice an
> >>> increase in the memory. If they are using a custom value, they will
> >>> think of bumping it up. Can we give them some ballpark % by which they
> >>> should increase their max_locks_per_transaction? E.g. double the
> >>> number or something?
> >>
> >> I don't think people who are using the defaults will notice. I'm worried
> >> about the people who have set max_locks_per_transactions manually, and
> >> now effectively get less lock space for the same setting. Yeah, doubling
> >> the previous value is a good rule of thumb.
> >
> > Users who have set max_locks_per_transaction to a non-default value
> > but have tuned their application to respect those limits are safe even
> > after this change, since their lock hash table never used wiggle room.
> > Those users who weren't careful to respect those limits will need to
> > bump their setting.
>
> That's technically true, but in practice it's very hard for someone to
> carefully tune the setting. It's difficult to know how many locks a
> particular set of queries take. In practice what people do is they bump
> up the setting if the get the "out of shared memory" error, until the
> error goes away. If you do the tuning that way, it's quite possible that
> you are relying the "wiggle room" without realizing it.
>
That's true.
> > I think the release notes should "nudge" all the
> > users who use non-default max_locks_per_transaction to increase it if
> > they see "out of memory" errors. I don't think it should provide a
> > blanket advise to double their locks
>
> How about:
>
> "If you had previously set max_locks_per_transaction, you might need to
> set it to a higher value in v19 to avoid "out of shared memory" errors.
> If you are unsure what to set it to and don't mind the increased memory
> usage, you can double the value to ensure that you can acquire at least
> as many locks as before"
The wiggle room is 100KB fixed + 10% of other two structures, so value
by which it should be increased is partly fixed and partly a multiple
of current value. "double the value" is simplest advice we can give.
+1.
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 12:55 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 14:14 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 16:13 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 16:44 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-03 13:03 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
@ 2026-04-03 17:32 ` Heikki Linnakangas <[email protected]>
2026-04-03 23:58 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Heikki Linnakangas @ 2026-04-03 17:32 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>; Matthias van de Meent <[email protected]>
On 03/04/2026 16:03, Ashutosh Bapat wrote:
> On Thu, Apr 2, 2026 at 10:15 PM Heikki Linnakangas <[email protected]> wrote:
>>> I think the release notes should "nudge" all the
>>> users who use non-default max_locks_per_transaction to increase it if
>>> they see "out of memory" errors. I don't think it should provide a
>>> blanket advise to double their locks
>>
>> How about:
>>
>> "If you had previously set max_locks_per_transaction, you might need to
>> set it to a higher value in v19 to avoid "out of shared memory" errors.
>> If you are unsure what to set it to and don't mind the increased memory
>> usage, you can double the value to ensure that you can acquire at least
>> as many locks as before"
>
> The wiggle room is 100KB fixed + 10% of other two structures, so value
> by which it should be increased is partly fixed and partly a multiple
> of current value. "double the value" is simplest advice we can give.
> +1.
Ok, committed these patches to remove the safety margins, make LOCK and
PROCLOCK fixed-size, and change the default to
max_locks_per_transaction=128. I will do one final self-review of the
remaining earlier patches from this thread next; I believe they're ready
to be committed too.
Thanks for the review!
- Heikki
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Shared hash table allocations
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 12:55 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 14:14 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 16:13 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-02 16:44 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-03 13:03 ` Re: Shared hash table allocations Ashutosh Bapat <[email protected]>
2026-04-03 17:32 ` Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
@ 2026-04-03 23:58 ` Heikki Linnakangas <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Heikki Linnakangas @ 2026-04-03 23:58 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: Tomas Vondra <[email protected]>; pgsql-hackers; Robert Haas <[email protected]>; Rahila Syed <[email protected]>; Matthias van de Meent <[email protected]>
On 03/04/2026 20:32, Heikki Linnakangas wrote:
> On 03/04/2026 16:03, Ashutosh Bapat wrote:
>> On Thu, Apr 2, 2026 at 10:15 PM Heikki Linnakangas <[email protected]>
>> wrote:
>>>> I think the release notes should "nudge" all the
>>>> users who use non-default max_locks_per_transaction to increase it if
>>>> they see "out of memory" errors. I don't think it should provide a
>>>> blanket advise to double their locks
>>>
>>> How about:
>>>
>>> "If you had previously set max_locks_per_transaction, you might need to
>>> set it to a higher value in v19 to avoid "out of shared memory" errors.
>>> If you are unsure what to set it to and don't mind the increased memory
>>> usage, you can double the value to ensure that you can acquire at least
>>> as many locks as before"
>>
>> The wiggle room is 100KB fixed + 10% of other two structures, so value
>> by which it should be increased is partly fixed and partly a multiple
>> of current value. "double the value" is simplest advice we can give.
>> +1.
>
> Ok, committed these patches to remove the safety margins, make LOCK and
> PROCLOCK fixed-size, and change the default to
> max_locks_per_transaction=128. I will do one final self-review of the
> remaining earlier patches from this thread next; I believe they're ready
> to be committed too.
>
> Thanks for the review!
And committed the rest of the patches from this thread now too, after
some small fixes and cleanups. Thanks again!
- Heikki
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-04-03 23:58 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-31 21:25 Re: Shared hash table allocations Heikki Linnakangas <[email protected]>
2026-04-02 10:24 ` Matthias van de Meent <[email protected]>
2026-04-02 11:52 ` Heikki Linnakangas <[email protected]>
2026-04-02 13:47 ` Matthias van de Meent <[email protected]>
2026-04-02 12:55 ` Ashutosh Bapat <[email protected]>
2026-04-02 14:14 ` Heikki Linnakangas <[email protected]>
2026-04-02 14:52 ` Ashutosh Bapat <[email protected]>
2026-04-02 16:13 ` Ashutosh Bapat <[email protected]>
2026-04-02 16:44 ` Heikki Linnakangas <[email protected]>
2026-04-03 13:03 ` Ashutosh Bapat <[email protected]>
2026-04-03 17:32 ` Heikki Linnakangas <[email protected]>
2026-04-03 23:58 ` Heikki Linnakangas <[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