Latest Blog Posts

A couple months back I posted about maybe adjusting random_page_cost to better reflect how current storage handles random and sequential access. I had a bunch of great discussions about the topic since then, but ultimately I got distracted by other stuff.

POSETTE happened last week, with my pre-recorded talk about this very topic (and many other great talks, BTW). Which reminded me that I started thinking about random_page_cost a bit differently. So here’s an update with some more thoughts.

Introduction

I recently had the privilege of speaking at POSETTE 2026 about pg_stats and how Postgres internal statistics work. This post is a written companion to that talk – aimed at giving you a working understanding of what pg_stats is, how it’s populated, and how it shapes the decisions the query planner makes on your behalf.

Imagine a customers table that looks roughly like this:

CREATE TABLE customers (
    id          bigserial PRIMARY KEY,
    city        text NOT NULL,
    state       text NOT NULL,
    signup_date date NOT NULL
);
-- Insert 1,000,000 rows

Consider a query you’ve probably written many times:

SELECT * FROM customers WHERE state = 'CA';

With separate indexes on state and city, you might expect an index scan on state. But the EXPLAIN ANALYZE output may look something like this:

                              QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on customers  (cost=0.00..19682.66 rows=173829 width=26)
                        (actual time=0.025..120.574 rows=172001 loops=1)
   Filter: (state = 'CA'::text)
   Rows Removed by Filter: 827972
   Buffers: shared hit=4601 read=2582
 Planning:   Buffers: shared hit=139
 Planning Time: 0.371 ms
 Execution Time: 128.136 ms

A sequential scan, even with an index available. We’ll get into the reasons for this today.

Query Plans Are Made by the Query Planner

When you submit a query to Postgres, the query planner is responsible for deciding how to execute it. You may assume the planner reads your actual data – it doesn’t. What it really reads is a summary of your data, stored in pg_statistic.

That summary tells the planner things like:

• How many distinct values appear in a column
• What the most common values are, and how often they show up
• What the rough distribution of values looks like across a range
• Whether the data is laid out on disk in roughly the same order as the column’s natural sort order

pg_statistic itself is a bit hard t

On June 5 2026, the PostgreSQL User Group Greece met, organized by Eftychia Kitsou and Charis Charalampidi.

Speaker:

• George Capnias
• Kostas Maistrelis

PGDay Boston happened on June 9 2026 Organized by:

• Tom Kincaid
• Aaryan Sonwane
• Pat Wright
• Shayon Mukherjee
• Shihao Zhong
• Regina Obe
• Kheli Fallon

Talk selection committee:

• Erik Pohi
• Greg Burd
• Kanchan Mohitey
• Geetha Setty
• Shree Vidhya Sampath
• Sachin Pawar
• Rob Emanuele

Code of Conduct Committee:

• Vibhor Kumar
• Stacey Haysler
• Ken Rugg

Speaker:

• Michael Stonebraker
• Richard Yen
• Brian Brennglass
• Bruce Momjian
• Robert Haas
• Shree Vidhya Sampath
• Ryan Booz

The Postgres Meetup Group Berlin met on June 10 2026, where David Wheeler delivered a talk. The Meetup was organized by

• Andreas Scherbaum
• Oleksii Kliukin
• Celeste Horgan
• Sergey Dudoladov

The Silicon Chalet Meetup Group met on June 11 2026 for the SC66: Meetup PostgreSQL

Organized by

• Maeva THIBURCE
• Guillaume Proust
• Franck Pachot

Speaker:

• Yingkun Bai
• Mathieu Perez
• Daniel Westermann

On June 11 2026, PostgreSQL Edinburgh Meetup June 2026 happened, organized by

• Jimmy Angelakos
• Jim Gardner
• Denys Rybalchenko

Claire Giordano and Aaron Wislang hosted and published a new podcast episode on June 12, 2026 “How I got started running a Postgres user group with Jeremy Schneider” from the Talking Postgres series.

PASS Summit On Tour: Frankfurt 2026 happened from June 10-11, 2026. PostgreSQL talks by:

• Grant Fritchey
• Pat Wright
• Akanksha Sheoran
• Andreas Jordan
• Per Christopher Undheim
• Chanpreet Singh
• Michael Banck

On Saturday, 13th June, 2026 , PgPune met for Event #5.

Organized by:

• Ashish Mehra
• Sachin Kotwal
• Sagar Jadhav
• Rushabh Lathia

