Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vpBz9-0035kw-15 for pgsql-general@arkaria.postgresql.org; Sun, 08 Feb 2026 21:06:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vpBz7-008aLN-1m for pgsql-general@arkaria.postgresql.org; Sun, 08 Feb 2026 21:06:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vpBz6-008aL6-33 for pgsql-general@lists.postgresql.org; Sun, 08 Feb 2026 21:06:25 +0000 Received: from mail-oo1-xc31.google.com ([2607:f8b0:4864:20::c31]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vpBz4-00000001B5l-0jGu for pgsql-general@postgresql.org; Sun, 08 Feb 2026 21:06:23 +0000 Received: by mail-oo1-xc31.google.com with SMTP id 006d021491bc7-6610f407959so520910eaf.2 for ; Sun, 08 Feb 2026 13:06:22 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770584781; cv=none; d=google.com; s=arc-20240605; b=CXEz7rJLOg6g9TWlqS+YGFxSsYeu0Uq61W7wOCtGIZ7ABERa8WpR6iDmMdtyRAccnY EPfZ+5tyQwoaAUNMT9gV5ndDEhbKOtC6SrDlaKS//we9P9nQBqTZXlXaWLJIAm/yUPzH GcL98cfgqSQn64jPgIxIkJRrtcpLaiPqdvDksgK4Awf/zmkdTXITJ2CEmFJFjxiL3wmp /hsOp5tP8IY4Jgr4QiVNw8Lz7PK740fap2OxQiWYyUpUKQZsF6/uUlg1MKuYgKLCTW8W fKpobFVZRFj4YK3AU9zXGCBF4Ic/dGsSYbXCsiCUYDnYfv1r6xrWr4gdvOC5MWxXc9bg N+DQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :dkim-signature; bh=sEOAhYHaqwJlwyWkb49Hl1/u4oqBJyc+XLoGDZSST6g=; fh=KNSq+t9BltSXFnT3Yof/aKGBtqxeA+bTALiYdvTslaY=; b=jhxQV9LMoPzt/mC5mKNS8g3G6AHaBtP4dWKiiOZD5x/PLk1UX2t0XNIHJ+HM9gcLdP q4sEMjJBXQzNKca3c0TiCoNtFxsihNfqf/sj3WOSls7MZWYTa0LsijMKGefMyYUFsLXX +m22YhgZ9uSPMh3KYMhldDXep9sL0saksb+oMwhGJMh86qqT6/stSIUkNqBOU1uiWNNw BipNCe9QzkN8WGS6N2DwyYQOXg4BUbFV14imsfcfiohlU7qIDjcn6s9CGZDdHxkHxHeN q1v9VvD1RTZJx61CzY4cXIAqu52VuKa8mD0Ri/C8SUUZYEcX8/R6XtAblFeHY7CBF50S HsEg==; darn=postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1770584781; x=1771189581; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=sEOAhYHaqwJlwyWkb49Hl1/u4oqBJyc+XLoGDZSST6g=; b=FnGqByO6Pam+8pm/p6qcrHZkfhv9d6lEktK3PRtWsx8Tn89iUaak18HeRzl59jUmzp ZjNUqb3MFf+XcLEEGvcdVCB2uhp1/P7l1ZGpbCcgJ9ESrAGSfCqJOH06P6QwNZYyPGUT FsWajmWeleXKZRemy3ziNl35hOKVWWRX/eHpkzq53UriHigPxWaH4Nr86i9S5QAm965H 06NvUTDNs9ONsI131GQrAYN2l7xtlotD/oe5f2Hg1nIkeKPZWE431U772xnBiE/MgJgh 9hOB93ux9L1VZ8sUUp+OPMoDB3andqesfbDn3poupt56xlVbqLt2tnAjDFqqdQBefa6v /3Eg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770584781; x=1771189581; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=sEOAhYHaqwJlwyWkb49Hl1/u4oqBJyc+XLoGDZSST6g=; b=w8TgtZKioBzwGJ3Wt397AU7ETlEFDtaTOcDecIbdxzcDEt2SysuBCuwnmHvevhsHO3 8rHwBi1WV1cZ3ISwpu/Vouk5ddkq+4LhIB1m33eNtAr/BvKGQnQPHMNaYbDyIdZ0way5 BWmF8eb4RDaV29KLblr34I/Xcte4vPHLGoa6k8jqBqcElzGDfkpMNhZV0YIZAwiiBktz nVBel4uWd5Kg9cXEQ/3tWfVV/wenyCnTsgASNLmzyMLziox2o/H3lUPcT3YmJzipXXkA JjeMV9lw4lOPoc/32KdQH8gWVAkW2QgPKkoTPNsmzw3n5Rn1V2ypRsLQpeO6ynQmQkAB btAw== X-Gm-Message-State: AOJu0YzKlaDpk5jg25k5i896NTHFDsxJXX0hR3KvraYUyXaftnsYvYwu 1QrMiHsWvBDsGVmG5rxjuV1On/B3EFhrp5I9iu+f4HYkrEkpUmyI0btC0BbUNZGi/mCke/gfC32 KcbVoS0lmQhxLo0p7Z+ldF9+/nDt6MfYkJ/z4 X-Gm-Gg: AZuq6aJcGa0QLgkrS90MEmc5gx/eQ5GkY/0Uf9D3vIR7QpORx5moXEO4C4dGiDPZrUX w6RI3VdLoZyCZfxWI0rzV/g58yY3KQ8PYrwL74i59pjtEbr3KmvOuExn+XwUo9yOK5ImB7CFlfZ dm0IlnWG5bgvmpZFRDTnaqh8XlmWHKN9xD/wvUallpUkalZEmqxb2vj5TsLlfRBKTGvh8jpXWKN VQ51JzNPEek5ktFBB88mzJUdvrseru4WwTNdZPfrFlo75TXSeKxfakSUp/+dSLvk/h04cnR X-Received: by 2002:a05:6820:80c7:b0:662:f91f:4a96 with SMTP id 006d021491bc7-66d0a096318mr3608590eaf.20.1770584780991; Sun, 08 Feb 2026 13:06:20 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Sun, 8 Feb 2026 16:06:09 -0500 X-Gm-Features: AZwV_QiJa1KRgBsswmOM0P_-GZkfzjcabpMV6liq2mfFDT15_3zDyS7Z8aOCXRo Message-ID: Subject: Re: Table bloat threshold limit to trigger repack To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000ea71bd064a5664db" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ea71bd064a5664db Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Feb 8, 2026 at 1:05=E2=80=AFPM Durgamahesh Manne wrote: > > > > On Sun, 8 Feb, 2026, 21:57 Ron Johnson, wrote: > >> On Sun, Feb 8, 2026 at 4:44=E2=80=AFAM Durgamahesh Manne < >> maheshpostgres9@gmail.com> wrote: >> >>> On Sun, 8 Feb, 2026, 13:15 Ron Johnson, wrote= : >>> >>>> On Sun, Feb 8, 2026 at 12:43=E2=80=AFAM Durgamahesh Manne < >>>> maheshpostgres9@gmail.com> wrote: >>>> >>>>> On Sun, 8 Feb, 2026, 10:59 Ron Johnson, >>>>> wrote: >>>>> >>>>>> On Sat, Feb 7, 2026 at 11:19=E2=80=AFPM Durgamahesh Manne < >>>>>> maheshpostgres9@gmail.com> wrote: >>>>>> >>>>>>> Hi >>>>>>> >>>>>>> How much table bloat is acceptable before it affects performance in >>>>>>> PostgreSQL? >>>>>>> >>>>>> >>>>>> How big is the table? (For small tables, it doesn't matter.) How >>>>>> active is it? How frequently are records updated? >>>>>> >>>>> >>>>> >>>> Hi >>>>> >>>>> Table size 100gb >>>>> I use pgstattuple_approx to get Table bloat is about 16gb as of now >>>>> since after repack is done on 27th of January >>>>> Fillfactor already in place >>>>> It's very critical application with updates on non partitioned table >>>>> >>>> >>>> What did you set the fillfactor to? >>>> Have you minimized the number of indexes? (That lets HOT work better.= ) >>>> How long does it take to VACUUM the table? >>>> >>> >>> >> Hi >>> >>> Fillfactor 80 >>> 3 composite and pkey on one column as queries use those >>> Vacuum 3min to complete >>> Here autovacuum 5min to complete during load even with param tuning >>> >> >> 1. What is autovacuum_vacuum_scale_factor set to? >> 2. How often does the autovacuum run? (pg_stat_user_tables will tell you= .) >> 3. Do you update any of those indexed columns? >> 4. How often do queries/reports need to read large chunks of the table >> (aka sequentially scan it)? >> 5. Is performance currently suffering, or are you proactively worrying? >> >> Note: Regular vacuuming eliminates bloat. >> > > Hi > > Periodic maintenance activity already enabled that runs for everyday once > > 1).sclae factor for toast 0.06 and non toast 0.1 > Good. > 2).observers that autovacuum runs for every 1hour > Good. > 3).2indexed columns are being updated but I think it shouldn't be > Interesting. As you seemingly suspect, fewer index updates speed things up= . > 4).most of the time index scan but not sequential scan > Well, as you probably know, bloat makes sequential scans slower, since there's more file to scan. Sometimes, though, you've got to choose "faster updates" or "faster sequential scans". > 5).Seem to be good average latency is less for queries > But trying to optimize better than now > If it's heavy on the updates, then lowering that fill factor *after* eliminating updates of indexed fields will definitely speed UPDATE statements *at the expense of* table sequential scans. https://www.postgresql.org/docs/17/storage-hot.html > Triggers are already removed > +1 --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000ea71bd064a5664db Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sun, Feb 8, 2026 at 1:05=E2=80=AFPM Du= rgamahesh Manne <maheshpost= gres9@gmail.com> wrote:



On Sun, 8 Feb, 2026, 21:57 Ron Johnson, &= lt;ronljohnson= jr@gmail.com> wrote:
On Sun, Feb 8, 2026 at 4:44= =E2=80=AFAM Durgamahesh Manne <maheshpostgres9@gmail.com> = wrote:
On Sun, 8 Feb, 2026, 13:15 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Sun, Feb 8, 20= 26 at 12:43=E2=80=AFAM Durgamahesh Manne <maheshpostgr= es9@gmail.com> wrote:
On Sun, 8 Feb, 2026, 10:59= Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
<= div dir=3D"ltr">On Sat, Feb 7, 2026 at 11:19=E2=80=AFPM Durgamahesh Manne &= lt;maheshpostgres9@gmail.com> wrote:
Hi

How = much table bloat is acceptable before it affects performance in PostgreSQL?= =C2=A0

How big is the table= ? (For small tables, it doesn't matter.) How active is it?=C2=A0 How fr= equently are records updated?
=C2=A0
Hi

Table size 100gb
I use pgstattuple_approx = to get Table bloat is about 16gb as of now since after repack is done on 27= th of January=C2=A0
Fillfactor already in place
It's very critical application with updates on non p= artitioned table=C2=A0

What did you set the fillfactor=C2=A0to?
Have you minimi= zed the number of indexes?=C2=A0 (That lets HOT work better.)
How= long does it take to VACUUM the table?
=C2=A0
Hi

Fillfactor = 80
3 composite and pkey on one column as queries use= those=C2=A0
Vacuum 3min to complete=C2=A0
Here autovacuum 5min to complete during load even with param = tuning=C2=A0

1. What is=C2=A0autovacuum_vacuum_scale_factor set to?
2. How o= ften does the autovacuum run? (pg_stat_user_tables will tell you.)
3. Do you update any of those indexed columns?
4. How often do = queries/reports need to read large chunks of the table (aka sequentially sc= an it)?
5. Is performance currently suffering, or are you proacti= vely worrying?

Note: Regular vacuuming eliminates = bloat.
=C2= =A0
Hi=C2=A0

Periodic maintenance activi= ty already enabled that runs for everyday once=C2=A0

1).sclae factor for toast 0.06 and non toast 0= .1

Good.
=C2=A0
=
2).observers that autovacuum runs for every 1hour=C2=A0

Good.
=C2=A0
= 3).2indexed columns are being updated but I think it shouldn't be=C2=A0=

Interesting.=C2=A0 As you seem= ingly suspect, fewer index updates speed things up.
=C2=A0
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
4).most of the time index scan but not sequential scan

Well, as you probably know, bloat makes s= equential scans slower, since there's more file to scan.=C2=A0 Sometime= s, though, you've got to choose "faster updates" or "fas= ter sequential scans".
=C2=A0
5).Seem to be = good average latency is less=C2=A0 for queries=C2=A0
But trying to optimize better than now=C2=A0
=
If it's heavy on the updates, then lowering that fill fa= ctor after eliminating updates of indexed fields will definitely spe= ed UPDATE statements at the expense of=C2=A0table sequential scans.<= /div>

=
=C2=A0
Triggers are already removed=C2=A0
<= /blockquote>

+1

--
Death to <Redacted>, and bu= tter sauce.
Don't boil me, I'm still alive.
<Re= dacted> lobster!
--000000000000ea71bd064a5664db--