Checking table and index sizes in PostgreSQL

In this recipe, we shall see some of the best ways to check the size of a table and index in PostgreSQL.

Getting ready

Creating a table in PostgreSQL requires you to have appropriate privileges. A newly created user can create a table in the public schema of any database. But, when you need to create a table inside another schema, it requires you to have sufficient privileges to do so. Similarly, it requires you to have sufficient privileges to read data from a table or perform writes to that table. However, it does not require you to have any privileges to see the size of a table in any schema.

How to do it...

The following are the steps involved in finding the size of a table and an index:

1. We will use \dt+ to get the table size:

percona=# \dt+ foo.dept 
                    List of relations
 Schema | Name | Type  | Owner | Size | Description 
--------+------+-------+----------+---------+-------------
 foo    | dept | table | postgres | 3568 kB | 
(1 row)

  
--- To get size of a table or a set of tables matching a pattern, the following can be used. 

percona=# \dt+ public.*bench*
                          List of relations
 Schema | Name | Type | Owner | Size | Description 
--------+------------------+-------+----------+---------+-------------
 public | pgbench_accounts | table | postgres | 1281 MB | 
 public | pgbench_branches | table | postgres | 40 kB | 
 public | pgbench_history | table | postgres | 0 bytes | 
 public | pgbench_tellers | table | postgres | 80 kB | 
(4 rows)
 

2. We will use \di+ to get the index size:

 percona=# \di+ foo.dept_pkey
                           List of relations
 Schema |   Name | Type  | Owner | Table |  Size | Description 
--------+-----------+-------+----------+-------+---------+-------------
 foo    | dept_pkey | index | postgres | dept  | 2208 kB | 
(1 row)

3. We will use pg_relation_size to get the table and index size:

 percona=# select pg_relation_size('foo.dept');
 pg_relation_size 
------------------
 3629056
(1 row)

How it works...

Database objects such as tables, indexes, views, materialized views, and so on may also be called relations in PostgreSQL. The three relations that can grow in size are tables, indexes, and materialized views. There are multiple ways to find the size of a table or an index in PostgreSQL. While one of them is using psql shortcuts, the other method is through a function called pg_relation_size().

As seen in Step 1, we can pass the fully qualified table name (schemaname.tablename) to \dt+ after connecting to the appropriate database using psql. And in order to find the size of an index, we can pass the fully qualified index name (that is, chemaname.indexname) to \di+ after connecting to the appropriate database using psql, as seen in Step 2.

We can also find the size of these relations using pg_relation_size() easily, as seen in Step 3. We need to make sure that we pass a fully qualified relation name with the schema prefix to the pg_relation_size() function.

There's more...

When we used the function: pg_relation_size() earlier, we noticed that the size of a relation is displayed in bytes. This is not easily readable. For that purpose, we have the function pg_size_pretty(). This function converts the bytes into the nearest MB or GB or KB but not bytes, always. The following example should be helpful to understand this in reality:

postgres=# SELECT schemaname, relname, pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as pretty_size FROM pg_stat_user_tables where schemaname = 'foo' and relname IN ('employee','sales','bar');
 schemaname | relname | pretty_size 
------------+----------+-------------
 foo | employee | 360 kB
 foo | bar | 640 MB
 foo | sales | 13 GB
(3 rows)