StormaticsStormatics

NUMA, Linux, and PostgreSQL before libnuma Support

This entry in this series covers the general interaction of PostgreSQL and Linux on NUMA systems. This topic is complex and so there are cases of simplification. However this distills the general information about running PostgreSQL 17 and below (or Postgres 18 without libnuma support) on NUMA systems, with Linux as a reference point. By the end of this blog entry, you should both be able to run Postgres on a NUMA system and also understand why the libnuma support in PostgreSQL 18 is so important.
Read More

What Are “Dirty Pages” in PostgreSQL?

PostgreSQL stores data in fixed‑size blocks (pages), normally 8 KB. When a client updates or inserts data, PostgreSQL does not immediately write those changes to disk. Instead, it loads the affected page into shared memory (shared buffers), makes the modification there, and marks the page as dirty. A “dirty page” means the version of that page in memory is newer than the on‑disk copy.
Read More

Introduction to NUMA

PostgreSQL and NUMA, part 1 of 4 This series covers the specifics of running PostgreSQL on large systems with many processors. My experience is that people spend months often learning the basics when confronted with the problem. This series tries to dispel these difficulties by providing a clear background into the topics in question. The hope is that future generations of database engineers and administrators don’t have to spend months figuring things out through trial and error. This entry in the series focuses on the low-level hows and whys of Non-Uniform Memory Access so that it is possible to understand the solutions and recommendations later with a focus on conceptual details. Unfortunately in many details this requires focusing on technical details as often the concepts without the details are confusing at best. Further entries will build upon the information in this post. We recommend reading it first, and then referring back as needed.
Read More

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

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

When PostgreSQL performance slows down, here is where to look first

PostgreSQL is built to perform. However, as workloads increase and systems evolve, even the most robust setups can begin to show signs of strain. Whether you are scaling a product or supporting enterprise SLAs, performance slowdowns tend to surface when you least want them to. If you are a technology leader overseeing a team of developers who manage PostgreSQL as part of a broader application stack, or you are responsible for uptime and customer satisfaction at scale, knowing where to look first can make all the difference.
Read More

How to Upgrade Major PostgreSQL Versions: A Practical Production Guide

PostgreSQL versions follow a well-defined five-year support lifecycle. Each major release receives bug fixes, security patches, and minor updates for five years from its initial release date. After that point, the version reaches end-of-life (EOL) and no longer receives official updates. Staying on an EOL version exposes your systems to security risks, potential compatibility issues, and missing performance improvements introduced in later releases. You can always check the current support status of PostgreSQL versions on the official PostgreSQL Versioning Policy page. Upgrading to the latest version ensures long-term stability, access to new features, and better support. Recently, I worked on upgrading a critical production PostgreSQL environment from version 11 to 15. Version 15 was chosen because the client’s application had only been tested up to that release. The system supported large batch workloads and live applications, so we had to be meticulous. While this article draws from that specific project, the steps are broadly applicable to anyone planning a major PostgreSQL upgrade, especially when crossing several versions. This guide outlines a generalized, production-ready approach for performing major version upgrades using the pg_dump/pg_restore method.
Read More

Best Practices for TimescaleDB Massive Delete Operations

Welcome to the second part of our TimescaleDB best practices series! In the first part, we explored how to perform massive backfill operations efficiently, sharing techniques to optimize performance and avoid common pitfalls. If you haven’t had a chance to read the first part yet, you can check it out using this link In today's blog, we will discuss another crucial aspect of time-series data management: massive delete operations. As your data grows over time, older records often lose their relevance but continue to occupy valuable disk space, potentially increasing storage costs and might degrade the performance if not managed well.  Let's walk through some strategies to clean up or downsample aged data in TimescaleDB, helping you maintain a lean, efficient, and cost-effective database.
Read More

How to Safely Perform Backfill Operations in TimescaleDB

Backfilling data into a TimescaleDB hypertable in production can be very tricky, especially when automated processes like compression policies are involved. From past experience, we have seen that if backfill operations aren’t handled properly, they can interfere with these automated tasks, sometimes causing them to stop working altogether.  This blog covers a safer and more reliable approach to backfilling hypertables, along with best practices to prevent disruptions to compression and other background processes. What is a Backfill Operation? Backfilling means adding old or missing data into the database table after some time has already passed.  Imagine you are collecting temperature readings every hour, but your system was down for a day and didn’t save any data. Later, you get that missing data from the local storage of the device or cloud storage, and want to put it back in the right hypertable, which is called backfilling.  In TimescaleDB, this is common with time-series data, but it needs to be done carefully. That’s because TimescaleDB might already be doing things in the background, like compressing old data to save space. If we are not careful, backfilling can mess up these automatic tasks.
Read More

Improving PostgreSQL Performance with Partitioning

My recommended methodology for performance improvement of PostgreSQL starts with query optimization. The second step is architectural improvements, part of which is the partitioning of large tables. Partitioning in PostgreSQL is one of those advanced features that can be a powerful performance booster. If your PostgreSQL tables are becoming very large and sluggish, partitioning might be the cure. The Big Table Problem Large tables tend to grow uncontrollably, especially in OLTP or time-series workloads. As millions or billions of rows accumulate, you begin to notice: Slow queries due to full table scans or massive indexes. Heavy I/O usage, especially when indexes cannot fit in memory. Bloated memory during operations like sorting or joining. Increased maintenance cost, with longer VACUUM, ANALYZE, and REINDEX times. Hard-to-manage retention policies, as purging old rows becomes expensive. These problems are amplified in cloud-hosted databases, where every IOPS, GB, or CPU upgrade increases cost.
Read More

Archives

Categories