by Jobin Augustine | Apr 30, 2024 | Insight for DBAs, Insight for Developers, PostgreSQL, Security
There are many cases where external connection poolers like pgBouncer become unavoidable despite the costs and complexities associated with them. PgBouncer is one of the most popular external connection poolers for PostgreSQL. It is thin and lightweight, so it... by Jobin Augustine | Apr 1, 2024 | Insight for DBAs, Insight for Developers, PostgreSQL
This blog was originally published in April 2023 and was updated in April 2024.Indexes are generally considered to be the panacea when it comes to SQL performance tuning, and PostgreSQL supports different types of indexes catering to different use cases. I keep seeing... by Jobin Augustine | Apr 1, 2024 | Insight for DBAs, PostgreSQL
This blog was originally published in June 2022 and was updated in April 2024.PostgreSQL’s built-in autovacuum – the housekeeper – is improving, version after version. It is becoming more capable while reducing its overhead and addressing edge cases.... by Jobin Augustine | Dec 29, 2023 | Insight for DBAs, PostgreSQL
PostgreSQL 14 introduced the parameter idle_session_timeout, and, unfortunately, many DBAs jumped to start using it without understanding or by ignoring the consequences. In a short span of time, it has become one of the most misused parameters in many PostgreSQL... by Jobin Augustine | Aug 30, 2023 | Insight for DBAs, Insight for Developers, PostgreSQL
This blog was published in August 2023 and updated in April 2024. PostgreSQL allows its users to set parameters at different scopes, and the same parameter can be specified at different places and using different methods. And there could be conflicts. Someone might be... by Jobin Augustine | Jul 20, 2023 | Insight for DBAs, Insight for Developers, PostgreSQL
It is very common to see many infrastructure layers standing between a PostgreSQL database and the Application server. The most common ones are connection poolers, load balancers, routers, firewalls, etc. We often forget or take for granted the network... by Jobin Augustine | Jul 2, 2023 | Insight for DBAs, PostgreSQL
This blog was originally written in September 2018 and was updated in June 2023.In this post, we cover the methods used to achieve an enterprise-grade backup strategy for the PostgreSQL cluster. In setting up our demonstration system, we use pg_basebackup and... by Jobin Augustine | Jan 24, 2023 | Insight for DBAs, PostgreSQL
Attempts to compress PostgreSQL WAL at different levels have always been around since the beginning. Some of the built-in features (wal_compression) have been there since 2016, and almost all backup tools do the WAL compression before taking it to the backup... by Jobin Augustine | Jan 24, 2023 | MongoDB, PostgreSQL
This post was originally published in 2019 and was updated in 2023.I presented at Percona University São Paulo about the new features in PostgreSQL that allow the deployment of simple shards. I’ve tried to summarize the main points in this post, as well... by Jobin Augustine | Jan 4, 2023 | Insight for DBAs, PostgreSQL
Last time I blogged about the New WAL Archive Module/Library feature available in PostgreSQL 15, which is quite transformative in how WALs are archived today in PostgreSQL. PostgreSQL 15 has many more improvements related to WAL archiving, which is worth discussing.... by Jobin Augustine | Dec 2, 2022 | Insight for DBAs, PostgreSQL
PostgreSQL traditionally uses shell commands to achieve continuous WAL archiving, which is essential for backups and stable standby replication. In the past, we blogged about the inefficiency in that design and how some of the backup tools like PgBackRest solve... by Jobin Augustine | Nov 9, 2022 | Insight for DBAs, PostgreSQL
In the first part of the article, we discussed how to achieve minimum security by adding a username and password to Patroni REST API and switching from HTTP traffic to HTTPS. In this article, we will be covering how to secure REST APIs using certificate... by Jobin Augustine | Nov 1, 2022 | Insight for DBAs, PostgreSQL
In recent years, Patroni emerged as the number one HA framework for PostgreSQL, currently with 5K stars in its git repository. We blogged about how some of the extraordinary capabilities of Patroni to solve problems like Logical Replication Slot Failover.One... by Jobin Augustine | Oct 11, 2022 | Insight for DBAs, PostgreSQL
Recently I blogged about a significant improvement in PostgreSQL 15: PostgreSQL 15: Stats Collector Gone? What’s New? While there is great cheer for this upcoming improvement, we could see a few comments about “inefficiencies” in previous... by Jobin Augustine | Aug 26, 2022 | Insight for DBAs, PostgreSQL
Anyone trying the upcoming PostgreSQL 15 might observe that one of the background processes is missing. postgres 1710 1 0 04:03 ? 00:00:00 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/ postgres 1711 1710 0 04:03 ? 00:00:00 postgres: logger postgres 1712 1710... by Jobin Augustine | Feb 25, 2022 | Insight for DBAs, PostgreSQL
I recently blogged about how Patroni Addresses the Problem of the Logical Replication Slot Failover in a PostgreSQL Cluster. In fact, nothing else was hurting a logical replication as much as this problem. Even while I am writing this post, I could see customers/users... by Jobin Augustine | Jan 25, 2022 | Insight for DBAs, PostgreSQL, Security
Recently, a few PostgreSQL users reported that they got connection failures after switching to PostgreSQL 14.“Why do I get the error FATAL: password authentication failed for a user in the new server?” has become one of the most intriguing... by Jobin Augustine | Jan 13, 2022 | Insight for DBAs, PostgreSQL
Failover of the logical replication slot has always been the pain point while using the logical replication in PostgreSQL. This lack of feature undermined the use of logical replication and acted as one of the biggest deterrents. The stake and impact were so high that... by Jobin Augustine | Oct 12, 2021 | Insight for DBAs, Insight for Developers, PostgreSQL
Often users come to us with incidents of database crashes due to OOM Killer. The Out Of Memory killer terminates PostgreSQL processes and remains the top reason for most of the PostgreSQL database crashes reported to us. There could be multiple reasons why a host... by Jobin Augustine | Aug 10, 2021 | Insight for DBAs, Insight for Developers, PostgreSQL
Many times, we see PostgreSQL users getting confused about the query/statement duration reported in PostgreSQL logs. Other PostgreSQL tools like pgBadger present the same data based on the log file, which further increases the confusion. Knowing the full impact of...