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.94.2) (envelope-from ) id 1vEwUm-004FRA-JP for pgsql-general@arkaria.postgresql.org; Fri, 31 Oct 2025 21:17:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vEwUl-000tSu-Hi for pgsql-general@arkaria.postgresql.org; Fri, 31 Oct 2025 21:17:14 +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.94.2) (envelope-from ) id 1vEwUl-000tSD-2A for pgsql-general@lists.postgresql.org; Fri, 31 Oct 2025 21:17:14 +0000 Received: from mail-oi1-x232.google.com ([2607:f8b0:4864:20::232]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vEwUh-004nTm-2z for pgsql-general@lists.postgresql.org; Fri, 31 Oct 2025 21:17:13 +0000 Received: by mail-oi1-x232.google.com with SMTP id 5614622812f47-443aa90f025so1694446b6e.0 for ; Fri, 31 Oct 2025 14:17:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761945430; x=1762550230; darn=lists.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=zxJ9ha+VStN9vM5heGQ2HotMndRIk8DzBYUQw2OsE9I=; b=THD8Sfmx2G7H2qw1An9V/cNjG3d/lQWkk1XP3EDBFcxmzHscvTWMK8Vx9Lm5pmpgr9 qhN68i40oSZM/yEaAu39kmwFxX9ZH0AXVK2W10xRBKHFBYJqBP7ld2W508vMkgs+3Pha 0NvNoN1vs+CmdqAJEMbqh0uZHIqbgLg1682YmD0Wi/xX9yko0V745zv12Uq0PVNetJa/ mOap0v/Zeru5hSK1gPfp1YWM0oTOTV5qLF9rVfP2OWcP9nnMdJchSP1YKdMsETrGfpjP asZYj6xS5d9gwUvcSZC+yn/kQRDXBSf54vDPrhAoU9USvtb3sZnpizOroEiCsiMu13fp FxKg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761945430; x=1762550230; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=zxJ9ha+VStN9vM5heGQ2HotMndRIk8DzBYUQw2OsE9I=; b=XfUuN3AUkHvAulAOCWpHhXFPBj9FA6eQFPQoIar3C+xnitXS1x8Up56XLRZfBtVdYL Dh8rTFavRxKpWdCjOMYOX4AwMiuPnArIEaWlg0yBL7A89iU6z85aP8ZNmxsa1tplISYq Wf9dq1kOXtc+Je1BQn7Mij/IUjYndst4hCVGDlYMamek09l8U7yY5kCyiJwuEMlI0JxY dk+jTPlZre7E5cZcn4iBxW6f82FM3ZIvqBZTIcckl1uEdseOsq//b4Bj9p6Mn/u2zZ72 z+RbIviryBYB4pJ9d25EHQqj3b/kil6csPmdZjM9pJsPuxj1ZUEmz4nlGlSRy6WDo9Jb 2DZg== X-Gm-Message-State: AOJu0Yw/oW2CnOcC/g10ErUHKd0YYcGUfs8IMo8rdQJQ5PGaZN31Yymn V8uXE34Qx3qHa8AjyIfWMxePOVRgsti8pmU80wQMfOV2emcq29N6ilVGQNpcpvPFHQ/cQdMVMv2 LQ2kiOlu4xErd1DMA0x8gipc3SkntrXt4kSUT X-Gm-Gg: ASbGncuXYnH48+5Qqz2up+UB1foOIWA/Ymg0uT/RYv/KGow6z0L7iFXAE08XK/+sMfH dIGkqNggKV15KjlVxNnP7BPE0rNBY8oUGNapJxhTlgBaKivDI29ugLyv8AyGO1T2vaMg/9BJNPo 1K45OwrHbiKLCyi5RE8Cw7rBIAHIiAe2tYxp8da47+FMjDrn7LVRecT2y83VBAu0Lpkv/c+alcQ 7/aU/Cqy1OXeNYu+T1xpr8tPQow1QP6CxyqCmTvT1eNm/XkVJeExPKjkj0byw== X-Google-Smtp-Source: AGHT+IErtIZaTLWY2myIxTA/2Rcv+L4SlJtfwi4hkmLtfS3vrMpxx8xJW0TMcUp9HMJ29TwOERqoEJatiHfvrQqWg88= X-Received: by 2002:a05:6808:2f0e:b0:44d:c21c:cef6 with SMTP id 5614622812f47-44f95ea9635mr2767145b6e.26.1761945430440; Fri, 31 Oct 2025 14:17:10 -0700 (PDT) MIME-Version: 1.0 References: <26qs98r6-0q81-non7-3n17-0r14o9851pp9@tzk.arg> <07sp7s76-r633-spqr-so3o-5oqs44r80np6@tzk.arg> In-Reply-To: From: Ron Johnson Date: Fri, 31 Oct 2025 17:16:59 -0400 X-Gm-Features: AWmQ_blfAymi2SmiFMhR9SV8_O2AVwdCrKd7GsabiBCgYA-Id5Fc55J7VkOX3vo Message-ID: Subject: Re: Why isn't my table auto-analyzed/vacuumed? To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000007ebdc006427ae310" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007ebdc006427ae310 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Oct 31, 2025 at 4:52=E2=80=AFPM Adrian Klaver wrote: > On 10/31/25 13:03, Dimitrios Apostolou wrote: > > On Thursday 2025-10-30 18:00, Ron Johnson wrote: > > > >> > >> > SELECT reltuples FROM pg_class WHERE relname =3D > >> 'test_runs_summarized_per_function' \gx > >> -[ RECORD 1 ]----------- > >> reltuples | 6.061923e+09 > >> > >> > SELECT name,setting FROM pg_settings WHERE name ILIKE '%factor%= ' > ; > >> name | setting > >> ---------------------------------------+--------- > >> autovacuum_analyze_scale_factor | 0.1 > >> > >> > >> 0.1 means 10%. > >> > >> autovacuum_vacuum_insert_scale_factor | 0.2 > >> autovacuum_vacuum_scale_factor | 0.2 > >> recursive_worktable_factor | 10 > >> > >> > >> n_mod_since_analyze=3D423101205 > >> n_live_tup=3D6484485348 > >> > >> n_mod_since_analyze/n_live_tup =3D 6.5% > >> > >> How can I get more info from postgres on the autovacuum logic? > >> > >> > >> I would: > >> 1) manually VACUUM ANALYZE the table, > >> 2) drop the three autovacuum_*_scale_factor values down to 0.03 (i.e. > >> 3%), > > > > Reporting back, after reducing the values, the table has been picked up > > for both autovacuum and analyze. Thank you for the immediate feedback! > > > > Since I had spent some time looking into these values and was "certain" > > that they were % while they are apparently *not*, I'm wondering if > > max_val=3D100 is there because of historical reasons, and if it would m= ake > > sense to change it to 1. > > But they are: > > 0.1/1 is 10% as is 10/100. > And 0.1/100 =3D 0.1%. Dimitrios is right: it's misleading to have a default of 0.1 that means 10%, but also have the max value be 100 because 10 is 10% of 100. https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOV= ACUUM-ANALYZE-SCALE-FACTOR certainly doesn't mention that you can use either reals (0,1] or integers (0,100]. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000007ebdc006427ae310 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Fri, Oct 31, 2025 at 4:52=E2=80=AFPM A= drian Klaver <adrian.klaver= @aklaver.com> wrote:
On 10/31/25 13:03,= Dimitrios Apostolou wrote:
> On Thursday 2025-10-30 18:00, Ron Johnson wrote:
>
>>
>> =C2=A0=C2=A0=C2=A0=C2=A0 > SELECT reltuples FROM pg_class WHERE= relname =3D
>> =C2=A0=C2=A0=C2=A0=C2=A0 'test_runs_summarized_per_function= 9; \gx
>> =C2=A0=C2=A0=C2=A0=C2=A0 -[ RECORD 1 ]-----------
>> =C2=A0=C2=A0=C2=A0=C2=A0 reltuples | 6.061923e+09
>>
>> =C2=A0=C2=A0=C2=A0=C2=A0 > SELECT name,setting FROM pg_settings= WHERE name ILIKE '%factor%' ;
>> =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 name=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 | setting
>> =C2=A0=C2=A0=C2=A0=C2=A0 ---------------------------------------+-= --------
>> =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 autovacuum_analyze_scale_factor=C2= =A0 =C2=A0 =C2=A0 =C2=A0| 0.1
>>
>>
>> 0.1 means 10%.
>>
>> =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 autovacuum_vacuum_insert_scale_fac= tor | 0.2
>> =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 autovacuum_vacuum_scale_factor=C2= =A0 =C2=A0 =C2=A0 =C2=A0 | 0.2
>> =C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 recursive_worktable_factor=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | 10
>>
>>
>> n_mod_since_analyze=3D423101205
>> n_live_tup=3D6484485348
>>
>> n_mod_since_analyze/n_live_tup =3D 6.5%
>>
>> =C2=A0=C2=A0=C2=A0=C2=A0 How can I get more info from postgres on = the autovacuum logic?
>>
>>
>> I would:
>> 1) manually VACUUM ANALYZE the table,
>> 2) drop the three autovacuum_*_scale_factor values down to 0.03 (i= .e.
>> 3%),
>
> Reporting back, after reducing the values, the table has been picked u= p
> for both autovacuum and analyze. Thank you for the immediate feedback!=
>
> Since I had spent some time looking into these values and was "ce= rtain"
> that they were % while they are apparently *not*,=C2=A0 I'm wonder= ing if
> max_val=3D100 is there because of historical reasons, and if it would = make
> sense to change it to 1.

But they are:

0.1/1 is 10% as is 10/100.

And 0.1/100 = =3D 0.1%.

Dimitrios is right: it's misleading = to have a default of 0.1 that means 10%, but also have the max value be 100= because 10 is 10% of 100.

https://www.postgresql.org/docs/17/runtime-config-autovacuu= m.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR certainly doesn't mentio= n that you can use either reals (0,1] or integers (0,100].

--
Death to <Redacted>, = and butter sauce.
Don't boil me, I'm still alive.
= <Redacted> lobster!
--0000000000007ebdc006427ae310--