Pivotal Knowledge Base

Follow

How to Check the Size of a Database

Environment

  • Pivotal Greenplum (GPDB) 4.3.x
  • Operating System- Red Hat Enterprise Linux 6.x

Purpose
This article discusses how to check database size, table size, and column size.

Function used 

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

Procedure

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

Please use this query format:

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

Here's an example:

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

 

Comments

Powered by Zendesk