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.
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:
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:
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:
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4