Init connection time grows quadratically

Sorry, I forgot to add the table and graph for point #8
The graph is attached. 
This is the table:
--------------------------------------------------
| Number of clients  |  Average init time, ms |
--------------------------------------------------
|            1024             |        ~435 +-20               |
|            2048            |        ~1062 +-20              |
|            4096            |        ~3284 +-40             |
|            8192             |        ~11617 +-120           |
|            16384           |        ~43391 +-230          |
--------------------------------------------------
 
Best regards,
Alexander Potapov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Tue, 27 May 2025 at 12:45, Потапов Александр
<a.potapov@postgrespro.ru> wrote:

Hello!

I ran some experiments with pgbench to measure the initialization time and found that the time increases quadratically with the number of clients. It was surprising to me and I would like to understand a reason of such behavior.

Some details on how it was done:

[...]

It turned out that the results correspond to a quadratic dependence like y ~ 0.0002x^2 where x is a number of clients and y is init time (ms).
Here there is a question: is it expected behavior or a bug? What do you think? I appreciate any comments and opinions.

Note that the value of "initial connection time" is based on the time
it takes from about the start of the pg_bench process until the moment
all N expected connections have been established, *not* the average
time it took pg_bench to connect to PostgreSQL. This does also not
exclude other known measurable delays (like spawning threads,
synchronization, etc), so the actual per-connection connection time is
probably closer to O(n) than O(n^2).

Q: Did you check that pgbench or the OS does not have
O(n_active_connections) or O(n_active_threads) overhead per worker
during thread creation or connection establishment, e.g. by varying
the number of threads used to manage these N clients? I wouldn't be
surprised if there are inefficiencies in e.g. the threading- or
synchronization model that cause O(N) per-thread overhead, or O(N^2)
overall when you have one thread per connection.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

Hi Matthias,
 
I did additional experiments (changed a number of threads on pgbench side an on server side) but it did find not chanches.
 

Вторник, Май 27, 2025 14:36 MSK, Matthias van de Meent <boekewurm+postgres@gmail.com> писал(а):

 
On Tue, 27 May 2025 at 12:45, Потапов Александр
<a.potapov@postgrespro.ru> wrote:

Hello!

I ran some experiments with pgbench to measure the initialization time and found that the time increases quadratically with the number of clients. It was surprising to me and I would like to understand a reason of such behavior.

Some details on how it was done:

[...]

It turned out that the results correspond to a quadratic dependence like y ~ 0.0002x^2 where x is a number of clients and y is init time (ms).
Here there is a question: is it expected behavior or a bug? What do you think? I appreciate any comments and opinions.

Note that the value of "initial connection time" is based on the time
it takes from about the start of the pg_bench process until the moment
all N expected connections have been established, *not* the average
time it took pg_bench to connect to PostgreSQL. This does also not
exclude other known measurable delays (like spawning threads,
synchronization, etc), so the actual per-connection connection time is
probably closer to O(n) than O(n^2).

Q: Did you check that pgbench or the OS does not have
O(n_active_connections) or O(n_active_threads) overhead per worker
during thread creation or connection establishment, e.g. by varying
the number of threads used to manage these N clients? I wouldn't be
surprised if there are inefficiencies in e.g. the threading- or
synchronization model that cause O(N) per-thread overhead, or O(N^2)
overall when you have one thread per connection.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

To be more precise I used constant number of threads (128 and 1024) to compare with previous results. The quadratic dependency exists everywhere, see new graph.

Q: Did you check that pgbench or the OS does not have
O(n_active_connections) or O(n_active_threads) overhead per worker
during thread creation or connection establishment, e.g. by varying
the number of threads used to manage these N clients? I wouldn't be
surprised if there are inefficiencies in e.g. the threading- or
synchronization model that cause O(N) per-thread overhead, or O(N^2)
overall when you have one thread per connection.

On 6/16/25 11:56, Потапов Александр wrote:

To be more precise I used constant number of threads (128 and 1024) to
compare with previous results. The quadratic dependency exists
everywhere, see new graph.

Q: Did you check that pgbench or the OS does not have
O(n_active_connections) or O(n_active_threads) overhead per worker
during thread creation or connection establishment, e.g. by varying
the number of threads used to manage these N clients? I wouldn't be
surprised if there are inefficiencies in e.g. the threading- or
synchronization model that cause O(N) per-thread overhead, or O(N^2)
overall when you have one thread per connection.

