MIME-Version: 1.0
References: <19505-7ec445dffe2732d2@postgresql.org>
 <CAK-MWwR6vm=0yg9wTovhsTUcSRnHJrBZ2X-d-KHBtyRM0j=qaw@mail.gmail.com>
In-Reply-To: 
 <CAK-MWwR6vm=0yg9wTovhsTUcSRnHJrBZ2X-d-KHBtyRM0j=qaw@mail.gmail.com>
From: Maxim Boguk <maxim.boguk@gmail.com>
Date: Mon, 22 Jun 2026 23:22:53 +0300
Message-ID: 
 <CAK-MWwRVb7Lz14uJNeiggM8O15Y=QLRny9evxec2Pquu5+DwBg@mail.gmail.com>
Subject: Re: BUG #19505: Some weird spikes postgresql processes in database
 (up to 200k sometime) without apparent reasons.
To: maxim.boguk@gmail.com, pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006a68800654dd6af2"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK-MWwRVb7Lz14uJNeiggM8O15Y%3DQLRny9evxec2Pquu5%2BDwBg%40mail.gmail.com>
Precedence: bulk

--0000000000006a68800654dd6af2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Jun 2, 2026 at 9:51=E2=80=AFPM Maxim Boguk <maxim.boguk@gmail.com> =
wrote:

>
>
> On Tue, Jun 2, 2026 at 9:37=E2=80=AFPM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference:      19505
>> Logged by:          Maxim Boguk
>> Email address:      maxim.boguk@gmail.com
>> PostgreSQL version: 18.4
>> Operating system:   Ubuntu 24.04.4 LTS
>> Description:
>>
>> I started investigation of this issue after found that process count of
>> postgresql on my replica sometime jump to 200k+ (with max_connections=3D=
1000
>> and real connections under 100 most time).
>> Somehow single (seems random by always heavy/analytical) query spawn
>> thousands of the threads and tens thousands of parallel workers.
>>
>> After some logging I caught one snapshot (ps -u postgres -L -o
>> pid,tid,ppid,lstart,args -ww 2 ) with 39257 processes:
>>
>> [postgres@db ~/tmp]$ zcat ps-L-2026-06-02_17-40-22.gz | wc -l
>> 39257
>>
>> Main content is:
>> PID          TID               PPID  StartTime
>> command
>> 2158552 2158552  948705 Tue Jun  2 17:40:17 2026 postgres: 18/main:
>> background_shared db [local] SELECT
>>
>> Then:
>> The same PID but 1620 different TIDS.
>> PID          TID               PPID  StartTime
>> command
>> #main process
>> 2158557 2158557  948705 Tue Jun  2 17:40:18 2026 postgres: 18/main:
>> background_shared db [local] SELECT
>> #1620 threads
>> 2158557 2158607  948705 Tue Jun  2 17:40:20 2026 postgres: 18/main:
>> background_shared db [local] SELECT
>> 2158557 2158608  948705 Tue Jun  2 17:40:20 2026 postgres: 18/main:
>> background_shared db [local] SELECT
>> 2158557 2158609  948705 Tue Jun  2 17:40:20 2026 postgres: 18/main:
>> background_shared db [local] SELECT
>>
>> Then, 37571 rows!!! of:
>> PID          TID               PPID  StartTime
>> command
>> 2158579 2159176  948705 Tue Jun  2 17:40:20 2026 postgres: 18/main:
>> parallel
>> worker for PID 2158557
>> 2158579 2159179  948705 Tue Jun  2 17:40:20 2026 postgres: 18/main:
>> parallel
>> worker for PID 2158557
>> 2158579 2159183  948705 Tue Jun  2 17:40:20 2026 postgres: 18/main:
>> parallel
>> worker for PID 2158557
>> 2158579 2159196  948705 Tue Jun  2 17:40:20 2026 postgres: 18/main:
>> parallel
>> worker for PID 2158557
>> 2158579 2159198  948705 Tue Jun  2 17:40:20 2026 postgres: 18/main:
>> parallel
>> worker for PID 2158557
>> 2158579 2159202  948705 Tue Jun  2 17:40:20 2026 postgres: 18/main:
>> parallel
>> worker for PID 2158557
>>
>> I double checked the query (it had been logged in database log): it run
>> with
>> 6 worker processes and without any issues on manual run.
>>
>> Related db configuration:
>> max_connections =3D 1000
>> max_worker_processes =3D 128              # (change requires restart)
>> max_parallel_workers_per_gather =3D 16    # limited by max_parallel_work=
ers
>> max_parallel_workers =3D 64
>> io_method =3D io_uring                    # worker, io_uring, sync
>> io_max_concurrency =3D -1         # Max number of IOs that one process
>> jit =3D on (usual suspect in case of weird things going on)
>>
>> Given that situation happens like 1-10 times per hour (and lead for shor=
t
>> LA
>> spikes up to 10000) - it's seriously affect the database replica
>> performance.
>>
>> No external/non-standard/C extensions except of pgq and postgis loaded
>> into
>> the database.
>>
>> I can look for any additional information and  perform any local researc=
h
>> but currently I'm out of ideas what my next steps should be.
>>
>> PS: it's seems that the issue could be triggered by different queries, b=
ut
>> not the one particular
>
>

