Re: [patch] Add schema total size to psql \dn+

Gilles Darold <gilles(dot)darold(at)dalibo(dot)com> writes:

> Le 21/02/2019 à 18:28, Julien Rouhaud a écrit :
>
>> On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold <gilles(dot)darold(at)dalibo(dot)com> wrote:
>>> Le 21/02/2019 à 12:01, Julien Rouhaud a écrit :
>>>> On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles(dot)darold(at)dalibo(dot)com> wrote:
>>>>>> When we want to get total size of all relation in a schema we have to
>>>>>> execute one of our favorite DBA query. It is quite simple but what
>>>>>> about displaying schema size when using \dn+ in psql ?
>>>>>> [...]
>>>>>> The attached simple patch adds this feature. Is there any cons adding
>>>>>> this information? The patch tries to be compatible to all PostgreSQL
>>>>>> version. Let me know if I have missed something.
>> I have a few comments about the patch.
>>
>> You're using pg_class LEFT JOIN pg_namespace while we need INNER JOIN
>> here AFAICT. Also, you're using pg_relation_size(), so fsm, vm won't
>> be accounted for. You should also be bypassing the size for 8.0-
>> servers where there's no pg_*_size() functions.
>
>
> I agree all points. Attached is a new version of the patch that use
> pg_total_relation_size() and a filter on relkind IN ('r','m','S'), JOIN
> fixes and no size report before 8.1.

Beware that those pg_relation_size() functions are going to block in
cases where existing objects are (for example) in transactionss such
as...

begin;
truncate foo;
big-nasty-reporting-jobs...;

Thus a bare-metal tallying of pg_class.relpages for heap/index/toast,
along with missing the FSM/VM size could be $preferred.

And/or at least mentioning this caveat in the related manual section :-)

FWIW

>
>
> Thanks for the review.

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net