Hi, all!

I've investigated slightly different scenario then Alexander and I want
share my thoughts in this thread too.

I found that when we run pgbench scenarios sequantially, without
postgres restart between iterations, initial time degrades from launch
to launch and eventually it stabilizes at the worst values then first
run(ICT_degradation.png attached).

Scenario details:

1. postgres rev 1a51ec16db7, 0001-Fix-fast-connection-rate-issue.patch
was applied

2. numa was disabled, swap was off, ramdisk was used for binaries and
pg_data,Hyperthreading and cpu boost were disabled

3. server was built with -02

4.Add to the postgresql.conf:
    huge_pages = off #for the sake of test stability and reproducibility
    shared_buffers = 1GB
    max_connections = 16384

5. Cycle iteration

psql -U postgres -c 'create database bench'

pgbench -U postgres -i -s100 bench

psql -U postgres -d bench -c "checkpoint"

pgbench -U postgres -c${clients} -t100 -j6 -S bench

psql -U postgres -c "DROP DATABASE bench"

Where ${clients} is one of 512/1024/2048/4096/8192

I paid attention that ICT for the first iteration much better than for
next ones. I investigated this behavior a little bit and found a lot of
minor page fault events in ProcArrayAdd method(perf_without_patch-j6.txt
attached) for code line

allProcs[procno].pgxactoff = index;

So, every proc.pgxactoff access generate page fault, because proc
objects accessed in memory randomly and page replacement can occur. I
have some ideas how to improve this - it seems we can put array
of pgxactoff separately

in shmem to have only few hot pages for them. I've attached appropriate
patch(0001-This-patch-reduce-connection-init-close-time.patch). Perf
with minor faults for updated version also was
attached(perf_with_patch-j6.txt attached),

as we can see, patched version fixes this. I made a series of
measurements for all versions and attached comparison
chart(ICT_degradation_with_patch.png attached). Also I add the table
with results

without_patch_first_iteration, average ICT, ms
without_patch_after_warmup, average ICT, ms with_patch_first_iteration,
average ICT, ms with_patch_after_warmup, average ICT, ms
512 ~480+-15 ~500+-10 ~470+-15 ~215 +-15
1024 ~920+-20 ~1000+-20 ~900+-15 ~920 +-20
2048 ~1780+-30 ~2240+-20 ~1760+-30 ~1800 +-30
4096 ~3570+-50 ~6140+-40 ~3450+-130 ~3740 +-60
8192 ~7440+-90 ~18840+-140 ~7440+-140 ~8100+-80

As we can see from attached charts, from one side there is no
degradation for the first iterations for patched and no patched
versions. From another side patched version is much better for next ones.

I've tried the same tests for different values of pgbench -j parameter
to avoid effects of multithreading pgbench nature and got the same picture.

I hope it will be interesting and helpful.

Best regards,

Maksim Melnikov

On Wed, 3 Jun 2026 at 08:33, Maksim.Melnikov <m.melnikov@postgrespro.ru> wrote:

On 6/16/25 11:56, Потапов Александр wrote:

To be more precise I used constant number of threads (128 and 1024) to compare with previous results. The quadratic dependency exists everywhere, see new graph.

Q: Did you check that pgbench or the OS does not have
O(n_active_connections) or O(n_active_threads) overhead per worker
during thread creation or connection establishment, e.g. by varying
the number of threads used to manage these N clients? I wouldn't be
surprised if there are inefficiencies in e.g. the threading- or
synchronization model that cause O(N) per-thread overhead, or O(N^2)
overall when you have one thread per connection.

Hi, all!

I've investigated slightly different scenario then Alexander and I want share my thoughts in this thread too.

I found that when we run pgbench scenarios sequantially, without postgres restart between iterations, initial time degrades from launch to launch and eventually it stabilizes at the worst values then first run(ICT_degradation.png attached).

Scenario details:

[...]

4.Add to the postgresql.conf:
huge_pages = off #for the sake of test stability and reproducibility

I think this is the main culprit of the extreme slowdown -- without
huge pages, you're effectively guaranteed to get many minor page
faults, and with it the relevant TLB miss rates. With huge pages
enabled, the proc array should fit on one (or just a few) memory
pages.

