亚马逊RedShift：如何查找数据库大小

Question

有很多谷歌搜索结果可以解决这个问题，但是对我来说似乎都不起作用。因此，我创建了这个问题并为自己未来的参考以及通过谷歌到达此帖子的任何其他人回答它。

- slayedbylucifer

Answer 1

以下是查询语句：

select sum(mbytes)/1024, database from (
select trim(pgdb.datname) as Database,
trim(a.name) as Table, b.mbytes
from stv_tbl_perm a
join pg_database as pgdb on pgdb.oid = a.db_id
join (select tbl, count(*) as mbytes
from stv_blocklist group by tbl) b on a.id=b.tbl
where a.slice=0
order by db_id, name)
group by database;

输出：

?column? |   database
----------+---------------
       62 | db1
       33 | db2
       33 | db3
        2 | db4
       37 | db5
       34 | db6
       35 | db7
       59 | db8
        2 | db9
       26 | db10
        2 | db11
       72 | db12
       36 | db13
       41 | db14

注意：上述数字以GB为单位

- slayedbylucifer

---

尝试了您的脚本，使用select sum(mbytes)/1024 as db_size_in_gb。很好，谢谢。 - Sandesh Deshmane

Answer 2

在查找表的磁盘空间使用情况时，请使用以下查询：

select
    trim(pgdb.datname) as Database,
    trim(pgn.nspname) as Schema,
    trim(a.name) as Table,
    b.mbytes,
    a.rows
from (
    select db_id, id, name, sum(rows) as rows
    from stv_tbl_perm a
    group by db_id, id, name
) as a
join pg_class as pgc on pgc.oid = a.id
join pg_namespace as pgn on pgn.oid = pgc.relnamespace
join pg_database as pgdb on pgdb.oid = a.db_id
join (
    select tbl, count(*) as mbytes
    from stv_blocklist
    group by tbl
) b on a.id = b.tbl
order by mbytes desc, a.db_id, a.name; 

- Tomasz Tybulewicz