From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Autovacuum on Partitioned Tables
Date: Tue, 1 Nov 2022 11:05:30 -0500
Message-ID: <50d1b1cd-2198-09f6-cd76-0ed4e207bf13@gmail.com> (raw)
In-Reply-To: <CAEuohQoxOQOgS6TefKJvgdmVwVNJMu7qE0hWrPT6J7h03gkYjA@mail.gmail.com>
References: <CAEuohQp_iK8jKceQ1BUKZWtzhz4F=utke1WcYqK31ZAV4FV-Og@mail.gmail.com>
<ecf63c8a-8eca-3152-9c62-3574cb7a2098@gmail.com>
<1071326.1667275514@sss.pgh.pa.us>
<0780cad9-0317-ef27-5b18-fa4f16087e7b@gmail.com>
<CAEuohQoxOQOgS6TefKJvgdmVwVNJMu7qE0hWrPT6J7h03gkYjA@mail.gmail.com>
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@gmail.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@gmail.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.
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: <50d1b1cd-2198-09f6-cd76-0ed4e207bf13@gmail.com>
References: <CAEuohQp_iK8jKceQ1BUKZWtzhz4F=utke1WcYqK31ZAV4FV-Og@mail.gmail.com> <ecf63c8a-8eca-3152-9c62-3574cb7a2098@gmail.com> <1071326.1667275514@sss.pgh.pa.us> <0780cad9-0317-ef27-5b18-fa4f16087e7b@gmail.com> <CAEuohQoxOQOgS6TefKJvgdmVwVNJMu7qE0hWrPT6J7h03gkYjA@mail.gmail.com> <50d1b1cd-2198-09f6-cd76-0ed4e207bf13@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