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, 2025With 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.
Continue reading→Postgres vs. SQL Server: B-Tree Index Differences & the Benefit of Deduplication
03 April, 2025When 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.
Continue reading→Constraint Programming in Action: Optimizing Postgres Index Selection
01 August, 2024In 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.
Continue reading→A practical introduction to Constraint Programming using CP-SAT and Python
02 July, 2024Imagine 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.
Continue reading→Introducing pg_query for Postgres 16 - Parsing SQL/JSON, Windows support, PL/pgSQL parse mode & more
11 January, 2024Parsing 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.
Continue reading→Postgres 16: Cumulative I/O statistics with pg_stat_io
14 February, 2023One 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.
Continue reading→Postgres in 2021: An Observer's Year In Review
07 January, 2022Every 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!
Continue reading→How we deconstructed the Postgres planner to find indexing opportunities
02 November, 2021Everyone 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.
Continue reading→Introducing pg_query 2.0: The easiest way to parse Postgres queries
18 March, 2021Parsing of "SELECT * FROM mytable" SQL statement into the associated Postgres parse tree 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.
Continue reading→Postgres 11: Monitoring JIT performance, Auto Prewarm & Stored Procedures
04 October, 2018Everyone’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. JIT Performance in Postgres 11 Just-In-Time compilation (JIT) in Postgres 11 Just-In-Time compilation (JIT) for query execution was added in Postgres 11.
Continue reading→Visualizing & Tuning Postgres Autovacuum
28 November, 2017VACUUM timeline visualization 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.
Continue reading→Whats New in Postgres 10: Monitoring Improvements
04 October, 2017Postgres 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.
Continue reading→Introducing pg_query: Parse PostgreSQL queries in Ruby
17 June, 2014In 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.
Continue reading→