Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k0JLi-0000g8-OP for pgsql-admin@arkaria.postgresql.org; Tue, 28 Jul 2020 06:44:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k0JLf-0007s4-Rc for pgsql-admin@arkaria.postgresql.org; Tue, 28 Jul 2020 06:44:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k0JLf-0007rq-II for pgsql-admin@lists.postgresql.org; Tue, 28 Jul 2020 06:44:27 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1k0JLa-0005xJ-B9 for pgsql-admin@lists.postgresql.org; Tue, 28 Jul 2020 06:44:25 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 823AC3803C2; Tue, 28 Jul 2020 08:44:20 +0200 (CEST) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id 965da4XU2sAR; Tue, 28 Jul 2020 08:44:20 +0200 (CEST) Received: from [192.168.178.97] (ip-37-201-154-65.hsi13.unitymediagroup.de [37.201.154.65]) by rs.plausibolo.de (Postfix) with ESMTPSA id 31D0C38033F; Tue, 28 Jul 2020 08:44:20 +0200 (CEST) Subject: Re: How can i be certain autovacuum is causing reuse if table still grows To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs Message-ID: Date: Tue, 28 Jul 2020 08:44:18 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------F80CA7BC50236E059256B638" Content-Language: de-DE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------F80CA7BC50236E059256B638 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit So you can use your SERIAL (or GENERATED AS IDENTITY) column for this. If you don't have this, you might add a DATE DEFAULT CURRENT_DATE column, even if it's of no use except being the partition key. Am 28.07.20 um 05:32 schrieb Ron: > Any key that "increases linearly over time" is a chronological key; > sequences are a perfect example. > > On 7/27/20 4:38 PM, Sidney Aloisio Ferreira Pryor wrote: >> >> Thank you Ron but this table has no date column. >> >> It has one line for each page of each scanned document. >> >> ------------------------------------------------------------------------ >> *De:* Ron >> *Enviado:* segunda-feira, 27 de julho de 2020 18:27 >> *Para:* pgsql-admin@lists.postgresql.org >> >> *Assunto:* Re: How can i be certain autovacuum is causing reuse if >> table still grows >>   >> Another suggestion is to partition the table by a chronological key, >> so that only a fraction of the table needs to be vacuumed. >> >> On 7/27/20 3:36 PM, Sidney Aloisio Ferreira Pryor wrote: >>> Thank you, David. >>> So can i assume that autovacuum only release dead tuples when it >>> ends its execution? >>> >>> Autovacuum is not finishing and is running for days. >>> We did not find a parameter to limit autovacuum executing time. >>> Do you have any recomendation? >>> >>> Thank you. >>> Sidney. >>> ------------------------------------------------------------------------ >>> *De:* David G. Johnston >>> >>> *Enviado:* segunda-feira, 27 de julho de 2020 17:17 >>> *Para:* Sidney Aloisio Ferreira Pryor >>> >>> *Cc:* pgsql-admin@lists.postgresql.org >>> >>> >>> >>> *Assunto:* Re: How can i be certain autovacuum is causing reuse if >>> table still grows >>>   >>> On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor >>> > wrote: >>> >>> Hi, we have a 9.6.5 postgres >>> >>>   >>> >>> Autovacuum is set with default parameters and is running on this >>> table for days without naping or stopping. >>> >>>  3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM >>> ANALYZE public.flip_pagina_edicao (to prevent wraparound) >>> (1 rows) >>> >>> >>> Its probably best avoid drawing conclusions here until the >>> anti-wraparound vacuum has completed and you’ve updated to the >>> latest minor version of PostgreSQL. >>> >>> David J. >>> >> >> -- >> Angular momentum makes the world go 'round. > > -- > Angular momentum makes the world go 'round. -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 --------------F80CA7BC50236E059256B638 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

So you can use your SERIAL (or GENERATED AS IDENTITY) column for this. If you don't have this, you might add a DATE DEFAULT CURRENT_DATE column, even if it's of no use except being the partition key.


Am 28.07.20 um 05:32 schrieb Ron:
Any key that "increases linearly over time" is a chronological key; sequences are a perfect example.

On 7/27/20 4:38 PM, Sidney Aloisio Ferreira Pryor wrote:

Thank you Ron but this table has no date column.

It has one line for each page of each scanned document.

De: Ron <ronljohnsonjr@gmail.com>
Enviado: segunda-feira, 27 de julho de 2020 18:27
Para: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
 
Another suggestion is to partition the table by a chronological key, so that only a fraction of the table needs to be vacuumed.

On 7/27/20 3:36 PM, Sidney Aloisio Ferreira Pryor wrote:
Thank you, David.
So can i assume that autovacuum only release dead tuples when it ends its execution?

Autovacuum is not finishing and is running for days.
We did not find a parameter to limit autovacuum executing time.
Do you have any recomendation?

Thank you.
Sidney.
De: David G. Johnston <david.g.johnston@gmail.com>
Enviado: segunda-feira, 27 de julho de 2020 17:17
Para: Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Assunto: Re: How can i be certain autovacuum is causing reuse if table still grows
 
On Monday, July 27, 2020, Sidney Aloisio Ferreira Pryor <sidney@tjrj.jus.br> wrote:
Hi, we have a 9.6.5 postgres
 
Autovacuum is set with default parameters and is running on this table for days without naping or stopping.

 3285 | -5 days -23:33:10.792166 | postgres | autovacuum: VACUUM ANALYZE public.flip_pagina_edicao (to prevent wraparound)
(1 rows)


Its probably best avoid drawing conclusions here until the anti-wraparound vacuum has completed and you’ve updated to the latest minor version of PostgreSQL.

David J.


--
Angular momentum makes the world go 'round.

--
Angular momentum makes the world go 'round.
-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------F80CA7BC50236E059256B638--