Find the Disk Size of a PostgreSQL Table and Its Indexes

  1. Find the Disk Size of a PostgreSQL Table and Database Using PSQL
  2. Find the Size of the Biggest Table in the Database

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.

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - PostgreSQL Table

  • Create a Pivot Table in PostgreSQL
  • Rename a Table Inside a Schema in PostgreSQL
  • Drop All Tables in PostgreSQL