MIME-Version: 1.0
References: <202602241757.6ac3iss2u4vo@alvherre.pgsql> <9116.1772009759@localhost>
In-Reply-To: <9116.1772009759@localhost>
From: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Date: Wed, 25 Feb 2026 19:25:42 +0530
Message-ID: <CAFC+b6o2yzA80YmfEhmMO9puN8qvGRvr-15BBLn3UmJxPfpr2w@mail.gmail.com>
Subject: Re: Adding REPACK [concurrently]
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>
Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>, 
	Pg Hackers <pgsql-hackers@lists.postgresql.org>, Robert Treat <rob@xzilla.net>
Content-Type: multipart/mixed; boundary="000000000000e8ba98064ba65cbb"
Archived-At: <https://www.postgresql.org/message-id/CAFC%2Bb6o2yzA80YmfEhmMO9puN8qvGRvr-15BBLn3UmJxPfpr2w%40mail.gmail.com>
Precedence: bulk

--000000000000e8ba98064ba65cbb
Content-Type: multipart/alternative; boundary="000000000000e8ba97064ba65cb9"

--000000000000e8ba97064ba65cb9
Content-Type: text/plain; charset="UTF-8"

Hello,

I did stress testing on v35 patches, where I did concurrency test using
pgbench with 50 concurrent clients, 4 threads with the below pgbench
script (dual_chaos.sql) on the following table setup(setup.sql).
I ran pgbench with 5M rows for 10 minutes and 50M for ~45 minutes
multiple times. REPACK (concurrently) ran successfully except "once"(see
below).
I created a shadow/clone table to use for checking the correctness after
doing
the concurrency test.I used 4 checks to verify that data is intact and
REPACK (concurrently) ran successfully.

1) table file OID(relfilenode) swapped?
2) bloat gone? victim relation size should be less than
shadow relation size.
3) using FULL JOIN logic (borrowed from repack.spec, with small change)
against the shadow table which goes under the same concurrent ops
done on the victim table , basically doing dual writes (see dual_chaos.sql)
to
verify table data integrity.
4) Physical Index Integrity (amcheck) (borrowed from Mihail's tests)

The concurrency test failed once. I tried to reproduce the below scenario
but no luck,i think the reason the assert failure happened because
after speculative insert there might be no spec CONFIRM or ABORT, thoughts?

TRAP: failed Assert("!specinsert"), File: "reorderbuffer.c", Line: 2610,
PID: 3956168
postgres: REPACK decoding worker for relation "stress_victim"
(ExceptionalCondition+0x98)[0xaaaab1251188]
postgres: REPACK decoding worker for relation "stress_victim"
(+0x67b1cc)[0xaaaab0f4b1cc]
postgres: REPACK decoding worker for relation "stress_victim"
(+0x67b86c)[0xaaaab0f4b86c]
postgres: REPACK decoding worker for relation "stress_victim"
(ReorderBufferCommit+0x74)[0xaaaab0f4b8f0]
postgres: REPACK decoding worker for relation "stress_victim"
(+0x66229c)[0xaaaab0f3229c]
postgres: REPACK decoding worker for relation "stress_victim"
(xact_decode+0x1a0)[0xaaaab0f312bc]
postgres: REPACK decoding worker for relation "stress_victim"
(LogicalDecodingProcessRecord+0xd4)[0xaaaab0f30e60]
postgres: REPACK decoding worker for relation "stress_victim"
(+0x3372e4)[0xaaaab0c072e4]
postgres: REPACK decoding worker for relation "stress_victim"
(+0x339634)[0xaaaab0c09634]
postgres: REPACK decoding worker for relation "stress_victim"
(RepackWorkerMain+0x1ac)[0xaaaab0c094e8]
postgres: REPACK decoding worker for relation "stress_victim"
(BackgroundWorkerMain+0x2b0)[0xaaaab0efc440]
postgres: REPACK decoding worker for relation "stress_victim"
(postmaster_child_launch+0x1f0)[0xaaaab0f00398]
postgres: REPACK decoding worker for relation "stress_victim"
(+0x639ca4)[0xaaaab0f09ca4]
postgres: REPACK decoding worker for relation "stress_victim"
(+0x639f94)[0xaaaab0f09f94]
postgres: REPACK decoding worker for relation "stress_victim"
(+0x638714)[0xaaaab0f08714]
postgres: REPACK decoding worker for relation "stress_victim"
(+0x635978)[0xaaaab0f05978]
postgres: REPACK decoding worker for relation "stress_victim"
(PostmasterMain+0x160c)[0xaaaab0f050c8]
postgres: REPACK decoding worker for relation "stress_victim"
(main+0x3dc)[0xaaaab0d974d4]
/lib/aarch64-linux-gnu/libc.so.6(+0x284c4)[0xffff867584c4]
/lib/aarch64-linux-gnu/libc.so.6(__libc_start_main+0x98)[0xffff86758598]
postgres: REPACK decoding worker for relation "stress_victim"
(_start+0x30)[0xaaaab09bc1f0]
2026-02-19 18:20:56.088 IST [3905812] LOG:  checkpoint starting: wal
2026-02-19 18:21:10.683 IST [3905808] LOG:  background worker "REPACK
decoding worker" (PID 3956168) was terminated by signal 6: Aborted


Crash Test:
i did crash test using debugger using a breakpoint
inside apply_concurrent_changes
to simulate a crash while concurrent changes are being done, after few
concurrent changes
are done , i crashed the server using "pg_ctl -m immediate stop", then
restarted the server,
i observed that REPACK (concurrently) didn't completed (expected), files
were not swapped and data
on the victim table is intact checked using FULL JOIN with shadow table,
but there are
some leftovers of the transient table we used for REPACK (concurrently)
such as
1) transient table's relation files - these consume extra space , i think
this was the
case with VACUUM FULL previously, so these has to be removed manually , but
I think this time we have a "leverage" which we can use to remove the extra
space.
2) transient table's WALs - these are generated because of concurrent
changes done while
applying the logical decoded changes on the new transient table, i think
this won't be a problem
until they only will get recycled but if they get archived , they are of no
use instead they
consume more space and time during the archival process.