Speaker:

• Hari Kiran
• Jeevan Chalke
• Mohini Ogale
• Ayush Shah
• Prafu

The PostGIS development team is pleased to provide postgis_tiger_geocoder extension. This is the very first release since the break from the PostGIS core. This version requires PostgreSQL 16 and above and should work with any supported PostGIS version.

PostGIS 3.6 series is the last series to include postgis_tiger_geocoder. PostGIS 3.7 will be shipped without postgis_tiger_geocoder.

Moving forward postgis_tiger_geocoder has its own dedicated repo at OSGeo Gitea postgis_tiger_geocoder under the PostGIS org.

The versioning model has also changed to be versioned based on the year of the Census US Tiger dataset that is current at time of it’s release.

pgAgent has been my go to scheduling solution for quite some time. Sadly in 6 months it will be completely retired and the pgAgent UI in pgAdmin will be gone. The main reasons I liked pgAgent were:

• Cross Platform: I have a lot on windows and linux customers, so this was important.
• Nice UI in pgAdmin, so I could do all work with PostgreSQL and schedule things at the same time as well as check status of jobs.
• The database backend is PostgreSQL, my favorite database
• Supports Multiple Agents with varying OS.
• Supports jobs having many ordered steps

Recently, I looked into how common polymorphic associations actually are in relational databases — a performance-hostile pattern built around a discriminated foreign key that ORMs (Rails, Django, Hibernate), CRM platforms (Salesforce), and 1C generate automatically. The front page of a typical online store, or the activity feed of a CRM, is built by exactly this kind of query: a base table is LEFT JOIN-ed to every possible subtype through a (type, id) pair of columns.

That earlier article answered the question 'how widespread is this pattern?' After all, if you're going to improve something, it helps to know how useful the improvement will be, right? Here, I want to give a sense of how this pattern leads to performance regressions and point out directions in the PostgreSQL optimiser that could make the situation easier.

Spoiler: not much yet — but a few things are moving on pgsql-hackers. Three patches, discussed across 2024–2026, target three different sources of regression. Each is covered below.

Where the problems come from

As a reminder, here is what the query looks like:

SELECT
    ol.id,
    COALESCE(p.name, g.name, s.name) AS item_name
FROM order_lines ol
  LEFT JOIN products p
    ON ol.type = 'A' AND ol.item_id = p.id
  LEFT JOIN gift_cards g
    ON ol.type = 'B' AND ol.item_id = g.id
  LEFT JOIN subscriptions s
    ON ol.type = 'C' AND ol.item_id = s.id
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.id = ol.order_id AND o.placed_at >= DATE '2024-01-01')
ORDER BY ol.popularity
LIMIT 100;

At the moment, the Postgres optimiser implements fairly primitive logic here. For every row of the base table (order_lines), the query probes each of the N subtype tables through a LEFT JOIN. Only one of those joins ever returns a match — the one whose discriminator matches the type value in that row. The remaining N−1 joins are guaranteed to come up empty: their ON predicate carries a different discriminator value (see the schema below).

Logical replication has been an integral part of Postgres since version 10 released in 2017. It's a very convenient system for synchronizing one or more tables from one running Postgres cluster to another, and the community has embraced it almost without reservation. It's a great feature we've all come to rely on.For all that, it has never been a flawless panacea. Perhaps the most glaring and conspicuous omission in Postgres logical replication is that of sequences. Novice users might run a logical migration or promote the new cluster, and meet a wall of duplicate key violations on the very first insert. The more experienced mind the gap and bolt on supplementary steps to circumvent this, quietly hoping they remember to run it during the cutover window.But why? Why were sequences left out of logical replication for the better part of a decade? Why let a freshly promoted cluster restart its counters at 1 and collide with the very rows it just spent hours copying over? That turns out to be a surprisingly long story, and one Postgres 19 finally gives a satisfying ending. So let's get into how sequences became the awkward holdout of logical replication, and what changed to bring them along at last.

The Sequence That Time Forgot

1. pgsql_tweaks is a bundle of functions and views for PostgreSQL
2. Changes In The pgsql_tweaks 1.0.4 Release

pgsql_tweaks is a bundle of functions and views for PostgreSQL

The source code is available on Codeberg.

The extension is also available on PGXN.

The extension is also availabe through the PostgreSQL rpm packages.

Changes In The pgsql_tweaks 1.0.4 Release