Update:  issue had been triggered by unconstrained spawn of helper threads
for io_method=3Dio_uring
(thousands/ten thousands of helper "iou-wrk-****" threads per bitmap scan).
Switching to the io_method=3Dworker fixed problem.

Seems io_uring have some unexpected issues with unconstrained threads spawn=
.


--=20
Maxim Boguk
Senior Postgresql DBA

Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

--0000000000006a68800654dd6af2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:monospace,monospace;font-size:large"><br></div></div><br><div clas=
s=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_att=
r">On Tue, Jun 2, 2026 at 9:51=E2=80=AFPM Maxim Boguk &lt;<a href=3D"mailto=
:maxim.boguk@gmail.com">maxim.boguk@gmail.com</a>&gt; wrote:<br></div><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"l=
tr"><div style=3D"font-family:monospace,monospace;font-size:large"><br></di=
v></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr=
">On Tue, Jun 2, 2026 at 9:37=E2=80=AFPM PG Bug reporting form &lt;<a href=
=3D"mailto:noreply@postgresql.org" target=3D"_blank">noreply@postgresql.org=
</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
The following bug has been logged on the website:<br>
<br>
Bug reference:=C2=A0 =C2=A0 =C2=A0 19505<br>
Logged by:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Maxim Boguk<br>
Email address:=C2=A0 =C2=A0 =C2=A0 <a href=3D"mailto:maxim.boguk@gmail.com"=
 target=3D"_blank">maxim.boguk@gmail.com</a><br>
