How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in Postgres Database

In this post, I am sharing few important function for finding the size of database, table and index in PostgreSQL.
Finding object size in postgresql database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in GB. The scripts have been formatted to work very easily with PUTTY SQL Editor.

1. Checking table size excluding table dependency:

SELECT pg_size_pretty(pg_relation_size('mhrordhu_shk.mut_kharedi_audit'));
 pg_size_pretty
----------------
 238 MB
(1 row)

2. Checking table size including table dependency:

SELECT pg_size_pretty(pg_total_relation_size('mhrordhu_shk.mut_kharedi_audit'));
 pg_size_pretty
----------------
 268 MB
(1 row)

3. Finding individual postgresql database size

SELECT pg_size_pretty(pg_database_size('db_name'));

4. Finding individual table size for postgresql database -including dependency index:

SELECT pg_size_pretty(pg_total_relation_size('Employee_Table'));

5. Individual table size for postgresql database – not including dependency size:

SELECT pg_size_pretty(pg_relation_size('Employee_table'));

6. Individual index size for postgresql database:

SELECT pg_size_pretty(pg_indexes_size('index_empid'));

7. Finding postgresql tablespace size
The following statement returns the size of the tablespace 

SELECT pg_size_pretty (pg_tablespace_size ('tablespace_name'));

8. Get a listing of all of your databases in Postgres and their sizes in GB, ordering by the largest size first

SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024/1024 AS size_in_GB FROM pg_database ORDER by size_in_GB DESC;
 database_name | size_in_gb
---------------+------------
 mumbai        |        422
 template1     |          0
 template0     |          0
(3 rows)

Get a listing of all of your databases in Postgres and their sizes by using meta-command 

nellore=# \l+

9. Script to Find all the table size in the current database.

SELECT
    table_schema || '.' || table_name AS TableName,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC

10. Script to Find all the table and index size in the current database.

SELECT
    TableName
    ,pg_size_pretty(pg_table_size(TableName)) AS TableSize
    ,pg_size_pretty(pg_indexes_size(TableName)) AS IndexSize
    ,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSize
FROM 
(
     SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName
     FROM information_schema.tables
) AS Tables
ORDER BY 4 DESC

11. Checking table size as well as dependencies size

SELECT  schemaname,
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As " table_Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
      schemaname       |                 Table                  | table_Size | External Size
-----------------------+----------------------------------------+------------+---------------
 mhrordhu_dhu          | ror_bulk_sign_data_audit               | 7940 MB    | 6632 MB
 mhrordhu_shi          | ror_bulk_sign_data_audit               | 7288 MB    | 6104 MB
 mhrordhu_shi          | ror_sign_tables_audit                  | 3458 MB    | 61 MB
 mhrordhu_sak          | ror_bulk_sign_data_audit               | 3174 MB    | 2667 MB
 mhrordhu_dhu_os       | holder_detail_audit                    | 2794 MB    | 776 kB
 mhrordhu_sak          | tbl_summary_audit                      | 2058 MB    | 584 kB
 mhrordhu_sak_os       | form7_khata_audit                      | 2042 MB    | 576 kB
 mhrordhu_dhu_his      | holder_detail                          | 1963 MB    | 552 kB
 mhrordhu_dhu_os       | holder_detail_audit_cor                | 1605 MB    | 464 kB

12. Size of all tables , table related objects size and total table size in a current schema or any schema

SELECT stats.relname
           AS table,
       pg_size_pretty(pg_relation_size(statsio.relid))
           AS table_size,
       pg_size_pretty(pg_total_relation_size(statsio.relid) 
           - pg_relation_size(statsio.relid))
           AS related_objects_size,
       pg_size_pretty(pg_total_relation_size(statsio.relid))
           AS total_table_size,
       stats.n_live_tup
           AS live_rows
  FROM pg_catalog.pg_statio_user_tables AS statsio
  JOIN pg_stat_user_tables AS stats
 USING (relname)
 WHERE stats.schemaname = current_schema  -- Replace with any schema name
 UNION ALL
SELECT 'TOTAL'
           AS table,
       pg_size_pretty(sum(pg_relation_size(statsio.relid)))
           AS table_size,
       pg_size_pretty(sum(pg_total_relation_size(statsio.relid) 
           - pg_relation_size(statsio.relid)))
           AS related_objects_size,
       pg_size_pretty(sum(pg_total_relation_size(statsio.relid)))
           AS total_table_size,
       sum(stats.n_live_tup)
           AS live_rows
  FROM pg_catalog.pg_statio_user_tables AS statsio
  JOIN pg_stat_user_tables AS stats
 USING (relname)
 WHERE stats.schemaname = current_schema  -- Replace with any schema name
 ORDER BY live_rows ASC;

      table       | table_size | related_objects_size | total_table_size | live_rows
------------------+------------+----------------------+------------------+-----------
 t11              | 0 bytes    | 0 bytes              | 0 bytes          |         0
 t1               | 0 bytes    | 16 kB                | 16 kB            |         0
 m_offic_temp     | 8192 bytes | 32 kB                | 40 kB            |         3
 temptbl          | 8192 bytes | 32 kB                | 40 kB            |        41
 form7_khata_temp | 1568 kB    | 32 kB                | 1600 kB          |      8507
 temp             | 0 bytes    | 8192 bytes           | 8192 bytes       |    119340
 temp             | 0 bytes    | 8192 bytes           | 8192 bytes       |    119340
 temp             | 25 MB      | 32 kB                | 25 MB            |    119340
 temp             | 0 bytes    | 8192 bytes           | 8192 bytes       |    119340
 temp             | 0 bytes    | 8192 bytes           | 8192 bytes       |    119340
 TOTAL            | 27 MB      | 176 kB               | 27 MB            |    605251

Following meta-command is also useful for listing the existing tables size in a current schema

nellore=# \d+
                              List of relations
 Schema |       Name       | Type  |      Owner      |  Size   | Description
--------+------------------+-------+-----------------+---------+-------------
 public | form7_khata_temp | table | postgres        | 1600 kB |
 public | temp             | table | postgres        | 25 MB   |
 public | temptbl          | table | raj_admin       | 40 kB   |
(3 rows)

Following meta-command is useful To show tables size of all schema

\dt+ *.*

Following meta-command is useful To show tables size for a particular schema

\dt+ schema_name.*

13.PostgreSQL column value size
To find how much space that needs to store a specific value, you use the pg_column_size() function, for examples:

nijam=# select pg_column_size(5::smallint);
 pg_column_size
----------------
              2
(1 row)

nijam=# select pg_column_size(5::int);
 pg_column_size
----------------
              4
(1 row)

nijam=# select pg_column_size(5::bigint);
 pg_column_size
----------------
              8
(1 row)

In this tutorial, you have learned various handy functions and script to get the size of a database, a table, indexes, a tablespace, a schema and a value.