Lætitia Avrot posted a blog post where she checked a view from the extension for readability and quality.

She found a typo in the view pg_bloat_info and send a pull request with the correction of the typo.

Big thanks for the correction Lætitia. And it is worth following her and her blog.

Funny engough, that the type did not make it to the documentation.

Our team is excited to announce pgEdge ColdFront v1.0.0-beta1: open-source, transparent data tiering for PostgreSQL that unites OLTP, analytics and AI workloads, with no application code changes required. The headline feature: a fully writable cold tier. Jimmy Angelakos is the lead engineer, and it’s available on GitHub and pgEdge Enterprise Postgres.Moving aging data off primary PostgreSQL storage is economically obvious. Keeping it fully operational once it’s there is where things get complicated.

The trade-off nobody wants to make

Part 1: Why the AI Agent Layer Exists

About This Series

This is the first post in a four-part series on building intelligent enterprise AI systems. Each part builds on the last, taking you from foundational concepts through to production-grade architecture.

Abstract

Most enterprise AI conversations still begin with models. Which model should we use? Which one has the best reasoning capability? Which one gives the lowest latency or the best cost per token? These are useful questions, but they are not the questions that usually determine whether an AI system succeeds in production.

The harder problem is the system around the model.

A language model can answer a question, summarize a document, or generate a response. An enterprise AI system must do something more demanding. It must retrieve trusted data, remember context, invoke tools safely, follow policy, maintain state, create an audit trail, and interact with real business processes. That work does not happen inside the model alone.

This is why the AI Agent Layer is becoming a first-class architectural concern. It is the layer that sits between foundation models and enterprise systems — managing memory,

Every AI application built on PostgreSQL hits the same inflection point. You've got pgvector installed, embeddings in a table, a similarity search query that returns surprisingly good results. The prototype works and your team is excited. Someone asks "when can we ship this?" and you suddenly realize that the distance between "it works on my laptop" and "it works in production across three regions" is a lot larger than you thought.The usual answer is to re-platform. Swap Postgres for a purpose-built vector database for the AI parts. Add a separate search service. Move to a managed offering that handles the scaling but forces you to redesign your data model. By the time you're done, the prototype you built is gone, replaced by an architecture that looks nothing like what you started with.There's a different path. One where Postgres remains the foundation at every stage, your schema doesn't change, and the tools you need show up when you need them, not before. That's what we built at pgEdge, and this is a walkthrough of what that path looks like in practice.

The Beginning: Setting Up pgvector in PostgreSQL for AI Search

Building With Ruby on Rails

The Aura Frames platform has been built with Ruby on Rails since the beginning (more than 10 years ago!). Christmas 2025 was the busiest day of the year for the company and technical platform, serving a peak of 41 million API requests per hour (~11.4K requests per second), and processing a peak of 11.8 million background jobs per hour (~3300 jobs/second). On the database side, the sum of DB peak transactions per second (TPS) was 226K.

For an introduction to the Aura Frames company and products, and a deeper dive on the Postgres side of things, please check out Part 1 of this series.

Brief Recap from Part 1: Besides Ruby on Rails, Aura Frames uses PostgreSQL and AWS as key technologies.

Due to not being ea

What’s Aura Frames?

Aura Frames (Aura Home, Inc.) is the company behind modern, high-quality, Wi-Fi connected digital photo frames that customers love.

The frames are easy to use via free iOS and Android apps, don’t require a subscription, and offer unlimited cloud storage for photos and videos. Once set up, family members can be invited to contribute photos and videos via the app from anywhere. Typically Aura frames have an average of 4 contributors adding content.

In 2025, more than 1 billion photos were shared to Aura frames globally.

While public engineering blog posts are limited, Aura was featured on the AWS Storage Blog in the past. Link: How Aura improves database performance using Amazon S3 Express One Zone for caching.

Disclosures

I began working with Aura in 2025. Au

Part 1 was about failures that start one layer below Postgres: the kernel, glibc, the page allocator. This post is about the worse class, where the failure is inside Postgres itself. The logs are clean. Recovery never runs. And a query either returns the wrong answer or drops rows that are still sitting on disk.

The three incidents below have nothing in common except the part that makes them dangerous: there is no error to alert on. A wrapped-around multixact counter, a missing TOAST chunk, a torn page on disk, none of them rings a bell. The database does not know it is wrong. You either schedule a job that goes looking, or you find out when a user tells you a row they swear existed is gone.

