PostgreSQL :- Get Table Size

Background

Getting database table sizes is a good way to familiarize oneself with a couple of PostgreSQL functions.

Code

SQL


select
          current_database()
            as "database"

        , tblN.nspname
            as "schema"

        , tblC.relname
            as "name"

        , tblAuth.rolname
            as "owner"

        , tblC.reltablespace
            as "tableSpace"

        , tblC.reltuples
            as "numberofRecords"

        , tblC.relpages
            as "numberofPages"

        -- use pg_relation_size::oid
        , pg_relation_size
            (
                tblC.oid
            )
            AS totalBytes

        -- use pg_relation_size::namespace||.||relname
        , pg_relation_size
            (
                cast
                (
                    quote_ident
                    (
                        tblN.nspname
                    )
                    || '.'
                    || quote_ident
                    (
                        tblC.relname
                    )
                    as text
                )
            ) AS totalBytesAlt

        -- use relationPages * current_setting('block_size')
        , tblC.relpages
            * (
                current_setting('block_size') ::bigint
              )
            AS totalBytesCalc

        , pg_size_pretty
            (
                pg_relation_size
                (
                    tblC.oid
                ) :: bigint
            )   as totalBytesPretty 

        , tblC.relkind
                as "relationKind"

        , case tblC.relkind

                when 'r' then 'table'
                when 'i' then 'index'
                when 'S' then 'Sequence'
                when 'v' then 'View'
                when 'm' then 'materialized view'
                when 't' then 'toast'
                when 'f' then 'Foreign Table'

          end
                as "objectType"

        , tblC.relpersistence
                as "relationPersistence"

        , case tblC.relpersistence

                when 'p' then 'Permanent Table'
                when 'u' then 'Unlogged Table'
                when 't' then 'Temporary Table'

          end as "persistency"

from pg_catalog.pg_class tblC

join pg_catalog.pg_namespace tblN

    on tblC.relnamespace = tblN.oid

join pg_catalog.pg_authid tblAuth

    on tblC.relowner = tblAuth.oid

where tblC.relkind in
        (
            -- r = ordinary table
            -- , i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
              'r'
            , 'v' -- view
            , 'm' -- Materialized View
            , 't' -- TOAST Table
            , 'p'
        )

and   tblN.nspname not in
        (
              'information_schema'
            , 'pg_catalog'
            , 'pg_toast'
        )

order by     

          tblN.nspname 

        , tblC.relname 

    ; 

Output

Output – Northwind

pg_catalog.pg_class.northwind.01.20190730.0116AM

Output – Sample Database

pg_catalog.pg_class.lab.dataRange.01.20190730.0115AM

Summary

Here is a quick rundown of the functions we used :-

  1. pg_relation_size
    • Returns table size
    • Accepts
      • pg_catalog.pg_class.oid ( object id )
      • or pg_catalog.pg_class.relname ( relation name )
      • or pg_catalog.pg_namespace || ‘.’ || pg_catalog.pg_class.relname ( schema name + relation name )
  2. current_setting(‘block_size’)
    • Current Block Size
    • Multiply by Number of Pages
  3. pg_size_pretty
    • Converts raw bytes to human readable format ( kb, mb, gb )

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s