Re: pg_(total_)relation_size and partitioned tables

On 17 December 2017 at 16:24, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Dec 14, 2017 at 12:23 AM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> You may have guessed from $subject that the two don't work together.
>
> It works exactly as documented:
>
> pg_total_relation_size(regclass) - Total disk space used by the
> specified table, including all indexes and TOAST data
>
> It says nothing about including partitions. If we change this, then
> we certainly need to update the documentation (that might be a good
> idea if we decide not to update this).
>
> Personally, I'm -1 on including partitions, because then you can no
> longer expect that the sum of pg_total_relation_size(regclass) across
> all relations in the database will equal the size of the database
> itself. Partitions will be counted a number of times equal to their
> depth in the partitioning hierarchy. However, I understand that I
> might get outvoted.

I'd also vote to leave the relation_size functions alone.

Perhaps it's worth thinking of changing pg_table_size() instead. We
have taken measures to try and hide the fact that a table is made up
of a bunch of partitions from the user in some cases, e.g DROP TABLE
works without CASCADE for a partitioned table. I'm sure there are
arguments in both directions here too though.

I generally think of the difference between a relation and a table
similarly to the difference between a tuple and a row. A relation is
just what we use to implement tables, and there can be multiple
relations per table (in the partitioning case), similar to how there
can be multiple tuple versions for a single row. So that might back up
that pg_table_size should include all relations that make up that
table.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services