Re: ToDo: show size of partitioned table - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: ToDo: show size of partitioned table |
| Date | |
| Msg-id | CAFj8pRBOH=EHc9tqdab7erQDdis4nGnr6pq3Gc7oi=GHYZRj6g@mail.gmail.com Whole thread Raw |
| In response to | Re: ToDo: show size of partitioned table (Alvaro Herrera <alvherre@2ndquadrant.com>) |
| List | pgsql-hackers |
čt 7. 2. 2019 v 16:03 odesílatel Alvaro Herrera <alvherre@2ndquadrant.com> napsal:
On 2019-Feb-07, Pavel Stehule wrote:
> Your example
>
> postgres=# \dPtn+
> List of partitioned tables
> ┌────────┬────────┬───────┬─────────────┬────────────┬─────────────┐
> │ Schema │ Name │ Owner │ Parent name │ Size │ Description │
> ╞════════╪════════╪═══════╪═════════════╪════════════╪═════════════╡
> │ public │ p │ pavel │ │ 8192 bytes │ │
> │ public │ p_1 │ pavel │ p │ 8192 bytes │ │
> │ public │ p_1_bc │ pavel │ p_1 │ 8192 bytes │ │
> └────────┴────────┴───────┴─────────────┴────────────┴─────────────┘
> (3 rows)
>
> I hope so the interpretation is clean .. there are three partitioned tables
> (two are subpartitioned tables). Any partitioned table has assigned 8KB of
> data.
>
> We can introduce new column "size with sub partitions" where these numbers
> can be counted together. But for term "size" I expect valid rule S1+S2+..
> SN = total size.
Right now, a partitioned table has no size of its own; its only storage
is in its children. But that might change in the future, for example if
we implement global indexes. Then it will be useful to show these sizes
separately.
I suggest that we should have one column for the aggregated size of its
children, and another column for the "local" size. So currently the
local size would always be zero for partitioned tables. The other
column (maybe "Children Size") would be the sum of the sizes of all its
partitions.
So I think this should be:
So this is third proposals :-)
Can I recapitulate it?
1. My proposal - the "size" = sum(partitions on level1), "size with nested partitioned tables" = sum(partitions on all levels)
2. Amit's proposal - the "size" = sum(partions on all levels)
3. Alvaro's proposal - the "size" = empty now, "children size" = sum(partitions on level1)
Every proposal is valid, and has some sense and display some information from some perspective.
So first we should not to use one word term "size" in this context, because there is any agreement.
Can we find some terminology for two *sizes*?
1. size of all partitions with nesting level = 1
2. size of all partitions without nesting level check.
maybe there can be third variant - size of all partitions with nesting level > 1
I am not sure so "children size" is good term because this structure is tree, and the deep is not specified.
Maybe "Immediate partitions size" for @1, "total partitions size" for @2, and "indirect partitions size" for @3.
Then we can display @1, @2 or @1 or @3.
I hope so I mentioned all possible variants.
Comments, notes?
Pavel
List of partitioned tables
┌────────┬────────┬───────┬─────────────┬────────────┬───────────────┬────────────
│ Schema │ Name │ Owner │ Parent name │ Size │ Children Size │ Description
╞════════╪════════╪═══════╪═════════════╪════════════╪═══════════════╡
│ public │ p │ pavel │ │ │ 8192 bytes │
│ public │ p_1 │ pavel │ p │ │ 8192 bytes │
│ public │ p_1_bc │ pavel │ p_1 │ 8192 bytes │ │
└────────┴────────┴───────┴─────────────┴────────────┴───────────────┴─────────────
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgsql-hackers by date: