How to Determine The Size of PostgreSQL Databases and Tables

Postgres is an advanced, robust and reliable enterprise-class open-source relational database. It supports both relational (SQL) and non-relational (JSON) querying.

Postgres is highly scalable. This rings true in both the volume of data it can handle and the number of concurrent users it can support.

Popular use cases for Postgres includes:

  • General-purpose OLTP (Online Transactional Processing) databases
  • Geospatial databases
  • Federated hub databases
  • LAPP (Linux, Apache, Postgres and PHP, Python and Perl) databases

This article will be looking at how to determine the size of PostgreSQL databases and tables using the psql command-line program.

But first, let’s take a look at Postgres architecture.

Understanding Postgres Architecture

Postgres architecture runs on the client and server model. The clients send requests to the server, and the server responds with the requests. They send these requests over a secured network.

How to Determine The Size of PostgreSQL Databases and Tables

Image Credit: Edecba

Client computers provide an interface to allow a Postgre user to request the server’s services and display its results. A Postgres server can handle multiple concurrent sessions from the client without logging any user out.

This capability makes Postgres suitable for complex and large-scale operations.

Postgres architecture consists of three components:

  • The shared memory reserves the memory for transactions and log catches.
  • Background processes; these are software programs that run behind the scene to make other software run faster or perform their tasks. 
  • The data directory contains the physical files of the Postgres database server.

Now, how can you determine the size of the PostgreSQL databases and tables?

Let’s get to it.

How to Determine The Size of Postgres Databases

To determine your Postgre database’s size from the command line, you’ll need to install a secure shell (SSH) access client on your local computer. 

The SSH provides secured command-line access to your hosting accounts. This allows you to log in to your server remotely and run commands from anywhere. It also will enable you to perform specific tasks you couldn’t do through the SPanel.

You can download SSH clients like PuTTY and WinSCP, or you can use your OS’ built-in command-line interface (PowerShell for Windows or the Terminal for macOS and Linux).

Run this command to access the database you want to determine its size:

psql databname username

Of course, replace databname and username with the name of the database you want to access and username, respectively. When prompted, enter the database user’s password. You’re connected when the remote server’s command line prompt appears.

Now, run this command to determine the database size.

SELECT pg_size_pretty( pg_database_size(‘databname‘) );

Replace databname with the name of the database you want to access. Running this command will make the server return the result of the database size.

Determining The Size of PostgreSQL Tables

To determine the size of a Postgres database table from the command line, log in to your server using SSH and access the database with the table you want to check.

Type this code in the command line to determine the size of any of the database’s tables.

SELECT pg_size_pretty( pg_total_relation_size(‘tablename’) );

Replace tablename with the name of the Postgres table you want to check. Doing this will allow the psql program to display the table’s size.

Wrapping It Up

SSH access is a time-saving solution that helps people speed up many tasks. You can check your PostgreSQL databases sizes and Postgres table sizes remotely from the command line. This guide has demonstrated how to go about that. Follow the steps here to get started. 

Was this helpful?

Do you want to make your WordPress/WooCommerce site lightning fast?

We guarantee to make your WordPress site load in less than 2 seconds on a managed VPS with ScalaHosting or give your money back. Just fill in the form below and we will get in touch with you.

Please enter a valid name
Please enter a valid website
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Do you want to learn how to grow your website in 2023?

An all-star team of SEO and web influencers are sharing their secret knowledge for the first time in years. You will learn about:

  • What is the future of SEO and how you can be ahead of the curve
  • Web Design best practices and why they matter
  • Secrets to getting the foundation for your website to thrive

An Exclusive Insiders Look Behind The SEO and Web Development Curtain

Rado
Author

Working in the web hosting industry for over 13 years, Rado has inevitably got some insight into the industry. A digital marketer by education, Rado is always putting himself in the client's shoes, trying to see what's best for THEM first. A man of the fine detail, you can often find him spending 10+ minutes wondering over a missing comma or slightly skewed design.