postgresql list and order tables by size

Question

How can I list all the tables of a PostgreSQL database and order them by size?

Marque

If you are using the command-line psql client then a simple \d+ will show you this information, though unsorted. – Ichor

Thanks. But I need it sorted, I have too many tables. – Marque

People looking for the same thing but for databases instead of tables : here is the solution. – Boondoggle

Re psql: start it with --echo-hidden and it will tell you the queries done for \d+ and other backslash commands. Easy to add sorting. – Woodbury

Answer 1

select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
  pg_total_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 3 desc;

This shows you the size of all tables in the schema public if you have multiple schemas, you might want to use:

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3

SQLFiddle example: http://sqlfiddle.com/#!15/13157/3

List of all object size functions in the manual.

Rhiamon

It's table_schema, not schema_name. The first query was fine but you had already started typing something in your psql session, which caused a syntax error. – Armrest

OK this code works: select table_schema, table_name, pg_relation_size(table_schema||'.'||table_name) from information_schema.tables order by 3; thanks for help! – Marque

any idea what this dont work in my case? #40978276 – Tearing

@Sucrenoir: "does not work" is not a valid Postgres error message. The query in my answer does work for me: rextester.com/KGKPR49004 – Rhiamon

I get zero rows when I run the first query. select * from information_schema.tables where table_schema = 'public'; yields zero rows even though \dn shows schema public. Maybe a change in 9.5 caused this? – Garrett

I had to use pg_table_size instead of pg_relation_size to get the actual size as some tables were showing smaller sizes for pg_relation_size. These tables had jsonb columns. Not sure why it was happening though. – Anemometry

You should use pg_total_relation_size to get the total size of the table including its indexes - see https://mcmap.net/q/100600/-what-39-s-the-difference-between-pg_table_size-pg_relation_size-amp-pg_total_relation_size-postgresql – Densify

You can also use pg_indexes_size to ge the size of the indexes associated with the table. – Sebbie

Answer 2

This will show you the schema name, table name, size pretty and size (needed for sort).

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

I build this based on the solutions from here list of schema with sizes (relative and absolute) in a PostgreSQL database

Gheber

This is damn useful; takes into account indexes also. – Hockey

Can be useful to add relkind to this to explicitly identifying table or index – Openeyed

This is only working for the public scheme. – Smarten

For me, pg_relation_size (line 11) only showed 8 kB on a table with a huge PickledObjectField; pg_total_relation_size showed more sensible 57 MB. You might want to check out https://mcmap.net/q/100600/-what-39-s-the-difference-between-pg_table_size-pg_relation_size-amp-pg_total_relation_size-postgresql/118520 for details. – Eastsoutheast

Change pg_relation_size to pg_total_relation_size to include indices! – Genoa

Answer 3

This will be more clear.

pg_size_pretty(<numeric_value>) - converts no.of bytes to human-readable format.

pg_database_size(<db_name>) - gets database size in bytes.

pg_total_relation_size(<relation_name>) - gets total size of table and its index in bytes.

pg_relation_size(<relation_name>) - gets relation (table/index) size in bytes.

pg_indexes_size(<relation_name>) - gets index size of the relation in bytes.

current_database() - gets the currently used database on which this query is being performed.

Query:

select current_database() as database,
       pg_size_pretty(total_database_size) as total_database_size,
       schema_name,
       table_name,
       pg_size_pretty(total_table_size) as total_table_size,
       pg_size_pretty(table_size) as table_size,
       pg_size_pretty(index_size) as index_size
       from ( select table_name,
                table_schema as schema_name,
                pg_database_size(current_database()) as total_database_size,
                pg_total_relation_size(table_name) as total_table_size,
                pg_relation_size(table_name) as table_size,
                pg_indexes_size(table_name) as index_size
                from information_schema.tables
                where table_schema=current_schema() and table_name like 'table_%'
                order by total_table_size
            ) as sizes;

