Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to find size of database, schema, table in redshift

Team,

my redshift version is:

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 

how to find out database size, tablespace, schema size & table size ?

but below are not working in redshift ( for above version )

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

Is there any alternate to find out like oracle ( from DBA_SEGMENTS )

for tble size, i have below query, but not sure about exact menaing of MBYTES. FOR 3rd row, MBYTES = 372. it means 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) 
like image 361
user3258784 Avatar asked Feb 13 '14 23:02

user3258784


People also ask

How do you find the schema of a Redshift table?

To get the column data and schema of a particular table: select * from information_schema. columns where tablename='<<table_name>>'

How do I check my Redshift database?

To query databases hosted by your Amazon Redshift cluster, you have two options: Connect to your cluster and run queries on the AWS Management Console with the query editor. If you use the query editor on the Amazon Redshift console, you don't have to download and set up a SQL client application.

How can I check my Redshift table partition?

Use SVV_EXTERNAL_PARTITIONS to view details for partitions in external tables. SVV_EXTERNAL_PARTITIONS is visible to all users. Superusers can see all rows; regular users can see only metadata to which they have access.


Video Answer


1 Answers

The above answers don't always give correct answers for table space used. AWS support have given this query to use:

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; 
like image 67
imcdnzl Avatar answered Sep 22 '22 08:09

imcdnzl