w3resource

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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