We're not generally in the business for optimizing workloads that have
huge_pages=off.

I paid attention that ICT for the first iteration much better than for next ones. I investigated this behavior a little bit and found a lot of minor page fault events in ProcArrayAdd method(perf_without_patch-j6.txt attached) for code line

allProcs[procno].pgxactoff = index;

So, every proc.pgxactoff access generate page fault, because proc objects accessed in memory randomly and page replacement can occur. I have some ideas how to improve this - it seems we can put array of pgxactoff separately

page replacement can occur

I doubt that this is an issue. Page tables are not removed until the
mapping is removed, and it is highly unlikely that hot shmem areas
(like the PGPROC array) are ever swapped out. It's just that with
smaller memory pages the OS will have to create more page mappings for
the same amount of shared memory, and that'll take more resources
(cpu, memory, time) than it would with large (or huge) memory pages.

in shmem to have only few hot pages for them. I've attached appropriate patch(0001-This-patch-reduce-connection-init-close-time.patch). Perf with minor faults for updated version also was attached(perf_with_patch-j6.txt attached),

I see. Despite your argument hinging on small pages, I think there is
still some benefit to using a dense array instead of PGPROC.pgxactoff:
With a dense array, ProcArrayAdd/ProcArrayRemove need to touch fewer
cache lines, which are also less likely to be recently dirtied by
unrelated shared proc updates.

However, I'm now a bit more concerned about the number of indirections
required for other operations. Before, accessing pgxactoff was an
offset off of the PgProc pointer, but with this patch getting its
value is a bit more involved.

as we can see, patched version fixes this. I made a series of measurements for all versions and attached comparison chart(ICT_degradation_with_patch.png attached). Also I add the table with results

Do you happen to have data with huge_pages enabled?

I hope it will be interesting and helpful.

Definitely interesting. I'm not so sure it's as effective on a
production configuration (with huge pages enabled), but I'm definitely
interested in seeing test results.

----

Some comments on the patch:

+++ b/src/backend/storage/lmgr/proc.c
+    size = add_size(size, mul_size(TotalProcs, sizeof(int)));

Let's use the following, to fit the surrounding pattern:

+ size = add_size(size, mul_size(TotalProcs,
sizeof(*ProcGlobal->pgxactoffs)));

@@ -273,7 +274,10 @@ ProcGlobalShmemInit(void *arg)
ProcGlobal->statusFlags = (uint8 *) ptr;
ptr = ptr + (TotalProcs * sizeof(*ProcGlobal->statusFlags));

-    /* make sure we didn't overflow */
+    ProcGlobal->pgxactoffs = (int *) ptr;
+    ptr = (char *) ptr + TotalProcs * sizeof(int);
+
+    /* make sure wer didn't overflow */
Assert((ptr > (char *) procs) && (ptr <= (char *) procs + requestSize));

This needs to be updated, because right now it fails to account for
alignment when (TotalProcs * sizeof(statusFlags)) is not a multiple of
sizeof(int). The other fields take care to be correctly aligned, but
your code doesn't do that yet. It's probably best to allocate and
assign *pgxactoffs just ahead of statusFlags.

+ /* make sure wer didn't overflow */

New typo introduced.

+++ b/src/include/storage/proc.h
+#define GetXactOffPGProc(proc) (ProcGlobal->pgxactoffs[(proc) - &ProcGlobal->allProcs[0]])
+#define GetMyXactOffPGProc() (GetXactOffPGProc(MyProc))

I'd replace this with

+#define ProcGetXactOff(procno) (ProcGlobal->pgxactoffs[(procno)])
+#define ProcGetMyXactOff() (GetXactOffPGProc(MyProcNo))

So that callers can use GetNumberFromPGProc() manually if they need
to, but the offset-based calculations of Proc-to-Number are avoided
when that is possible.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

On 6/3/26 16:35, Matthias van de Meent wrote:

On Wed, 3 Jun 2026 at 08:33, Maksim.Melnikov<m.melnikov@postgrespro.ru> wrote:

On 6/16/25 11:56, Потапов Александр wrote:

To be more precise I used constant number of threads (128 and 1024) to compare with previous results. The quadratic dependency exists everywhere, see new graph.

