How PostgreSQL query table size (separate query and batch query)

Preface

Querying the size of a PG table usually requires the use of functions/views to implement it, and it is divided into scenarios of separate queries and batch queries. Here is a brief list:

1. Single table size query

If you want to query the size of a single table, you can use commonly used functions, and the reference statement is as follows:

select pg_size_pretty(pg_relation_size('Table name'));

Note: This query result does not include the index size. If you want to query the index size, you can get it by querying information_schema.tables.

2. Batch query of all database table sizes

If you want to query the size of all tables, including the index, then the most convenient way is to directly query the information_schema.tables table. You can refer to the following query statement:

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;

Attachment: Query the database size

-- Query a single database size
select pg_size_pretty(pg_database_size('postgres')) as size;
 
-- Query all database sizes
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;

Summarize

This is the end of this article about how PostgreSQL query table size. For more information about PostgreSQL query table size, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!