How to Find the Disk Size of a PostgreSQL Table and Its Indexes

Shihab Sikder Feb 02, 2024
  1. Find the Disk Size of a PostgreSQL Table and Database Using PSQL
  2. Find the Size of the Biggest Table in the Database
How to Find the Disk Size of a PostgreSQL Table and Its Indexes

This article will discuss how we can find the disk size of a PostgreSQL table and its indexes.

Find the Disk Size of a PostgreSQL Table and Database Using PSQL

You can use the \l+ to see the database sizes and \d+ to show the table size. But before this, you need to log in to the database to perform the query.

Here’s the following command and output for showing the table and database size in Postgres:

postgres=# \l+

Output:

Database Size

postgres-# \d+
                                            List of relations
 Schema |       Name       |   Type   |  Owner   | Persistence | Access method |    Size    | Description
--------+------------------+----------+----------+-------------+---------------+------------+-------------
 public | book_lends       | table    | postgres | permanent   | heap          | 16 kB      |
 public | books            | table    | postgres | permanent   | heap          | 16 kB      |
 public | employee         | table    | postgres | permanent   | heap          | 16 kB      |
 public | employee_id_seq  | sequence | postgres | permanent   |               | 8192 bytes |
 public | events           | table    | postgres | permanent   | heap          | 16 kB      |
 public | mock_data        | table    | postgres | permanent   | heap          | 48 kB      |
 public | product          | table    | postgres | permanent   | heap          | 16 kB      |
 public | product_id_seq   | sequence | postgres | permanent   |               | 8192 bytes |
 public | products         | table    | postgres | permanent   | heap          | 16 kB      |
 public | products_id_seq  | sequence | postgres | permanent   |               | 8192 bytes |
 public | prroducts        | table    | postgres | permanent   | heap          | 8192 bytes |
 public | prroducts_id_seq | sequence | postgres | permanent   |               | 8192 bytes |
 public | stores           | table    | postgres | permanent   | heap          | 16 kB      |
 public | stores_id_seq    | sequence | postgres | permanent   |               | 8192 bytes |
 public | users            | table    | postgres | permanent   | heap          | 16 kB      |
(15 rows)

Here it shows all the tables with their name, type, owner, size, access method, etc., that we have in the Postgres database.

Find the Size of the Biggest Table in the Database

Here is a code snippet that Postgres official wrote to show the table sizes in descending order.

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 10;

Output:

     relation      | total_size
-------------------+------------
 public.mock_data  | 48 kB
 public.product    | 32 kB
 public.products   | 32 kB
 public.books      | 32 kB
 public.book_lends | 32 kB
 public.employee   | 32 kB
 public.stores     | 32 kB
 public.users      | 32 kB
 public.events     | 16 kB
 public.prroducts  | 16 kB
(10 rows)

We searched for the 10 largest tables under postgres in the database.

You can click the following link to know more queries about disk size in Postgres. You will find the SQL query for finding the largest cluster, biggest relations, partitioned tables, etc.

Here is more about the functions regarding database size.

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website

Related Article - PostgreSQL Table