From: Nathan Bossart <nathandbossart@gmail.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>
Cc: yuzuko <yuzukohosoya@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: document the need to analyze partitioned tables
Date: Thu, 12 Jan 2023 15:27:47 -0800
Message-ID: <20230112232747.GA2111950@nathanxps13> (raw)
In-Reply-To: <1fd81ddc7710a154834030133c6fea41e55c8efb.camel@cybertec.at>
References: <20210913035409.GA10647@telsasoft.com>
<20211008125822.GJ27491@telsasoft.com>
<681e76ad-3a7b-ea3c-b6b0-55946239c49b@enterprisedb.com>
<20220121180200.GM23027@telsasoft.com>
<19c248d9-3687-9e0f-0b1a-2adad1de6fbe@enterprisedb.com>
<CA+TgmoYNaSWQg1W3JZ62UBM9iMphBpVJr_oDxn5T1X_viOPBtw@mail.gmail.com>
<20220315230011.GZ28503@telsasoft.com>
<88903179-5ce2-3d4d-af43-7830372bdcb6@enterprisedb.com>
<1fd81ddc7710a154834030133c6fea41e55c8efb.camel@cybertec.at>
On Wed, Oct 05, 2022 at 10:37:01AM +0200, Laurenz Albe wrote:
> On Mon, 2022-03-28 at 15:05 +0200, Tomas Vondra wrote:
>> I've pushed the last version, and backpatched it to 10 (not sure I'd
>> call it a bugfix, but I certainly agree with Justin it's worth
>> mentioning in the docs, even on older branches).
>
> I'd like to suggest an improvement to this. The current wording could
> be read to mean that dead tuples won't get cleaned up in partitioned tables.
Well, dead tuples won't get cleaned up in partitioned tables, as
partitioned tables do not have storage. But I see what you mean. Readers
might misinterpret this to mean that autovacuum will not process the
partitions. There's a good definition of what the docs mean by
"partitioned table" [0], but FWIW it took me some time before I
consistently read "partitioned table" to mean "only the thing with relkind
set to 'p'" and not "both the partitioned table and its partitions." So,
while the current wording it technically correct, I think it'd be
reasonable to expand it to help avoid confusion.
Here is my take on the wording:
Since all the data for a partitioned table is stored in its partitions,
autovacuum does not process partitioned tables. Instead, autovacuum
processes the individual partitions that are regular tables. This
means that autovacuum only gathers statistics for the regular tables
that serve as partitions and not for the partitioned tables. Since
queries may rely on a partitioned table's statistics, you should
collect statistics via the ANALYZE command when it is first populated,
and again whenever the distribution of data in its partitions changes
significantly.
[0] https://www.postgresql.org/docs/devel/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
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 using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, laurenz.albe@cybertec.at, tomas.vondra@enterprisedb.com, pryzby@telsasoft.com, robertmhaas@gmail.com, alvherre@alvh.no-ip.org, yuzukohosoya@gmail.com
Subject: Re: document the need to analyze partitioned tables
In-Reply-To: <20230112232747.GA2111950@nathanxps13>
* 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