So this post is half incidents, half detection. The detection is the point.

Multixacts: the wraparound nobody watches

This is the one that scares me most, so it goes first. When more than one transaction holds a row-level lock on the same row, Postgres cannot record a single transaction ID on that row. It allocates a MultiXactId, a small object that names the set of transactions involved, and stores that instead. SELECT ... FOR SHARE, SELECT ... FOR UPDATE, and the FOR KEY SHARE locks that foreign-key checks take all create them.

Postgres 9.3 leaned on this much harder than any version before it. It added gentler row-lock modes so foreign-key checks stopped blocking each other, and that machinery ran on multixacts. The feature was good. The implementation took a while to settle, and the settling happened in production. Across the early 9.3 minor releases a series of multixact bugs got fixed, with the heavy data-integrity work landing in 9.3.5 in July 2014: wraparound handling for pg_multixact/members, and a change to truncate pg_multixact during checkpoints instead of during VACUUM, specifically so segments still needed for WAL replay after a crash could not be removed early. If you ran 9.3 at point-zero, you ran the version before those fixes existed.

The lesson that outlived 9.3 is structural. Mul

On March 8, 2026, British Columbia moved their clocks to a year-round Pacific Daylight Savings Time. In March, they did the spring forward one hour with their clocks to UTC-7, but they won't fall back to UTC-8 in November. Going forward, the UTC offset for America/Vancouver timezone is permanently UTC-7.

Let's use this as an opportunity to talk about date and time zone storage. In the most basic examples, the default is to store the UTC value, then calculate local time relative to UTC. However, people using calendar systems think in terms of local time (i.e. wall clock time), and never consider UTC. After modifying time zone data, these time calculations from UTC for a region will differ from the user's input value.

If you stored timestamps in a UTC-based column for British Columbia-based appointment in 2026 and beyond, your November through March appointments may be off by an hour!

See timestamptz columns don't store the local time. They store the UTC time, and the timezone is only used to convert to and from UTC when inserting and querying. If you stored a future appointment as a timestamptz in the America/Vancouver timezone, it was converted to UTC using the rules at the time of storage. When you query that appointment later, it converts back to local time using the current rules. If the rules changed from storage to query, the local time you get back is not what the user originally intended.

If you've not updated your tzdata package, then Postgres doesn't know about the change, and it will continue to convert using the old rules. How often are the tzdata packages in Ubuntu updated? Surprisingly, every few months.

If your columns are stored in timestamptz column types and work with customers in British Columbia, use the following SQL query to determine if the tzdata package has been updated:

SELECT
  to_char(
    '2026-12-01 10:00:00'::timestamp AT TIME ZONE 'America/Vancouver',
    'HH24:MI:SS OF'
  ) AS november_2026_vancouver_offset;

If the value is 17:00:00 +00, then tzdata

I'm lately back from Chicago, where PG DATA 2026 is now in the books! This is the two-day successor to PGDay Chicago, where I've been involved for the past few years volunteering, speaking, and finally organizing (and also volunteering and speaking). Everything went blessedly smoothly with only a few close calls, and we're already starting to plan next year's event.

I've been on program committees for a few years now: besides prior PGDays in Chicago, I participated in the committees for PGConf EU Athens in 2024 and PGDay Armenia earlier this year. PG DATA is my first time chairing the committee, and I am immensely thankful for Karen Jex's tutelage. This is just long enough, just widely enough, at just the right historical moment, to have a front-row seat for a transition that's making talk selection much, much harder.

Fundamentally, assembling a conference program has worked like this: interested speakers submit abstracts that describe briefly their topic and goal. The committee deliberates, usually through some kind of voting mechanism. They rank a longlist, then draw a shortlist from it attempting to balance factors including topic variety, skill level coverage, diversity of speaker affiliation (half your speakers being from a single company doesn't look great at community-run conferences), and more. This makes up the bank of accepted talks and the reserve list in case accepted speakers cancel. Once speakers confirm acceptance, the committee plays schedule Tetris until things look like an event people want to go to.

It still works like that. What's changed, as far as talk selection goes, is that it is now possible to generate a superficially polished abstract with minimal effort and near-zero subject matter knowledge. You can demand an LLM "generate an abstract for an XYZ conference" with no more context than that, and receive a statistically plausible derivative of the conference abstracts and information about XYZ it was trained on. It will flow like a real abstract, it will build up something