14 Aug 2025

Testing PostgreSQL on Debian/Hurd: A Windows + QEMU Adventure

Curiosity often leads to the most interesting technical adventures. This time, I decided to explore something off the beaten path: running Debian GNU/Hurd inside a virtual machine on my Windows 11 host and compiling PostgreSQL from source.

This post is part 1 of a multi-part series documenting the process, challenges, and discoveries along the way. Future parts will dive deeper into advanced topics, automation, and ongoing compatibility work—so if you're interested in PostgreSQL, alternative operating systems, or open source testing, stay tuned!

What is Debian?
Debian is one of the oldest and most respected Linux distributions, known for its stability, vast software repositories, and commitment to free software principles. While most people associate Debian with the Linux kernel, it’s actually a complete operating system that can run on different kernels.

What is GNU/Hurd?
GNU/Hurd is an alternative kernel developed by the GNU Project. Unlike Linux, GNU/Hurd is built on a microkernel architecture (specifically GNU Mach), aiming for greater modularity and flexibility. While GNU/Hurd is still experimental and not as mature or widely used as Linux, it represents a fascinating approach to operating system design.

Debian GNU/Hurd combines the familiar Debian userland (tools, package management, etc.) with the GNU/Hurd kernel, offering a unique environment for open source enthusiasts and OS tinkerers.

My goal for this experiment was to see how far I could get with a modern database stack—specifically, compiling and running PostgreSQL—on this unusual platform.



Setting Up the VM

Instead of the CD image, I used the pre-built disk image available here. After downloading and extracting the .img file, I launched the VM with QEMU using the following command:

qemu-system-x86_64.exe -machine type=pc,accel=whpx,kernel-irqchip=off -boot d -m 4096 -usb -display default,show-cursor=on -drive file=".\debian-hurd-i386-20250807.img",cache=writeback

Explanation of the command:

  • qemu-system-x86_64.exe: Runs QEMU for 64-bit x86 systems (works for 32-bit guests too).
  • -machine type=pc,accel=whpx,kernel-irqchip=off: Specifies a PC-type machine, enables Windows Hypervisor Platform acceleration (WHPX), and disables kernel IRQ chip emulation for compatibility.
  • -boot d: Boots from the first hard disk.
  • -m 4096: Allocates 4GB of RAM to the VM.
  • -usb: Enables USB support.
  • -display default,show-cursor=on: Uses the default display and ensures the mouse cursor is visible.
  • -drive file=".\debian-hurd-i386-20250807.img",cache=writeback: Uses the extracted Hurd disk image as the hard drive and enables writeback caching for better disk performance.

This boots directly into the installed Debian/Hurd system with improved performance and usability on a Windows 11 host.

Preparing to Build PostgreSQL

Debian/hurd is minimal out of the box, so the first step was to install all the build tools and libraries required for compiling PostgreSQL:

sudo apt-get update
sudo apt-get install build-essential git libxml2-dev libxslt-dev autotools-dev automake libreadline-dev zlib1g-dev bison flex libssl-dev libpq-dev ccache

This command installs the compiler, linker, version control tools, XML and SSL libraries, autotools, and all other dependencies PostgreSQL may need for a successful build and test cycle.

Downloading and Compiling PostgreSQL

Instead of downloading a release tarball, I cloned the official PostgreSQL git repository and compiled the master branch:

git clone https://github.com/postgres/postgres.git
cd postgres
./configure --prefix=~/proj/localpg
make
make install

This approach ensures you're building the latest development version of PostgreSQL directly from source, and installs it locally to your user's ~/proj/localpg directory.

Setting Up the Database Cluster