Q: Did you check that pgbench or the OS does not have
O(n_active_connections) or O(n_active_threads) overhead per worker
during thread creation or connection establishment, e.g. by varying
the number of threads used to manage these N clients? I wouldn't be
surprised if there are inefficiencies in e.g. the threading- or
synchronization model that cause O(N) per-thread overhead, or O(N^2)
overall when you have one thread per connection.

Hi, all!

I've investigated slightly different scenario then Alexander and I want share my thoughts in this thread too.

I found that when we run pgbench scenarios sequantially, without postgres restart between iterations, initial time degrades from launch to launch and eventually it stabilizes at the worst values then first run(ICT_degradation.png attached).

Scenario details:

[...]

4.Add to the postgresql.conf:
huge_pages = off #for the sake of test stability and reproducibility

I think this is the main culprit of the extreme slowdown -- without
huge pages, you're effectively guaranteed to get many minor page
faults, and with it the relevant TLB miss rates. With huge pages
enabled, the proc array should fit on one (or just a few) memory
pages.

We're not generally in the business for optimizing workloads that have
huge_pages=off.

Yes, I agree, huge_pages=off is not  common setup now. My motivation was
that even if some configuration isn't commonly used, it does not mean
that it isn't interesting for someone else at all and, as a consequence,
it can be optimized without degradation for basic scenarios . Moreover,
huge_pages = try is the default value, so with huge_pages set to try,
the server will try to request huge pages, but fall back to the
huge_page=off if that fails. As I know on linux default value
for  vm.nr_hugepages = 0, this means that by default, the os does not
use HugeTLB pages. Of course, DBA should setup this, but on practice
they can miss this. Anyway, if community isn't interested in such kinds
of optimizations, it is ok. It was interesting and educational
investigation for me, thanks for your help.

.....

as we can see, patched version fixes this. I made a series of measurements for all versions and attached comparison chart(ICT_degradation_with_patch.png attached). Also I add the table with results

Do you happen to have data with huge_pages enabled?

I hope it will be interesting and helpful.

Definitely interesting. I'm not so sure it's as effective on a
production configuration (with huge pages enabled), but I'm definitely
interested in seeing test results.

I've made comparative measurements for configurations with huge_pages =
on/off. Please, you can check results below.

Clients number *Huge-pages-off-with-patch*
Huge-pages-off-without-patch Huge-pages-on-with-patch
Huge-pages-on-without-patch
512 ~480 +- 3.5% ms ~490 +- 3% ms ~420 +- 3.5% ms ~420+-3.5% ms
1024 ~910 +- 1.3% ms ~990 +- 2% ms ~790 +- 1.7% ms ~800+-1.8% ms
2048 ~1810 +- 1.4% ms ~2230 +- 0.9% ms ~1540 +- 0.7% ms ~1530 +-
1.4% ms
4096 ~3690 +- 1.9% ms ~6060 +- 0.8% ms ~3070 +- 0.6% ms ~3070 +-
0.9% ms
8192 ~9900 +- 0.6% ms ~18530 +- 0.4% ms ~6220 +- 0.7% ms ~6230 +-
0.7% ms

Also comparison chart is attached.

As we can see the measurements prove patch efficiency for configuration
with huge_page=off(the same result as in previous message), but for
huge_pages=on I've got the same results for both versions, no
improvement and no degradation.

----

Some comments on the patch:

Patch with fixes was attached. Thanks for review.

Best regards,

Maksim Melnikov

On Thu, 11 Jun 2026 at 16:31, Maksim.Melnikov <m.melnikov@postgrespro.ru> wrote:

Patch with fixes was attached. Thanks for review.

You removed the pgxactoff field with its comment from PGPROC, but
haven't yet added a comment on PROC_HDR's pgxactoffs. Please add a
comment to the pgxactoffs array that describes its contents. A simple
adaptation of the comment removed with PGPROC's pgxactoff field should
be sufficient.

The comments on PROC_HDR and PGPROC also reference the deleted
PGPROC->pgxactoff field, so that also must be adjusted; there may be
several other places, as I didn't do a full check on the codebase.

Once the relevant comments are added and adjusted I think this is
ready for a committer.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

On 7/17/26 11:54, Matthias van de Meent wrote:

On Thu, 11 Jun 2026 at 16:31, Maksim.Melnikov <m.melnikov@postgrespro.ru> wrote:

