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

I'm coming to Postgres from Oracle and looking for a way to find the table and index size in terms of bytes/MB/GB/etc, or even better the size for all tables. In Oracle I had a nasty long query that looked at user_lobs and user_segments to give back an answer.

I assume in Postgres there's something I can use in the information_schema tables, but I'm not seeing where.

Try the Database Object Size Functions. An example:

SELECT pg_size_pretty(pg_total_relation_size('"<schema>"."<table>"'));

For all tables, something along the lines of:

SELECT
	table_schema || '.' || table_name AS table_full_name,
	pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
	pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

Edit: Here's the query submitted by @phord, for convenience:

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 (
		SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
		FROM information_schema.tables
	) AS all_tables
	ORDER BY total_size DESC
) AS pretty_sizes;

I've modified it slightly to use pg_table_size() to include metadata and make the sizes add up.

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 it 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 slide 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

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

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/