Disk space used by databases and tables in MySQL PostgreSQL
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 ;
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";
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'));
Getting the space used by all databases in list format is even easier
\l+
Space used for each table
\d+
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'));