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.
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.
