check-size-of-table-postgresql.md

aliases	category	classification	date	date_modified	draft	id	image	links	local_archive_links	pinned	print	series	tags	title	type
check-size-of-table-postgresql	postgresql	public	2020-09-22 05:50:20 -0700	2020-09-22 05:50:20 -0700	false	20200922125020				false	false		postgresql psql tables size database	Check Size of a Table in PostgreSQL	tech-note

To get an overview of how much space is taken by each table in a database.

psql --host=<host> --dbname=<dbname> --username=<username> --command='SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As "Size", pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;'

The SQL above, in a prettier format looks like this:

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Get the size of all objects:

psql --host=<host> --dbname=<dbname> --username=<username> --command='SELECT relname AS objectname, relkind AS objecttype, reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size FROM pg_class WHERE relpages >= 8 ORDER BY relpages DESC;' > db_table_object_size.txt

Again, the SQL, in a prettier format:

SELECT
   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
   FROM pg_class
   WHERE relpages >= 8
   ORDER BY relpages DESC;