pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Autovacuum on Partitioned Tables
Date: Mon, 31 Oct 2022 22:27:56 -0500
Message-ID: <ecf63c8a-8eca-3152-9c62-3574cb7a2098@gmail.com> (raw)
In-Reply-To: <CAEuohQp_iK8jKceQ1BUKZWtzhz4F=utke1WcYqK31ZAV4FV-Og@mail.gmail.com>
References: <CAEuohQp_iK8jKceQ1BUKZWtzhz4F=utke1WcYqK31ZAV4FV-Og@mail.gmail.com>

On 10/31/22 17:26, Ryan Ruenroeng wrote:
> Hello there!
>
> There is a statement in the Postgres Docs: "Partitioned tables are not 
> processed by autovacuum."
>
> What does the above statement mean?
> Does autovacuum not process both the parent and the child tables in a 
> partition relationship?

Partitioned (aka parent) tables are "virtual".  There's nothing to vacuum 
are analyze.

>
> What is the definition of a partitioned table?
>
> I have a table with 50+ million rows that gets data added to/wiped from it 
> every 90 days. We are planning to break this table into a few thousand 
> partitions.

1. That's a lot of partitions.  Older (like v12) query optimizers don't do a 
great job handle that many partitions.
2. Be careful what you partition on.  (We added "partition_date" to PKs so 
as to partition by date, even though the "real" PK is a synthetic key; it 
was a query performance *disaster* in Pg 12.)

> More partitions will likely be added in the future, but we don't have 
> plans to delete any of the partitions. Will we need to manually track the 
> statistics of these partitions and manually vacuum the tables or will 
> autovacuum help to manage them?

Autovacuum will handle it.

-- 
Angular momentum makes the world go 'round.

view thread (7+ messages)  latest in thread

Message-ID: <ecf63c8a-8eca-3152-9c62-3574cb7a2098@gmail.com>
Permalink:  ../ecf63c8a-8eca-3152-9c62-3574cb7a2098@gmail.com/
Also on:    postgresql.org/message-id/ecf63c8a-8eca-3152-9c62-3574cb7a2098@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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: Autovacuum on Partitioned Tables
  In-Reply-To: <ecf63c8a-8eca-3152-9c62-3574cb7a2098@gmail.com>
  References: <CAEuohQp_iK8jKceQ1BUKZWtzhz4F=utke1WcYqK31ZAV4FV-Og@mail.gmail.com> <ecf63c8a-8eca-3152-9c62-3574cb7a2098@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox