by Dharmesh Patel
Are you looking to optimise your PostgreSQL database's performance? One key aspect is understanding the size of your database tables, especially the largest ones. Let's explore how you can list the top 10 largest tables in your PostgreSQL database using a simple SQL query.
SQL Query
To list the top 10 largest tables in your PostgreSQL database, you can use the following SQL query:
SELECT schemaname
AS
table_schema,
relname
AS table_name,
Pg_size_pretty(Pg_total_relation_size(relid))
AS total_size,
Pg_size_pretty(Pg_relation_size(relid))
AS data_size,
Pg_size_pretty(Pg_total_relation_size(relid) - Pg_relation_size(relid))=
AS
external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY Pg_total_relation_size(relid) DESC,
Pg_relation_size(relid) DESC
LIMIT 10;
This query retrieves the size of each table in your database and then sorts them in descending order by their total size, displaying the top 10 largest tables.
Output
The output of the query appears as follows.
Understanding the size of your database tables is crucial for optimising database performance. By identifying the largest tables, you can focus on optimising them for improved efficiency and speed.