PostgreSQL – Size of a Table
In this article, we will look into the function that is used to get the size of the PostgreSQL database table. In this article, we will be using a sample database for reference which is described here and can be downloaded from here.
The pg_relation_size() function is used to get the size of a table.
Syntax: select pg_relation_size('table_name');
Example 1:
Here we will query for the size “country” table from the sample dvdrental database using the below command:
select pg_relation_size('country');
Output:
To make the result readable, one can use the pg_size_pretty() function. The pg_size_pretty() function takes the result of another function and formats it using bytes, kB, MB, GB or TB as required.
SELECT pg_size_pretty (pg_relation_size('country'));
Output:
Example 2:
Here we will query for the size “customer” table from the sample dvdrental database using the below command:
SELECT pg_size_pretty (pg_relation_size('customer'));
Output:
Example 3:
Here we will query for the size “film” table from the sample dvdrental database using the below command:
SELECT pg_size_pretty (pg_relation_size('film'));
Output:
Example 4:
Here we will query for the top 10 biggest tables in the dvdrental database.
SELECT relname AS "tables", pg_size_pretty ( pg_total_relation_size (X .oid) ) AS "size" FROM pg_class X LEFT JOIN pg_namespace Y ON (Y.oid = X .relnamespace) WHERE nspname NOT IN ( 'pg_catalog', 'information_schema' ) AND X .relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size (X .oid) ASC LIMIT 10;
Output:
Contact Us