How to Check Postgres Database & Table Size

This guide is designed to assist you in finding out the size of databases and tables on your PostgreSQL server, making it easier to manage your data effectively.

To start, you’ll need to access your server via the command line and establish a connection with the PostgreSQL server. Here’s how you can determine the sizes of your databases and tables in PostgreSQL using straightforward commands.

1. Determine PostgreSQL Database Size Using SQL Queries

To find out how much space your PostgreSQL database occupies, follow these steps:

• First, open a terminal and log into the PostgreSQL command interface:

  
  sudo -u postgres psql
  
  

• Next, to check the database size, use the built-in function pg_database_size():

  
  SELECT pg_database_size('mydb');
  
  

  

  Quick Tip: Enhance the readability of database size information by combining the pg_size_pretty() function. This will display the size in an easy-to-understand format such as KB, MB, and GB:

  
  SELECT pg_size_pretty(pg_database_size('mydb'));
  
  

  

2. View PostgreSQL Database Size in pgAdmin

For those using pgAdmin, you can easily view your database size through its graphical interface:

Log into pgAdmin and select your database. Navigate to the “Statistics” tab, where you’ll see the size of your database displayed in a clear and concise manner.

3. Identify PostgreSQL Table Size

To find out the size of a specific table within your database:

• Connect to your desired database using:

  
  \c mydb;
  
  

• Then, determine the table size with the pg_total_relation_size() function, which provides the size in a human-friendly format:

  
  SELECT pg_size_pretty(pg_total_relation_size('employee'));
  
  

  

Concusion

This guide simplifies the process of measuring the size of databases and tables in PostgreSQL, offering clear instructions for both command-line and pgAdmin users. It’s a valuable resource for anyone looking to monitor their PostgreSQL server’s data storage efficiently.