PostgreSQL disk usage


To check the size of the current database:

SELECT pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(CURRENT_DATABASE()))

To check the size of each database:

SELECT
		d.datname AS "Database",
		pg_catalog.pg_get_userbyid(d.datdba) AS "Owner",
		CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
			THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
			ELSE 'No Access'
		END AS "Size"
	FROM pg_catalog.pg_database d
    ORDER BY
		CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
			THEN pg_catalog.pg_database_size(d.datname)
			ELSE 0
		END DESC

To check the size of each table in a database:

SELECT
		relname AS "Table",
		pg_size_pretty(pg_total_relation_size(pg_class.oid)) AS "Total size",
		pg_size_pretty(pg_table_size(pg_class.oid)) AS "Table size",
		pg_size_pretty(pg_indexes_size(pg_class.oid)) AS "Index size",
		pg_size_pretty(pg_relation_size(pg_class.oid, 'main')) AS "Relation main size",
		pg_size_pretty(pg_relation_size(pg_class.oid, 'fsm')) AS "Relation fsm size",
		pg_size_pretty(pg_relation_size(pg_class.oid, 'vm')) AS "Relation vm size",
		pg_size_pretty(pg_relation_size(pg_class.oid, 'init')) AS "Relation init size",
		pg_size_pretty(pg_total_relation_size(pg_class.reltoastrelid)) AS "Toast size"
	FROM pg_class
	LEFT JOIN pg_namespace
		ON (pg_namespace.oid = pg_class.relnamespace)
	WHERE
		nspname NOT IN ('pg_catalog', 'information_schema')
		AND pg_class.relkind <> 'i'
		AND nspname !~ '^pg_toast'
		AND pg_total_relation_size(pg_class.oid) > 1024 * 64
	ORDER BY
		pg_total_relation_size(pg_class.oid) DESC

To check the size of indexes:

SELECT
		pg_class_table.relname || '.' || pg_class_index.relname,
		pg_size_pretty(pg_relation_size(pg_class_index.oid)),
		pg_class_index.relpages
	FROM
		pg_class pg_class_table,
		pg_class pg_class_index, pg_index i
	WHERE
		pg_class_table.oid = i.indrelid
		AND pg_class_index.oid = i.indexrelid
	ORDER BY
		pg_relation_size(pg_class_index.oid) DESC;

Date: 2018-12-03

Tags:  postgresql

Share: