MIME-Version: 1.0
References: 
 <CAHgTRff=eH1GwVb=ENMzX6hBEajdTccCQbeVRkj4OtYLm69a9g@mail.gmail.com>
 <6EEE234B-D3DC-48D9-97EE-0A86D63554BC@thebuild.com>
In-Reply-To: <6EEE234B-D3DC-48D9-97EE-0A86D63554BC@thebuild.com>
From: James Pang <jamespang886@gmail.com>
Date: Sun, 19 Apr 2026 15:22:51 +0800
Message-ID: 
 <CAHgTRff8Z_ntRLg8cAWtdTi47m716T7j0-xfy9c2PsJigcX-WA@mail.gmail.com>
Subject: Re: table bloat very fast and free space can not be reused
To: Christophe Pettus <xof@thebuild.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000620893064fcb0d0f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHgTRff8Z_ntRLg8cAWtdTi47m716T7j0-xfy9c2PsJigcX-WA%40mail.gmail.com>
Precedence: bulk

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

1. we use default fill factor in both source and target, sorry, we have two
environment, v14-->v17,  v16-->v17. both see similar issue.
2. any key difference between in-core and pglogical extension to apply
UPDATEs? we want to use pglogical extension conflict resolution, it's a
bi-directional replication(when workload on source v16 it replicate data to
v17, when it running on v17, so it can replication back to v16)
3. free space is high in source and target, but the size of freespace show
big difference

we found this issue , and try to vacuum full several time on v17, but after
vacuum full , it bloating very quickly.you see total toast size
much more than source (workload is still running),  target v17(by pglogical=
)

xxx=3D> select * from pgstattuple('xxxx');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+--=
--------------+--------------------+------------+--------------
 191160320 |       81911 | 107823097 |          56.4 |             1439 |
     2020471 |               1.06 |   79614944 |        41.65
(1 row)

xxx=3D>  select * from pgstattuple('pg_toast.pg_toast_xxxxx');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+--=
--------------+--------------------+------------+--------------
 905928704 |      117924 | 169760027 |         18.74 |             6330 |
     9336459 |               1.03 |  722476480 |        79.75
(1 row)


target v17
xxxx=3D>  select * from pgstattuple('xxxxxx');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+--=
--------------+--------------------+------------+--------------
 194191360 |       81905 | 106028087 |          54.6 |             4004 |
     5801923 |               2.99 |   80468096 |        41.44
(1 row)

xxxx=3D>  select * from pgstattuple('pg_toast.pg_toast_xxxxx');
 table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
dead_tuple_len | dead_tuple_percent | free_space | free_percent
------------+-------------+-----------+---------------+------------------+-=
---------------+--------------------+------------+--------------
 1666334720 |      117921 | 169758344 |         10.19 |             9302 |
      13732019 |               0.82 | 1475454612 |        88.54
(1 row)

James

Christophe Pettus <xof@thebuild.com> =E6=96=BC 2026=E5=B9=B44=E6=9C=8819=E6=
=97=A5=E9=80=B1=E6=97=A5 =E4=B8=8A=E5=8D=8811:56=E5=AF=AB=E9=81=93=EF=BC=9A

>
>
> > On Apr 18, 2026, at 18:28, James Pang <jamespang886@gmail.com> wrote:
> >
> > experts:
> >      source database v14 ,  pglogical extension 2.4.5 replication to ne=
w
> 17, source table very frequent UPDATEs /DELETES /INSERTS and has two text
> and jsonb. in source database, application update/delete/insert through
> SQL, table size did not increased quickly. but in target pg v17 , by
> pglogical apply, we found table increased very quickly , table size got
> doubled in days.
> >     Vacuum analyze did not help , only vacuum full can help. pgstattupl=
e
> show most of space are free space , that much more than source.  it looks
> like these replicate DML always asking new pages instead of reuse existin=
g
> freespace.
> >     Thanks,
> >
> > James
>
> Hello,
>
> 1. First, are you *sure* that the free space is the source of the bloat,
> and not dead tuples?  Could you share the queries you ran to detrmine thi=
s?
>
> 2. Have you set fillfactor to anything besides 100 on either the source o=
r
> the destination?
>
> 3. You might consider using in-core logical replication rather than
> pglogical for this.  By PostgreSQL v14, in-core logical replication is
> likely a better choice.
>
> Best,
> -- Christophe

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

<div dir=3D"ltr"><div dir=3D"ltr">1. we use default fill factor in both sou=
rce and target, sorry, we have two environment, v14--&gt;v17,=C2=A0 v16--&g=
t;v17. both see similar issue.<div>2. any key difference between in-core an=
d pglogical extension to apply UPDATEs? we want to use pglogical extension =
conflict resolution, it&#39;s a bi-directional replication(when workload on=
 source v16 it replicate data to v17, when it running on v17, so it can rep=
lication back to v16)</div><div>3. free space is high in source and target,=
 but the size of freespace show big difference</div><div><br></div><div>we =
found this issue , and try to vacuum full several time on v17, but after va=
cuum full , it bloating very quickly.you see total toast size <br>much more=
 than source (workload is still running), =C2=A0target v17(by pglogical)<br=
