{"id":6079,"date":"2026-09-02T07:00:00","date_gmt":"2026-09-02T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6079"},"modified":"2026-09-02T10:16:58","modified_gmt":"2026-09-02T07:16:58","slug":"pos-vriskoyme-to-megethos-ton-pinakon-s","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-vriskoyme-to-megethos-ton-pinakon-s\/","title":{"rendered":"How to find the size of tables in a PostgreSQL database"},"content":{"rendered":"

When managing production databases, sooner or later the time will come when the available disk space will start to decrease dangerously. The crucial question then is which table is eating up the space. In this article we will see a ready-made, SQL query to instantly detect the size of the tables.<\/p>\n\n\n\n

Unlike other databases, PostgreSQL does not have a ready-made graph to show you at a glance the size of a table, but it does have two functions: pg_relation_size<\/code> and pg_total_relation_size<\/code> that will help us find the information.<\/p>\n\n\n\n

The SQL Query<\/h2>\n\n\n\n

The following script returns all the tables in your database, sorted from largest to smallest:<\/p>\n\n\n\n

SELECT \n    schemaname AS schema_name,\n    tablename AS table_name,\n    pg_size_pretty(pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename))) AS table_size,\n    pg_size_pretty(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename))) AS total_size,\n    ROUND(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) \/ 1024.0 \/ 1024.0 \/ 1024.0, 4) AS total_size_gb\nFROM pg_tables\nWHERE schemaname NOT IN ('pg_catalog', 'information_schema')\nORDER BY pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) DESC;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
What exactly is it calculating?<\/h2>\n\n\n\n
    \n
  1. table_size<\/code><\/strong>: Shows exclusively the space occupied by the table data itself.<\/li>\n\n\n\n
  2. total_size<\/code><\/strong>: Indicates the real <\/strong>size of the table on disk. This includes the table, all indexes<\/code> that are connected to it.<\/li>\n\n\n\n
  3. total_size_gb<\/code><\/strong>: Gives the number to Gigabytes<\/strong>.<\/li>\n<\/ol>\n\n\n\n
    <\/p>\n\n\n\n
    Bonus: How to find the total size of the Database<\/h2>\n\n\n\n
    If, in addition to the tables, we want to see at a glance how much space the database we are in takes up in total, we can run:<\/p>\n\n\n\n
    SELECT \n    pg_database.datname AS database_name,\n    pg_size_pretty(pg_database_size(pg_database.datname)) AS db_size\nFROM pg_database\nWHERE pg_database.datname = current_database();<\/pre>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Sources:<\/h2>\n\n\n\n