Patch with fixes was attached. Thanks for review.

You removed the pgxactoff field with its comment from PGPROC, but
haven't yet added a comment on PROC_HDR's pgxactoffs. Please add a
comment to the pgxactoffs array that describes its contents. A simple
adaptation of the comment removed with PGPROC's pgxactoff field should
be sufficient.

The comments on PROC_HDR and PGPROC also reference the deleted
PGPROC->pgxactoff field, so that also must be adjusted; there may be
several other places, as I didn't do a full check on the codebase.

Once the relevant comments are added and adjusted I think this is
ready for a committer.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

Sorry for the delay, was unavailable for several weeks.

Thanks for review, patch was updated.

Best regards,

Maksim Melnikov

On Fri, 7 Aug 2026 at 16:05, Maksim.Melnikov <m.melnikov@postgrespro.ru> wrote:

On 7/17/26 11:54, Matthias van de Meent wrote:

On Thu, 11 Jun 2026 at 16:31, Maksim.Melnikov <m.melnikov@postgrespro.ru> wrote:

Patch with fixes was attached. Thanks for review.

You removed the pgxactoff field with its comment from PGPROC, but
haven't yet added a comment on PROC_HDR's pgxactoffs. Please add a
comment to the pgxactoffs array that describes its contents. A simple
adaptation of the comment removed with PGPROC's pgxactoff field should
be sufficient.

The comments on PROC_HDR and PGPROC also reference the deleted
PGPROC->pgxactoff field, so that also must be adjusted; there may be
several other places, as I didn't do a full check on the codebase.

Once the relevant comments are added and adjusted I think this is
ready for a committer.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

Sorry for the delay, was unavailable for several weeks.

Thanks for review, patch was updated.

I noticed this patch wasn't registered at the commitfest yet, so I
took the liberty to do that for you. It's registered at [0]https://commitfest.postgresql.org/patch/7139/.

The CFBot then noticed crashes caused by the patch in various tests,
which I think can be caused by a lack of initialization of pgxactoffs
in ProcArrayShmemAttach, but I haven't worked on a fix.

Attached are some copy-edits of comments on top of your v3, and a
missed replacement of "GetNumberFromPGProc(MyProc)" with
"MyProcNumber".

This is an incremental patch on your v3, and doesn't fix the CFBot failures.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

[0]: https://commitfest.postgresql.org/patch/7139/

On 8/12/26 23:19, Matthias van de Meent wrote:

On Fri, 7 Aug 2026 at 16:05, Maksim.Melnikov<m.melnikov@postgrespro.ru> wrote:

On 7/17/26 11:54, Matthias van de Meent wrote:

On Thu, 11 Jun 2026 at 16:31, Maksim.Melnikov<m.melnikov@postgrespro.ru> wrote:

Patch with fixes was attached. Thanks for review.

You removed the pgxactoff field with its comment from PGPROC, but
haven't yet added a comment on PROC_HDR's pgxactoffs. Please add a
comment to the pgxactoffs array that describes its contents. A simple
adaptation of the comment removed with PGPROC's pgxactoff field should
be sufficient.

The comments on PROC_HDR and PGPROC also reference the deleted
PGPROC->pgxactoff field, so that also must be adjusted; there may be
several other places, as I didn't do a full check on the codebase.

Once the relevant comments are added and adjusted I think this is
ready for a committer.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

Sorry for the delay, was unavailable for several weeks.

Thanks for review, patch was updated.

I noticed this patch wasn't registered at the commitfest yet, so I
took the liberty to do that for you. It's registered at [0].

Thanks!

The CFBot then noticed crashes caused by the patch in various tests,
which I think can be caused by a lack of initialization of pgxactoffs
in ProcArrayShmemAttach, but I haven't worked on a fix.

Yes, thanks, there was the problem with alignment of pgxactoff array on
shmem, I've fixed it with re-ordering of
elements. Also I've rebased master and noticed new commit c6d55714ba4,
that introduced new shmem alloc functions, so I've added minor fix.
I've attached incremental patch for ease of review of last CI fixes.

Attached are some copy-edits of comments on top of your v3, and a
missed replacement of "GetNumberFromPGProc(MyProc)" with
"MyProcNumber".

This is an incremental patch on your v3, and doesn't fix the CFBot failures.

Thanks. I've attached overall patch with your edits for commitfest too.

