by Jobin Augustine | Jun 24, 2021 | Insight for DBAs, PostgreSQL
pg_repack is one of the oldest, widely used, extension projects for PostgreSQL. It is so much popular that even DBaaS service providers couldn’t avoid it. It is a “power tool” in the hands of a DBA to deal with bloated/fragmented tables. I... by Jobin Augustine | Jun 11, 2021 | Insight for DBAs, Percona Live, Percona Software, PostgreSQL
A couple of weeks ago, Jobin and I did a short presentation during Percona Live Online bearing a similar title as the one for this post: “PostgreSQL HA With Patroni: Looking at Failure Scenarios and How the Cluster Recovers From Them”. We deployed a 3-node... by Jobin Augustine | Feb 26, 2021 | Benchmarks, Insight for DBAs, Insight for Developers, PostgreSQL
Yes, this post is about connection queueing, not just pooling. Because “connection pooling” – pre-created connections as a pool – is a much-celebrated feature. Almost every discussion on connection pool/pgBouncer starts with the overhead of... by Jobin Augustine | Jan 22, 2021 | Benchmarks, Cloud, Insight for DBAs, PostgreSQL
The expected growth of ARM processors in data centers has been a hot topic for discussion for quite some time, and we were curious to see how it performs with PostgreSQL. The general availability of ARM-based servers for testing and evaluation was a major obstacle.... by Jobin Augustine | Dec 1, 2020 | Insight for DBAs, Percona Software, PostgreSQL
In one of my previous blog posts, Why PostgreSQL WAL Archival is Slow, I tried to explain three of the major design limitations of PostgreSQL’s WAL archiver which is not so great for a database with high WAL generation. In this post, I want to discuss how... by Jobin Augustine | Sep 10, 2020 | Insight for DBAs, PostgreSQL
Indexes are one of the core features of all the database management systems (DBMS). Indexes have a very long history in PostgreSQL, which has quite a rich set of index features. PostgreSQL has B-Tree, Hash, GIN, GIST, and BRIN indexes. And because the... by Jobin Augustine | Sep 9, 2020 | Cloud, Insight for DBAs, PostgreSQL
It is very common to see many customer cases where a sudden increase in disk space usage is caused by a lot of WAL segments filling up the WAL directory (pg_wal). Panicking customers generally ask “Why isn’t PostgreSQL deleting them?”. The most... by Jobin Augustine | Aug 21, 2020 | Insight for DBAs, PostgreSQL
I find myself discussing and explaining sychronous_commit with many PostgreSQL users, especially with novice users. So, I thought of noting down all the key points as a blog post which will be useful for more users. Recently I got an opportunity to talk about some... by Jobin Augustine | May 21, 2020 | Insight for DBAs, Insight for Developers, PostgreSQL
Missing Piece: Failover of the Logical Replication Slot Logical decoding capability has existed in PostgreSQL for the last several versions, and a lot more functionalities are added over time. However, one of the very crucial functionalities is still missing in... by Jobin Augustine | Apr 16, 2020 | Insight for DBAs, Insight for Developers, PostgreSQL
This is one of the most common questions asked by developers who write SQL queries against the PostgreSQL database. There are multiple ways in which a sub select or lookup can be framed in a SQL statement. PostgreSQL optimizer is very smart at optimizing queries, and... by Jobin Augustine | Feb 13, 2020 | Insight for DBAs, PostgreSQL
As hardware and software evolve, the bottlenecks in a database system also shift. Many old problems might disappear and new types of problems pop-up. Old Limitations There were days when CPU and Memory was a limitation. More than a decade back, servers with 4 cores... by Jobin Augustine | Jan 17, 2020 | Insight for DBAs, PostgreSQL
Python 2 has officially completed its life as of Jan 1st, 2020 and the Python 2.x branch will not be maintained anymore. You might be seeing repeated notices of deprecation in log files and terminals like: Shell DEPRECATION: Python 2.7 will reach the end of its life... by Jobin Augustine | Dec 3, 2019 | PostgreSQL
There is a less-talked-about improvement in PostgreSQL 12 which can greatly reduce the benign log entries. This patch is probably one of the smallest in PostgreSQL 12.The commit message says: Shell Don't log incomplete startup packet if it's empty This will stop... by Jobin Augustine | Nov 8, 2019 | Insight for DBAs, PostgreSQL
We discussed one of the traditional ways to configure HAProxy with PostgreSQL in our previous blog about HAProxy using Xinetd. There we briefly mentioned the limitation of the HAProxy’s built-in pgsql-check health check option. It lacks features to detect and... by Jobin Augustine | Oct 31, 2019 | Insight for DBAs, PostgreSQL
Recently we published a blog about a very simple application failover using libpq features which could be the simplest of all automatic application connection routing. In this blog post, we are discussing how a proxy server using HAProxy can be used for... by Jobin Augustine | Aug 15, 2019 | Benchmarks, PostgreSQL
by Jobin Augustine | Jul 31, 2019 | PostgreSQL
One of the great features of PostgreSQL is its extendability. My colleague and senior PostgreSQL developer Ibar has blogged about developing an extension with much broader capabilities including callback functionality. But in this blog post, I am trying to address a... by Jobin Augustine | Jul 16, 2019 | PostgreSQL
BRIN Index was introduced in PostgreSQL 9.5, but many users postponed the usage of it in their design and development just because it was “new”. But now we understand that it has stood the test-of-time! It is time to reconsider BRIN if you have not done it... by Jobin Augustine | Jul 10, 2019 | PostgreSQL
WAL retention is a very important topic for PostgreSQL database management. But very often we come across DBAs getting into surprise situations such as: 1. Several TBs of WALs piled up in archive destination 2. WALs filling up pg_wal/pg_xlog directory due to failing... by Jobin Augustine | Jun 24, 2019 | PostgreSQL
Introduction Those DBAs who are experts in one database system look for other database systems to have “similar features”. It is a human tendency to look at any new technology and compare it with a world they are familiar with. Most of the time, I keep...