Postgres DB Size Command

Full database size toolkit

Database size query at a glance

For a panoramic view of your entire database landscape, a more all-around SQL query does the trick:

SELECT 
  datname AS "Database Name", 
  pg_size_pretty(pg_database_size(datname)) AS "Size"
FROM 
  pg_database
ORDER BY 
  pg_database_size(datname) DESC;


This query gives sizes in a human-readable format and orders databases by size, handy for focusing on bigger databases first.

Fast travel guide on psql commands

Capitalizing on psql, you can swiftly get around all databases’ sizes at a high level of detail:

\l+


This simple command returns a list of all databases accessible by the current user along with their sizes and other attributes.

Detailed examination of table sizes

Psychoanalyzing tables with psql

Going Freudian on your tables and understanding what takes up space can be done with a single command:

\dt+ *


You will get an in-depth view of every table’s footprint in the currently connected schema.

Surgical precision with SQL

To take it a step further, you can use the following query to neatly list tables and their sizes, sans the system tables:

SELECT 
  relname AS "Table",
  pg_size_pretty(pg_relation_size(C.oid)) AS "Size"
FROM 
  pg_class C
LEFT JOIN 
  pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') 
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY 
  pg_relation_size(C.oid) DESC;


It comprehensively excludes auxiliary system tables to allow crystal-clear focus on user-defined tables.

Specialist procedures for advanced users

Extracting raw size data

While pg_size_pretty enhances readability, some use cases—like **monitoring systems—**demand raw size in bytes:

SELECT 
  datname AS "Database Name", 
  pg_database_size(datname) AS "Size in Bytes"
FROM 
  pg_database
ORDER BY 
  "Size in Bytes" DESC;


This modification of the query ensures that you get the raw data sizes, no prettifying whatsoever.

Cross-referencing with other statistics

It's like making the database hold a mirror up to itself. Augment your data with row counts, user connections, or other relevant stats and churn out some comprehensive, all-encompassing database insights.

Advanced insights for experienced users

Time-lapse of data growth

Who doesn't love a good trend analysis? Save size snapshots at regular intervals, plot growth trends, and be the Nostradamus of your database’s storage future.

Addressing fragmented spaces

Fragmented spaces play hard to get, but those can be reclaimed using vacuum or reindexing.

Maintenance schedule for peak performance

Good databases need good upkeep. Regular tasks such as vacuuming or analyzing tables for consistent performance and efficient space utilization pay off in spades.