PostgreSQL needs a data directory (cluster) to store its databases. Since the installation was local to my user, I simply initialized the cluster and started the server using the full path to the binaries (since they're not in my PATH):

~/proj/localpg/bin/initdb -D ~/proj/localpg/pgdata
~/proj/localpg/bin/pg_ctl -D ~/proj/localpg/pgdata -l logfile start

Connecting and Creating a Table

With the server running, I connected to the database and created a sample table:

~/proj/localpg/bin/psql -d postgres

Inside psql:

CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test_table (name) VALUES ('Hello from Debian/Hurd!');
SELECT * FROM test_table;

Example output:

CREATE TABLE
INSERT 0 1
 id |         name         
----+----------------------
  1 | Hello from Debian/Hurd!
(1 row)

Running the Test Suite

To ensure the build was solid, I went back to the source directory and ran:

cd ~/postgres
make check

This runs PostgreSQL's regression tests, verifying that the core features work as expected—even on Hurd. This ran mostly fine (except for a few tests that failed - more to be researched on that failure).

Quick QEMU Tip

When working with QEMU, remember that Ctrl-Alt-G is your friend—it releases the mouse and keyboard from the VM window, making it much easier to switch back to your host system.

Adding a Separate Volume for More Disk Space

The base Debian/Hurd image is quite small and can easily run out of space, especially when compiling large projects or running make check. I frequently hit disk full errors during testing.

Solution:

  1. Shutdown the VM.

  2. Resize the disk image:

    qemu-img resize debian-hurd-i386-20250807.img +10G
    

    This adds 10GB to the existing disk image.

  3. Restart the VM.

  4. Create a new partition:

    • Use fdisk /dev/hd0 (or the appropriate device) to create a new partition in the extra space.
  5. Format the new partition:

    mkfs.ext4 /dev/hd0s3
    

    (Note: On my setup, the original root partition was /dev/hd0s2, so the new partition created for extra space was /dev/hd0s3. Adjust the device name as needed for your configuration.)

    Although the root volume is of ext2 type (!!!), Debian/Hurd works fine with ext4—so feel free to use ext4 for the new partition.

  6. Mount the new volume:

    mkdir -p /mnt/newvol
    mount /dev/hd0s3 /mnt/newvol
    
  7. Grant non-root user access:

    • As root, change ownership:
      chown robins:robins /mnt/newvol
      
    • Now your non-root user (e.g., robins) can use /mnt/newvol for compiling PostgreSQL and running make check without running out of disk space.

Why use a non-root user for PostgreSQL? PostgreSQL is designed to run as a non-root user for security reasons. Running the database server or its tests as root can expose your system to unnecessary risks and may even cause certain operations to fail. Always use a dedicated non-root user for installation, testing, and day-to-day database operations.

This approach made it possible to complete the build and test cycle without disk space issues.

Final Thoughts

Running Debian/Hurd in a VM on Windows 11 was surprisingly smooth, though some packages and features are less mature than on Linux. Compiling PostgreSQL from scratch was a great way to explore the system's capabilities and compatibility. If you're looking for a fun, geeky weekend project, give Debian/Hurd a try!

Next Steps & What's Still Pending

This is only part 1 of a multi-part series. In future installments, I'll cover:

  • Setting up the PostgreSQL buildfarm for automated testing on Debian/Hurd
  • Deeper investigation into SMP/multi-core support (currently not working)
  • More QEMU optimization and compatibility testing
  • Additional performance tuning and disk management strategies
  • Troubleshooting Perl module installation issues (e.g., LWP::Protocol::https, LWP::Simple, Net::SSLeay), which currently fail to install—more research is needed to understand and resolve these problems.
  • Investigating why make check did not complete successfully (failed on a few tests)—this requires further research.

Some features, like multi-core support, full buildfarm integration, reliable Perl module installation, and passing all PostgreSQL regression tests, are not yet working or fully tested. These will be explored in detail in future posts. Stay tuned!

6 Aug 2025

Pi-hole Part 2: Going Fully Independent with Unbound

After my positive first impressions with Pi-hole, I decided to take the next logical step: eliminating my dependence on external DNS resolvers entirely. While Quad9 served me well, there's something unsettling about routing all my DNS queries through a third party, no matter how trustworthy they appear.

The solution? Unbound - a validating, recursive, caching DNS resolver that can operate completely independently, resolving domain names directly from authoritative sources without relying on upstream DNS providers.

Why Make the Switch?

My motivation goes beyond just privacy paranoia (though that's certainly part of it):

Privacy First: Every DNS query reveals your browsing patterns. Even with Quad9's privacy promises, I prefer keeping that data entirely within my network.

Security Enhancement: Unbound performs DNSSEC validation by default, ensuring the authenticity of DNS responses and protecting against DNS spoofing attacks.

Reduced Latency: While counterintuitive, eliminating the round-trip to external resolvers can actually improve response times for frequently accessed domains through better local caching.

True Independence: No more wondering about logging policies, data retention, or potential government requests to DNS providers.

The Downsides to Consider

Before diving in, it's worth acknowledging the trade-offs:

Increased Complexity: You're now responsible for maintaining and troubleshooting your own DNS infrastructure. When things break, you can't just blame your ISP's DNS servers.

Initial Query Delays: Cold cache scenarios will be slower as Unbound has to walk the entire DNS hierarchy from root servers. First visits to new domains will take noticeably longer.

Resource Usage: While minimal, you're now running an additional service that consumes memory and CPU cycles on your Pi.

Potential Connectivity Issues: If your Pi goes down, your entire network loses DNS resolution. External resolvers provide redundancy that you're giving up.

CDN Sub-optimization: Content delivery networks may not route you to the geographically closest servers, potentially affecting streaming and download performance. Many large DNS providers like Cloudflare and Google use Anycast networks, where the same IP address is announced from multiple geographic locations, automatically routing you to the nearest server. When you run your own recursive resolver, you lose this geographic optimization and might connect to CDN endpoints that are further away.

False Sense of Security: While your DNS queries are now private, it's important to remember that all your actual web traffic (HTTP/HTTPS requests) still flows through your ISP and is visible in their logs. You've privatized the "phone book lookup" but not the actual "conversation" - your ISP can still see which IP addresses you're connecting to, just not the domain names that resolved to them.

Maintenance Overhead: The root hints file should be updated periodically (though it changes infrequently), and you're responsible for keeping your Unbound configuration current and secure.

The Verdict: Benefits Outweigh the Costs

After weighing these trade-offs, the privacy and security benefits still made a compelling case for proceeding. The complexity is manageable for anyone comfortable with basic Linux administration, and the performance impacts are largely theoretical for typical home usage. Most importantly, the peace of mind from knowing exactly where my DNS queries go and how they're handled proved worth the additional overhead.

The Setup Process

Installing and configuring Unbound alongside Pi-hole turned out to be surprisingly straightforward.

Step 1: Install Unbound

sudo apt update
sudo apt install unbound -y

Step 2: Configure Unbound for Security and Performance

I created a custom configuration at /etc/unbound/unbound.conf.d/pi-hole.conf:

sudo nano /etc/unbound/unbound.conf.d/pi-hole.conf

Here's my security-focused configuration:

server:
    # Basic settings
    port: 5335
    do-ip4: yes
    do-ip6: yes
    do-udp: yes
    do-tcp: yes
    
    # Security settings
    trust-anchor-file: "/var/lib/unbound/root.key"
    auto-trust-anchor-file: "/var/lib/unbound/root.key"
    val-clean-additional: yes
    val-permissive-mode: no
    val-log-level: 1
    
    # Privacy settings
    hide-identity: yes
    hide-version: yes
    harden-glue: yes
    harden-dnssec-stripped: yes
    harden-below-nxdomain: yes
    harden-referral-path: yes
    use-caps-for-id: yes
    
    # Performance settings (security-first approach)
    cache-min-ttl: 300
    cache-max-ttl: 86400
    prefetch: yes
    prefetch-key: yes
    
    # Interface settings
    interface: 127.0.0.1
    access-control: 127.0.0.1/32 allow
    access-control: ::1 allow
    
    # Logging
    verbosity: 1
    log-queries: no
    log-replies: no
    
    # Root hints
    root-hints: "/var/lib/unbound/root.hints"

Step 3: Root Hints Management

Root hints are essential files that tell Unbound where to find the DNS root servers - the starting point for all DNS resolution. When installing Unbound via package manager, root hints are included and should be updated automatically through regular system updates.

Note: The root hints file changes infrequently (typically a few times per year when root servers are added, removed, or have IP changes). Rather than manual updates, it's best to keep your system updated through your package manager, which will handle root hints updates appropriately - roughly every 6 months is more than sufficient for most users.

Step 4: Initialize DNSSEC Root Key (Usually Optional)

Modern Unbound packages typically handle DNSSEC root key initialization automatically. However, if you encounter DNSSEC validation errors or want to ensure the key is properly configured, you can initialize it manually:

sudo unbound-anchor -a "/var/lib/unbound/root.key"
sudo chown unbound:unbound /var/lib/unbound/root.key

Note: If this step fails or the file already exists, it's likely already configured correctly by the package installation.

Step 5: Start and Enable Unbound

sudo systemctl enable unbound
sudo systemctl start unbound

Step 6: Test Unbound

Before integrating with Pi-hole, I verified Unbound was working with both valid and invalid domain lookups:

Testing with an existing domain:

robins@pi4:~ $ dig @127.0.0.1 -p 5335 google.com | egrep -w 'status|^google'
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 58713
google.com.             269     IN      A       142.250.70.206

Testing with a non-existent domain:

dig @127.0.0.1 -p 5335 asdfasdsfasfdfsafasd | egrep -w 'status|flags'
robins@pi4:~ $ dig @127.0.0.1 -p 5335 asdfasdsfasfdfsafasd | egrep -w 'status|flags'
;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 19691
;; flags: qr rd ra ad; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 1
; EDNS: version: 0, flags:; udp: 1232

Both tests confirm Unbound is working correctly. The existing domain test shows successful resolution with NOERROR status, while the non-existent domain test returns NXDOMAIN (Non-eXistent DOMAIN), which is the proper response when a domain doesn't exist. Note the ad flag in the non-existent domain response, indicating that even failed lookups are DNSSEC-validated - Unbound cryptographically verified that this domain truly doesn't exist rather than just accepting an unvalidated negative response.

Step 7: Configure Pi-hole to Use Unbound

In the Pi-hole admin interface:

  1. Navigate to SettingsDNS
  2. Uncheck all upstream DNS servers
  3. Add 127.0.0.1#5335 as a custom DNS server
  4. Enable DNSSEC validation
  5. Save settings

The Results: Worth the Effort

After 24 hours of operation with the new setup, the benefits are clear:

Complete Privacy: All DNS queries now resolve independently without touching external servers (except for the initial root server queries, which reveal no specific domain information).

DNSSEC Validation: Every response is cryptographically verified, providing protection against DNS manipulation.

Improved Cache Efficiency: Unbound's more sophisticated caching algorithm seems to provide better hit rates for our household's browsing patterns.



Performance Impact: Negligible. The Pi 4 handles both Pi-hole and Unbound without breaking a sweat:

robins@pi4:~ $ uptime
 21:22:25 up 2 days,  9:47,  3 users,  load average: 0.00, 0.00, 0.00

Security Considerations

My configuration prioritizes security over raw performance:

  • Conservative TTL settings: Shorter cache times mean more frequent validation
  • Strict DNSSEC validation: No permissive mode that might accept invalid responses
  • Minimal logging: No query logging to preserve privacy even locally
  • Restricted access: Only localhost can query Unbound directly

Final Thoughts

Setting up Unbound with Pi-hole represents the logical conclusion of taking control over your DNS infrastructure. While the privacy and security benefits are the primary motivators, the technical satisfaction of running a completely self-contained DNS resolution system is considerable.

The setup process is more involved than simply pointing Pi-hole at an external resolver, but the configuration is straightforward and well-documented. For anyone concerned about DNS privacy or wanting to reduce external dependencies, this combination provides an excellent solution.

The Pi 4 continues to prove itself as the perfect platform for this type of network infrastructure project - handling both Pi-hole and Unbound with resources to spare. As I mentioned in part 1, even a Pi 2 would likely suffice for most households, making this an accessible upgrade for anyone looking to enhance their home network's privacy and security posture.

5 Aug 2025

Pi-hole First Impressions: Home DNS Caching Done Right

(This is first of a 2 part series, where I explore pi-hole as a DNS caching solution-Here's Part 2)

I've had a Raspberry Pi 4 sitting idle for months, and finally decided to put it to good use by setting up Pi-hole for network-wide DNS caching and ad blocking. After less than a day of operation, I'm genuinely impressed with both the installation process and the results.

The Installation: Surprisingly Smooth

Setting up Pi-hole turned out to be one of the smoothest software installations I've experienced in recent memory. The entire process is handled by a single bash script:


curl -sSL https://install.pi-hole.net | bash


That's it! What impressed me most wasn't just the simplicity, but the thoroughness of the pre-installation checks. Before the script even attempted to install anything, it ran through a comprehensive validation process:

  • Network connectivity tests
  • DNS resolution verification
  • Package manager status checks
  • System requirements validation
  • Port availability confirmation

This rigorous validation gave me real confidence that the installation would succeed. Too often, installation scripts fail halfway through, leaving you with a partially configured mess. Pi-hole's approach of "check everything first, then install" meant that once the green lights were all showing, the installation proceeded flawlessly.

Interestingly, what took the most time during the entire setup wasn't Pi-hole itself, but my initial attempt to use Docker. I'd assumed Docker would be the quick and easy route (as it usually is), but the Pi had some leftover Docker installations that needed cleaning up first - removing old docker, docker.io, and docker-compose packages was a messy affair. Then the Pi seemed to have issues with the Docker installation process itself. In the end, I abandoned the Docker approach and went with the native installation, which ironically turned out to be much faster and cleaner.

One lesser-known tip that made the physical setup even smoother: if you're running a Google mesh network, the routers with both WAN and LAN ports can easily be used as wifi-to-LAN bridges. Rather than connecting the Pi 4 via WiFi, I simply plugged it into the LAN port of one of the mesh routers. This eliminated the need for additional network switches and reduced latency by a few milliseconds by avoiding the WiFi overhead entirely.

Another major convenience: you don't need to update DNS settings on each individual device. A single configuration change at the router level does the job for the entire household. In Google mesh, I simply changed the DNS setting from "Automatic" to "Custom" and set the Pi 4's IP as the primary DNS server. This immediately moved all devices on the network to use Pi-hole without touching any individual device settings. The only exceptions are devices that explicitly force their own DNS (looking at you, certain smart TVs and streaming devices), but those are relatively rare.

The Results: Better Than Expected

After 23 hours of operation protecting our home network, the statistics are eye-opening:



DNS Query Volume: 100,000 requests in 23 hours

  • That's roughly 4,350 DNS queries per hour from our household
  • Averages out to about 72 queries per minute
  • Shows just how chatty modern devices really are

Cache Performance: 70% cache hit rate

  • Pi-hole is successfully caching DNS responses locally
  • Reduces latency for frequently accessed domains
  • Decreases load on upstream DNS servers
  • Currently using Quad9 (9.9.9.9) as the upstream resolver - the only non-US DNS service I still trust in this day and age

Blocking Effectiveness: 8.5% of requests blocked

  • More than 1 in 12 DNS requests were for ad/tracking domains
  • That's 8,500 blocked requests in just 23 hours
  • Represents a significant reduction in unwanted network traffic

System Performance: Negligible Impact

One concern with running Pi-hole on a Pi 4 was whether it would impact system performance. For context, here are the system specs:


robins@pi4:~ $ uname -a
Linux pi4 6.12.34+rpt-rpi-v8 #1 SMP PREEMPT Debian 1:6.12.34-1+rpt1~bookworm
  (2025-06-26) aarch64 GNU/Linux

robins@pi4:~ $ free -h
               total        used        free      shared  buff/cache   available
Mem:           3.7Gi       463Mi       1.7Gi        34Mi       1.6Gi       3.3Gi
Swap:          511Mi          0B       511Mi


The performance numbers are reassuring:

CPU Usage: Essentially zero load:


robins@pi4:~ $ uptime
 10:39:33 up 23:04,  3 users,  load average: 0.00, 0.00, 0.00

  • Zero computational overhead even after 23+ hours of operation
  • Pi 4 is more than capable of handling the workload
  • Plenty of headroom for additional services

Memory Usage: Minimal footprint

  • Pi-hole runs efficiently even on modest hardware
  • No noticeable impact on system responsiveness

Honestly, the Pi 4 seems like overkill for this task. Given the minimal resource requirements, a Pi 2 (with the older Cortex-A7) would likely handle Pi-hole just fine, leaving the Pi 4 (with the more capable Cortex-A72) free for more power-hungry side projects. The beauty of Pi-hole is that it's so lightweight, you can run it on practically any Raspberry Pi model and still have resources to spare.

The Web Interface: Polished and Responsive

Pi-hole's web dashboard deserves special mention. It's:

  • Fast and Responsive: Page loads are snappy, even on the Pi 4
  • Live Updates: The dashboard shows real-time query statistics
  • Well-Designed: Clean interface that makes data easy to understand
  • Comprehensive: Detailed logs, statistics, and configuration options



The live dashboard is particularly satisfying to watch - seeing blocked queries in real-time gives you a visceral sense of how much unwanted traffic Pi-hole is filtering out.

Real-World Benefits

Beyond the statistics, the practical benefits are noticeable:

  • Faster Browsing: Pages load quicker without ad network delays
  • Cleaner Experience: Websites feel less cluttered
  • Privacy Improvement: Reduced tracking across devices
  • Bandwidth Savings: Less unwanted traffic on the network

One particular benefit I'm looking forward to testing: my network occasionally suffers from stuttering ping latencies. Instead of the usual 10ms responses, I'll sometimes see 100ms or even 1-second ping times that persist for minutes. During these episodes, streaming mostly works and browsing barely functions, but what really hurts is DNS resolution - especially for websites requiring multiple round-trips. With Pi-hole's 70% cache hit rate, those problematic periods should be noticeably less laggy since most DNS queries won't need to traverse the struggling network connection. More testing needed, but the potential is promising.

The ad filtering aspect wasn't my primary motivation, but it doesn't hurt either. For websites I frequent that provide genuinely good content - like Phoronix with their constant stream of quality tech updates - I've set up pass-throughs so they can rightfully earn the advertising revenue they deserve for their work. For projects like Pi-hole itself, I prefer the direct contribution route via donations. It's about supporting the creators and maintainers who provide value, whether through allowing ads or direct financial support.

Worth Supporting

The Pi-hole project has created something genuinely useful that "just works" out of the box. The quality of both the software and the installation experience makes this a project worth supporting financially. I'll definitely be making a donation to help ensure continued development.

Final Thoughts

Pi-hole represents the best kind of open-source software: it solves a real problem elegantly, installs without drama, and delivers measurable benefits immediately. If you have a spare Raspberry Pi lying around, setting up Pi-hole is an excellent way to put it to work improving your entire network's performance and privacy.

The fact that it blocked 8,500 unwanted requests in less than a day while using virtually no system resources makes it a clear win. Sometimes the best technology is the kind you set up once and then forget about - until you look at the statistics and realize how much work it's quietly doing in the background.

18 Jun 2025

Taming ReorderBufferWrite - Boost Logical Decoding in Postgres

Taming ReorderBufferWrite - Boost Logical Decoding in Postgres


Performance bottlenecks in Postgres logical replication or Change Data Capture (CDC) stream can be subtle, but one specific wait event, ReorderBufferWrite, often points directly at how your application interacts with the database during these processes. Let's unpack this wait-event and see how your application's workload patterns can trigger it.


Core Concepts: Logical Decoding and WAL

To understand the problem, we first need some context:

  • Write-Ahead Log (WAL) is PostgreSQL's transaction log and every change is written here first (to ensure durability and enable recovery/replication)

  • Logical Decoding is a powerful feature that reads this WAL. Instead of just replaying physical changes (like physical replication), it translates the WAL records into a logical, easy-to-understand stream of changes, carefully taking care of rows and transactions, which is then consumed by logical replication subscribers or CDC systems.


The Reorder Buffer: Ensuring Transactional Order

Logical decoding needs to present changes in the exact order transactions were committed. However, changes from different concurrent transactions are interleaved within the WAL. To solve this, PostgreSQL uses an in-memory area called the Reorder Buffer. Its job is to collect decoded changes belonging to transactions that are still in progress. Only when a transaction commits are its changes released from the buffer in the correct sequence.

Analogy: The Assembly Line QC Station

For an analogy, imagine an assembly line where components (WAL entries) arrive continuously where workers assemble different products (transactions). Some products are quick builds, others complex multi-step assemblies. Completed products move to a Quality Control (QC) holding station (Reorder Buffer). The QC inspector (logical decoding process) ships out batches of products, but only when all products belonging to a specific batch (committed transaction) have passed inspection. The batches must be shipped in the strict order they were completed.


The Bottleneck: ReorderBufferWrite

The Reorder Buffer uses memory allocated by the logical_decoding_work_mem setting (often defaulting to 64MB). What happens if this QC holding station (Reorder Buffer) gets completely filled up with products waiting for their batch-mates (changes from active transactions), especially for those complex, slow builds (long-running transactions)?

The system can't just discard these waiting items and the best it can do is to move them temporarily to an overflow warehouse (spilling to disk). This action – writing the buffer's contents to slower disk storage – is precisely the ReorderBufferWrite wait event.

Waiting for disk I/O is significantly slower than operating in memory. Frequent ReorderBufferWrite events directly translate to increased replication lag and reduced throughput for the logical change stream. Retrieving items from the overflow warehouse (disk) drastically slows down the QC inspector's (logical decoding) shipping rate.


Workloads That Cause Spills to Disk

ReorderBufferWrite waits are primarily triggered by workload patterns that overwhelm the in-memory Reorder Buffer. Key culprits include:

  1. Very Large Transactions: A single transaction modifying hundreds of thousands or millions of rows generates a massive volume of changes that must sit in the Reorder Buffer until the final commit. This can rapidly exhaust the available memory. Think of assembling a huge, complex product requiring many components – it occupies a lot of space at the QC station while being built.

  2. Long-Running Transactions: A transaction might not change that much data, but if it stays open for a long time (perhaps due to complex calculations, waiting for external input, or slow queries within it), its changes linger in the Reorder Buffer. Meanwhile, other transactions complete, adding their changes. The buffer fills up with changes from many transactions, bottlenecked by the one(s) taking a long time to commit. This is like one single (inactive / idling) product assembly holding up the shipment of multiple completed batches at the QC station.

  3. High Volume of Concurrent Changes: Even with reasonably sized and timed transactions, a very high rate of change across many concurrent sessions can collectively generate data faster than the logical decoding process can handle within the allocated Reorder Buffer memory, leading to spills. Imagine hundreds of small, quick products arriving at the QC station so fast it still gets overwhelmed.

    • Sudden workload spike: A related reasoning is when there is a sudden surge of application workload (writes) and they cause a rate of change higher than the logical decoding process can generally handle. In terms of workload pattern, to an experienced DBA, this may appear as:

    • connection spike => followed by replica-lag => and thereafter a slow recovery of that replica lag.

  4. Insufficient logical_decoding_work_mem: If this setting is too low for your workload's typical peak demands, spills will occur more readily, even for moderately busy workloads.


What need be done?

As an application developer, you have significant control over the transaction patterns if ReorderBufferWrite waits are causing issues:

  1. Audit & Split Large Transactions: Review bulk DML operations performed within a single transaction and refactor these into smaller batches. For e.g. Instead of one transaction for say 100k rows, use 10 transactions of 10k rows each. This drastically reduces the peak Reorder Buffer usage.

  2. Minimize Transaction Duration: Identify code paths where transactions are held open unnecessarily long. This could be from end-user applications waiting for user-input, or doing non-database work, or calling slow external services inside a database transaction that modifies replicated tables. Try to keep database transactions short and focused. Perform reads, calculations, or external calls before starting the write transaction, or after it commits. 

  3. Optimize Queries Within Transactions: Slow SELECT, INSERT, UPDATE, or DELETE queries will naturally extend transaction duration. Use query analysis tools (EXPLAIN ANALYZE) to find and optimize slow queries involved in your transactions.

  4. Isolate Read-Only Work: If long-running processes only read data, ensure they aren't unnecessarily starting read-write transactions on the primary where logical decoding is happening. Similarly use read-only transactions or more permissive isolation levels where possible.

  5. Collaborate on Configuration: If application-side optimizations aren't sufficient, discuss the observed workload with your DBA or platform team. It would help to provide them with information about your transaction patterns. This could help them review whether an increase in logical_decoding_work_mem is warranted and safe within the server's overall resource constraints. Tuning memory should usually follow workload optimization.


Conclusion

ReorderBufferWrite isn't just an obscure PostgreSQL internal wait-event. It's a performance indicator directly linked to application workload patterns during logical decoding. By designing your application to use shorter, smaller, and more efficient transactions, especially on tables involved in replication, you can minimize these disk spill events and ensure a performant database.


11 May 2025

FOR KEY SHARE optimization and the SLRU Trap

 

FOR KEY SHARE optimization and the SLRU Trap


Optimizing database concurrency is a constant balancing act. We often tweak locking strategies in PostgreSQL, aiming to allow more simultaneous operations without compromising data integrity. A common scenario involves shifting from stricter row-level locks to more lenient ones. But sometimes, what seems like a straightforward optimization can lead to unexpected performance bottlenecks in less obvious parts of the system.

This post explores one such scenario: moving from SELECT FOR NO KEY UPDATE to SELECT FOR KEY SHARE, the potential for subsequent MultiXactOffsetSLRU wait events, and how PostgreSQL 17 offers a direct solution.


The Locking Strategy Shift: Aiming for Higher Concurrency

Let's start with the locks themselves. Within a transaction, you might initially use:

  • SELECT FOR NO KEY UPDATE: This acquires a moderately strong row lock. It prevents others from deleting the row, updating key columns, or acquiring FOR UPDATE / FOR NO KEY UPDATE locks on it. However, it does allow concurrent non-key updates and weaker FOR SHARE / FOR KEY SHARE locks. Importantly (and we’ll see why later), only one transaction can hold this lock (or FOR UPDATE) on a given row at a time.

To potentially increase concurrency, especially if you only need to prevent key changes or deletions (like ensuring a foreign key reference remains valid), you might switch to:

  • SELECT FOR KEY SHARE: This is a weaker, shared lock. It blocks deletions and key updates but allows concurrent non-key updates and even other concurrent SELECT FOR KEY SHARE (or FOR SHARE) locks on the exact same row.

The intended outcome of switching to FOR KEY SHARE is often to reduce blocking and allow more transactions to proceed in parallel, particularly if the main concern is referential integrity rather than preventing all concurrent modifications.


The Unforeseen Bottleneck: Enter MultiXacts and SLRU Caches

While the switch does allow higher concurrency at the row-lock level, it can create pressure elsewhere. Here’s the chain reaction:

  1. Increased Shared Lock Concurrency: Your application now has more situations where multiple transactions hold a shared lock (FOR KEY SHARE) on the same row simultaneously.

  2. The MultiXact System: How does PostgreSQL track that multiple transactions (potentially dozens or hundreds) have a shared interest in a single row? It uses a mechanism called MultiXact IDs (Multi-Transaction IDs). Instead of just one transaction ID locking the row, PostgreSQL assigns a special MultiXact ID that represents the group of transactions currently sharing a lock on it.

  3. SLRU Caches: Managing this MultiXact metadata efficiently requires quick access. PostgreSQL uses specialized SLRU (Simple Least Recently Used) caches in shared memory for this. These caches store the mappings (offsets) from rows to their MultiXact member lists (MultiXactOffsetSLRU) and the member lists themselves (MultiXactMemberSLRU).

  4. The Bottleneck (PG 16 and older): Before PostgreSQL 17, these SLRU caches had relatively small, fixed sizes determined at compile time. When the workload switch dramatically increased the demand for MultiXact tracking (due to more concurrent shared locks), these small caches could easily become overwhelmed.

  5. The Symptom (MultiXactOffsetSLRU Waits): An overloaded SLRU cache leads to performance degradation manifesting as specific Wait Events. You might see high MultiXactOffsetSLRU waits, indicating processes are frequently:

  • Waiting for disk I/O because the required MultiXact offset data wasn't found in the small cache (cache miss).

  • Waiting to acquire low-level locks needed to access or update the cache's shared memory buffers, because many processes are trying to use the limited cache concurrently (lock contention).

  • Many backends appear hung - as can be seen in this recent community thread.




So trying to increase concurrency at the row-level, created a bottleneck in the underlying mechanism to manage that very concurrency!



PostgreSQL 17 to the Rescue: Configurable SLRU Buffers

Recognizing this potential bottleneck, the PostgreSQL developers introduced direct solutions in PostgreSQL 17 (released September 2024). These come in the form of new configurable parameters:

  • multixact_offset_buffers:

  • This parameter controls the size (in buffer pages) of the MultiXactOffset SLRU cache in shared memory. The default value is very small (16) and this allows administrators to allocate more RAM to cache the crucial row-to-member-list mappings. This significantly increases the cache hit rate, reduces disk I/O for MultiXact offsets, and directly alleviates the pressure causing MultiXactOffsetSLRU waits.

  • multixact_member_buffers:

  • This parameter controls the size of the MultiXactMember SLRU cache, which stores the actual lists of transaction IDs. This is possibly less directly tied to the Offset wait event, ensuring in-cache member lists improves the overall performance and throughput of the entire MultiXact lookup process, which is essential when handling high shared-lock concurrency.

You can learn more about these new parameters in this fantastic discussion - https://pganalyze.com/blog/5mins-postgres-17-configurable-slru-cache

These parameters allow DBAs to tune the memory allocated to these critical caches based on their specific workload, moving away from the one-size-fits-all limitation of previous versions.

Conclusion

Switching locking strategies in PostgreSQL requires careful consideration not just of the direct blocking rules but also of the potential impact on underlying mechanisms. Moving from SELECT FOR NO KEY UPDATE to the more concurrency-friendly SELECT FOR KEY SHARE can be beneficial, but it increases the load on the MultiXact system. In versions before PostgreSQL 17, this could lead to performance bottlenecks manifesting as MultiXactOffsetSLRU wait events due to contention on small, fixed-size caches.

Thankfully, PostgreSQL 17 provides the tools needed to manage this directly with the multixact_offset_buffers and multixact_member_buffers GUCs. If you encounter these specific wait events after increasing shared lock usage, upgrading to PostgreSQL 17+ and tuning these parameters should be a key part of your resolution strategy. As always, monitor your system's wait events and performance metrics closely when making changes to locking or configuration.


Testing PostgreSQL on Debian/Hurd: A Windows + QEMU Adventure

Curiosity often leads to the most interesting technical adventures. This time, I decided to explore something off the beaten path: running D...