Date: Tue, 13 Jul 2021 22:07:25 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Subject: [PATCH] psql: \dn+ to show size of each schema..
Message-ID: <20210714030725.GG9600@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210714030725.GG9600%40telsasoft.com>
Precedence: bulk

\db+ and \l+ show sizes of tablespaces and databases, so I was surprised in the
past that \dn+ didn't show sizes of schemas.  I would find that somewhat
convenient, and I assume other people would use it even more useful.

\db+ and \l+ seem to walk the filesystem, and this is distinguished from those
cases.  (Also, schemas are per-DB, not global).

Maybe it's an issue if \dn+ is slow and expensive, since that's how to display
ACL.  But \db+ has the same issue.  Maybe there should be a \db++ and \dn++ to
allow \dn+ to showing the ACL but not the size.

pg_relation_size() only includes one fork, and the other functions include
toast, which should be in its separate schema, so it has to be summed across
forks.

postgres=# \dnS+
 child              | postgres |                      |                                  | 946 MB
 information_schema | postgres | postgres=UC/postgres+|                                  | 88 kB
                    |          | =U/postgres          |                                  | 
 pg_catalog         | postgres | postgres=UC/postgres+| system catalog schema            | 42 MB
                    |          | =U/postgres          |                                  | 
 pg_toast           | postgres |                      | reserved schema for TOAST tables | 3908 MB
 public             | postgres | postgres=UC/postgres+| standard public schema           | 5627 MB
                    |          | =UC/postgres         |                                  | 

From c2d68eb54f785c759253d4100460aa1af9cbc676 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Tue, 13 Jul 2021 21:25:48 -0500
Subject: [PATCH] psql: \dn+ to show size of each schema..

See also: 358a897fa, 528ac10c7
---
 src/bin/psql/describe.c | 5 +++++
 1 file changed, 5 insertions(+)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 2abf255798..6b9b6ea34a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5036,6 +5036,11 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		appendPQExpBuffer(&buf,
 						  ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
 						  gettext_noop("Description"));
+
+		appendPQExpBuffer(&buf,
+						  ",\n  (SELECT pg_catalog.pg_size_pretty(sum(pg_relation_size(oid,fork))) FROM pg_catalog.pg_class c,\n"
+						  "     (VALUES('main'),('fsm'),('vm'),('init')) AS fork(fork) WHERE c.relnamespace = n.oid) AS \"%s\"",
+						  gettext_noop("Size"));
 	}
 
 	appendPQExpBufferStr(&buf,
-- 
2.17.0