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)
To get the column data and schema of a particular table: select * from information_schema. columns where tablename='<<table_name>>'
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With