StormaticsStormatics

Configuring Linux Huge Pages for PostgreSQL

Huge pages are a Linux kernel feature that allocates larger memory pages (typically 2 MB or 1 GB instead of the normal 4 KB). PostgreSQL’s shared buffer pool and dynamic shared memory segments are often tens of gigabytes, and using huge pages reduces the number of pages the processor must manage. Fewer page‑table entries mean fewer translation‑lookaside‑buffer (TLB) misses and fewer page table walks, which reduces CPU overhead and improves query throughput and parallel query performance.
Read More

Understanding Disaster Recovery in PostgreSQL

System outages, hardware failures, or accidental data loss can strike without warning. What determines whether operations resume smoothly or grind to a halt is the strength of the disaster recovery setup. PostgreSQL is built with powerful features that make reliable recovery possible. This post takes a closer look at how these components work together behind the scenes to protect data integrity, enable consistent restores, and ensure your database can recover from any failure scenario.
Read More

Don’t Skip ANALYZE: A Real-World PostgreSQL Story

Recently, we worked on a production PostgreSQL database where a customer reported that a specific SELECT query was performing extremely slowly. The issue was critical since this query was part of a daily business process that directly impacted their operations.
Read More

Understanding PostgreSQL WAL and optimizing it with a dedicated disk

If you manage a PostgreSQL database with heavy write activity, one of the most important components to understand is the Write-Ahead Log (WAL). WAL is the foundation of PostgreSQL’s durability and crash recovery as it records every change before it’s applied to the main data files. But because WAL writes are synchronous and frequent, they can also become a serious performance bottleneck when they share the same disk with regular data I/O.
Read More

3 Features I am Looking Forward to in PostgreSQL 18

It is that time of the year again. The first release candidate of PostgreSQL 18 is out, and things look promising. We should expect General Availability in the next 2-4 weeks. Exciting times! Over the past many years and as many releases, the PostgreSQL community has done a phenomenal job of being disciplined about the annual release process. And we have done so averaging 150+ new features with each release!
Read More

The Hidden Bottleneck in PostgreSQL Restores and its Solution

In July 2025, during the PG19-1 CommitFest, I reviewed a patch targeting the lack of parallelism when adding foreign keys in pg_restore. Around the same time, I was helping a client with a large production migration where pg_restore dragged on for more than 24 hours and crashed multiple times. In this blog, I will talk about the technical limitations in PostgreSQL, the proposed fix, and a practical workaround for surviving large restores.
Read More

Cold, Warm, and Hot Standby in PostgreSQL: Key Differences

When working with customers, a common question we get is: “Which standby type is best for our HA needs?” Before answering, we ensure they fully understand the concepts behind each standby type and provide the necessary guidance A standby server is essentially a copy of your primary database that can take over if the primary fails. There are different types of standby setups, each with its own use cases, pros, and cons. In this blog, we will discuss the three types: Cold Standby, Warm Standby, and Hot Standby.
Read More

PostgreSQL Database SLAs: Why Hidden Issues Often Break Customer Commitments

SLAs feel reassuring when signed—but their substance lies in what happens behind the scenes. Often, the most damaging breaches don’t stem from cloud outages or server failures, but from invisible issues hidden in how PostgreSQL was initially set up and configured. Increasingly sluggish queries, split-brain scenarios, silent backup failures, any of these can suddenly explode into customer-facing crises. 1. Slow Queries: The Sneaky SLA Saboteur The Hidden Cost of Delayed Queries A seemingly minor tuning oversight, like a missing index or outdated statistics, can turn a 200 ms query into a 10-second slog. It might not seem urgent initially, but as concurrency increases, cascading delays build up. A Slow Query Accelerated 1000× In one case study, an engineer faced a painfully slow query that scanned 50 million rows through a sequential scan—even though it was a simple query filtering on two columns (col_1, col_2) and selecting by id. After creating an index using those columns plus an INCLUDE (id) clause, the query performance improved dramatically: what had taken seconds dropped to just milliseconds, representing up to a 1,000× improvement in the worst-case runtime. [Ref: Learnings from a slow query analysis in PostgreSQL] This shows how even a simple query, if not indexed properly, can pose an SLA risk as data volume increases.
Read More

Archives

Categories