Disk Usage
Finding the size of various object in your database
General Table Size Information Grouped For Partitioned Tables
This will report size information for all tables, that are not inherited, in the "pretty" form. Inherited tables are grouped together.
WITHRECURSIVEpg_inherit(inhrelid,inhparent)AS(selectinhrelid,inhparentFROMpg_inheritsUNIONSELECTchild.inhrelid,parent.inhparentFROMpg_inheritchild,pg_inheritsparentWHEREchild.inhparent=parent.inhrelid),pg_inherit_shortAS(SELECT*FROMpg_inheritWHEREinhparentNOTIN(SELECTinhrelidFROMpg_inherit))SELECTtable_schema,TABLE_NAME,row_estimate,pg_size_pretty(total_bytes)AStotal,pg_size_pretty(index_bytes)ASINDEX,pg_size_pretty(toast_bytes)AStoast,pg_size_pretty(table_bytes)ASTABLE,total_bytes::float8/sum(total_bytes)OVER()AStotal_size_shareFROM(SELECT*,total_bytes-index_bytes-COALESCE(toast_bytes,0)AStable_bytesFROM(SELECTc.oid,nspnameAStable_schema,relnameASTABLE_NAME,SUM(c.reltuples)OVER(partitionBYparent)ASrow_estimate,SUM(pg_total_relation_size(c.oid))OVER(partitionBYparent)AStotal_bytes,SUM(pg_indexes_size(c.oid))OVER(partitionBYparent)ASindex_bytes,SUM(pg_total_relation_size(reltoastrelid))OVER(partitionBYparent)AStoast_bytes,parentFROM(SELECTpg_class.oid,reltuples,relname,relnamespace,pg_class.reltoastrelid,COALESCE(inhparent,pg_class.oid)parentFROMpg_classLEFTJOINpg_inherit_shortONinhrelid=oidWHERErelkindIN('r','p'))cLEFTJOINpg_namespacenONn.oid=c.relnamespace)aWHEREoid=parent)aORDERBYtotal_bytesDESC;General Table Size Information Grouped For Partitioned Tables
Will show tables like above, but sizes split individually for each tablespace.
WITHRECURSIVEpg_inherit(inhrelid,inhparent)AS(selectinhrelid,inhparentFROMpg_inheritsUNIONSELECTchild.inhrelid,parent.inhparentFROMpg_inheritchild,pg_inheritsparentWHEREchild.inhparent=parent.inhrelid),pg_inherit_shortAS(SELECT*FROMpg_inheritWHEREinhparentNOTIN(SELECTinhrelidFROMpg_inherit))SELECTparent::regclass,coalesce(spcname,'default')pg_tablespace_name,row_estimate,pg_size_pretty(total_bytes)AStotal,pg_size_pretty(index_bytes)ASINDEX,pg_size_pretty(toast_bytes)AStoast,pg_size_pretty(table_bytes)ASTABLE,100*total_bytes::float8/sum(total_bytes)OVER()ASPERCENTFROM(SELECT*,total_bytes-index_bytes-COALESCE(toast_bytes,0)AStable_bytesFROM(SELECTparent,reltablespace,SUM(c.reltuples)ASrow_estimate,SUM(pg_total_relation_size(c.oid))AStotal_bytes,SUM(pg_indexes_size(c.oid))ASindex_bytes,SUM(pg_total_relation_size(reltoastrelid))AStoast_bytesFROM(SELECTpg_class.oid,reltuples,relname,relnamespace,reltablespacereltablespace,pg_class.reltoastrelid,COALESCE(inhparent,pg_class.oid)parentFROMpg_classLEFTJOINpg_inherit_shortONinhrelid=oidWHERErelkindIN('r','p'))cGROUPBYparent,reltablespace)a)aLEFTJOINpg_tablespaceON(pg_tablespace.oid=reltablespace)ORDERBYtotal_bytesDESC;General Table Size Information
This will report size information for all tables, in both raw bytes and "pretty" form.
SELECT*,pg_size_pretty(total_bytes)AStotal,pg_size_pretty(index_bytes)ASindex,pg_size_pretty(toast_bytes)AStoast,pg_size_pretty(table_bytes)AStableFROM(SELECT*,total_bytes-index_bytes-coalesce(toast_bytes,0)AStable_bytesFROM(SELECTc.oid,nspnameAStable_schema,relnameAStable_name,c.reltuplesASrow_estimate,pg_total_relation_size(c.oid)AStotal_bytes,pg_indexes_size(c.oid)ASindex_bytes,pg_total_relation_size(reltoastrelid)AStoast_bytesFROMpg_classcLEFTJOINpg_namespacenONn.oid=c.relnamespaceWHERErelkind='r')a)a;Finding the largest databases in your cluster
Databases to which the user cannot connect are sorted as if they were infinite size.
SELECTd.datnameasName,pg_catalog.pg_get_userbyid(d.datdba)asOwner,CASEWHENpg_catalog.has_database_privilege(d.datname,'CONNECT')THENpg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))ELSE'No Access'ENDasSizeFROMpg_catalog.pg_databasedorderbyCASEWHENpg_catalog.has_database_privilege(d.datname,'CONNECT')THENpg_catalog.pg_database_size(d.datname)ELSENULLENDdesc-- nulls firstLIMIT20;Finding the size of your biggest relations
Relations are objects in the database such as tables and indexes, and this query shows the size of all the individual parts. Tables which have both regular and TOAST pieces will be broken out into separate components; an example showing how you might include those into the main total is available in the documentation, and as of PostgreSQL 9.0 it's possible to include it automatically by using pg_table_size here instead of pg_relation_size:
Note that all of the queries below this point on this page show you the sizes for only those objects which are in the database you are currently connected to.
SELECTnspname||'.'||relnameAS"relation",pg_size_pretty(pg_relation_size(C.oid))AS"size"FROMpg_classCLEFTJOINpg_namespaceNON(N.oid=C.relnamespace)WHEREnspnameNOTIN('pg_catalog','information_schema')ORDERBYpg_relation_size(C.oid)DESCLIMIT20;Example output (from a database created with pgbench, scale=25):
relation | size ------------------------+------------ public.accounts | 326 MB public.accounts_pkey | 44 MB public.history | 592 kB public.tellers_pkey | 16 kB public.branches_pkey | 16 kB public.tellers | 16 kB public.branches | 8192 bytes
Finding the total size of your biggest tables
This version of the query uses pg_total_relation_size, which sums total disk space used by the table including indexes and toasted data rather than breaking out the individual pieces:
SELECTnspname||'.'||relnameAS"relation",pg_size_pretty(pg_total_relation_size(C.oid))AS"total_size"FROMpg_classCLEFTJOINpg_namespaceNON(N.oid=C.relnamespace)WHEREnspnameNOTIN('pg_catalog','information_schema')ANDC.relkind<>'i'ANDnspname!~'^pg_toast'ORDERBYpg_total_relation_size(C.oid)DESCLIMIT20;Easy access to these queries
~/.psqlrc tricks: table sizes shows how to make it easy to run size related queries like this in psql.