Result:

 database  | total_database_size | schema_name | table_name | total_table_size | table_size | index_size
-----------+---------------------+-------------+------------+------------------+------------+------------
 vigneshdb | 1586 MB             | corpdata    | table_aaa  | 16 kB            | 0 bytes    | 8192 bytes
 vigneshdb | 1586 MB             | corpdata    | table_bbb  | 24 kB            | 0 bytes    | 16 kB
 vigneshdb | 1586 MB             | corpdata    | table_ccc  | 640 kB           | 112 kB     | 488 kB
 vigneshdb | 1586 MB             | corpdata    | table_ddd  | 9760 kB          | 3152 kB    | 6568 kB
 vigneshdb | 1586 MB             | corpdata    | table_eee  | 1120 MB          | 311 MB     | 808 MB

The humanized format is represent in bytes, kB, MB, GB, and TB.

bytes to kB - begins from 10240 bytes

bytes to MB - begins from 10485248 bytes = 10239.5 kB ~ 10 MB

bytes to GB - begins from 10736893952 bytes = 10239.5 MB ~ 10 BG

bytes to TB - begins from 10994579406848 bytes = 10239.5 GB ~ 10 TB

All unit conversions starts from 10 + <unit>.

For reference - Postgres Official Documentation

Mablemabry

This example does not work with uppercase table names – Barnardo

In more recent Postgres versions such as 12.4, this query gives an error - the fix is to use quote_ident() around the table_name. – Hagans

It is pg_indexes_size not pg_index_size. – Personalism

I get error: function pg_total_relation_size(information_schema.sql_identifier) does not exist. null and after using quote_ident() I get empty result. – Spiritualism

Answer 4

I like following statement:

SELECT 
  table_name, 
  pg_size_pretty( pg_total_relation_size(quote_ident(table_name))), 
  pg_total_relation_size(quote_ident(table_name))
FROM 
  information_schema.tables
WHERE 
  table_schema = 'public'
ORDER BY 
  pg_total_relation_size(quote_ident(table_name)) DESC

You can see total size in a pretty format, but it is ordered correctly too.

Pignut

Perfect thank you! – Wanitawanneeickel

This is so far the best one. – Busty

Great answer, better than those currently above it. Thanks! – Rozele

Answer 5

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "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;

taken from here https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database

Repp

Answer 6

I needed to find which tables use the most space.

Based on other answers, I used that query:

select table_name, pg_size_pretty( pg_relation_size(quote_ident(table_name)) )
from information_schema.tables
where table_schema = 'public'
order by pg_relation_size(quote_ident(table_name)) desc

I get the following result:

table_name              pg_size_pretty
--------------------------------------
trade_binance           96 GB
closs_v2_binance_stash  46 GB
closs_bitfinex_stash    5725 MB
trade_bitfinex          5112 MB
...
api_requests            0 bytes
trade_huobi             0 bytes

I should have bought a bigger SSD.

Exciting

Answer 7

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
  ;

Credit: https://makandracards.com/makandra/52141-postgresql-how-to-show-table-sizes

Pushover

Answer 8

If you're looking for a breakdown in total, toast and index sizes use this:

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes 
FROM (
  SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
          , c.reltuples AS row_estimate
          , pg_total_relation_size(c.oid) AS total_bytes
          , pg_indexes_size(c.oid) AS index_bytes
          , pg_total_relation_size(reltoastrelid) AS toast_bytes
      FROM pg_class c
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE relkind = 'r'
  ) a
) a ORDER BY total_bytes DESC;

Drogin

Answer 9

It can be easily done from psql console, see the meta commands reference.

Table Size: \dt+

Database size \l+

Index size: \di+

For table size, first connect the database using \c db_name.

Please Note: Above won't sort by size as OP asked, still it could help.

Parisi

Can it show the sum? – Chrysotile

Answer 10