"Leverage" Idea:
i think we can re-use these transient table's relation files and WALs
during crash recovery,
so that user don't have to re-run the REPACK (concurrently) after server
has recovered,
for this we might need to write a WAL for REPACK (concurrently) to let
startup process
know REPACK (concurrently) occurred which sets a flag, so at the end of
startup process
all the WALs of the transient table are already applied so transient table
perfect now ,
at the end we can do swapping (finish_heap_swap) after checking the flag ,
these are
all my initial thoughts on this idea to reuse the "residue" files of the
transient table.
I could be totally wrong :) Please correct me if I am.

i think we need to update this statement in repack.sgml regarding wal_level
       <listitem>
        <para>
          The <link
linkend="guc-wal-level"><varname>wal_level</varname></link>
          configuration parameter is less than <literal>logical</literal>.
        </para>
       </listitem>
because of this commit POC: enable logical decoding when wal_level =
'replica' without a server restart (67c2097)


-- 
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/

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

<div dir=3D"ltr"><div dir=3D"ltr">Hello,<br><br>I did stress testing on v35=
 patches, where I did concurrency test using<br>pgbench with 50 concurrent =
clients, 4 threads with the below pgbench<br>script (dual_chaos.sql) on the=
 following table setup(setup.sql).<br>I ran pgbench with 5M rows for 10 min=
utes and 50M for ~45 minutes<br>multiple times. REPACK (concurrently) ran s=
uccessfully except &quot;once&quot;(see below).<br>I created a shadow/clone=
 table to use for checking the correctness after doing</div><div dir=3D"ltr=
