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
Post a Comment