Re: Autovacuum on Partitioned Tables

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.