><br>xxx=3D&gt; select * from pgstattuple(&#39;xxxx&#39;);<br>=C2=A0table_l=
en | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tupl=
e_len | dead_tuple_percent | free_space | free_percent<br>-----------+-----=
--------+-----------+---------------+------------------+----------------+--=
------------------+------------+--------------<br>=C2=A0191160320 | =C2=A0 =
=C2=A0 =C2=A0 81911 | 107823097 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A056.4 | =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1439 | =C2=A0 =C2=A0 =C2=A0 =C2=
=A02020471 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1.06 | =C2=A0=
 79614944 | =C2=A0 =C2=A0 =C2=A0 =C2=A041.65<br>(1 row)<br><br>xxx=3D&gt; =
=C2=A0select * from pgstattuple(&#39;pg_toast.pg_toast_xxxxx&#39;);<br>=C2=
=A0table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |=
 dead_tuple_len | dead_tuple_percent | free_space | free_percent<br>-------=
----+-------------+-----------+---------------+------------------+---------=
-------+--------------------+------------+--------------<br>=C2=A0905928704=
 | =C2=A0 =C2=A0 =C2=A0117924 | 169760027 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 18.=
74 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 6330 | =C2=A0 =C2=A0 =C2=A0 =
=C2=A09336459 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 1.03 | =C2=
=A0722476480 | =C2=A0 =C2=A0 =C2=A0 =C2=A079.75<br>(1 row)<br><br><br>targe=
t v17<br>xxxx=3D&gt; =C2=A0select * from pgstattuple(&#39;xxxxxx&#39;);<br>=
=C2=A0table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_coun=
t | dead_tuple_len | dead_tuple_percent | free_space | free_percent<br>----=
-------+-------------+-----------+---------------+------------------+------=
----------+--------------------+------------+--------------<br>=C2=A0194191=
360 | =C2=A0 =C2=A0 =C2=A0 81905 | 106028087 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A054.6 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 4004 | =C2=A0 =C2=A0=
 =C2=A0 =C2=A05801923 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 2.=
99 | =C2=A0 80468096 | =C2=A0 =C2=A0 =C2=A0 =C2=A041.44<br>(1 row)<br><br>x=
xxx=3D&gt; =C2=A0select * from pgstattuple(&#39;pg_toast.pg_toast_xxxxx&#39=
;);<br>=C2=A0table_len =C2=A0| tuple_count | tuple_len | tuple_percent | de=
ad_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_pe=
rcent<br>------------+-------------+-----------+---------------+-----------=
-------+----------------+--------------------+------------+--------------<b=
r>=C2=A01666334720 | =C2=A0 =C2=A0 =C2=A0117921 | 169758344 | =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 10.19 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 9302 | =C2=
=A0 =C2=A0 =C2=A0 13732019 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 0.82 | 1475454612 | =C2=A0 =C2=A0 =C2=A0 =C2=A088.54<br>(1 row)</div><d=
iv><br></div><div>James</div></div><br><div class=3D"gmail_quote gmail_quot=
e_container"><div dir=3D"ltr" class=3D"gmail_attr">Christophe Pettus &lt;<a=
 href=3D"mailto:xof@thebuild.com">xof@thebuild.com</a>&gt; =E6=96=BC 2026=
=E5=B9=B44=E6=9C=8819=E6=97=A5=E9=80=B1=E6=97=A5 =E4=B8=8A=E5=8D=8811:56=E5=
=AF=AB=E9=81=93=EF=BC=9A<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"><br>
<br>
&gt; On Apr 18, 2026, at 18:28, James Pang &lt;<a href=3D"mailto:jamespang8=
86@gmail.com" target=3D"_blank">jamespang886@gmail.com</a>&gt; wrote:<br>
&gt; <br>
&gt; experts:<br>
&gt;=C2=A0 =C2=A0 =C2=A0 source database v14 ,=C2=A0 pglogical extension 2.=
4.5 replication to new 17, source table very frequent UPDATEs /DELETES /INS=
ERTS and has two text and jsonb. in source database, application update/del=
ete/insert through SQL, table size did not increased quickly. but in target=
 pg v17 , by pglogical apply, we found table increased very quickly , table=
 size got doubled in days.=C2=A0 <br>
&gt;=C2=A0 =C2=A0 =C2=A0Vacuum analyze did not help , only vacuum full can =
help. pgstattuple show most of space are free space , that much more than s=
ource.=C2=A0 it looks like these replicate DML always asking new pages inst=
ead of reuse existing freespace. <br>
&gt;=C2=A0 =C2=A0 =C2=A0Thanks,<br>
&gt; <br>
&gt; James<br>
<br>
Hello,<br>
<br>
1. First, are you *sure* that the free space is the source of the bloat, an=
d not dead tuples?=C2=A0 Could you share the queries you ran to detrmine th=
is?<br>
<br>
2. Have you set fillfactor to anything besides 100 on either the source or =
the destination?<br>
<br>
3. You might consider using in-core logical replication rather than pglogic=
al for this.=C2=A0 By PostgreSQL v14, in-core logical replication is likely=
 a better choice.<br>
<br>
Best,<br>
-- Christophe</blockquote></div></div>

--000000000000620893064fcb0d0f--