">the concurrency test.I used 4 checks to verify that data is intact and<br=
>REPACK (concurrently) ran successfully.<br><br>1) table file OID(relfileno=
de) swapped?<br>2) bloat gone? victim relation size should be less than<br>=
shadow relation size.<br>3) using FULL JOIN logic (borrowed from repack.spe=
c, with small change)</div><div dir=3D"ltr">against the shadow table which =
goes under the same concurrent ops<br>done on the victim table , basically =
doing dual writes (see dual_chaos.sql) to<br>verify table data integrity.<b=
r>4)=C2=A0Physical Index Integrity (amcheck) (borrowed from Mihail&#39;s te=
sts)<br><br>The concurrency test failed once. I tried to reproduce the belo=
w scenario<br>but no luck,i think the reason the assert failure happened be=
cause<br>after speculative insert there might be no spec CONFIRM or ABORT, =
thoughts?<br><br>TRAP: failed Assert(&quot;!specinsert&quot;), File: &quot;=
reorderbuffer.c&quot;, Line: 2610, PID: 3956168<br>postgres: REPACK decodin=
g worker for relation &quot;stress_victim&quot; (ExceptionalCondition+0x98)=
[0xaaaab1251188]<br>postgres: REPACK decoding worker for relation &quot;str=
ess_victim&quot; (+0x67b1cc)[0xaaaab0f4b1cc]<br>postgres: REPACK decoding w=
orker for relation &quot;stress_victim&quot; (+0x67b86c)[0xaaaab0f4b86c]<br=
>postgres: REPACK decoding worker for relation &quot;stress_victim&quot; (R=
eorderBufferCommit+0x74)[0xaaaab0f4b8f0]<br>postgres: REPACK decoding worke=
r for relation &quot;stress_victim&quot; (+0x66229c)[0xaaaab0f3229c]<br>pos=
tgres: REPACK decoding worker for relation &quot;stress_victim&quot; (xact_=
decode+0x1a0)[0xaaaab0f312bc]<br>postgres: REPACK decoding worker for relat=
ion &quot;stress_victim&quot; (LogicalDecodingProcessRecord+0xd4)[0xaaaab0f=
30e60]<br>postgres: REPACK decoding worker for relation &quot;stress_victim=
&quot; (+0x3372e4)[0xaaaab0c072e4]<br>postgres: REPACK decoding worker for =
relation &quot;stress_victim&quot; (+0x339634)[0xaaaab0c09634]<br>postgres:=
 REPACK decoding worker for relation &quot;stress_victim&quot; (RepackWorke=
rMain+0x1ac)[0xaaaab0c094e8]<br>postgres: REPACK decoding worker for relati=
on &quot;stress_victim&quot; (BackgroundWorkerMain+0x2b0)[0xaaaab0efc440]<b=
r>postgres: REPACK decoding worker for relation &quot;stress_victim&quot; (=
postmaster_child_launch+0x1f0)[0xaaaab0f00398]<br>postgres: REPACK decoding=
 worker for relation &quot;stress_victim&quot; (+0x639ca4)[0xaaaab0f09ca4]<=
br>postgres: REPACK decoding worker for relation &quot;stress_victim&quot; =
(+0x639f94)[0xaaaab0f09f94]<br>postgres: REPACK decoding worker for relatio=
n &quot;stress_victim&quot; (+0x638714)[0xaaaab0f08714]<br>postgres: REPACK=
 decoding worker for relation &quot;stress_victim&quot; (+0x635978)[0xaaaab=
0f05978]<br>postgres: REPACK decoding worker for relation &quot;stress_vict=
im&quot; (PostmasterMain+0x160c)[0xaaaab0f050c8]<br>postgres: REPACK decodi=
ng worker for relation &quot;stress_victim&quot; (main+0x3dc)[0xaaaab0d974d=
4]<br>/lib/aarch64-linux-gnu/libc.so.6(+0x284c4)[0xffff867584c4]<br>/lib/aa=
rch64-linux-gnu/libc.so.6(__libc_start_main+0x98)[0xffff86758598]<br>postgr=
es: REPACK decoding worker for relation &quot;stress_victim&quot; (_start+0=
x30)[0xaaaab09bc1f0]<br>2026-02-19 18:20:56.088 IST [3905812] LOG: =C2=A0ch=
eckpoint starting: wal<br>2026-02-19 18:21:10.683 IST [3905808] LOG: =C2=A0=
background worker &quot;REPACK decoding worker&quot; (PID 3956168) was term=
inated by signal 6: Aborted<br><br><br></div><div>Crash Test:<br>i did cras=
h test using debugger using a breakpoint inside=C2=A0apply_concurrent_chang=
es<br>to simulate a crash while concurrent changes are being done, after fe=
w concurrent changes<br>are done , i crashed the server using &quot;pg_ctl =
-m immediate stop&quot;, then restarted the server,<br>i observed that REPA=
CK (concurrently) didn&#39;t completed (expected), files were not swapped a=
nd data<br>on the victim table is intact checked using FULL JOIN with shado=
w table, but there are<br>some leftovers of the transient table we used for=
 REPACK (concurrently) such as<br>1) transient table&#39;s=C2=A0relation fi=