select table_name,n_live_tup, pg_size_pretty(pg_relation_size(table_name))
from information_schema.tables
inner join pg_stat_user_tables  on table_name=relname
where table_schema = 'public'
order by 2 desc

Another alternative

Outline

Answer 11

This query provides for each table:

• total_size - total size of table and indexes in human-readable format
• data_size - size of table itself
• index_size - size of indexes of that table
• rows - the number of rows in the table
• total_row_size - an approximate size of indexes and data for each row
• row_size - an approximate size of data for each row

Execute in SQL Fiddle

SELECT
  nspname                                               AS "schema",
  pg_class.relname                                      AS "table",
  pg_size_pretty(pg_total_relation_size(pg_class.oid))  AS "total_size",
  pg_size_pretty(pg_relation_size(pg_class.oid))        AS "data_size",
  pg_size_pretty(pg_indexes_size(pg_class.oid))         AS "index_size",
  pg_stat_user_tables.n_live_tup                        AS "rows",
  pg_size_pretty(
    pg_total_relation_size(pg_class.oid) / 
    (pg_stat_user_tables.n_live_tup + 1)
  )                                                     AS "total_row_size",
  pg_size_pretty(
    pg_relation_size(pg_class.oid) / 
    (pg_stat_user_tables.n_live_tup + 1)
  )                                                     AS "row_size"
FROM 
  pg_stat_user_tables 
JOIN 
  pg_class
ON
  pg_stat_user_tables.relid = pg_class.oid
JOIN 
  pg_catalog.pg_namespace AS ns
ON
  pg_class.relnamespace = ns.oid
-- WHERE
--   nspname = 'public'
ORDER BY 
  pg_total_relation_size(pg_class.oid) DESC;

To filter by schema, uncomment the WHERE clause.

To better understand the query:

pg_size_pretty(<numeric_value>) - converts number of bytes to human-readable format.

pg_total_relation_size(<relation_id>) - gets total size of table and its indexes in bytes.

pg_relation_size(<relation_id>) - gets relation (table/index) size in bytes.

pg_indexes_size(<relation_id>) - gets index size of the relation in bytes.

pg_stat_user_tables.n_live_tup - is the number of rows in the table.

Output may look like this:

   schema    |                table                | total_size | data_size  | index_size |  rows  | total_row_size |  row_size  