Kind regards

Melnikov Maksim

On Tue, 25 Aug 2026 at 08:45, Maksim.Melnikov <m.melnikov@postgrespro.ru> wrote:

On 8/12/26 23:19, Matthias van de Meent wrote:

Attached are some copy-edits of comments on top of your v3, and a
missed replacement of "GetNumberFromPGProc(MyProc)" with
"MyProcNumber".

This is an incremental patch on your v3, and doesn't fix the CFBot failures.

Thanks. I've attached overall patch with your edits for commitfest too.

Thanks!

I think this is ready for a committer to take a look at, so I'll
update the CF entry accordingly.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

PS. I attached v5 again, so that CFbot will pick up the patch, without
the incremental patch that contains changes that conflict with those
in the main patch of v5.
In the future, it's useful to mark patches that you want the CFBot to
ignore with 'nocfbot' in their file name, so that the bot can test the
patches that you want it to include in automated testing.

On Wed, Sep 9, 2026 at 6:05 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:

On Tue, 25 Aug 2026 at 08:45, Maksim.Melnikov <m.melnikov@postgrespro.ru> wrote:

On 8/12/26 23:19, Matthias van de Meent wrote:

Attached are some copy-edits of comments on top of your v3, and a
missed replacement of "GetNumberFromPGProc(MyProc)" with
"MyProcNumber".

This is an incremental patch on your v3, and doesn't fix the CFBot failures.

Thanks. I've attached overall patch with your edits for commitfest too.

Thanks!

I think this is ready for a committer to take a look at, so I'll
update the CF entry accordingly.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

PS. I attached v5 again, so that CFbot will pick up the patch, without
the incremental patch that contains changes that conflict with those
in the main patch of v5.
In the future, it's useful to mark patches that you want the CFBot to
ignore with 'nocfbot' in their file name, so that the bot can test the
patches that you want it to include in automated testing.

Hi,

I dug a bit further into the startup scaling on Linux. I instrumented the
startup path with help from Codex, and the main O(N) cost I found in the
retained-connection case is:

heap_page_prune_opt()
-> GlobalVisTestIsRemovableXid()
-> GlobalVisUpdate()
-> ComputeXidHorizons()

ComputeXidHorizons() scans the live ProcArray. In my tests the horizon scan
cost about 18.75 ns per live backend.

The visibility check currently runs before the page-full/free-space check,
even when the latter would reject pruning anyway. For the pg_database page
seen during startup, there were about 7620 bytes free with an 819-byte
threshold. Master refreshed the horizon and then decided not to prune; the
pruning worker was never called.

I first tried avoiding the refresh and relying only on cached visibility state.
That removed most of the startup slope, but it also suppressed useful pruning:
HOT rates dropped and some update tests grew the heap by 33-142%. I dropped
that approach.

v6 only changes the order of the existing checks: do the page-local usefulness
check first, and only then do the refresh-capable visibility test.
allow_update = true, the cleanup lock, the locked recheck, and the pruning
worker are unchanged.

Five fresh-postmaster runs gave:

connections master v6
500 0.477s 0.467s
1000 0.958 0.933
2000 1.941 1.883
4000 3.960 3.776
8000 8.344 7.674
16000 18.061 15.509

Fitting the per-connection startup times gives about 21.70 ns/live backend on
master and 2.70 ns/live with v6. The independently measured horizon slope was
18.75 ns/live, close to the 19.00 ns/live reduction with v6.

Across 16000 retained connections, that horizon slope corresponds to about 2.4
seconds of cumulative work, close to the observed 2.55-second reduction in
total startup time.

I couldn’t reproduce the HOT/bloat problem from the cached-only experiment.
Fixed-update and 40000-update/20-VACUUM tests had the same heap sizes and HOT
rates as master, and catalog churn produced the same pruning-worker counts and
relation sizes.

There is a small concurrency difference: the unlocked page-local heuristic is
now evaluated earlier, so a concurrent page change can make v6 miss an
opportunistic prune that master might later have attempted. GlobalVisUpdate
itself does not affect the free-space predicate; the difference is only the
earlier observation relative to concurrent page changes. Pages that pass the
early check still follow the original visibility/cleanup-lock/recheck path.

