Rahila Syed

Journey of developing a performance optimization feature in PostgreSQL

local CEST (Livestream 4)

In this talk, I will share the journey of identifying and optimizing a performance bottleneck in PostgreSQL. The session will walk through a systematic approach to diagnosing performance issues — distinguishing whether the bottleneck lies in the CPU, I/O, or network — and how iterative profiling and analysis can guide effective optimizations. Using perf and other diagnostic tools, we’ll examine how bottlenecks can shift during optimization, sometimes masking real gains. I will demonstrate how to effectively measure improvement in performance through careful tuning of the database, along with use of pgbench and custom benchmarking scripts tailored to the optimization under test.

As a practical example, we will explore an optimization in PostgreSQL’s physical replication that enables the WAL sender to transmit WAL records to standbys before they are flushed to disk on the primary. This enhancement aims to reduce replication latency by leveraging WAL buffers to send data more proactively, minimizing disk reads and improving network utilization. For large transactions, this approach allows most WAL data to be sent in parallel with ongoing writes on master, aligning the flush operations on primary and standby more closely and significantly reducing replication lag.