Re: Autovacuum on Partitioned Tables

On 11/1/22 10:11, Ryan Ruenroeng wrote:T
> Thanks all for your responses. We have a couple of tables. Each with
> 50-70mil rows currently, but they are expected to grow. Partitioning seems
> to be a better long-term strategy, queries to these tables, using their
> existing indexes, leaves them basically unusable (loooong run times).
>
> FYI, we are currently on PG v11.16
>
> Let's assume we do go with my current proposal because it is inline with
> how we are planning to drop partitions that stop seeing activity in the
> future:
>
> [snip] from Ron's post above:
> Autovacuum will handle it.
>
> I still have some doubts based on this:
> "Tuples changed in partitions and inheritance children do not trigger
> analyze on the parent table. If the parent table is empty or rarely
> changed, it may never be processed by autovacuum, and the statistics for
> the inheritance tree as a whole won't be collected. It is necessary to run
> |ANALYZE|/ on the parent table manually in order to keep the statistics up to
> date." /[Link <https://www.postgresql.org/docs/15/routine-vacuuming.html>]
> Q1: Will we at least need to call Analyze via a cron job on the parent
> table to ensure that the statistics are up to date for autovacuum to catch
> the tables?

Tuning auto-analyze variables is supposed to obviate the need for manual
vacuuming *analyzing*.  I do manual vacuuming *anayzing* anyway in a cron
job. "This set of tables have had more than X amount of changes, so I'll
/explictly/ analyze them."

Once a week, I do bloat calculations and vacuum based on that.

> From reading the documentation that a few of you have pointed me to, I'm
> led to believe that the parent table is the "Partition" table.

"\d" will show you exactly what you need to know.

> The children tables are treated by the autovacuum as tables

Child tables *are* tables.

> Q2: Autovacuum will act on the partitions/children to the parent table.
> *Is that a correct statement?*

Parents are meta-tables.  There's nothing to vacuum or analyze.

> --
> It's good to know that the query optimizer will improve with partitions on
> versions 12+. Thank you.
>
> Best,
> Ryan
>
> Ryan N Ruenroeng (He/His)
> (717) 578-3929 <tel:(717)+578-3929>
>
>
> | RRuenroeng(at)gmail(dot)com
>
>
>
> | Madison, WI <https://maps.google.com/?q=Madison,%20WI>
>
> <https://github.com/rruenroeng> <https://www.facebook.com/ryan.ruenroeng>
> <https://www.linkedin.com/in/ryan-ruenroeng>
>
>
>
> On Tue, Nov 1, 2022 at 2:54 AM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
> On 10/31/22 23:05, Tom Lane wrote:
> [snip]
> > TBH, if you've got 50m rows, I'm not sure you need partitions at all.
>
> Big rows (i.e. document storage tables with bytea or xml fields) can make
> databases explode in size even with only 50M rows.
>
> (Yes, I know the arguments against it, but it works quite well when the
> database is in a cloud instance.  Worries about backup times, at
> least, are
> eliminated.)
>
> --
> Angular momentum makes the world go 'round.
>
>

--
Angular momentum makes the world go 'round.