postgresql 检查postgres中的多个表大小

kd3sttzy 于 2022-12-12 发布在 PostgreSQL

关注(0)|答案(3)|浏览(131)

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'

postgresql

来源：https://stackoverflow.com/questions/29482578/check-multiple-table-size-in-postgres

3条答案

按热度按时间

erhoui1w1#

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)

赞(0）回复(0）举报 2022-12-12

9rbhqvlz2#

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

赞(0）回复(0）举报 2022-12-12

ukxgm1gy3#

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;

赞(0）回复(0）举报 2022-12-12

我来回答

postgresql 检查postgres中的多个表大小

3条答案

相关问题

热门标签

最新问答