Postgres Internals

How Postgres works under the hood: VACUUM, the buffer cache, storage layout, WAL, and other core subsystems.

Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

07 May, 2025

With the Postgres 18 Beta 1 release this week, a multi-year effort and significant architectural shift in Postgres is taking shape: Asynchronous I/O (AIO). These capabilities are still under active development, but they represent a fundamental change in how Postgres handles I/O, offering the potential for significant performance gains, particularly in cloud environments where latency is often the bottleneck. Why asynchronous I/O matters How Postgres 17’s read streams paved the way New io_method…

Postgres vs. SQL Server: B-Tree Index Differences & the Benefit of Deduplication

03 April, 2025

When it comes to optimizing query performance, indexing is one of the most powerful tools available to database engineers. Both PostgreSQL and Microsoft SQL Server (or Azure SQL) use B-Tree indexes as their default indexing structure, but the way each system implements, maintains, and uses those indexes varies in subtle but important ways. In this blog post, we explore key areas where PostgreSQL and SQL Server diverge: how their B-Tree indexes implementations behave under the hood and how they…

Constraint Programming in Action: Optimizing Postgres Index Selection

01 August, 2024

In a previous article we introduced constraint programming (CP), a declarative paradigm to solve discrete optimization problems. We discussed some theory, and considered a realistic example by constructing a work schedule for a small store. In this follow-up article, we’ll now take our understanding of constraint programming, and apply it to a real-world example: Selecting optimal indexes in Postgres. The model presented in this article is similar to the CP model that was presented at PGDay…

A practical introduction to Constraint Programming using CP-SAT and Python

02 July, 2024

Imagine you're an e-commerce giant that would like to build a new warehouse to improve service to your customers, but you need to know what is the best location for it. Or you're a global shipping company that assigns packages to their delivery trucks and has to choose the best routes in order to save gas and reduce driver overtime. Or an airline that is looking to offer service to a new location, and needs to know which types of planes they should use and on what schedule, to maximize the…

Introducing pg_query for Postgres 16 - Parsing SQL/JSON, Windows support, PL/pgSQL parse mode & more

11 January, 2024

Parsing SQL queries and turning them into a syntax tree is not a simple task. Especially when you want to support special syntax that is specific to a particular database engine, like Postgres. And when you’re working with queries day in day out, like we do at pganalyze, understanding the actual intent of a query, which tables it scans, which columns it filters on, and such, is essential. Almost 10 years ago, we determined that in order to create the best product for monitoring and optimizing…

Postgres 16: Cumulative I/O statistics with pg_stat_io

14 February, 2023

One of the most common questions I get from people running Postgres databases at scale is: How do I optimize the I/O operations of my database? Historically, getting a complete picture of all the I/O produced by a Postgres server has been challenging. To start with, Postgres splits its I/O activity into writing the WAL stream, and reads/writes to the data directory. The real challenge is understanding second-order effects around writes: Typically the write to the data directory happens after the…

Postgres in 2021: An Observer's Year In Review

07 January, 2022

Every January, the pganalyze team takes time to sit down to reflect on the year gone by. Of course, we are thinking about pganalyze, our customers and how we can improve our product. But, more importantly, we always take a bird's-eye view at what has happened in our industry, and specifically in the Postgres community. As you can imagine: A lot! So we thought: Instead of trying to summarize everything, let's review what happened with the Postgres project, and what is most exciting from our…

How we deconstructed the Postgres planner to find indexing opportunities

02 November, 2021

Everyone who has used Postgres has directly or indirectly used the Postgres planner. The Postgres planner is central to determining how a query gets executed, whether indexes get used, how tables are joined, and more. When Postgres asks itself "How do we run this query?”, the planner answers. And just like Postgres has evolved over decades, the planner has not stood still either. It can sometimes be challenging to understand what exactly the Postgres planner does, and which data it bases its…

Introducing pg_query 2.0: The easiest way to parse Postgres queries

18 March, 2021

The query parser is a core component of Postgres: the database needs to understand what data you're asking for in order to return the right results. But this functionality is also useful for all sorts of other tools that work with Postgres queries. A few years ago, we released pg_query to support this functionality in a standalone C library. pganalyze uses pg_query to parse and analyze every SQL query that runs on your Postgres database. Our initial motivation was to create pg_query for checking…

Postgres 11: Monitoring JIT performance, Auto Prewarm & Stored Procedures

04 October, 2018

Everyone’s favorite database, PostgreSQL, has a new release coming out soon: Postgres 11 In this post we take a look at some of the new features that are part of the release, and in particular review the things you may need to monitor, or can utilize to increase your application and query performance. Just-In-Time compilation (JIT) in Postgres 11 Just-In-Time compilation (JIT) for query execution was added in Postgres 11. It's not going to be enabled for queries by default, similar to parallel…

Visualizing & Tuning Postgres Autovacuum

28 November, 2017

In this post we'll take a deep dive into one of the mysteries of PostgreSQL: VACUUM and autovacuum. The Postgres autovacuum logic can be tricky to understand and tune - it has many moving parts, and is hard to understand, in particular for application developers who don't spend all day looking at database documentation. But luckily there are recent improvements in Postgres, in particular the addition of pg_stat_progress_vacuum in Postgres 9.6, that make understanding autovacuum and VACUUM…

Whats New in Postgres 10: Monitoring Improvements

04 October, 2017

Postgres 10 has been stamped on Monday, and will most likely be released this week, so this seems like a good time to review what this new release brings in terms of Monitoring functionality built into the database. In this post you'll see a few things that we find exciting about the new release, as well as some tips on what to adjust, whether you use a hosted Postgres monitoring tool like pganalyze, or if you've written your own scripts. New "pg_monitor" Monitoring Role Most users of Postgres…

Introducing pg_query: Parse PostgreSQL queries in Ruby

17 June, 2014

In this article we'll take a look at the new pg_query Ruby library. pg_query is a Ruby library I wrote to help you parse SQL queries and work with the PostgreSQL parse tree. We use this extension inside pganalyze to provide contextual information for each query and find columns which might need an index. At the end of this article you'll also find monitor.rb - a ready-to-use example that filters pg_stat_statements output and restricts it to only show a specific table. Existing Solutions to Parse…