Pivotal Knowledge Base

Follow

How to check the size of a Database

Environment

 Product  Version
 Pivotal Greenplum  4.3.x
 OS  RHEL 6.x

Purpose

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

Instructions

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

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