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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/postgres-db-size-command.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics