APP Blog

It’s a common database administration task, but it’s somehow not that easy with any database I worked with. For Postgres, here are three examples with varying degree of ugliness, depending on the information required and output format:

1. Simple list of all user’s tables in the current schema with their respective sizes:

SELECT c.relname AS Name,
       pg_size_pretty(pg_relation_size(c.relname)) AS Data,
       pg_size_pretty(pg_total_relation_size(c.relname)-
           pg_relation_size(c.relname)) AS Indices,
       pg_size_pretty(pg_total_relation_size(c.relname)) AS Total
  FROM pg_class c
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind IN ('r','')
   AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
   AND pg_table_is_visible(c.oid)
 ORDER BY c.relpages DESC;

which produces

name               data        indices     total
 -----------------  ----------  ----------  --------
 transaction_log    30 GB       15 GB       45 GB
 tester             476 MB      8192 bytes  476 MB
 users              6448 kB     1880 kB     8328 kB
...

2. A more complicated list with all user’s tables, their indexes, and their respective sizes:

SELECT c3.relname AS "Table",
       c2.relname AS "Index",
       pg_size_pretty(pg_relation_size(c3.relname)) AS "Data Size",
       pg_size_pretty(pg_relation_size(c2.relname)) AS "Index Size",
       pg_size_pretty(pg_total_relation_size(c3.relname)) AS "Total"
  FROM pg_class c2
  LEFT JOIN pg_index i  ON c2.oid = i.indexrelid
  LEFT JOIN pg_class c1 ON c1.oid = i.indrelid
 RIGHT OUTER JOIN pg_class c3 ON c3.oid = c1.oid
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c3.relnamespace
 WHERE c3.relkind IN ('r','')
   AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
   AND pg_catalog.pg_table_is_visible(c3.oid)
 ORDER BY c3.relpages DESC;

which produces

Table              Index                                Data Size     Index Size     Total
 -----------------  -----------------------------------  ------------  -------------  --------
 transaction_log    translog_by_source_provider          30 GB         15 GB          45 GB
 tester             (null)                               476 MB        (null)         476 MB
 users              users_by_id                          6448 kB       736 kB         8328 kB
 users              users_by_name                        6448 kB       1136 kB        8328 kB
...

Please note that table ‘tester’ does not have any indices, ‘transaction_log’ has one, ‘users’ has two.

3. A rather elaborate query, which is, in effect, the same as with the example 2, but with added layers of selects to achieve the desired formatting of the output:

SELECT CASE WHEN total IS NULL THEN '' ELSE name END AS table,
       index,
       pg_size_pretty(size) AS size,
       CASE WHEN total IS NULL THEN '' ELSE pg_size_pretty(total) END AS total
 FROM
       (SELECT name,
              index,
              size,
              total
        FROM
              (SELECT c.relname AS name, '' as index,
                      pg_relation_size(c.relname) AS Size,
                      pg_total_relation_size(c.relname) AS total
                 FROM pg_class c
                 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
                WHERE c.relkind IN ('r','')
                  AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                  AND pg_table_is_visible(c.oid)

               UNION

               SELECT c3.relname AS name,
                      c2.relname AS index,
                      pg_relation_size(c2.relname) AS size,
                      NULL AS total
                 FROM pg_class c2
                 LEFT JOIN pg_index i  ON c2.oid = i.indexrelid
                 LEFT JOIN pg_class c1 ON c1.oid = i.indrelid
                RIGHT OUTER JOIN pg_class c3 ON c3.oid = c1.oid
                 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c3.relnamespace
                WHERE c3.relkind IN ('r','')
                  AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
                  AND pg_catalog.pg_table_is_visible(c3.oid)
             ) tmp1
        WHERE index IS NOT NULL
        ORDER BY name, index
       ) tmp2;

It makes really pretty report:

 table              index                                size        total
 -----------------  -----------------------------------  ----------  --------
 transaction_log                                         30 GB       45 GB
                    translog_by_source_provider          15 GB
 tester                                                  476 MB      476 MB
 users                                                   6448 kB     8328 kB
                    users_by_id                          736 kB
                    users_by_name                        1136 kB
...