amazon-web-services - 如何在redshift中找到数据库、模式、表的大小

Question

团队，

我的 Redshift 版本是:

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.735

如何找出数据库大小、表空间、模式大小和表大小？

但以下不适用于 Redshift (对于以上版本)

SELECT pg_database_size('db_name');
SELECT pg_size_pretty( pg_relation_size('table_name') );

是否有任何替代方法可以找到像 oracle (来自 DBA_SEGMENTS )

对于 tble 大小，我有以下查询，但不确定 MBYTES 的确切含义。对于第三行，MBYTES = 372。这意味着 372 MB ？

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 a.db_id, a.name;
   database    |    schema    |      table       | mbytes |   rows
---------------+--------------+------------------+--------+----------
      postgres | public       | company          |      8 |        1
      postgres | public       | table_data1_1    |      7 |        1
      postgres | proj_schema1 | table_data1    |    372 | 33867540
      postgres | public       | table_data1_2    |     40 |  2000001

(4 rows)

Answer 1

上述答案并不总是为使用的表空间提供正确答案。 AWS 支持已提供此查询使用:

SELECT   TRIM(pgdb.datname) AS Database,
         TRIM(a.name) AS Table,
         ((b.mbytes/part.total::decimal)*100)::decimal(5,2) AS pct_of_total,
         b.mbytes,
         b.unsorted_mbytes
FROM     stv_tbl_perm a
JOIN     pg_database AS pgdb
  ON     pgdb.oid = a.db_id
JOIN     ( SELECT   tbl,
                    SUM( DECODE(unsorted, 1, 1, 0)) AS unsorted_mbytes,
                    COUNT(*) AS mbytes
           FROM     stv_blocklist
           GROUP BY tbl ) AS b
       ON a.id = b.tbl
JOIN     ( SELECT SUM(capacity) AS total
           FROM   stv_partitions
           WHERE  part_begin = 0 ) AS part
      ON 1 = 1
WHERE    a.slice = 0
ORDER BY 4 desc, db_id, name;