Optimise PostgreSQL Database Performance
Listing the Top 10 Largest Tables

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.

NumlaDatabase=# 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;

table_schema table_name total_size data_size external_size
public auditlog_log_line 38 GB 1967 MB 36 GB
public stock_move 4769 MB 1593 MB 3176 MB
public mail_message 3161 MB 1810 MB 1351 MB
public account_move_line 2601 MB 1015 MB 1585 MB
public stock_move_line 1765 MB 1073 MB 692 MB
public account_move 776 MB 360 MB 416 MB
public ir_attachment 766 MB 187 MB 578 MB
public mail_followers 560 MB 166 MB 394 MB
public stock_valuation 415 MB 272 MB 143 MB
public ir_model_data 387 MB 194 MB 193 MB

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.

Integrating Stripe Payment Gateway into Your Flutter App
Streamline Payments & Enhance User Experience