Re: ToDo: show size of partitioned table

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Michael Paquier <michael(at)paquier(dot)xyz>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, mathias(at)brossard(dot)org, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: ToDo: show size of partitioned table
Date: 2019-02-15 06:50:11
Message-ID: b809fdb4-9c48-a769-39c5-27d6463cff53@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi Pavel,

Thanks for updating the patch.

On 2019/02/08 17:26, Pavel Stehule wrote:
> I renamed originally calculated column "size" to "direct partitions size"
> .. see Alvaro's comment. Then I introduced new column "total partitions
> size" that is calculated like you propose.
>
> Now the result of dPn+ looks like
>
> List of partitioned relations
> ┌────────┬────────┬───────┬─────────────┬────────────────────────┬───────────────────────┬─────────────┐
> │ Schema │ Name │ Owner │ Parent name │ Direct partitions size │ Total
> partitions size │ Description │
> ╞════════╪════════╪═══════╪═════════════╪════════════════════════╪═══════════════════════╪═════════════╡
> │ public │ p │ pavel │ │ 8192 bytes │ 24
> kB │ │
> │ public │ p_1 │ pavel │ p │ 8192 bytes │ 16
> kB │ │
> │ public │ p_1_bc │ pavel │ p_1 │ 8192 bytes │ 8192
> bytes │ │
> └────────┴────────┴───────┴─────────────┴────────────────────────┴───────────────────────┴─────────────┘
> (3 rows)

OK, so for each listed partitioned table (root and nested), this shows the
total size of the directly attached leaf partitions *and* the total size
of all partitions in its (sub-) tree.

By the way, what I think Alvaro meant by "local size" is not what the
"direct partition size" above shows. I think "local size" means the size
of the storage assigned to the table itself, not to partitions attached to
it, which are distinct relations. We don't implement that concept in
Postgres today, but may in the future. I'm not sure if we'll add a
another column to show "local size" in the future when we do implement
that concept or if Alvaro meant that there should only be "local size"
(not "direct partition size") which will always show 0 for now and "total
partition size" columns.

Anyway, I have a few more suggestions to improve the patch, but instead of
sending the minute-level changes in the email, I've gone ahead and made
those changes myself. I've attached a delta patch that applies on top of
your v9 patch. Summary of the changes I made is as follows:

* Documentation rewording here and there (also mentioned the "direct
partitions size" and "total partitions size" division in the \dPn output
per the latest patch)

* Wrapped some lines in code so that they don't look too wide

* Renamed show_nested_partitions to show_nested

* Changed "Partitioned relations" in the output headers to say
"Partitioned tables" where appropriate

* Fixed quiet mode output to use correct word between object_name vs
objects_name

Please merge these changes if you think they are reasonable.

Thanks,
Amit

Attachment Content-Type Size
v9-delta-amit.patch text/plain 13.1 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Sergei Kornilov 2019-02-15 07:32:30 Re: Planning counters in pg_stat_statements (using pgss_store)
Previous Message John Naylor 2019-02-15 06:46:49 Re: Delay locking partitions during INSERT and UPDATE