MIME-Version: 1.0
References: 
 <CAHgTRff=eH1GwVb=ENMzX6hBEajdTccCQbeVRkj4OtYLm69a9g@mail.gmail.com>
 <CAOQPKatQ0NCP5vDCZ_pz_JQkyM+AP=nPL5wPY3awWVoZXA4T1A@mail.gmail.com>
In-Reply-To: 
 <CAOQPKatQ0NCP5vDCZ_pz_JQkyM+AP=nPL5wPY3awWVoZXA4T1A@mail.gmail.com>
From: Rick Otten <rottenwindfish@gmail.com>
Date: Mon, 20 Apr 2026 10:39:24 -0400
Message-ID: 
 <CAMAYy4JzAuDNLAy-k=ZgELT8VwvcBUhVrxgfM9vA1qJqzB9usQ@mail.gmail.com>
Subject: Re: table bloat very fast and free space can not be reused
To: Kristjan Mustkivi <sonicmonkey@gmail.com>
Cc: James Pang <jamespang886@gmail.com>,
 pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000876beb064fe5449a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMAYy4JzAuDNLAy-k%3DZgELT8VwvcBUhVrxgfM9vA1qJqzB9usQ%40mail.gmail.com>
Precedence: bulk

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

On Sun, Apr 19, 2026 at 7:44=E2=80=AFAM Kristjan Mustkivi <sonicmonkey@gmai=
l.com>
wrote:

> On Sun, Apr 19, 2026 at 4:28=E2=80=AFAM James Pang <jamespang886@gmail.co=
m> 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.
>
> Hi,
>
> We have had the exact same problem in the past - a large json column
> getting very frequent updates (hundreds even thousand per minute) on
> the source and the replica side table bloating up. No amount of tuning
> attempts made any difference (conversion of json to text, different
> table specific autovacuum settings etc). Eventually, the table was
> taken out of the replication and later on made UNLOGGED which also
> freed up a giant swathes of storage from WAL written. Such design was
> then called an antipattern and has not been allowed since.
>

 Which design is an antipattern?  Using json for volatile data sets or
unlogging the table?

Does `pg_repack` help?  I know it probably isn't practical to run it every
couple of days.  It also can sometimes causes headaches when repacking a
table with a ton of logical replication activity, but it might be a tool to
consider if you haven't already.   If you can partition the table with the
crazy amount of json changes, you don't have to repack all the partitions,
you might be able to repack just the older ones with the worst bloat.

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Apr 19,=
 2026 at 7:44=E2=80=AFAM Kristjan Mustkivi &lt;<a href=3D"mailto:sonicmonke=
y@gmail.com">sonicmonkey@gmail.com</a>&gt; wrote:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">On Sun, Apr 19, 2026 at 4:28=E2=80=AFAM J=
ames Pang &lt;<a href=3D"mailto:jamespang886@gmail.com" target=3D"_blank">j=
amespang886@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.<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>
<br>
Hi,<br>
<br>
We have had the exact same problem in the past - a large json column<br>
getting very frequent updates (hundreds even thousand per minute) on<br>
the source and the replica side table bloating up. No amount of tuning<br>
attempts made any difference (conversion of json to text, different<br>
table specific autovacuum settings etc). Eventually, the table was<br>
taken out of the replication and later on made UNLOGGED which also<br>
freed up a giant swathes of storage from WAL written. Such design was<br>
then called an antipattern and has not been allowed since.<br></blockquote>=
<div><br></div><div>=C2=A0Which design is an antipattern?=C2=A0 Using json =
for volatile data sets or unlogging the table?</div><div><br></div><div>Doe=
s `pg_repack` help?=C2=A0 I know it probably isn&#39;t practical to run it =
every couple of days.=C2=A0 It also can sometimes causes headaches when rep=
acking a table with a ton of logical replication activity, but it might be =
a tool to consider if you haven&#39;t already.=C2=A0 =C2=A0If you can parti=
tion the table with the crazy amount of json changes, you don&#39;t have to=
 repack all the partitions, you might be able to repack just the older ones=
 with the worst bloat.</div><div><br></div></div></div>

--000000000000876beb064fe5449a--