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