les - these consume extra space , i think this was the<br>case with VACUUM =
FULL previously, so these has to be removed manually , but</div><div>I thin=
k this time we have a &quot;leverage&quot; which we can use to remove the e=
xtra space.<br>2) transient table&#39;s=C2=A0WALs - these are generated bec=
ause of concurrent changes done while<br>applying the logical decoded chang=
es on the new transient table, i think this won&#39;t be a problem<br>until=
 they only will get recycled but if they get archived , they are of no use =
instead they<br>consume more space and time during the archival process.<br=
><br>&quot;Leverage&quot; Idea:<br>i think we can re-use these transient ta=
ble&#39;s=C2=A0relation files and WALs during crash recovery,<br>so that us=
er don&#39;t have to re-run the REPACK (concurrently) after server has reco=
vered,<br>for this we might need to write a WAL for REPACK (concurrently) t=
o let startup process<br>know REPACK (concurrently) occurred which sets a f=
lag, so at the end of startup process<br>all the WALs of the=C2=A0transient=
 table are already applied so transient table perfect now ,<br>at the end w=
e can do swapping (finish_heap_swap) after checking the flag , these are<br=
>all my initial thoughts on this idea to reuse the &quot;residue&quot; file=
s of the transient table.<br>I could be totally wrong :) Please correct me =
if I am.<br><br>i think we need to update this statement in repack.sgml reg=
arding wal_level<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0&lt;listitem&gt;<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 &lt;para&gt;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 The=
 &lt;link linkend=3D&quot;guc-wal-level&quot;&gt;&lt;varname&gt;wal_level&l=
t;/varname&gt;&lt;/link&gt;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 configura=
tion parameter is less than &lt;literal&gt;logical&lt;/literal&gt;.<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 &lt;/para&gt;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0&lt;/l=
istitem&gt;<br>because of this commit POC: enable logical decoding when wal=
_level =3D &#39;replica&#39; without a server restart (67c2097)</div><div d=
ir=3D"ltr"><br><br></div><div><br></div><span class=3D"gmail_signature_pref=
ix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"lt=
r"><span style=3D"color:rgb(34,34,34)">Thanks,</span><br style=3D"color:rgb=
(34,34,34)"><span style=3D"color:rgb(34,34,34)">Srinath Reddy Sadipiralla</=
span><br style=3D"color:rgb(34,34,34)"><span style=3D"color:rgb(34,34,34)">=
EDB:=C2=A0</span><a href=3D"https://www.enterprisedb.com/" style=3D"color:r=
gb(17,85,204)" target=3D"_blank">https://www.enterprisedb.com/</a></div></d=
iv></div>

--000000000000e8ba97064ba65cb9--
--000000000000e8ba98064ba65cbb
Content-Type: application/octet-stream; name="setup.sql"
Content-Disposition: attachment; filename="setup.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mm23hlv50>
X-Attachment-Id: f_mm23hlv50

