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

view thread (7+ messages)

Message-ID: <50d1b1cd-2198-09f6-cd76-0ed4e207bf13@gmail.com>
Permalink:  ../50d1b1cd-2198-09f6-cd76-0ed4e207bf13@gmail.com/
Also on:    postgresql.org/message-id/50d1b1cd-2198-09f6-cd76-0ed4e207bf13@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: <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