Tue, 30 Apr 2024 14:46:30 +0000 en-US hourly 1 https://wordpress.org/?v=6.5.2 https://www.percona.com/blog/wp-content/uploads/2023/02/cropped-percona-favicon-32x32.png 32 32 76301791 https://www.percona.com/blog/ldap-authentication-in-pgbouncer-through-pam/ https://www.percona.com/blog/ldap-authentication-in-pgbouncer-through-pam/#respond Tue, 30 Apr 2024 14:46:30 +0000 https://www.percona.com/blog/?p=96054 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 […]]]> https://www.percona.com/blog/ldap-authentication-in-pgbouncer-through-pam/feed/ 0 96054 https://www.percona.com/blog/postgresql-indexes-can-hurt-you-negative-effects-and-the-costs-involved/ https://www.percona.com/blog/postgresql-indexes-can-hurt-you-negative-effects-and-the-costs-involved/#respond Mon, 01 Apr 2024 11:15:43 +0000 https://www.percona.com/blog/?p=87199 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 speeds […]]]> https://www.percona.com/blog/postgresql-indexes-can-hurt-you-negative-effects-and-the-costs-involved/feed/ 0 87199 https://www.percona.com/blog/importance-of-postgresql-vacuum-tuning-and-custom-scheduled-vacuum-job/ https://www.percona.com/blog/importance-of-postgresql-vacuum-tuning-and-custom-scheduled-vacuum-job/#respond Mon, 01 Apr 2024 08:00:16 +0000 https://www.percona.com/blog/?p=81237 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 doubt […]]]> https://www.percona.com/blog/importance-of-postgresql-vacuum-tuning-and-custom-scheduled-vacuum-job/feed/ 0 81237 https://www.percona.com/blog/human-factors-behind-incidents-why-settings-like-idle_session_timeout-can-be-a-bad-idea/ https://www.percona.com/blog/human-factors-behind-incidents-why-settings-like-idle_session_timeout-can-be-a-bad-idea/#comments Fri, 29 Dec 2023 13:42:39 +0000 https://www.percona.com/blog/?p=92930 PostgreSQL 14 introduced the parameter [crayon-664c7152ac029867479896-i/], 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 [crayon-664c7152ac02e405311090-i/]  from a technical perspective; even without this parameter, […]]]> https://www.percona.com/blog/human-factors-behind-incidents-why-settings-like-idle_session_timeout-can-be-a-bad-idea/feed/ 1 92930 https://www.percona.com/blog/postgresql-parameters-scope-and-priority-users-should-know/ https://www.percona.com/blog/postgresql-parameters-scope-and-priority-users-should-know/#respond Wed, 30 Aug 2023 13:45:26 +0000 https://www.percona.com/blog/?p=89813 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 […]]]> https://www.percona.com/blog/postgresql-parameters-scope-and-priority-users-should-know/feed/ 0 89813 https://www.percona.com/blog/how-to-measure-the-network-impact-on-postgresql-performance/ https://www.percona.com/blog/how-to-measure-the-network-impact-on-postgresql-performance/#respond Thu, 20 Jul 2023 13:20:11 +0000 https://www.percona.com/blog/?p=89385 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 […]]]> https://www.percona.com/blog/how-to-measure-the-network-impact-on-postgresql-performance/feed/ 0 89385 https://www.percona.com/blog/postgresql-backup-strategy-enterprise-grade-environment/ https://www.percona.com/blog/postgresql-backup-strategy-enterprise-grade-environment/#comments Sun, 02 Jul 2023 17:13:16 +0000 https://www.percona.com/blog/?p=52811 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 continuous archiving. The size of the database and your database environment—if it is on […]]]> https://www.percona.com/blog/postgresql-backup-strategy-enterprise-grade-environment/feed/ 6 52811 https://www.percona.com/blog/wal-compression-in-postgresql-and-recent-improvements-in-version-15/ https://www.percona.com/blog/wal-compression-in-postgresql-and-recent-improvements-in-version-15/#comments Tue, 24 Jan 2023 14:03:01 +0000 https://www.percona.com/blog/?p=84777 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 […]]]> https://www.percona.com/blog/wal-compression-in-postgresql-and-recent-improvements-in-version-15/feed/ 2 84777 https://www.percona.com/blog/an-overview-of-sharding-in-postgresql-and-how-it-relates-to-mongodbs/ https://www.percona.com/blog/an-overview-of-sharding-in-postgresql-and-how-it-relates-to-mongodbs/#comments Tue, 24 Jan 2023 09:44:38 +0000 https://www.percona.com/blog/?p=57306 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 haven’t […]]]> https://www.percona.com/blog/an-overview-of-sharding-in-postgresql-and-how-it-relates-to-mongodbs/feed/ 5 57306 https://www.percona.com/blog/speed-up-of-the-wal-archiving-in-postgresql-15/ https://www.percona.com/blog/speed-up-of-the-wal-archiving-in-postgresql-15/#respond Wed, 04 Jan 2023 13:42:34 +0000 https://www.percona.com/blog/?p=84481 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 […]]]> https://www.percona.com/blog/speed-up-of-the-wal-archiving-in-postgresql-15/feed/ 0 84481 https://www.percona.com/blog/new-wal-archive-module-library-in-postgresql-15/ https://www.percona.com/blog/new-wal-archive-module-library-in-postgresql-15/#respond Fri, 02 Dec 2022 12:29:54 +0000 https://www.percona.com/blog/?p=83826 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 […]]]> https://www.percona.com/blog/new-wal-archive-module-library-in-postgresql-15/feed/ 0 83826 https://www.percona.com/blog/securing-patroni-rest-api-end-points-part-2-using-ssl-certificates/ https://www.percona.com/blog/securing-patroni-rest-api-end-points-part-2-using-ssl-certificates/#comments Wed, 09 Nov 2022 14:15:19 +0000 https://www.percona.com/blog/?p=83504 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 to […]]]> https://www.percona.com/blog/securing-patroni-rest-api-end-points-part-2-using-ssl-certificates/feed/ 2 83504 https://www.percona.com/blog/securing-patroni-rest-api-end-points-part-1/ https://www.percona.com/blog/securing-patroni-rest-api-end-points-part-1/#respond Tue, 01 Nov 2022 11:54:37 +0000 https://www.percona.com/blog/?p=83319 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 be […]]]> https://www.percona.com/blog/securing-patroni-rest-api-end-points-part-1/feed/ 0 83319 https://www.percona.com/blog/dont-forget-to-tune-stats-collector-for-postgresql-14-and-older/ https://www.percona.com/blog/dont-forget-to-tune-stats-collector-for-postgresql-14-and-older/#respond Tue, 11 Oct 2022 13:16:41 +0000 https://www.percona.com/blog/?p=82738 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 official […]]]> https://www.percona.com/blog/dont-forget-to-tune-stats-collector-for-postgresql-14-and-older/feed/ 0 82738 https://www.percona.com/blog/postgresql-15-stats-collector-gone-whats-new/ https://www.percona.com/blog/postgresql-15-stats-collector-gone-whats-new/#comments Fri, 26 Aug 2022 12:09:21 +0000 https://www.percona.com/blog/?p=82240 Anyone trying the upcoming PostgreSQL 15 might observe that one of the background processes is missing. [crayon-664c7152b1780145566883/] if we compare this with PostgreSQL 14: [crayon-664c7152b1784515355474/] 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 might be […]]]> https://www.percona.com/blog/postgresql-15-stats-collector-gone-whats-new/feed/ 1 82240 https://www.percona.com/blog/logical-replication-decoding-improvements-in-postgresql-13-and-14/ https://www.percona.com/blog/logical-replication-decoding-improvements-in-postgresql-13-and-14/#respond Fri, 25 Feb 2022 14:15:20 +0000 https://www.percona.com/blog/?p=80169 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 […]]]> https://www.percona.com/blog/logical-replication-decoding-improvements-in-postgresql-13-and-14/feed/ 0 80169 https://www.percona.com/blog/postgresql-14-and-recent-scram-authentication-changes-should-i-migrate-to-scram/ https://www.percona.com/blog/postgresql-14-and-recent-scram-authentication-changes-should-i-migrate-to-scram/#respond Tue, 25 Jan 2022 14:02:56 +0000 https://www.percona.com/blog/?p=79829 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 questions.At least in one case, it was a bit of a surprise that the application message […]]]> https://www.percona.com/blog/postgresql-14-and-recent-scram-authentication-changes-should-i-migrate-to-scram/feed/ 0 79829 https://www.percona.com/blog/how-patroni-addresses-the-problem-of-the-logical-replication-slot-failover-in-a-postgresql-cluster/ https://www.percona.com/blog/how-patroni-addresses-the-problem-of-the-logical-replication-slot-failover-in-a-postgresql-cluster/#comments Thu, 13 Jan 2022 13:31:17 +0000 https://www.percona.com/blog/?p=79769 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 many organizations had to discard their plans around logical replication, […]]]> https://www.percona.com/blog/how-patroni-addresses-the-problem-of-the-logical-replication-slot-failover-in-a-postgresql-cluster/feed/ 13 79769 https://www.percona.com/blog/why-linux-hugepages-are-super-important-for-database-servers-a-case-with-postgresql/ https://www.percona.com/blog/why-linux-hugepages-are-super-important-for-database-servers-a-case-with-postgresql/#comments Tue, 12 Oct 2021 14:03:15 +0000 https://www.percona.com/blog/?p=78456 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 machine could run out of memory, and the most common […]]]> https://www.percona.com/blog/why-linux-hugepages-are-super-important-for-database-servers-a-case-with-postgresql/feed/ 2 78456 https://www.percona.com/blog/impact-of-network-and-cursor-on-query-performance-of-postgresql/ https://www.percona.com/blog/impact-of-network-and-cursor-on-query-performance-of-postgresql/#respond Tue, 10 Aug 2021 12:55:10 +0000 https://www.percona.com/blog/?p=77543 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 network-related overhead and cursors is important not only to alleviate the confusion but also to […]]]> https://www.percona.com/blog/impact-of-network-and-cursor-on-query-performance-of-postgresql/feed/ 0 77543