CODEWITHSUNDEEP
postgresql
mmrobins
mmrobins

Reputation: 13755

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.

Upvotes: 269

Views: 189707

Answers (11)

NFeruch - FreePalestine
NFeruch - FreePalestine

Reputation: 1145

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

Upvotes: 0

Greg Smith
Greg Smith

Reputation: 18136

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

Upvotes: 12

SQLpro
SQLpro

Reputation: 5103

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;

Upvotes: 1

zkutch
zkutch

Reputation: 401

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

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

Upvotes: 28

Ahmed MANSOUR
Ahmed MANSOUR

Reputation: 2559

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;

Upvotes: 25

aib
aib

Reputation: 46921

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.

Upvotes: 453

Ciges
Ciges

Reputation: 1143

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

Upvotes: 8

Sajeev
Sajeev

Reputation: 799

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

Upvotes: 1

Uma
Uma

Reputation: 11

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

Upvotes: 1

Anvesh
Anvesh

Reputation: 7683

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/

Upvotes: -1

Hendy Irawan
Hendy Irawan

Reputation: 21384

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.)

Upvotes: 227

Related Questions