v5 and this patch appear to address orthogonal costs. This patch removes the
retained-startup horizon scan, while v5 improves ProcArray renumbering locality
under backend churn. I haven’t benchmarked the combination.

For example, at 16000 retained connections in the churn workload, v5 reached
about 484 replacements/sec versus 373 with v6 and 355 on master.

One result remains unexplained: at 8000 connections, v6 reduced replacement
throughput by 4.9% versus master in all five paired runs, although it improved
again at 16000. I checked pruning-worker counts and the HOT/bloat workloads and
found no corresponding loss of useful pruning. I haven’t yet profiled that case
for CPU or lock-wait differences.

The assertion build passes make check and check-world with
xid_wraparound enabled.

Comments welcome, particularly on moving the unlocked usefulness check earlier.

Best,
Haibo

Hi,

On 2026-09-09 15:04:53 +0200, Matthias van de Meent wrote:

From a62922a732b261ccfe5c7623aa4fe0e9b18c4aeb Mon Sep 17 00:00:00 2001
From: Maksim Melnikov <m.melnikov@postgrespro.ru>
Date: Tue, 18 Nov 2025 17:20:09 +0300
Subject: [PATCH v5] This patch reduce connection init/close time.

ProcArrayRemove/ProcArrayAdd are expensive in terms of accessing pgxactoff
field in PGPROC, because its are placed on different pages and it the
reason of page_faults occurence. Now one of the use case with pgxactoff
is iteration with gaps over PGPROCs and read/write pgxactoff, PGPROC
allocate ~1KB and it quite enough to have page_faults in case of such
accessing.

So we placed all pgxactoff in the separate array pgxactoffs to have adjacent
pages for them all. Indexes in allProcs aligned with pgxactoffs array, so
the Nth element in pgxactoffs refer to Nth PGPROC.

Eventually it helps to avoid extra page_faults and reduce connection
init/close time. The main benefit is seen on configurations without huge
pages.

Maybe I am just missing something, but is this ever a benefit outside of
completely arbitrary scenarios? Who has >= 8k concurrent connections doing
nothing but connecting/disconnecting? Even if one were to agree that it's
useful to optimize large huge_pages=off workloads, surely that's an absurd
enough workload that nobody cares?

