Thread: [patch] Add schema total size to psql \dn+
Hi all, 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 ? gilles=# \dn+ List of schemas Name | Owner | Access privileges | Size | Description --------+----------+----------------------+---------+------------------------ public | postgres | postgres=UC/postgres+| 608 kB | standard public schema | | =UC/postgres | | test | gilles | | 57 MB | empty | gilles | | 0 bytes | (3 rows) 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. Best regards, -- Gilles Darold Consultant PostgreSQL http://dalibo.com - http://dalibo.org
Attachment
Le 20/02/2019 à 23:26, Gilles Darold a écrit : > Hi all, > > > 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 ? > > > gilles=# \dn+ > List of schemas > Name | Owner | Access privileges | Size | Description > --------+----------+----------------------+---------+------------------------ > public | postgres | postgres=UC/postgres+| 608 kB | standard public schema > | | =UC/postgres | | > test | gilles | | 57 MB | > empty | gilles | | 0 bytes | > (3 rows) > > 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. Improve this patch by using LATERAL JOIN when version >= 9.3. -- Gilles Darold Consultant PostgreSQL http://dalibo.com - http://dalibo.org
Attachment
On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles.darold@dalibo.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 needed that quite often, so I'm +1 to add this! Please register this patch on the next commitfest.
Le 21/02/2019 à 12:01, Julien Rouhaud a écrit : > On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles.darold@dalibo.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 needed that quite often, so I'm +1 to add this! Please register > this patch on the next commitfest. Added to next commitfest. -- Gilles Darold Consultant PostgreSQL http://dalibo.com - http://dalibo.org
On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold <gilles.darold@dalibo.com> wrote: > > Le 21/02/2019 à 12:01, Julien Rouhaud a écrit : > > On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles.darold@dalibo.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.
Le 21/02/2019 à 18:28, Julien Rouhaud a écrit :
> On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold <gilles.darold@dalibo.com> wrote:
>> Le 21/02/2019 à 12:01, Julien Rouhaud a écrit :
>>> On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles.darold@dalibo.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.
Thanks for the review.
--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org
Attachment
Gilles Darold <gilles.darold@dalibo.com> writes:
> Le 21/02/2019 à 18:28, Julien Rouhaud a écrit :
>
>> On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold <gilles.darold@dalibo.com> wrote:
>>> Le 21/02/2019 à 12:01, Julien Rouhaud a écrit :
>>>> On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles.darold@dalibo.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.consulting@comcast.net
Le 21/02/2019 à 21:57, Jerry Sievers a écrit :
> Gilles Darold <gilles.darold@dalibo.com> writes:
>
>> Le 21/02/2019 à 18:28, Julien Rouhaud a écrit :
>>
>>> On Thu, Feb 21, 2019 at 5:42 PM Gilles Darold <gilles.darold@dalibo.com> wrote:
>>>> Le 21/02/2019 à 12:01, Julien Rouhaud a écrit :
>>>>> On Thu, Feb 21, 2019 at 11:49 AM Gilles Darold <gilles.darold@dalibo.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 :-)
It's true but we already have this caveats with \d+ or \dt+. They are
interactive commands so they can be canceled if they takes too long time.
I've attached the v4 of the patch that adds psql documentation update
for the \dn command to add on-disk report in verbose mode. Thanks for
the reminder :-)
--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org
Attachment
On Wed, Feb 20, 2019 at 5:26 PM Gilles Darold <gilles.darold@dalibo.com> wrote: > The attached simple patch adds this feature. Is there any cons adding > this information? Well, it'll take time to compute, maybe a lot of time if the database is big and the server is busy. Not sure how serious that problem can get. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Feb 20, 2019 at 5:26 PM Gilles Darold <gilles.darold@dalibo.com> wrote:
>> The attached simple patch adds this feature. Is there any cons adding
>> this information?
> Well, it'll take time to compute, maybe a lot of time if the database
> is big and the server is busy. Not sure how serious that problem can
> get.
Is there any permissions issue involved here? I'd be a bit worried
about whether \dn+ could fail, or deliver misleading answers, when
run by a user without permissions on (some) tables. Also, even if
we allow people to get size info on tables they can't read today,
having this feature would be a roadblock to tightening that in
the future.
regards, tom lane
On Fri, Feb 22, 2019 at 7:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Is there any permissions issue involved here? I'd be a bit worried > about whether \dn+ could fail, or deliver misleading answers, when > run by a user without permissions on (some) tables. Also, even if > we allow people to get size info on tables they can't read today, > having this feature would be a roadblock to tightening that in > the future. Gilles' patch is using pg_total_relation_size(), so there's no permission check at all. Also AFAICS this function even allows any user to get the size of any other user backend's temporary table.
Le 22/02/2019 à 19:21, Tom Lane a écrit : > Robert Haas <robertmhaas@gmail.com> writes: >> On Wed, Feb 20, 2019 at 5:26 PM Gilles Darold <gilles.darold@dalibo.com> wrote: >>> The attached simple patch adds this feature. Is there any cons adding >>> this information? >> Well, it'll take time to compute, maybe a lot of time if the database >> is big and the server is busy. Not sure how serious that problem can >> get. > Is there any permissions issue involved here? I'd be a bit worried > about whether \dn+ could fail, or deliver misleading answers, when > run by a user without permissions on (some) tables. Also, even if > we allow people to get size info on tables they can't read today, > having this feature would be a roadblock to tightening that in > the future. That's right, I've removed the patch. My first idea was to add a server side function pg_schema_size() but I was thinking that a psql implementation was enough but obviously that was not my best idea ever. Let me know if there is any interest in having this pg_schema_size() server side function that could take care of user permissions or be used by a super user only. Best regards, -- Gilles Darold Consultant PostgreSQL http://dalibo.com - http://dalibo.org
Le 22/02/2019 à 17:06, Robert Haas a écrit : > On Wed, Feb 20, 2019 at 5:26 PM Gilles Darold <gilles.darold@dalibo.com> wrote: >> The attached simple patch adds this feature. Is there any cons adding >> this information? > Well, it'll take time to compute, maybe a lot of time if the database > is big and the server is busy. Not sure how serious that problem can > get. > I agree, this king of report should be reserved to a super user voluntary action and not as a default psql behavior. Patch removed. Best regards, -- Gilles Darold Consultant PostgreSQL http://dalibo.com - http://dalibo.org