How to check the size of a database

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article discusses how to check the size, the table size, and column size of a database.

Functions

Name	Return Type	Description
pg_column_size(any)	int	Number of bytes used to store a particular value (possibly compressed)
pg_database_size(oid)	bigint	Disk space used by the database with the specified OID
pg_relation_size(oid)	bigint	Disk space used by the table or index with the specified OID
pg_size_pretty(bigint)	text	Converts a size in bytes into a human-readable format with size units

 

Environment

Resolution

Checking the database size

test=# SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database order by pg_database_size desc;
 datname | pg_database_size | pg_size_pretty
-----------+------------------+----------------
 gpperfmon | 221741302 | 211 MB
 template1 | 151315146 | 144 MB
 test | 35651758 | 34 MB
 postgres | 30867630 | 29 MB
 template0 | 28475452 | 27 MB
(5 rows)

Checking the table size (order by size)

test=# select pg_relation_size(schemaname||'.'||tablename),pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)), schemaname,tablename from pg_tables where schemaname not in ('xxx') order by 1 desc;
 pg_relation_size | pg_size_pretty | schemaname | tablename
------------------+----------------+--------------------+-------------------------------
 1769472 | 1728 kB | pg_catalog | pg_rewrite
 1671168 | 1632 kB | pg_catalog | pg_proc
 1671168 | 1632 kB | pg_catalog | pg_attribute
 ...

Checking the column size

Use this query format:

select pg_size_pretty(pg_column_size(<column_name>)) as relnamesize from <table_name> order by 1 desc;

Example:

test=# select pg_size_pretty(pg_column_size(relname)) as relnamesize from pg_class order by 1 desc;
 relnamesize
-------------
 64 bytes
 64 bytes
...

Additional Information