PostgreSQL Database Size Command: Syntax and Examples

PostgreSQL Command to check Database Size

PostgreSQL provides several commands and functions to help users monitor the storage used by a database, schema, or individual tables. Using these commands, you can quickly determine the size of your PostgreSQL database or specific tables.

Description

To find the size of a PostgreSQL database, PostgreSQL offers convenient functions like pg_database_size() and pg_size_pretty() that allow you to get the exact storage space used in a more readable format. These commands help administrators monitor storage usage and optimize data management.

Syntax:

SELECT pg_size_pretty(pg_database_size('database_name'));

Get Size of All Databases:

Code:

SELECT datname AS database_name, 
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;

Get Size of Tables within a Database:

Code:

SELECT table_name, 
       pg_size_pretty(pg_total_relation_size(table_name::regclass)) AS size
FROM information_schema.tables 
WHERE table_schema = 'public';

Example: Checking a Single Database Size

Code:

-- Get the size of a specific database by name
SELECT pg_size_pretty(pg_database_size('my_database'));

Explanation:

• pg_database_size('my_database'): Calculates the size in bytes for the database named my_database.
• pg_size_pretty(): Converts bytes into a human-readable format (e.g., MB, GB).

Example: Checking Sizes of All Databases

Code:

-- Retrieve sizes of all databases in PostgreSQL
SELECT datname AS database_name, 
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;

Explanation:

• datname: Represents the database name.
• pg_database_size(datname): Calculates each database’s size.
• pg_size_pretty() formats each result, making it easy to compare the size of multiple databases.

Example: Checking Table Sizes within a Database

Code:

-- Find the size of each table within the 'public' schema
SELECT table_name, 
       pg_size_pretty(pg_total_relation_size(table_name::regclass)) AS size
FROM information_schema.tables 
WHERE table_schema = 'public';

Explanation:

• table_name: The name of each table in the public schema.
• pg_total_relation_size(table_name::regclass): Computes the total size of each table, including indexes and additional storage.
• pg_size_pretty(): Formats the size into a readable format.

Full Example:

Code:

-- Example: Check the size of a specific PostgreSQL database

-- Retrieve the human-readable size of 'my_database'
SELECT pg_size_pretty(pg_database_size('my_database'));

-- List all databases and their sizes
SELECT datname AS database_name, 
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;

-- Find the size of each table within the 'public' schema
SELECT table_name, 
       pg_size_pretty(pg_total_relation_size(table_name::regclass)) AS size
FROM information_schema.tables 
WHERE table_schema = 'public'; 

All PostgreSQL Questions, Answers, and Code Snippets Collection.