Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Calculate A Postgresql Database Objects Size Examples

To get the largest table we need to query the pg_class table. This is a catalog containing pretty much anything that has columns or is similar to a table. We can get the ten largest tables with the following query:
SELECT relname, relpages FROM pg_class WHERE relkind IN ('r', 't', 'f') ORDER BY relpages DESC LIMIT 10;

Once we execute the query we get our results:
postgres=# SELECT relname, relpages FROM pg_class WHERE relkind IN ('r', 't', 'f') ORDER BY relpages DESC LIMIT 10;
         relname               | relpages
-------------------------------------+----------
 accounts                            |   373017
 mailing_list                        |   353234
 pg_toast_272815                     |   348236
 order_history                       |   291386
 login_failures                      |   284682
 blog                                |   279218
 blog_data                           |   262035
 pg_toast_356234                     |   226826
 name_list                           |   194564
 categories                          |   188161
(10 rows)


Defining the above SELECT:
relname is the name of the object. This could be a table, index, or view
relpages is the  size of the given table in pages. This is only an estimate and is updated periodically.
relkind is the type of object and is defined as follows: r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table.
A note on pg_toast tables. TOAST (The Oversized-Attribute Storage Technique) tables are created when the data set of a table is too big. By default PostgreSQL uses 8kB page sizes. If tuples are larger than 8kB they cannot span multiple pages. To fix this, large values are compressed and split up into multiple physical rows.

How To Find The Largest Index, Sequence, View, Materialized View, Or Composite Type In A Postgresql Database
SELECT relname, relpages FROM pg_class WHERE relkind IN ('i', 'S', 'v', 'm', 'c') ORDER BY relpages DESC LIMIT 10;

Similar to our query for finding the largest table, however now we look for a different type of relkind value.
postgres=# SELECT relname, relpages FROM pg_class WHERE relkind IN ('i', 'S', 'v', 'm', 'c') ORDER BY relpages DESC LIMIT 10;
                       relname                        | relpages
------------------------------------------------------+----------
 accounts_index                                       |   138742
 mailing_list_index                                   |   104217
 address_list_index                                   |    76413
 order_history_index                                  |    66447
 login_failures_index                                 |    57619
 blog_index                                           |    57243
 blog_data_index                                      |    52554
 mailing_list_index                                   |    51961
 name_list_index                                      |    48066
 categories_index                                     |    48066
(10 rows)


How To Calculate The Disk Space Used By A Postgresql Database
The pg_database_size() function will return the file size in bytes of a given database. It only accepts one parameter which is the database name.
postgres=# SELECT pg_database_size('my_database_name');
 pg_database_size
------------------
     120749803704
(1 row)

The output is returned in bytes. To print out the size in a more user friendly format we can call the same pg_database_size() function, but this time we pass it into pg_size_pretty().
postgres=# SELECT pg_size_pretty(pg_database_size('my_database_name'));
 pg_size_pretty
----------------
 112 GB
(1 row)


How To Calculate The Disk Space Used By A Postgresql Table
There are two functions for retrieving the disk usage of a table. The first, pg_relation_size() which returns the size of a table excluding the index.
postgres=# SELECT pg_size_pretty(pg_relation_size('accounts'));
 pg_size_pretty
----------------
 2914 MB
(1 row)

The second option, pg_total_relation_size() which will include the index and toasted data.
postgres=# SELECT pg_size_pretty(pg_total_relation_size('accounts'));
 pg_size_pretty
----------------
 5783 MB

How To Calculate The Index Size Of A Table
pg_indexes_size() returns the physical disk usage of only indexes for a particular table.
postgres=# SELECT pg_size_pretty(pg_indexes_size('accounts'));
 pg_size_pretty
----------------
 2868 MB
(1 row)


How To Calculate The Size Of A Postgresql Tablespace
Lastly we calculate the size of tablespaces, this we do with the function pg_tablespace_size().
postgres=# SELECT pg_size_pretty(pg_tablespace_size('my_tablespace'));
 pg_size_pretty
----------------
 118 MB


(1 row)

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

What's is the difference between streaming replication Vs hot standby vs warm standby ?

Script to kill ALL IDLE Connection In postgreSQL