Skip to content
Related Articles

Related Articles

PostgreSQL – Size of a Table

View Discussion
Improve Article
Save Article
  • Last Updated : 22 Feb, 2021
View Discussion
Improve Article
Save Article

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: 

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!