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.
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