This isn't entirely free, needing to look at a separate cachelines that are
frequently modified (and thus commonly won't be in the cpu-local cache), in
reasonably common codepaths like ProcArrayEndTransactionInternal(),
TransactionIdIsInProgress(), GetSnapshotData() is far from free.

Greetings,

Andres Freund

Hi,

As CF manager, I moved this entry from Ready for Committer to Waiting on
Author.

Maybe I am just missing something, but is this ever a benefit outside of
completely arbitrary scenarios?

On Andres's question, the closest workload I have seen is an AI app
behind pgbouncer. Some transactions stay open while they wait for an LLM
response, and background loading causes transactionid and tuple lock
waits. In spikes, pgbouncer grew to about 4000 server connections, then
shrank again.

The real problem there is locks and long transactions. This patch might help
the connection storm, but may not fix it.

In addition, CFBot now tests v6, not v5. Haibo, please post v6 separately.

Thanks,
Shihao

On Fri, Sep 18, 2026 at 3:55 AM shihao zhong <zhong950419@gmail.com> wrote:

Maybe I am just missing something, but is this ever a benefit outside of
completely arbitrary scenarios?

On Andres's question, the closest workload I have seen is an AI app
behind pgbouncer. Some transactions stay open while they wait for an LLM
response, and background loading causes transactionid and tuple lock
waits. In spikes, pgbouncer grew to about 4000 server connections, then
shrank again.

The real problem there is locks and long transactions. This patch might help
the connection storm, but may not fix it.

I'm afraid PostgreSQL is not ready to deterministically handle those kind of
connection counts, but that doesnt mean people don't run such setups anyway.

I'm afraid nothing will help the connection storm until the issue is diagnosed
(root cause of what is causing it) and then stuff is (re)configured properly.
But... kind it's unrelated to this $patch itself that I saw stuff like that
happening from time to time in the field/production systems, but we are often
completley blind to connection rates and/or saturating postmaster in the first
place, so rarely people solve those issues until it's way too late. I've tried
to propose some damage control to that in [1]/messages/by-id/CAKZiRmxeTNAe_VZ9m=rt=5155mvL=CFsQ4ENJWhuUdgMe=_Udg@mail.gmail.com so at least users would know
this - at least partially for postmaster saturation cases, v3 just logs:

postmaster potentially overloaded, stats: avg 2905.00 conns/sec; 2904.00 disconns/sec; 0.00 parallel workers started/sec; CPU: user: 0.01 s, system: 0.97 s, elapsed: 1.00

Kind of related is that I've also tried to have PoC for throtthing rates
of incoming connections, but had some issues there (basically be able to
throttle to like e.g. 30 TCP new conns per sec per single IP or /24 network;
that exactly assumes you are DBA and trying to damage-control some rouge
user), but the issue there in how we throw error to the client (which AFAIR
libpq interprets as some kind of generic error and there is no way to explictly
say to the client *you've been throttled, slow down* something like HTTP/429,
because the way interpret every error as some security issue there, I can dig
more details on this if someone is interested).

-J.

[1]: /messages/by-id/CAKZiRmxeTNAe_VZ9m=rt=5155mvL=CFsQ4ENJWhuUdgMe=_Udg@mail.gmail.com

On Fri, 11 Sept 2026, 21:51 Andres Freund, <andres@anarazel.de> wrote:

Hi,

On 2026-09-09 15:04:53 +0200, Matthias van de Meent wrote:

From a62922a732b261ccfe5c7623aa4fe0e9b18c4aeb Mon Sep 17 00:00:00 2001
From: Maksim Melnikov <m.melnikov@postgrespro.ru>
Date: Tue, 18 Nov 2025 17:20:09 +0300
Subject: [PATCH v5] This patch reduce connection init/close time.

ProcArrayRemove/ProcArrayAdd are expensive in terms of accessing pgxactoff
field in PGPROC, because its are placed on different pages and it the
reason of page_faults occurence. Now one of the use case with pgxactoff
is iteration with gaps over PGPROCs and read/write pgxactoff, PGPROC
allocate ~1KB and it quite enough to have page_faults in case of such
accessing.

So we placed all pgxactoff in the separate array pgxactoffs to have adjacent
pages for them all. Indexes in allProcs aligned with pgxactoffs array, so
the Nth element in pgxactoffs refer to Nth PGPROC.

Eventually it helps to avoid extra page_faults and reduce connection
init/close time. The main benefit is seen on configurations without huge
pages.

Maybe I am just missing something, but is this ever a benefit outside of
completely arbitrary scenarios? Who has >= 8k concurrent connections doing
nothing but connecting/disconnecting? Even if one were to agree that it's
useful to optimize large huge_pages=off workloads, surely that's an absurd
enough workload that nobody cares?

Prepared transactions are also a workload that go through
ProcArrayAdd+Remove, and I don't think that such a workload (with 100s
or even 1000s of prepared xacts) is completely absurd. Uncommon,
sure, but unlike systems with multiple 1000s of connections I would
not call it absurd.

This isn't entirely free, needing to look at a separate cachelines that are
frequently modified (and thus commonly won't be in the cpu-local cache), in
reasonably common codepaths like ProcArrayEndTransactionInternal(),
TransactionIdIsInProgress(), GetSnapshotData() is far from free.

This is true, but if it is likely that the new pgxactoff cache line is
modified, then it's more likely than not that on HEAD our Proc entry's
xmin/xid cache line would've been modified instead (depending on cache
line size etc). If we're already going to look at nonlocally dirtied
cache lines, why not make sure those lines are densely packed with
dirtied information, so that the modifying backend can be done with it
sooner, and with fewer false sharing opportunities? Note that
pgxactoff is only dirtied if a proc with procnumber smaller than ours
is added or removed, or (with patch) smaller than the last procnumber
on the cacheline.

Currently, ProcArrayAdd/Remove in HEAD frequently falsely shares a
proc's own xmin/xmax, and I don't believe that that dependency is
free, either. Yes, the offsets array's cache line is more likely to
be modified because more backends' offsets fit on the cache line, and
so you trade false sharing your proc's xmin/xmax with other procs'
offsets, but I think that the overall cost is lower, because fewer
recently modified cache lines total need to be modified and moved
around the NUMA domains -- especially given that these are now only
modified by ProcArrayAdd/Remove's code, when we're already under very
heavy locks.

Kind regards,

Matthias van de Meent