Re: [PATCH] psql: \dn+ to show size of each schema (and \dA+ for AMs)

On Wed, Jul 14, 2021 at 07:42:33AM +0200, Laurenz Albe wrote:
> Besides, schemas are not physical, but logical containers.  So I see a point in
> measuring the storage used in a certain tablespace, but not so much by all objects
> in a certain schema.  It might be useful for accounting purposes, though.

We use only a few schemas, 1) to hide child tables; 2) to exclude some extended
stats from backups, and 1-2 other things.  But it's useful to be able to see
how storage is used by schema, and better to do it conveniently.

I think it'd be even more useful for people who use schemas more widely than we
do:
    "Who's using all our space?"
    \dn++
    "Oh, it's that one - let me clean that up..."

Or, "what's the pg_toast stuff, and do I need to do something about it?"

> But I don't expect it to be in frequent enough demand to add a psql command.
>
> What about inventing a function pg_schema_size(regnamespace)?

But for "physical" storage it's also possible to get the size from the OS, much
more efficiently, using /bin/df or zfs list (assuming nothing else is using
those filesystems).  The pg_*_size functions are inefficient, but psql \db+ and
\l+ already call them anyway.

For schemas, there's no way to get the size from the OS, so it's nice to make
the size available from psql, conveniently.

v3 patch:
 - fixes an off by one in forkNum loop;
 - removes an unnecessary subquery in describe.c;
 - returns 0 rather than NULL if the schema is empty;
 - adds pg_am_size;

regression=# \dA++
                                  List of access methods
  Name  | Type  |       Handler        |              Description               |  Size   
--------+-------+----------------------+----------------------------------------+---------
 brin   | Index | brinhandler          | block range index (BRIN) access method | 744 kB
 btree  | Index | bthandler            | b-tree index access method             | 21 MB
 gin    | Index | ginhandler           | GIN index access method                | 2672 kB
 gist   | Index | gisthandler          | GiST index access method               | 2800 kB
 hash   | Index | hashhandler          | hash index access method               | 2112 kB
 heap   | Table | heap_tableam_handler | heap table access method               | 60 MB
 heap2  | Table | heap_tableam_handler |                                        | 120 kB
 spgist | Index | spghandler           | SP-GiST index access method            | 5840 kB
(8 rows)

regression=# \dn++
                                   List of schemas
        Name        |  Owner  | Access privileges  |      Description       |  Size   
--------------------+---------+--------------------+------------------------+---------
 fkpart3            | pryzbyj |                    |                        | 168 kB
 fkpart4            | pryzbyj |                    |                        | 104 kB
 fkpart5            | pryzbyj |                    |                        | 40 kB
 fkpart6            | pryzbyj |                    |                        | 48 kB
 mvtest_mvschema    | pryzbyj |                    |                        | 16 kB
 public             | pryzbyj | pryzbyj=UC/pryzbyj+| standard public schema | 69 MB
                    |         | =UC/pryzbyj        |                        |
 regress_indexing   | pryzbyj |                    |                        | 48 kB
 regress_rls_schema | pryzbyj |                    |                        | 0 bytes
 regress_schema_2   | pryzbyj |                    |                        | 0 bytes
 testxmlschema      | pryzbyj |                    |                        | 24 kB
(10 rows)

--
Justin