-------------+-------------------------------------+------------+------------+------------+--------+----------------+------------
 public      | mp                                  | 66 MB      | 32 MB      | 34 MB      | 200659 | 344 bytes      | 167 bytes
 hdb_catalog | event_invocation_logs               | 18 MB      | 16 MB      | 1736 kB    |  11034 | 1667 bytes     | 1495 bytes
 hdb_catalog | event_log                           | 6760 kB    | 5032 kB    | 1608 kB    |  11038 | 627 bytes      | 466 bytes
 public      | links                               | 3208 kB    | 760 kB     | 2416 kB    |   7565 | 434 bytes      | 102 bytes
 hdb_catalog | hdb_cron_event_invocation_logs      | 1712 kB    | 1464 kB    | 216 kB     |   1280 | 1368 bytes     | 1170 bytes
 hdb_catalog | hdb_cron_events                     | 672 kB     | 208 kB     | 424 kB     |   1600 | 429 bytes      | 133 bytes
 public      | selectors_cache                     | 656 kB     | 48 kB      | 576 kB     |    333 | 2011 bytes     | 147 bytes
 public      | objects                             | 408 kB     | 120 kB     | 256 kB     |   1288 | 324 bytes      | 95 bytes
 public      | strings                             | 360 kB     | 96 kB      | 160 kB     |    451 | 815 bytes      | 217 bytes
 public      | promise_links                       | 328 kB     | 8192 bytes | 256 kB     |      0 | 328 kB         | 8192 bytes
 hdb_catalog | hdb_metadata                        | 296 kB     | 48 kB      | 32 kB      |      1 | 148 kB         | 24 kB
 public      | bool_exp                            | 176 kB     | 16 kB      | 128 kB     |     24 | 7208 bytes     | 655 bytes
 public      | links__tables                       | 120 kB     | 0 bytes    | 112 kB     |      0 | 120 kB         | 0 bytes
 public      | numbers                             | 120 kB     | 0 bytes    | 112 kB     |      0 | 120 kB         | 0 bytes
 hdb_catalog | hdb_schema_notifications            | 64 kB      | 8192 bytes | 16 kB      |      1 | 32 kB          | 4096 bytes
 hdb_catalog | hdb_version                         | 48 kB      | 8192 bytes | 32 kB      |      1 | 24 kB          | 4096 bytes
 storage     | buckets                             | 32 kB      | 8192 bytes | 16 kB      |      1 | 16 kB          | 4096 bytes
 public      | reserved                            | 32 kB      | 8192 bytes | 16 kB      |      8 | 3640 bytes     | 910 bytes
 hdb_catalog | hdb_source_catalog_version          | 32 kB      | 8192 bytes | 16 kB      |      1 | 16 kB          | 4096 bytes
 hdb_catalog | hdb_scheduled_events                | 24 kB      | 0 bytes    | 16 kB      |      0 | 24 kB          | 0 bytes
 storage     | files                               | 24 kB      | 0 bytes    | 16 kB      |      0 | 24 kB          | 0 bytes
 hdb_catalog | hdb_scheduled_event_invocation_logs | 16 kB      | 0 bytes    | 8192 bytes |      0 | 16 kB          | 0 bytes
 hdb_catalog | hdb_action_log                      | 16 kB      | 0 bytes    | 8192 bytes |      0 | 16 kB          | 0 bytes
(23 rows)

P.S.

I do +1 only to ignore devision by zero error, if you know a better way tell me about it.

More functions in PostgreSQL docs.

Spiritualism

Answer 12

You can get total relation size and relation size, which may be different depending on your tables relationships. Now here's how to get top 100 tables on your database:

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 100;

Safranine

Answer 13

 select uv.a tablename, pg_size_pretty(uv.b) sizepretty 
 from (select tb.tablename a, pg_table_size('schemaname.'||tb.tablename::text) b 
        from pg_tables tb 
        where tb.schemaname ilike 'schemaname' 
        order by 2 desc
       ) uv

Doddering

Your answer would be more valuable if you include an explanation as to why the suggested approach is helpful. – Nonfiction

Its similar to horse's answer, just doing sorting by size with pretty since sort view will be easy to look. – Doddering

Please add that text to your Answer, using the Edit link in the "Answer" space. Then your contribution will conform to the StackOverflow guidelines (read a bit in the Help Center) :-) – Nonfiction

Answer 14

select table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
from information_schema.tables
where table_schema = 'public'
order by pg_total_relation_size(quote_ident(table_name));

pg_total_relation_size would include size of indexes as well as tables. If you want only the table size, then pg_relation_size would be enough.

Hockey

Answer 15

I like to use this one (as it shows index size too)

SELECT
   relname  as table_name,
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Index Size",
   pg_size_pretty(pg_relation_size(relid)) as "Actual Size"
   FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

Myrmecophagous

Answer 16

Most of the answers here use pg_size_pretty which is very useful, but if you want to the output as a numerical value you can calculate this yourself like

SELECT tab_size /1024 AS size_kb
      ,tab_size /1024 /1024 AS size_mb
      ,tab_size /1024 /1024 / 1024 AS size_gb
      ,tab_size /1024 /1024 / 1024 / 1024 AS size_tb
  FROM 
      (
       SELECT pg_total_relation_size(relid) AS tab_size
         FROM pg_catalog.pg_statio_user_tables
        WHERE schemaname = 'your_schema' 
          AND relname = 'your_table'
      ) AS tabs;

Gen