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.
'"' || table_schema || '"."' || table_name || '"' to format('%I.%I', table_schema, table_name).
pg_size_pretty and just do the inner query if you want raw byte counts to be processed programmatically, such as for your own javascript dashboard.
'"' || table_schema || '"."' || table_name || '"' to table_schema.table_name - PostgreSQL only requires indentifier quoting if there are spaces, caplitalisation or punctuation! Stick to snake_case with characters and digits (digit can't, IIRC, be the first character of an identifer) - with 63 characters you_can_have_quite_a_long_idenfier_name_with_no_problems_at_all (63 chars!).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}'
dbsize.
-t to psql command to avoid column title in the output (which can be useful for automation), e.g.: psql -c "\l+ snort" -t | awk -F "|" '{print $7}'
\d+ for tablesTry 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:
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
-- tables from 'public' subquery, the table_name needs to be cast to text. After making that change, the query worked for me. Without it, I get a generic type error on the pg_table_size function. table_name is of type information_schema.sql_identifierA 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/
