How Do You Find The Disk Size Of A Postgres / PostgreSQL…

To find the disk size of a PostgreSQL database or instance, you can use SQL queries that provide detailed information about the storage and size of your PostgreSQL tables, databases, and indexes. Here are some useful methods to get this information:

1. Find the Total Size of a Database

To get the total size of a specific database in PostgreSQL, you can run the following query:

SELECT pg_size_pretty(pg_database_size('your_database_name'));

• Replace 'your_database_name' with the name of your database.
• The function pg_database_size() returns the total disk space used by the specified database, and pg_size_pretty() formats the output in a human-readable format (e.g., MB, GB).

Example:

SELECT pg_size_pretty(pg_database_size('my_database'));

Output:

 pg_size_pretty 
----------------
 1 GB

2. Find the Size of All Databases

To list the sizes of all databases in your PostgreSQL instance, use the following query:

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

This query will return a list of all databases with their respective disk sizes.

Example output:

 database_name | size
-------------------------
 postgres      | 10 MB
 my_database   | 1 GB
 template1     | 1.5 MB
 template0     | 1.5 MB

3. Find the Size of a Specific Table

To find the size of a specific table (including its indexes) in a database, you can use the following query:

SELECT 
    pg_size_pretty(pg_total_relation_size('your_table_name')) AS table_size;

• Replace 'your_table_name' with the name of your table.
• The function pg_total_relation_size() includes the table itself as well as the associated indexes.

Example:

SELECT 
    pg_size_pretty(pg_total_relation_size('my_table')) AS table_size;

Output:

 table_size 
--------------
 50 MB

4. Find the Size of All Tables in a Database

If you want to find the sizes of all tables in a specific database, use this query:

SELECT 
    tablename, 
    pg_size_pretty(pg_total_relation_size(tablename)) AS table_size
FROM 
    pg_tables
WHERE 
    schemaname = 'public';  -- You can adjust the schema name if needed

This query will list all tables in the public schema (you can adjust the schema name if your tables are in a different schema) and display their sizes.

Example output:

 tablename  | table_size
------------+------------
 users      | 200 MB
 orders     | 50 MB
 payments   | 10 MB

5. Find the Size of Indexes

To find the size of indexes associated with a table, you can use the following query:

SELECT 
    indexname, 
    pg_size_pretty(pg_indexes_size(indexname)) AS index_size
FROM 
    pg_indexes
WHERE 
    tablename = 'your_table_name';

This will show the size of each index related to the given table.

Example:

SELECT 
    indexname, 
    pg_size_pretty(pg_indexes_size(indexname)) AS index_size
FROM 
    pg_indexes
WHERE 
    tablename = 'users';

6. Check the Size of the Entire Schema

If you want to know the size of an entire schema (including tables and indexes), use the following query:

SELECT 
    nspname AS schema_name,
    pg_size_pretty(sum(pg_total_relation_size(pg_class.oid))) AS schema_size
FROM 
    pg_catalog.pg_namespace
    LEFT JOIN pg_catalog.pg_class ON pg_class.relnamespace = pg_namespace.oid
WHERE 
    nspname NOT LIKE 'pg_%' AND nspname != 'information_schema'
GROUP BY 
    nspname;

This will give you the total size of all objects within each schema, excluding system schemas.

7. Use the \l+ Command (psql)

If you’re using the psql command-line client, you can quickly get the size of all databases by running the following command:

\l+  -- Lists all databases with their sizes

This will display a list of databases with their respective sizes and other information.

• Database Size:

  SELECT pg_size_pretty(pg_database_size('your_database_name'));
  

• All Database Sizes:

  SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
  

• Table Size:

  SELECT pg_size_pretty(pg_total_relation_size('your_table_name'));
  

• All Tables Sizes:

  SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename)) FROM pg_tables WHERE schemaname = 'public';
  

• Index Sizes:

  SELECT indexname, pg_size_pretty(pg_indexes_size(indexname)) FROM pg_indexes WHERE tablename = 'your_table_name';
  

These queries will help you check the disk usage for your PostgreSQL database, tables, and indexes.