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 1k0Hy9-0005mT-5s for pgsql-admin@arkaria.postgresql.org; Tue, 28 Jul 2020 05:16:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1k0Hy5-00087b-Jo for pgsql-admin@arkaria.postgresql.org; Tue, 28 Jul 2020 05:16:01 +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 1k0Hy5-00087T-8j for pgsql-admin@lists.postgresql.org; Tue, 28 Jul 2020 05:16:01 +0000 Received: from mail-oi1-x22a.google.com ([2607:f8b0:4864:20::22a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1k0Hy2-00059J-P1 for pgsql-admin@lists.postgresql.org; Tue, 28 Jul 2020 05:16:00 +0000 Received: by mail-oi1-x22a.google.com with SMTP id k4so16422697oik.2 for ; Mon, 27 Jul 2020 22:15:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=OkDs1ZVX78jkFUJkbCDgfFytPmW1PDXtlvLo4oRo41c=; b=oUjmyRCgV2jz+pjW6sxz7QZ++0BiPvWPfyEFt+ag4CizFyExVDNiTwWG2mNOv5quwn FwG5fxlCWRaw4TREb4IOq5DZuBq80XQhPUCQEI+TQSCfFkz2EfoWVt3wkua81jAUPd1F 9kJmaIE7ESP8hhmzjAHvZmsfwxzSkMLsovAZXDhmz6We2KZyvtVkjGMZ03lHh8jMsfTC 0ikwT0PEPVbMlE4+2ZmLGJxUYChgP18Du+Pau2BsRkDupJuDD3g6s/1KfRytYpHKm+jQ PNXEOnsZ7Lm1j0PiTtmIGbKhgeSlZCTrTz7wB9O/lGiq4x5mZUy9OOC4eWshJUprrIu6 KfKg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=OkDs1ZVX78jkFUJkbCDgfFytPmW1PDXtlvLo4oRo41c=; b=oCZdkgkNSMoof2xTOlPcJEorfVOPseYESCs5Yb4c2utdS0FQDnVWbE987NpGFw60lM 80EFvFFktBwDKjx+FsD106P37dvMaH3oN0KvbmWh2bb/EdFxW7fshLxjt91InzpA5jnB A9E/6ZFBU3IZGj0ZC/pBHOoTM7JEU4ikM3sl9z/MsOY4VKl/CnL9co+WfUbYox/pjMxM QuwVIZ37mL1qeJJWOeetGaBpJynXVhCmvlQ2EquVzxqN5/UlyvkLhVLa0OfnYgkNFW7p 7XY54E1iul38B5KfKa0oPQXinO1i4e/yDVaiQXJTN0WHuVUEYEzeFA2KxLe8TJsR1pav CRoA== X-Gm-Message-State: AOAM533FGde22+sb8WDpaL580s5uuSu9CiPlY6eLYSjjArPfD2hLzmx0 Iqv0i1rlMvg1W8kiUNIX6JqWCBdF4lE= X-Google-Smtp-Source: ABdhPJxSAhJFQnz9MRNO2VPJjbxb45HeA1w24GvvcO5fb8NJQU4gWw2X+/eY7+Ip/ninjv0xKgzwDA== X-Received: by 2002:aca:f3d4:: with SMTP id r203mr2235242oih.23.1595913356237; Mon, 27 Jul 2020 22:15:56 -0700 (PDT) Received: from [192.168.1.10] (ip70-186-126-197.no.no.cox.net. [70.186.126.197]) by smtp.googlemail.com with ESMTPSA id f187sm2504470oig.12.2020.07.27.22.15.54 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 27 Jul 2020 22:15:55 -0700 (PDT) Subject: Re: How can i be certain autovacuum is causing reuse if table still grows To: "pgsql-admin@lists.postgresql.org" References: <20d2fb88-89f8-34b9-0e23-81a8b1d8c7a2@gmail.com> From: Ron Message-ID: <2500ca1c-312e-c495-ad98-1b593d030c71@gmail.com> Date: Tue, 28 Jul 2020 00:15:54 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------B5FE668CF742B2E8B61827F4" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------B5FE668CF742B2E8B61827F4 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit > All lines even old ones might be updated by our application. Ah.  Even so, there are benefits to maintaining "lots of smaller chunks" instead of One Giant Table: "pg_dump --jobs=X" will run faster, for example, when there are more small-to-mid-sized tables instead of A Few Giant Tables.  Ditto "reindexdb --jobs=X". Given that even "old" records get updated, I'd look into some other key that you can "naturally" partition on. On 7/27/20 10:54 PM, Sidney Aloisio Ferreira Pryor wrote: > Thank you Ron. > Yes there is a column id in which value is provided by a sequence. > > But it was not clear to me exactly how to partition? > The idea is to cover a limited range of "id" so there will be less lines > to vacuum on each operation? > > Because yet we will need eventually to vacuum all others. > All lines even old ones might be updated by our application. > > ---------------------------------------------------------------------------- > *De:* Ron > *Enviado:* terça-feira, 28 de julho de 2020 00:39 > *Para:* pgsql-admin@lists.postgresql.org > *Assunto:* Re: How can i be certain autovacuum is causing reuse if table > still grows > On 7/27/20 5:19 PM, Keith Fiske wrote: > [snip] > > Also, I would not recommend partitioning simply to improve vacuuming. > > Especially if extensive tuning hasn't been tried first. Most times you can > > get per-table tuning working well enough to get autovacuum running > > properly. Especially on 9.6 and even more-so on PG11, where autovacuum has > > itself been improved. > > SIMPLY to improve vacuum performance?  No.  But there are reasons that > partitioning was "invented", and minimizing the work needed to be done on > the whole of a Very Large and Rapidly Growing table is one of them. > > -- > Angular momentum makes the world go 'round. > > -- Angular momentum makes the world go 'round. --------------B5FE668CF742B2E8B61827F4 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
> All lines even old ones might be updated by our application.

Ah.  Even so, there are benefits to maintaining "lots of smaller chunks" instead of One Giant Table: "pg_dump --jobs=X" will run faster, for example, when there are more small-to-mid-sized tables instead of A Few Giant Tables.  Ditto "reindexdb --jobs=X". 

Given that even "old" records get updated, I'd look into some other key that you can "naturally" partition on.

On 7/27/20 10:54 PM, Sidney Aloisio Ferreira Pryor wrote:
Thank you Ron.
Yes there is a column id in which value is provided by a sequence.

But it was not clear to me exactly how to partition?
The idea is to cover a limited range of "id" so there will be less lines to vacuum on each operation?

Because yet we will need eventually to vacuum all others.
All lines even old ones might be updated by our application.

De: Ron <ronljohnsonjr@gmail.com>
Enviado: terça-feira, 28 de julho de 2020 00:39
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
 
On 7/27/20 5:19 PM, Keith Fiske wrote:
[snip]
> Also, I would not recommend partitioning simply to improve vacuuming.
> Especially if extensive tuning hasn't been tried first. Most times you can
> get per-table tuning working well enough to get autovacuum running
> properly. Especially on 9.6 and even more-so on PG11, where autovacuum has
> itself been improved.

SIMPLY to improve vacuum performance?  No.  But there are reasons that
partitioning was "invented", and minimizing the work needed to be done on
the whole of a Very Large and Rapidly Growing table is one of them.

--
Angular momentum makes the world go 'round.



--
Angular momentum makes the world go 'round.
--------------B5FE668CF742B2E8B61827F4--