postgresql list and order tables by size

Question

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

If you are using the command-line psql client then a simple `\d+` will show you this information, though unsorted. — cdhowie, Feb 12 '14 at 20:08
People looking for the same thing but for databases instead of tables : [here is the solution](https://stackoverflow.com/a/18907188/812102). — Skippy le Grand Gourou, Jun 12 '17 at 10:46
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. — Jürgen Strobel, Oct 16 '18 at 20:41

score 370 · Accepted Answer · edited Feb 14 '23 at 06:27

370

select
  table_name,
  pg_size_pretty(pg_relation_size(quote_ident(table_name))),
  pg_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.

edited Feb 14 '23 at 06:27

David Wolever

148,955
89
346
502

answered Feb 12 '14 at 20:07

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. – yieldsfalsehood Feb 12 '14 at 20:13
5

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! – nothing-special-here Feb 12 '14 at 20:19
any idea what this dont work in my case? http://stackoverflow.com/questions/40977776/why-cant-see-my-schema-size – Juan Carlos Oropeza Dec 05 '16 at 20:14
@Sucrenoir: "does not work" is not a valid Postgres error message. The query in my answer does work for me: http://rextester.com/KGKPR49004 – Feb 20 '17 at 09:05
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? – sheepdog May 31 '17 at 10:48
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. – Padmika May 16 '20 at 14:10
11

You should use `pg_total_relation_size` to get the total size of the table **including its indexes** - see https://stackoverflow.com/a/41991566/1668200 – Thomas Landauer Aug 17 '21 at 13:56

score 197 · Answer 2 · edited May 23 '17 at 11:54

197

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

edited May 23 '17 at 11:54

Community

1
1

answered Feb 12 '14 at 20:19

Kuchi

4,204
3
29
37

3

This is damn useful; takes into account indexes also. – Debodirno Chandra Nov 18 '20 at 10:11
1

Can be useful to add relkind to this to explicitly identifying table or index – Rob Bygrave Apr 06 '21 at 02:40
This is only working for the *public* scheme. – drookie Jan 23 '22 at 08:22
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://stackoverflow.com/q/41991380/118520 for details. – egor83 Jul 25 '22 at 10:45
1

Change `pg_relation_size` to `pg_total_relation_size` to include indices! – Sina Rezaei Dec 28 '22 at 07:20

score 66 · Answer 3 · edited May 17 '22 at 09:07

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

In more recent Postgres versions such as 12.4, this query gives an error - the fix is to [use quote_ident()](https://stackoverflow.com/a/65417941/992887) around the `table_name`. — RichVel, Nov 05 '21 at 06:16

michal.jakubeczy · Answer 4 · 2021-07-21T14:32:48.053

46

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.

edited Jul 21 '21 at 14:32

answered Oct 30 '20 at 11:43

michal.jakubeczy

8,221
1
59
63

Perfect thank you! – Andy Oct 16 '21 at 19:52
This is so far the best one. – Kaizendae May 16 '22 at 08:21

score 28 · Answer 5 · edited May 04 '17 at 06:45

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

score 22 · Answer 6 · answered Nov 18 '19 at 14:06

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.

score 11 · Answer 7 · answered Jun 21 '21 at 12:46

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

score 11 · Answer 8 · answered Nov 24 '21 at 23:09

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;

score 6 · Answer 9 · answered Apr 27 '15 at 01:38

6

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

answered Apr 27 '15 at 01:38

Lauri Lüüs

189
2
8

score 5 · Answer 10 · answered May 11 '22 at 22:37

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;

score 3 · Answer 11 · answered Nov 18 '20 at 10:16

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.

score 3 · Answer 12 · answered Feb 08 '23 at 08:20

3

It can be easily done from psql console.

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.

answered Feb 08 '23 at 08:20

Radix

2,527
1
19
43

score 2 · Answer 13 · edited Jul 30 '19 at 06:30

2

 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

edited Jul 30 '19 at 06:30

EAmez

837
1
9
25

answered Jan 12 '16 at 06:00

Spike

734
1
12
22

1

Your answer would be more valuable if you include an explanation as to why the suggested approach is helpful. – Cindy Meister Jan 12 '16 at 07:05
Its similar to horse's answer, just doing sorting by size with pretty since sort view will be easy to look. – Spike Jan 12 '16 at 07:57
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) :-) – Cindy Meister Jan 13 '16 at 16:36

score -1 · Answer 14 · answered Jun 15 '22 at 10:21

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;

postgresql list and order tables by size

14 Answers14

Linked

Related