Featured
Percona Products
Percona Services
Use Cases
Jobin AugustineThis blog was originally written in September 2018 and was updated in March 2025. In this post, we cover the methods used to achieve an enterprise-grade PostgreSQL backup strategy. We’ll explore options like pg_basebackup and WAL archiving to enable PostgreSQL Point-in-Time Recovery (PITR), discuss PostgreSQL backup best practices, and touch upon enterprise database backup tools. […]
This post was originally published in March 2019 and was updated in February 2025. Planning a PostgreSQL upgrade but not sold on pg_upgrade? You’re not alone. If pg_upgrade feels too risky or limiting, pg_dump and pg_restore give you something better: full control. You can move across major versions, make schema changes, and leave storage format […]
PostgreSQL is one of the most powerful database systems in the world. I have always been passionate about its great power, especially its modern SQL language features. However, that doesn’t mean everything is great. There are areas where it hurts. Novice users unaware of the problem might get into trouble, and I see such things […]
We have been promoting and using Patroni as the best high availability framework for PostgreSQL, and Etcd was the preferred/recommended DCS for the Patroni cluster. Both Patroni and Etcd have been part of PostgreSQL distribution from Percona for years now. But one area where we were stuck was the Etcd version, and we continued to […]
Multi-tenancy/co-hosting is always challenging. Running multiple PG instances could help to reduce the internal contention points (scalability issues) within PostgreSQL. However, the load caused by one of the tenants could affect other tenets, which is generally referred to as the “Noisy Neighbor” effect. Luckily, Linux allows users to control the resources consumed by each program […]
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 doesn’t have built-in authentication features like LDAP, which is essential for many enterprises. Luckily, pgBouncer has […]
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 many articles and talks on “tuning” discussing how creating new indexes […]
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. I think there is no PostgreSQL version that comes out without any autovacuum improvement, and no […]
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 installations. There is nothing wrong with idle_session_timeout from a technical perspective; even without this parameter, […]
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 wondering why certain changes are not coming into effect, so it […]
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 hops involved and the additional overhead it creates on the overall performance. But it could cause […]
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 repository. But it is time to take another look at the built-in wal_compression because […]
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 as provide an introductory overview of sharding itself. Please note I […]
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. In this blog, I would like to highlight some of them which solve great […]
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 that problem. It is a well-known problem in the PostgreSQL community, and many discussions happened […]
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 authentication. Certificate authentication is the only option if we want […]
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 outstanding feature of Patroni is its powerful set of REST APIs. These APIs can […]
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 versions. That brought me to the realization that even though the feature to tune stats collector is part of the […]
Anyone trying the upcoming PostgreSQL 15 might observe that one of the background processes is missing.
|
1 2 3 4 5 6 7 |
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 0 04:03 ? 00:00:00 postgres: checkpointer postgres 1713 1710 0 04:03 ? 00:00:00 postgres: background writer postgres 1715 1710 0 04:03 ? 00:00:00 postgres: walwriter postgres 1716 1710 0 04:03 ? 00:00:00 postgres: autovacuum launcher postgres 1717 1710 0 04:03 ? 00:00:00 postgres: logical replication launcher |
if we compare this with PostgreSQL 14:
|
1 2 3 4 5 6 7 8 |
postgres 1751 1 0 04:04 ? 00:00:00 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/ postgres 1752 1751 0 04:04 ? 00:00:00 postgres: logger postgres 1754 1751 0 04:04 ? 00:00:00 postgres: checkpointer postgres 1755 1751 0 04:04 ? 00:00:00 postgres: background writer postgres 1756 1751 0 04:04 ? 00:00:00 postgres: walwriter postgres 1757 1751 0 04:04 ? 00:00:00 postgres: autovacuum launcher postgres 1758 1751 0 04:04 ? 00:00:00 postgres: stats collector postgres 1759 1751 0 04:04 ? 00:00:00 postgres: logical replication launcher |
Yes, the “stats collector” is missing, and it is gone for good. One of the major bottlenecks and headaches is gone forever. What does the stats collector do? Novice users […]
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 who don’t have Patroni struggling to address this. Thanks to the […]