How do you find the disk size of a Postgres / PostgreSQL table and its indexes

Show database sizes:

\l+

e.g.

=> \l+ berbatik_prd_commerce | berbatik_prd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 19 MB | pg_default | berbatik_stg_commerce | berbatik_stg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8633 kB | pg_default | bursasajadah_prd | bursasajadah_prd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 1122 MB | pg_default | 

Show table sizes:

\d+

e.g.

=> \d+ public | tuneeca_prd | table | tomcat | 8192 bytes | public | tuneeca_stg | table | tomcat | 1464 kB | 

Only works in psql.

(Summary of @zkutch's answer.)

If the database name is snort, the following sentence give its size:

psql -c "\l+ snort" | awk -F "|" '{print $7}' 

Try this : (Index size/usage statistics)

SELECT t.tablename, indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size, pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size, CASE WHEN indisunique THEN 'Y' ELSE 'N' END AS UNIQUE, idx_scan AS number_of_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched FROM pg_tables t LEFT OUTER JOIN pg_class c ON t.tablename=c.relname LEFT OUTER JOIN ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x JOIN pg_class c ON c.oid = x.indrelid JOIN pg_class ipg ON ipg.oid = x.indexrelid JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid ) AS foo ON t.tablename = foo.ctablename WHERE t.schemaname='public' ORDER BY 1,2; 

PostgreSQL tables have three components: the table itself, any indexes on it, and potentially TOAST data. There's a couple of examples showing how to slice and dice the available information various ways at http://wiki.postgresql.org/wiki/Disk_Usage

Just for info, I have got the excelent answer from @aib and modified it a little for:

• getting only tables from "public" schema
• show also materialized views data and index size

On materialized view we can use index for refreshing materialized views concurrently, which allows using them while updating.

Well, my query will be the following:

SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS indexes_size, pg_total_relation_size(table_name) AS total_size FROM ( -- tables from 'public' SELECT table_name FROM information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE' union -- materialized views SELECT oid::regclass::text as table_name FROM pg_class WHERE relkind = 'm' order by table_name ) AS all_tables -- ORDER BY total_size DESC order by table_name ) AS pretty_sizes 

A running percent of table size :

WITH T AS ( SELECT table_schema, table_name, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') / (1024.0 * 1024.0) AS TABLE_SIZE_MB, SUM(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) OVER() / (1024.0 * 1024.0) AS TOTAL_TABLE_SIZE_MB, ROW_NUMBER() OVER(ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"'), table_schema, table_name) AS N FROM information_schema.tables WHERE table_type = 'BASE TABLE' ) SELECT table_schema, table_name, CAST(TABLE_SIZE_MB AS DECIMAL(16, 2)) AS TABLE_SIZE_MB, CAST(TOTAL_TABLE_SIZE_MB AS DECIMAL(16, 2)) AS TOTAL_TABLE_SIZE_MB, CAST(100.0 * TABLE_SIZE_MB / TOTAL_TABLE_SIZE_MB AS DECIMAL(5,2)) AS PERCENT_TABLE_SIZE, CAST(100.0 * SUM(TABLE_SIZE_MB / TOTAL_TABLE_SIZE_MB) OVER(ORDER BY N DESC) AS DECIMAL(5,2)) AS RUNNING_PERCENT_SIZE FROM T ORDER BY 3 desc; 

check this wiki. https://wiki.postgresql.org/wiki/Disk_Usage

 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 

The Query below will serve you

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 LIMIT 20; 

See this Link: https://wiki.postgresql.org/wiki/Disk_Usage

Here is a modified version of the accepted answer that includes a column for the row count of each table, if anyone is similarly interested

DO $$ DECLARE rec RECORD; row_count BIGINT; BEGIN FOR rec IN SELECT table_schema || '.' || table_name AS table_name, pg_size_pretty(pg_table_size(quote_ident(table_schema) || '.' || quote_ident(table_name))) AS table_size, pg_size_pretty(pg_indexes_size(quote_ident(table_schema) || '.' || quote_ident(table_name))) AS indexes_size, pg_size_pretty(pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name))) AS total_size FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name)) DESC LOOP EXECUTE 'SELECT count(*) FROM ' || rec.table_name INTO row_count; RAISE NOTICE '% | % | % | % | %', rec.table_name, rec.table_size, rec.indexes_size, rec.total_size, row_count; END LOOP; END $$; 

I was unhappy with the accepted answer because its ORDER BY didn't actually work. Sizes would be sorted by their first digit, with no accounting for the unit size. As a result, something like '60 kB' would show as "more" than '59 MB', both of which were less than '8192 bytes'. I ended up using that answer to feed data into a CTE which then calculated out the size in MB for each table, creating a single standard to sort upon.

WITH size_query AS ( SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS SIZE, CAST(REGEXP_REPLACE(pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')), '[a-zA-Z\s]', '', 'g') AS NUMERIC) AS SIZE_NUMBER_ONLY FROM information_schema.tables ) SELECT table_full_name, SIZE AS long_form_size, CASE WHEN SIZE LIKE '%' THEN ROUND(SIZE_NUMBER_ONLY / 1000 / 1000, 4) WHEN SIZE LIKE '%kB' THEN ROUND(SIZE_NUMBER_ONLY / 1000, 4) WHEN SIZE LIKE '%MB' THEN SIZE_NUMBER_ONLY WHEN SIZE LIKE '%GB' THEN SIZE_NUMBER_ONLY * 1000 WHEN SIZE LIKE '%TB' THEN SIZE_NUMBER_ONLY * 1000 * 1000 END AS SORTABLE_SIZE_IN_MB FROM size_query ORDER BY 3 DESC 

Rounding to 4 decimal places makes sure that if you have small tables (measured in Bytes) that they're distinguishable from a table that is truly empty (0 Bytes). This query let me search easily through a PostgreSQL database where I knew the items of interest would be in very large tables.

I'm sure there's a little easier way to do that (especially the CASE statement), but this was readable to other data analysts I was working with, and it works.

Try this script to find all table size:

SELECT table_schema || '.' || table_name AS TableName, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS TableSize FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC 

For other different script to find size in PostgreSQL, Please visit this url: http://www.dbrnd.com/2015/05/how-to-find-size-of-database-and-table-in-postgresql/