July 15th, 2014 by depesz | Tags: , , , , | 4 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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!

  1. 4 comments

  2. # JIm Nasby
    Jul 16, 2014

    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…

  3. # Bob Henkel
    Jul 16, 2014

    @Jim So reading that data will cause locking?

  4. Jul 16, 2014

    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).

  5. # Ross Reedstrom
    Jul 17, 2014

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

Leave a comment