How to Find the Disk Size of a PostgreSQL Table and Its Indexes
- Find the Disk Size of a PostgreSQL Table and Database Using PSQL
- 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:
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.
