Disk space used by databases and tables in MySQL PostgreSQL

07 July
2012
,

I don't use raw SQL very often so when I do I usually end up checking the manual for the correct syntax. One query I've wanted to run a couple of times recently and always struggled to find the correct statement for is checking the amount of disk space used by a table or database.

For the ease of future reference here they are.

MySQL

Space used for each database

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / (1024 * 1024) "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ; 
Source

Space used for each table

SELECT table_name, round(((data_length + index_length) / (1024*1024)),2) as "size in megs" FROM information_schema.tables WHERE table_schema = "named_db";
Source

One potential gotcha with tables is if you have recently deleted a large number of rows. I ran into this when I deleted several thousand spam comments here on the blog. Running an OPTIMIZE TABLE query on the table solves the issue.

Postgresql

Space used for a named database

SELECT pg_database_size('named_db');
SELECT pg_size_pretty(pg_database_size('named_db'));
Source

Getting the space used by all databases in list format is even easier

\l+
Source

Space used for each table

\d+
Source

I found that the sum of the sizes returned by \d+ did not equal the size of the database returned by \l+ or pg_database_size. The sum of the sizes returned by the following command did equal the size of the database.

SELECT pg_size_pretty(pg_total_relation_size('named_table'));
Source

Comments