How PostgreSQL Scans Your Data

To understand how PostgreSQL scans data, we first need to understand how PostgreSQL stores it. A table is stored as a collection of 8KB pages (by default) on disk. Each page has a header, an array of item pointers (also called line pointers), and the actual tuple data growing from the bottom up. Each tuple has its own header containing visibility info: xmin, xmax, cmin/cmax, and infomask bits.
Read More

Fixing ORM Slowness by 80% with Strategic PostgreSQL Indexing

Modern applications heavily rely on ORMs (Object-Relational Mappers) for rapid development. While ORMs accelerate development, they often generate queries that are not fully optimized for database performance. In such environments, database engineers have limited control over query structure, leaving indexing and database tuning as the primary performance optimization tools.
Read More

PostgreSQL Materialized Views: When Caching Your Query Results Makes Sense (And When It Doesn’t)

Your dashboard queries are timing out at 30 seconds. Your BI tool is showing spinners. Your users are refreshing the page, wondering if something's broken. You've indexed everything. You've tuned shared_buffers. You've rewritten the query three times. The problem isn't bad SQL - it's that you're forcing PostgreSQL to aggregate, join, and scan millions of rows every single time someone opens that report.
Read More

Unused Indexes In PostgreSQL: Risks, Detection, And Safe Removal

Indexes exist to speed up data access. They allow PostgreSQL to avoid full table scans, significantly reducing query execution time for read-heavy workloads.From real production experience, we have observed that well-designed, targeted indexes can improve query performance by 5× or more, especially on large transactional tables. However, indexes are not free. And in this blog, we are going to discuss what issues unused indexes can cause and how to remove them from production systems with a rollback plan, safely
Read More

Scaling Up Wasn’t the Plan — Until It Was the Only Plan

If you have ever generated a complex report in Odoo only to watch the loading spinner for minutes, you are not alone. One of our customers ran into exactly this scenario: their system ground to a near stall whenever they tried to compile business reports. After a systematic investigation, we achieved a 93 % performance improvement, but only by choosing the last resort: upgrading the instance’s resources.
Read More

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

Archives

Categories