How to Find Redshift Table Size? – Examples

  • Post author:
  • Post last modified:September 2, 2019
  • Post category:Redshift
  • Reading time:4 mins read

Amazon Redshift is a fully managed, distributed relational database on the AWS cloud. Hardware’s are completely managed by the Amazon and to make database administrator life easy, it has bundled with many system tables. Those system tables hold abundant information about your database, tables, rows, statistics and many more. You can use these system tables to find Redshift table size without giving a second thought.

How to Find Redshift Table Size?

When you create a table in Redshift database, the table does not consume any space on the data slices until you insert one or more rows into that table.

Initially, Redshift does not allocate any space for a table, the only table structure is created. When you insert records into table, Redshift allocate the data block and distribute the records to data slice based on the distribution type you used and sort key that you defined on the table.

You can use the SVV_TABLE_INFO system view to find the size of Amazon Redshift table size.

The SVV_TABLE_INFO system view shows summary information for tables in the database. The view filters system tables and shows only user-defined tables.

For example, below is the sample query that you can use to find out table size.

SELECT “table”, size, tbl_rows FROM SVV_TABLE_INFO;

    table    | size | tbl_rows
-------------+------+----------
 merge_demo2 |    6 |        3
 merge_demo1 |   12 |        6
 test_table1 |    5 |        3
(3 rows)

I have created only a few tables for a demo, and you can see those tables in the query output.

Note that, Redshift system tables are visible to only super user. Normal user may access them but cannot see them when they check database.

How to Find Redshift Table and Database Size?

Obviously, the next question that comes to your mind is how to find the database and table size.

You can combine a bunch of system tables to get required information. I got a neat query on the internet that does the job perfectly.

Below is the same query that i have provided. You can use it for your calculations.

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;

 database |    table    | pct_of_total | mbytes | unsorted_mbytes
----------+-------------+--------------+--------+-----------------
 d1      | merge_demo1 |         0.00 |     12 |              12
 d2      | merge_demo2 |         0.00 |      6 |               6
 d3      | test_table1 |         0.00 |      5 |               5
(3 rows)

Hope this helps 🙂

Tags: ,