In every database, indexes are essential for the speed of SELECT queries. However, each index we create has a cost, it takes up disk space, burdens memory and delays INSERT, UPDATE and DELETE operations, since the… read more
How to find the size of tables in a PostgreSQL database
When managing production databases, sooner or later there will come a time when the available disk space starts to decrease dangerously. The crucial question then is which table is eating up the space. In this article we will see… read more
How to check permissions, Grants and Default Privileges in PostgreSQL
In managing a PostgreSQL database, access control is one of the most critical parts. Unlike other RDBMSs that have single system views for all permissions, PostgreSQL relies on System Catalogs and special… read more
How to Backup and Restore PostgreSQL using the pgBackrest tool
As we have seen in previous articles, in older versions of PostgreSQL before version 17 there was no possibility for block level backup, as a result we could not do differential and incremental backups and we would have to take them every day... read more
How to Point in Time Restore a PostgreSQL database without using 3rd party tools
In a previous article we saw how to take full and log (WAL) backups without using 3rd party tools. In this article we will now see how we can restore the database with Point in Time Restore (PITR). The… read more
How to Backup a PostgreSQL Database Without Using 3rd Party Tools
In this article we will see step by step how to do a backup without using 3rd party tools full backup and log backup so that we can do PITR (Point in Time Recovery) if needed in the future. Finally we will see… read more
How memory works in PostgreSQL and what parameters do we configure?
In a previous article we have analyzed the architecture of Oracle Database (SGA/PGA). This time we will look at the memory architecture of PostgreSQL and the key parameters for optimal performance. The memory model of PostgreSQL differs significantly from that of… read more
What are WALs in PostgreSQL and how do we configure them?
In this article we will look at the mechanism that connects memory to disk and ensures that we will not lose data if the database goes down (e.g. due to power failure): Write-Ahead Logs (WAL) and Checkpoints. When we execute… read more
What is MVCC and Vacuum in PostgreSQL database and how do we avoid Bloat?
In this article we will see how PostgreSQL manages concurrent access through MVCC, what Bloat is, what Data Churn is, what Vacuum is, and how to properly configure it with Autovacuum. What is… read more
