The following Postgres SQL query will list all tables from all schemas and their sizes and index sizes. If a table is just an index table, it will show up as 100% index.
SELECT schema,
name,
pg_size_pretty(CASE WHEN is_index THEN 0 ELSE s END) AS size,
pg_size_pretty(CASE WHEN is_index THEN s ELSE st - s END) AS index,
CASE WHEN st = 0 THEN 0
WHEN is_index THEN 100
ELSE 100 - ((s*100) / st) END || '%' as ratio,
pg_size_pretty(st) as total
FROM (SELECT *,
st = s AS is_index
FROM (SELECT nspname as schema,
relname as name,
pg_relation_size(nspname || '.' || relname) as s,
pg_total_relation_size(nspname || '.' || relname) as st
FROM pg_class
JOIN pg_namespace ON (relnamespace = pg_namespace.oid)) AS p)
AS pp
ORDER BY st DESC LIMIT 30;
It will give the following results:
schema | name | size | index | ratio | total
----------------+------------------------+---------+---------+-------+---------
public | conf | 4072 kB | 4360 kB | 52% | 8432 kB
archive | product_param | 4048 kB | 3968 kB | 50% | 8016 kB
public | conf_pkey | 0 bytes | 4320 kB | 100% | 4320 kB
archive | product_value | 1568 kB | 1136 kB | 43% | 2704 kB
public | param_mapping | 1472 kB | 832 kB | 37% | 2304 kB
archive | supplie_price | 944 kB | 896 kB | 49% | 1840 kB
public | product_param_param_id | 0 bytes | 1552 kB | 100% | 1552 kB
archive | product_param_id | 0 bytes | 1536 kB | 100% | 1536 kB
I've come to a point where I can't see the forest for all the trees, and it's starting to get a bit unwieldy.
I'm wondering if there's anything in it that can be simplified or made redundant? The columns mustn't necessarily stay the same if the query can be made much simpler.
I get comparable results (with different formatting) with this query:
select
nspname as schema,
relname as name,
pg_relation_size(pg_class.oid) as size,
pg_indexes_size(pg_class.oid) as index,
pg_total_relation_size(pg_class.oid) as total,
100 * case when relkind = 'i' then pg_relation_size(pg_class.oid)
else pg_indexes_size(pg_class.oid) end
/ pg_total_relation_size(pg_class.oid) as i_ratio
from
pg_class
join pg_namespace on relnamespace = pg_namespace.oid
order by 5 desc
All I really want to do is point out that quzary's response should be using oid's and not creating strings that will fail to get parsed back to oid's.
Now I've got to write a proper post (maybe this is the point of stopping newbies from commenting?) here's another cleaned and prettied up version:
WITH p AS (
SELECT n.nspname AS schema,
c.relname AS name,
pg_relation_size(c.oid) AS s,
pg_total_relation_size(c.oid) AS st
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid
)
SELECT schema, name,
pg_size_pretty(s) AS size,
pg_size_pretty(st - s) AS index,
(100.0 * s / NULLIF(st, 0))::numeric(10,1) AS "% data of total",
st AS total
FROM p
ORDER BY st DESC
LIMIT 30;
Note that it may be useful to add in the following line:
AND c.relkind = 'r'
in the WHERE
clause of p
. That will limit it to relations/tables only and makes the code useful for a general summary of table sizes.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With