Mezgani blog

September 2, 2009

How to find the PostgreSQL database size

Filed under: database — Tags: , , — Ali MEZGANI @ 1:49 am

PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.
If you want to find the size of a database, in our example the sqview database, please tape:
postgresql-300x10
sqview-# SELECT pg_database_size(‘sqview’);
pg_database_size
——————
111415944
(1 ligne)

The result in octal, for a pretty print we will use the pg_size_pretty function which converts the size in bytes to human understandable format.
sqview-# SELECT pg_size_pretty(pg_database_size(‘sqview’));
pg_size_pretty
—————-
106 MB
(1 ligne)

Well if we need to get the size of all databases
sqview-# SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
datname | size
———–+———
postgres | 3537 kB
template1 | 3537 kB
template0 | 3480 kB
freedom | 25 MB
anakeen | 6081 kB
sqview | 106 MB
rt | 8201 kB
(7 lignes)

PostgreSQL can also give the size of a table ‘users’ like this

sqview-# SELECT pg_size_pretty(pg_relation_size(‘users’));
pg_size_pretty
—————-
64 kB
(1 ligne)

This value exclude indexes and some auxiliary data.
If you want to include them use pg_total_relation_size instead of pg_relation_size as shown below.
sqview-# SELECT pg_size_pretty(pg_total_relation_size(‘users’));
pg_size_pretty
—————-
152 kB
(1 ligne)

to find the largest table in the postgreSQL database.
sqview-# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
relname | relpages
———+———-
sqview | 9666
(1 ligne)

About these ads

3 Comments »

  1. very useful commands thank you

    Comment by boopathi — August 23, 2012 @ 5:58 am

  2. Nice summaries. Thanks~

    Comment by geminihzh — January 14, 2013 @ 4:10 pm

  3. Very helpful

    Comment by Vladimir — April 11, 2013 @ 9:34 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: