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 could use SSH clients like PuTTY and WinSCP to establish an SSH connection. Of course, these SSH clients work only for Windows users.

Linux and macOS have in-built terminals, so there is no need to install SSH clients. Now, log in to your server using secure shell (SSH) access. 

On the client-side, 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. 

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.

Write a Comment

Required*