How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL 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.

SELECTtable_schema || '.' || table_name AS TableName,pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSizeFROM information_schema.tablesORDER BYpg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC

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

SELECTTableName,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 TotalSizeFROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName FROM information_schema.tables) AS TablesORDER 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 ALLSELECT '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.