public inbox for pgsql-performance@postgresql.org  

help / color / mirror / Atom feed

From: Kristjan Mustkivi <sonicmonkey@gmail.com>
To: Rick Otten <rottenwindfish@gmail.com>
Cc: James Pang <jamespang886@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: table bloat very fast and free space can not be reused
Date: Tue, 21 Apr 2026 11:33:53 +0300
Message-ID: <CAOQPKatLGcDMJ+tFpRqO-NWfs8xgN3v692CmxrV9MMG=qZn1Hg@mail.gmail.com> (raw)
In-Reply-To: <CAMAYy4JzAuDNLAy-k=ZgELT8VwvcBUhVrxgfM9vA1qJqzB9usQ@mail.gmail.com>
References: <CAHgTRff=eH1GwVb=ENMzX6hBEajdTccCQbeVRkj4OtYLm69a9g@mail.gmail.com>
	<CAOQPKatQ0NCP5vDCZ_pz_JQkyM+AP=nPL5wPY3awWVoZXA4T1A@mail.gmail.com>
	<CAMAYy4JzAuDNLAy-k=ZgELT8VwvcBUhVrxgfM9vA1qJqzB9usQ@mail.gmail.com>

On Mon, Apr 20, 2026 at 5:39 PM Rick Otten <rottenwindfish@gmail.com> wrote:
>
>  Which design is an antipattern?  Using json for volatile data sets or unlogging the table?

For us, a large (i.e spilling over to TOAST) json blob in a table
where this json blob (or text, does not matter) gets very frequent
(hundreds and thousands of) updates per minute.

> 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.

This feels like a complexity I personally would like to avoid. Far
more preferable is to get rid of the json. Split the large json into a
normalized table design based on the most useful/frequent patterns.

Br,
-- 
Kristjan Mustkivi

Email: kristjan.mustkivi@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: sonicmonkey@gmail.com, rottenwindfish@gmail.com, jamespang886@gmail.com, pgsql-performance@lists.postgresql.org
  Subject: Re: table bloat very fast and free space can not be reused
  In-Reply-To: <CAOQPKatLGcDMJ+tFpRqO-NWfs8xgN3v692CmxrV9MMG=qZn1Hg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox