Check multiple table size in postgres

Question

i found this query on how to check the size of a table in Postgres

select pg_size_pretty(pg_relation_size('the_table'));

I want to get the size from multiple tables. My database has this kind of table name:

tblresource_01012014
tblresource_02012014
tblresource_03012014
...

It has date on its name. i want to get the size for a specific month. something like,

select pg_size_pretty(pg_relation_size('tblresource_**012014'));

Absolutely this query is wrong. anybody knows what are the right query to execute

MY SOLUTION
Hey guys, for now i have a solution for this which is quite simple but i wont say it as a good answer though cause it might only works for my case. and doesn't fully meet my goal. here how i do it.

select table_name, pg_size_pretty(pg_relation_size(table_name))
from information_schema.tables
where table_name like 'tblresource_%012014'

Gordon Linoff · Accepted Answer · 2015-04-07 02:13:42Z

1

You could generate the values using a query:

select pg_size_pretty(pg_relation_size('tblresource_' || lpad(n.n::varchar, 2, '0') || '012014'))
from generate_series(1, 12) n(n)

answered Apr 7, 2015 at 2:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vivek S. Over a year ago

nitpicking : what if tblresource_12012014 doesn't exists ??

Gordon Linoff Over a year ago

@unique_id . . . Then the query will generate an error.

Vivek S. · Accepted Answer · 2015-04-07 07:27:22Z

SELECT tbl tbl_name
    ,pg_size_pretty(pg_relation_size(format('%s', t.tbl))) size
FROM (
    SELECT table_name tbl
    FROM information_schema.tables
    WHERE table_schema = 'public'
        AND table_type = 'BASE TABLE'
        AND table_name ilike '%tblresource_%' --your table
        AND table_name ilike '%012014' --to search 
    ) t;

sqlfiddle demo

You can wrap above select into a function like below

CREATE OR REPLACE FUNCTION get_tblresource_size (_yr TEXT)
RETURNS TABLE (
        tbl_name TEXT
        ,size TEXT
        ) AS $$

BEGIN
    RETURN QUERY

    SELECT tbl::TEXT tbl_name
        ,pg_size_pretty(pg_relation_size(format('%s', t.tbl))) size
    FROM (
        SELECT table_name tbl
        FROM information_schema.tables
        WHERE table_schema = 'public'
            AND table_type = 'BASE TABLE'
            AND table_name ilike '%tblresource_%'
            AND table_name ilike '%' || _yr || '' --012014 will come here
        ) t;
END $$

LANGUAGE plpgsql

function call :

select * from get_tblresource_size('012014') --passing the search value

emre avci · Accepted Answer · 2022-12-06 10:59:53Z

Following query will returns sum of two tables with union,split_part and sum functions.

select sum(sizeByte) from (select split_part(pg_size_pretty(hypertable_size('table1')), ' ', 1)::bigint *
case split_part(pg_size_pretty(hypertable_size('table1')), ' ', 2)
  when 'bytes' then 1
  when 'kB' then 1024
  when 'MB' then 1024*1024
  when 'GB' then 1024*1024*1024
  when 'TB' then 1024*1024*1024*1024::bigint
end as sizeByte
union
select split_part(pg_size_pretty(hypertable_size('table2')), ' ', 1)::bigint *
case split_part(pg_size_pretty(hypertable_size('table2')), ' ', 2)
  when 'bytes' then 1
  when 'kB' then 1024
  when 'MB' then 1024*1024
  when 'GB' then 1024*1024*1024
  when 'TB' then 1024*1024*1024*1024::bigint
end as sizeByte) t;

Collectives™ on Stack Overflow

Check multiple table size in postgres

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related