Q1JFQVRFIFRBQkxFIHN0cmVzc192aWN0aW0gKAogICAgaWQgU0VSSUFMIFBSSU1BUlkgS0VZLAog
ICAgYmFsYW5jZSBJTlQsCiAgICBwYXlsb2FkIFRFWFQKKTsKCkNSRUFURSBUQUJMRSBzdHJlc3Nf
c2hhZG93ICgKICAgIGlkIFNFUklBTCBQUklNQVJZIEtFWSwKICAgIGJhbGFuY2UgSU5ULAogICAg
cGF5bG9hZCBURVhUCik7CgpJTlNFUlQgSU5UTyBzdHJlc3NfdmljdGltIChiYWxhbmNlLCBwYXls
b2FkKQpTRUxFQ1QKICAgIChyYW5kb20oKSoxMDAwKTo6aW50LAogICAgcmVwZWF0KG1kNShyYW5k
b20oKTo6dGV4dCksIDYpCkZST00gZ2VuZXJhdGVfc2VyaWVzKDEsIDUwMDAwMDApOwoKSU5TRVJU
IElOVE8gc3RyZXNzX3NoYWRvdyBTRUxFQ1QgKiBGUk9NIHN0cmVzc192aWN0aW07
--000000000000e8ba98064ba65cbb
Content-Type: application/octet-stream; name="dual_chaos.sql"
Content-Disposition: attachment; filename="dual_chaos.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mm23hpns1>
X-Attachment-Id: f_mm23hpns1

XHNldCBpZCByYW5kb21femlwZmlhbigxLCA1MDAwMDAwLCAyLjUpClxzZXQgcmVhZF9pZCByYW5k
b20oMSwgNTAwMDAwMCkKXHNldCB2YWwgcmFuZG9tKDEsIDEwMDApCgpCRUdJTjsKICBTRUxFQ1Qg
cGdfYWR2aXNvcnlfeGFjdF9sb2NrKDppZCk7CiAgU0VMRUNUIHBnX2Fkdmlzb3J5X3hhY3RfbG9j
ayg6aWQrMSk7CgogIFNFTEVDVCBiYWxhbmNlLCBwYXlsb2FkIEZST00gc3RyZXNzX3ZpY3RpbSBX
SEVSRSBpZCA9IDpyZWFkX2lkOwoKICBTRUxFQ1QgY291bnQoKikgRlJPTSBzdHJlc3NfdmljdGlt
IFdIRVJFIGlkIEJFVFdFRU4gOmlkIEFORCA6aWQgKyA1OwoKICBVUERBVEUgc3RyZXNzX3ZpY3Rp
bSBTRVQgYmFsYW5jZSA9IDp2YWwgV0hFUkUgaWQgPSA6aWQ7CgogIFVQREFURSBzdHJlc3Nfc2hh
ZG93IFNFVCBiYWxhbmNlID0gOnZhbCBXSEVSRSBpZCA9IDppZDsKIAogIERFTEVURSBGUk9NIHN0
cmVzc192aWN0aW0gV0hFUkUgaWQgPSA6aWQgKyAxOwogIERFTEVURSBGUk9NIHN0cmVzc19zaGFk
b3cgV0hFUkUgaWQgPSA6aWQgKyAxOwogCiAgSU5TRVJUIElOVE8gc3RyZXNzX3ZpY3RpbSAoaWQs
IGJhbGFuY2UsIHBheWxvYWQpCiAgVkFMVUVTICg6aWQgKyAxLCA6dmFsLCAnbmV3JykKICBPTiBD
T05GTElDVCAoaWQpIERPIFVQREFURSBTRVQgYmFsYW5jZSA9IEVYQ0xVREVELmJhbGFuY2UsIHBh
eWxvYWQgPSBFWENMVURFRC5wYXlsb2FkOwogCiAgSU5TRVJUIElOVE8gc3RyZXNzX3NoYWRvdyAo
aWQsIGJhbGFuY2UsIHBheWxvYWQpCiAgVkFMVUVTICg6aWQgKyAxLCA6dmFsLCAnbmV3JykKICBP
TiBDT05GTElDVCAoaWQpIERPIFVQREFURSBTRVQgYmFsYW5jZSA9IEVYQ0xVREVELmJhbGFuY2Us
IHBheWxvYWQgPSBFWENMVURFRC5wYXlsb2FkOwpDT01NSVQ7
--000000000000e8ba98064ba65cbb--