Table of Contents Table of Contents Why We Upgraded VACUUM Improvements Bottom-Up Index Deletion compactify_tuples Performance Radix Tree TID Store (PostgreSQL 17+) Asynchronous I/O for Sequential Scans WAL Insert Lock Optimization Logical Replication Improvements Monitoring Improvements What We Considered Before Upgrading Choosing the Target Version: PostgreSQL 17 or 18 The Upgrade Playbook Design Reducing Upgrade Duration Preflight: The Most Important Phase Why Upgrade Process Was Dangerous and Risky Still Logical Replication: Two Scenarios The Constraint That Existed Twice The Root Cause The Scenario That Triggers It The Fix Community Bug Report Summary for This Problem The Role That Was Gone But Not Forgotten Background: What is pg_init_privs The Sequence That Produces Orphan OIDs How This Breaks pg_upgrade The Fix: Clean Up Orphan OIDs Before Upgrading Post-Upgrade Metrics and Validation Log Prerequisites Error and Fatal Rate Slow Query Volume Cancelled Queries Checkpoint Duration Autovacuum Duration Conclusion: A Checklist for DBAs Why We Upgraded PostgreSQL major version upgrades are rarely simple. The larger the cluster — more databases, more extensions, more replication topology — the more moving parts you have to coordinate simultaneously. This post documents our journey upgrading a production PostgreSQL infrastructure from version 13 to version 18. It covers the reasons that drove the decision, the automated playbook we designed to handle it, the edge cases we discovered along the way, and most importantly, two non-obvious bugs we encountered and had to solve before the upgrade could succeed. ...

Introduction PostgreSQL gives you powerful tools to build highly available setups with synchronous replication and logical slot failover. But the confusion comes from thatsynchronous_standby_names and synchronized_standby_slots look related, sound related, and live in the same configuration file. Yet they govern completely different things, and assuming otherwise can silently break your setup in ways that are hard to diagnose under pressure. synchronous_standby_names is about transaction durability. It controls which standbys must confirm receipt of WAL before a commit is acknowledged back to the client. synchronized_standby_slots, introduced in PostgreSQL 17, is about something else entirely. It controls which physical replication slots must confirm WAL receipt before the primary advances its logical walsenders. In other words, one governs when your application gets a commit acknowledgment, and the other governs when your logical replication slots are considered safe to advance. They operate on separate code paths, follow separate rules, and one does not imply the other. ...

Introduction In the world of PostgreSQL performance tuning, we often obsess over query execution times. We tweak indexes, rewrite joins, and adjust work_mem. However, a database is only as fast as its ability to maintain itself. This is where maintenance_io_concurrency comes into play. Introduced to allow PostgreSQL to take advantage of modern SSDs and RAID arrays during heavy lifting, this Grand Unified Configuration (GUC) setting controls the number of concurrent I/O operations that PostgreSQL can have “in flight” during maintenance tasks. ...

Introduction Hi all, in this blogpost my goal is to reveal some obvious and known facts about using btree indexes in PostgreSQL. In database world we almost all rely on btree indexes to improve range scan read performance. In other words, to improve query performance in OLTP databases. But using indexes is not free always and we as DBAs or software developers have to pay for it. I’ll try to discuss how can we achieve a sustainable indexing in PostgreSQL. ...

Introduction Have you ever thought about PostgreSQL connections — specifically, idle ones? If so, this blog post will offer a new perspective on how you manage them. I’ll be focusing on idle connections in the context of network failures, with the goal of helping you build a more robust and reliable PostgreSQL environment. In the PostgreSQL world, we often concentrate on queries, indexes, and configuration tuning — but effective connection management is just as crucial. In particular, handling “zombie” connections (which we’ll define shortly) can lead to more efficient resource usage and a more stable database platform. ...

Introduction Previously I discussed idle connections which are in zombie state. In other words, we discussed how to maintain connections which are idle and dead in the network layer. Visit the following blogpost to find out the idea and the history behind this post. Zombie Connections in PostgreSQL and TCP Keepalives I wanted to write a follow-up blogpost to cover remaining timeout GUCs on PostgreSQL to manage client connections more efficiently. In other words, I wanted to highlight and provide use-case examples about a couple of timeout GUCs on PostgreSQL which can help us to improve database reliability. Keep in mid that, the GUCs I’m going to discuss will be used many different ways but my main purpose is to highlight their importance and provide follow-up examples. ...

Introduction Hello everyone! In this blog post, I’ll discuss the relationship between the work_mem (and maintenance_work_mem) and max_connections parameters in PostgreSQL. These two settings play a key role in determining memory usage and overall performance, making them central to any tuning efforts aimed at future performance goals. While PostgreSQL includes other memory-related parameters, this post will focus specifically on how work_mem and max_connections interact and impact system performance. Table of Contents Definition of Parameters The Relationship Between Two Parameters Configuring Max Connections and Work Mem Parameters in PostgreSQL Benchmarks and Results Conclusion Definition of Parameters The definitions are stated at official PostgreSQL documentation page. Basically work_mem sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. If this value is specified without units, it is taken as kilobytes. ...

Introduction We explore the fundamental role memory plays within the architecture of PostgreSQL databases, shedding light on the intricacies of memory-related parameters and their profound impact on system performance and resource utilization. Our journey begins with an exploration of the memory architecture of PostgreSQL, uncovering the underlying mechanisms that govern memory allocation and usage within the database engine. From there, we’ll study on the crucial memory-related parameters that administrators must navigate to fine-tune their PostgreSQL deployments for optimal performance. We’ll dissect each parameter, discussing its significance and the potential ramifications of setting values too low or too high. We’ll guide you through the intricate process of configuring these parameters, providing step-by-step instructions to ensure your PostgreSQL instance is finely tuned to meet the demands of your workload. And finally, we’ll bring theory into practice, embarking on a journey of benchmarking and analysis to uncover the real-world impact of these memory parameters on performance and resource utilization. ...

Introduction This article aims to provide practical insights into optimizing PostgreSQL database performance by adjusting the commit_delay and wal_writer_delay configuration parameters. The focus is on improving system responsiveness, reducing write latency, and enhancing the efficiency of Write-Ahead Logging (WAL) processes. Table of Contents What are commit_delay and wal_writer_delay in PostgreSQL? What is the impact on performance? How can we change these parameters? How can we test via pgbench? Conclusion References What are commit_delay and wal_writer_delay in PostgreSQL? commit_delay: This parameter controls the delay imposed on transaction commit. It represents the time, in microseconds, that a transaction commit will wait before it is acknowledged. Increasing this delay can have implications on the overall throughput and latency of the database. ...