PostgreSQL version: 18.4<br>
Operating system:=C2=A0 =C2=A0Ubuntu 24.04.4 LTS<br>
Description:=C2=A0 =C2=A0 =C2=A0 =C2=A0 <br>
<br>
I started investigation of this issue after found that process count of<br>
postgresql on my replica sometime jump to 200k+ (with max_connections=3D100=
0<br>
and real connections under 100 most time).<br>
Somehow single (seems random by always heavy/analytical) query spawn<br>
thousands of the threads and tens thousands of parallel workers.<br>
<br>
After some logging I caught one snapshot (ps -u postgres -L -o<br>
pid,tid,ppid,lstart,args -ww 2 ) with 39257 processes:<br>
<br>
[postgres@db ~/tmp]$ zcat ps-L-2026-06-02_17-40-22.gz | wc -l<br>
39257<br>
<br>
Main content is:<br>
PID=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 TID=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0PPID=C2=A0 StartTime<br>
command<br>
2158552 2158552=C2=A0 948705 Tue Jun=C2=A0 2 17:40:17 2026 postgres: 18/mai=
n:<br>
background_shared db [local] SELECT<br>
<br>
Then:<br>
The same PID but 1620 different TIDS.<br>
PID=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 TID=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0PPID=C2=A0 StartTime<br>
command<br>
#main process<br>
2158557 2158557=C2=A0 948705 Tue Jun=C2=A0 2 17:40:18 2026 postgres: 18/mai=
n:<br>
background_shared db [local] SELECT<br>
#1620 threads<br>
2158557 2158607=C2=A0 948705 Tue Jun=C2=A0 2 17:40:20 2026 postgres: 18/mai=
n:<br>
background_shared db [local] SELECT<br>
2158557 2158608=C2=A0 948705 Tue Jun=C2=A0 2 17:40:20 2026 postgres: 18/mai=
n:<br>
background_shared db [local] SELECT<br>
2158557 2158609=C2=A0 948705 Tue Jun=C2=A0 2 17:40:20 2026 postgres: 18/mai=
n:<br>
background_shared db [local] SELECT<br>
<br>
Then, 37571 rows!!! of:<br>
PID=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 TID=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0PPID=C2=A0 StartTime<br>
command<br>
2158579 2159176=C2=A0 948705 Tue Jun=C2=A0 2 17:40:20 2026 postgres: 18/mai=
n: parallel<br>
worker for PID 2158557<br>
2158579 2159179=C2=A0 948705 Tue Jun=C2=A0 2 17:40:20 2026 postgres: 18/mai=
n: parallel<br>
worker for PID 2158557<br>
2158579 2159183=C2=A0 948705 Tue Jun=C2=A0 2 17:40:20 2026 postgres: 18/mai=
n: parallel<br>
worker for PID 2158557<br>
2158579 2159196=C2=A0 948705 Tue Jun=C2=A0 2 17:40:20 2026 postgres: 18/mai=
n: parallel<br>
worker for PID 2158557<br>
2158579 2159198=C2=A0 948705 Tue Jun=C2=A0 2 17:40:20 2026 postgres: 18/mai=
n: parallel<br>
worker for PID 2158557<br>
2158579 2159202=C2=A0 948705 Tue Jun=C2=A0 2 17:40:20 2026 postgres: 18/mai=
n: parallel<br>
worker for PID 2158557<br>
<br>
I double checked the query (it had been logged in database log): it run wit=
h<br>
6 worker processes and without any issues on manual run.<br>
<br>
Related db configuration:<br>
max_connections =3D 1000<br>
max_worker_processes =3D 128=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 # (change requires restart)<br>
max_parallel_workers_per_gather =3D 16=C2=A0 =C2=A0 # limited by max_parall=
el_workers<br>
max_parallel_workers =3D 64<br>
<span class=3D"gmail_default" style=3D"font-family:monospace,monospace;font=
-size:large"></span>io_method =3D io_uring=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 # worker, io_uring, sync<br>
io_max_concurrency =3D -1=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0# Max number of =
IOs that one process<br>
jit =3D on (usual suspect in case of weird things going on)<br>
<br>
Given that situation happens like 1-10 times per hour (and lead for short L=
A<br>
spikes up to 10000) - it&#39;s seriously affect the database replica<br>
performance.<br>
<br>
No external/non-standard/C extensions except of pgq and postgis loaded into=
<br>
the database.<br>
<br>
I can look for any additional information and=C2=A0 perform any local resea=
rch<br>
but currently I&#39;m out of ideas what my next steps should be.<br>
<br>
PS: it&#39;s seems that the issue could be triggered by different queries, =
but<br>
not the one particular</blockquote></div></div></blockquote><div><br></div>=
<div><br></div><div style=3D"font-family:monospace,monospace;font-size:larg=
e" class=3D"gmail_default">Update:=C2=A0 issue had been triggered by uncons=
trained spawn of helper threads for=C2=A0io_method=3Dio_uring</div><div sty=
le=3D"font-family:monospace,monospace;font-size:large" class=3D"gmail_defau=
lt">(thousands/ten thousands of helper=C2=A0&quot;<span style=3D"white-spac=
e:pre-wrap">iou-wrk-****&quot; </span>threads=C2=A0per bitmap scan).</div><=
div style=3D"font-family:monospace,monospace;font-size:large" class=3D"gmai=
l_default">Switching to the io_method=3Dworker fixed problem.</div><div sty=
le=3D"font-family:monospace,monospace;font-size:large" class=3D"gmail_defau=
lt"><br></div><div style=3D"font-family:monospace,monospace;font-size:large=
" class=3D"gmail_default">Seems io_uring have some unexpected issues with u=
nconstrained threads spawn.</div><div style=3D"font-family:monospace,monosp=
ace;font-size:large" class=3D"gmail_default"><br></div><div style=3D"font-f=
amily:monospace,monospace;font-size:large" class=3D"gmail_default"><br></di=
v></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"lt=
r" class=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><d=
iv dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D=
"ltr"><div><div dir=3D"ltr"><span style=3D"font-family:monospace,monospace"=
>Maxim Boguk<br>Senior Postgresql DBA<br><br>Phone UA: +380 99 143 0000<br>=
Phone AU: +61=C2=A0 45 218 5678<br></span><br></div></div></div></div></div=
></div></div></div></div></div></div></div></div></div></div>

--0000000000006a68800654dd6af2--