How to Retrieve the Database Size?

1. Introduction

Unlike table size checks, monitoring the size of a database is an essential task for administrators and developers to ensure efficient storage management and optimal performance.

In this tutorial, we’ll explore how to use the built-in tools and system views of SQL Server and PostgreSQL, which enable us to quickly get a snapshot of the size of a single database or all databases in an instance.

2. How to Check Database Size in PostgreSQL?

PostgreSQL provides built-in functions that make retrieving the database size fairly trivial.

Here, the query retrieves the size of the specific PostgreSQL database named university, showing it as 294 MB:

# SELECT pg_database.datname AS "Database", size_pretty(pg_database_size(pg_database.datname)) AS "Size" FROM pg_database WHERE datname = 'university';
   Database    |  Size   
---------------+---------
 university    | 294 MB
(1 row)

Also, the pg_database_size function returns the size in bytes, whilst pg_size_pretty makes it human-readable (e.g., in MB, GB).

Furthermore, we can list the sizes of all databases on the server, ordered from largest to smallest via ORDER BY:

# SELECT datname AS "Database", pg_size_pretty(pg_database_size(datname)) AS "Size" FROM pg_database ORDER BY pg_database_size(datname) DESC;
   Database    |  Size   
---------------+---------
 gateway       | 543 MB
 university    | 294 MB
 apiserver     | 288 MB
 ...
 ...
 ...
 data_archival | 8436 kB
(11 rows)

From the output, we can see that gateway is the largest database at 543 MB, followed by university and apiserver. On the other hand, the smallest database is data_archival with 8436 kB in size.

3. How to Check Database Size in SQL Server?

Similarly, SQL Server provides built-in functions to check database sizes.

To begin with, we can use the sp_spaceused procedure, which returns the total size and unallocated space of the current database:

# EXEC sp_spaceused;

 database_name   |  database_size  | unallocated space
-----------------+-----------------+-------------------
  university     |    294 MB       |    121 MB

Alternatively, the sys.master_files can show the university database size and report its total size in MB:

# SELECT DB_NAME(database_id) AS [Database], CAST(SUM(size) * 8 / 1024 AS VARCHAR(20)) + ' MB' AS [Size] FROM sys.master_files WHERE DB_NAME(database_id) = 'university' GROUP BY database_id;

   Database    |  Size   
---------------+---------
 university    | 294 MB

Next, to see the sizes of all databases in the SQL Server instance, we can join sys.databases with sys.master_files:

# SELECT d.name AS [Database], CAST(SUM(mf.size) * 8 / 1024 AS VARCHAR(20)) + ' MB' AS [Size] FROM sys.databases d JOIN sys.master_files mf ON d.database_id = mf.database_id GROUP BY d.name ORDER BY SUM(mf.size) DESC;

   Database    |  Size
---------------+---------
 gateway       | 543 MB
 university    | 294 MB
 apiserver     | 288 MB
 ...
 ...
 ...
 data_archival | 8436 kB

Typically, built-in functions like sp_spaceused provide the easiest way to get the current database size.

Additionally, for more detailed insights, we can query sys.master_files and check the sizes of a specific database or all databases.

4. How to Check Database Size in MySQL?

MySQL doesn’t provide any built-in function like PostgreSQL or SQL Server to check database size, but it offers a straightforward way through the information_schema.tables system view.

With that in mind, we can calculate the size of a specific database (e.g., university) by summing up the data and index lengths for all tables under that schema:

> SELECT table_schema AS "Database", FLOOR(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.tables WHERE table_schema = 'university' GROUP BY table_schema;

+------------+------------+
| Database   | Size (MB)  |
+------------+------------+
| university | 294        |
+------------+------------+

Here, the query returns the total size of the university database as 294 MB.

Furthermore, to view the sizes of all databases on the server and sort them from largest to smallest, we can leverage a fairly simple query:

> SELECT table_schema AS "Database", FLOOR(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables GROUP BY table_schema ORDER BY SUM(data_length + index_length) DESC;

+----------------+------------+
| Database       | Size (MB)  |
+----------------+------------+
| gateway        | 543        |
| university     | 294        |
| applicationdbs | 121        |
| data_archival  | 8          |
+----------------+------------+

Thus, the output shows the total size of each database by summing data_length (table data) and index_length (indexes) from all tables in the schema. As such, it reflects the actual on-disk storage in megabytes, excluding logs or temporary files.

5. Conclusion

In this article, we got into database size checks.

In summary, monitoring database sizes is essential for efficient storage management and optimal performance. SQL Server and PostgreSQL provide built-in functions to quickly check the size of single or multiple databases, while MySQL offers system views like information_schema that enable similar insights through custom queries.

These tools can be leveraged to plan storage, optimize performance, and avoid unexpected space issues.