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

pá 17. 9. 2021 v 11:10 odesílatel Justin Pryzby <pryzby(at)telsasoft(dot)com>
napsal:

> 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)
>
>
I tested this patch. It looks well. The performance is good enough. I got
the result for a schema with 100K tables in 3 seconds.

I am not sure if using \dt+ and \dP+ without change is a good idea. I can
imagine \dt+ and \dt++. \dP can exist just only in ++ form or we can ignore
it (like now, and support \dP+ and \dP++) with same result

I can live with the proposed patch, and I understand why ++ was
introduced. But I am still not sure it is really user friendly. I prefer to
extend \dA and \dn with some columns (\dA has only two columns and \dn has
two columns too), and then we don't need special ++ variants for sizes.
Using three levels of detail looks not too practical (more when the basic
reports \dA and \dn) are really very simple).

Regards

Pavel

--
> Justin
>