Waiting for 9.5 – psql: Show tablespace size in \db+

On 14th of July, Alvaro Herrera committed patch:

psql: Show tablespace size in \db+
 
Fabrízio de Royes Mello

As I previously mentioned – I'm sucker for psql additions. And while todays patch is rather small, I really like it.

What it does? It simply makes \db+ show size of all objects in given tablespace.

Let's see:

$ CREATE tablespace pg1 location '/tmp/pg1';
$ CREATE tablespace pg2 location '/tmp/pg2';

Now some test tables:

$ CREATE TABLE t AS SELECT i, repeat('depesz', 100) AS x FROM generate_series(1,10000) i;
$ CREATE TABLE t1 AS SELECT i, repeat('depesz', 100) AS x FROM generate_series(1,20000) i;
$ CREATE TABLE t2 AS SELECT i, repeat('depesz', 100) AS x FROM generate_series(1,30000) i;

Move them to new tablespaces:

$ ALTER TABLE t1 SET tablespace pg1;
$ ALTER TABLE t2 SET tablespace pg2;

And now let's see the sizes:

$ \db+
                                 List OF tablespaces
    Name    | Owner  | Location | Access privileges | Options |  SIZE  | Description 
------------+--------+----------+-------------------+---------+--------+-------------
 pg1        | depesz | /tmp/pg1 |                   |         | 13 MB  | 
 pg2        | depesz | /tmp/pg2 |                   |         | 20 MB  | 
 pg_default | pgdba  |          |                   |         | 54 MB  | 
 pg_global  | pgdba  |          |                   |         | 453 kB | 
(4 ROWS)

pg_default is largest because I have some other tables there, but Size column clearly shows how much is where. Great!

4 thoughts on “Waiting for 9.5 – psql: Show tablespace size in \db+”

Note that (unless pg_tablespace_size has changed) that will lock every object in the tablespace one at a time. Could lead to very unexpected behavior in a prod environment…

@Jim So reading that data will cause locking?

Based on what can I see it’s just accesssharelock. Nothing else. Which means that it will not cause problems with anything that doesn’t need “access exclusive lock” (ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, and VACUUM FULL).

Ah, as in “all the usual non-production friendly actions” 🙂

Comments are closed.