Latest Blog Posts

pg_clickhouse 0.3.2: Ready For Postgres 19
Posted by David Wheeler on 2026-06-23 at 16:14

I’ve got a new post over on the ClickHouse blog today: What’s New in pg_clickhouse v0.3.2: Postgres 19, TLS, Regex, and Memory. The big news is Postgres 19 support:

The topline change? Support for PostgreSQL 19 Beta1. The new Postgres version required relatively minor revisions to the pg_clickhouse source code to take advantage of tuple and array optimizations, remove old typedefs, add new headers, and some test outputs. And with that, we’ll be ready for the final Postgres release this fall and ship day one on Manged Postgres for ClickHouse.

Other new stuff in this release of pg_clickhouse, the interface for querying ClickHouse from Postgres, includes regular expression pushdown improvements TLS connection and binary protocol compression parameters, and various bug fixes. Get it from the usual sources:

Introducing pg_hardstorage: A New Community-Driven Approach to PostgreSQL Backup and Recovery
Posted by Hans-Juergen Schoenig in Cybertec on 2026-06-23 at 12:31

PostgreSQL today looks very different from the PostgreSQL many of us started working with over 25 years ago.

PostgreSQL was once primarily deployed on dedicated servers and virtual machines, and now runs across managed database services, Kubernetes platforms, cloud environments, hybrid infrastructures, and everything in between. Many organizations operate several of these models simultaneously, often while supporting growing data volumes and increasingly demanding recovery requirements. As PostgreSQL deployments have evolved, so have the conversations surrounding backup and recovery.

At CYBERTEC, those conversations have been taking place across customer environments, community discussions, architectural reviews, and operational workshops for many years. The result is pg_hardstorage, a new open-source community project focused on PostgreSQL backup and recovery.

The project reflects a collection of ideas, observations, and experiences gathered from working alongside PostgreSQL users operating in a rapidly changing landscape.

The Reason behind pg_hardstorage

The PostgreSQL ecosystem already has several excellent backup and recovery solutions.

Tools such as pgBackRest, Barman, WAL-G, and others have earned the trust of the community through years of development, operational experience, and continuous improvement. They remain an important part of how PostgreSQL is operated around the world today.

Which naturally leads to the question: why introduce another backup tool?

The answer is not that existing tools are lacking. In many ways, the strength of the PostgreSQL ecosystem comes from having multiple approaches to solving the same problem. Different organizations have different requirements, different operational models, and different priorities.

Over time, we found ourselves repeatedly discussing topics that reflected how PostgreSQL deployments continue to change. New deployment models, managed services, evolving operational practices, and changing expectations around backu

[...]

Some more thoughts on random_page_cost
Posted by Tomas Vondra on 2026-06-23 at 09:00

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.

All Your GUCs in a Row: enable_async_append
Posted by Christophe Pettus in pgExperts on 2026-06-23 at 01:00
Async append lets the planner fan out queries across remote shards in parallel instead of one at a time, but it's a diagnostic switch, not a tuning knob.

waxsql: Wax Fruit for Your Query Planner
Posted by Christophe Pettus in pgExperts on 2026-06-22 at 15:00
Generate valid SQL that looks real, nourishes nothing, and never spoils.

pg_stats: How Postgres Internal Stats Work
Posted by Richard Yen on 2026-06-22 at 08:00

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

[...]

Contributions for week 23 & 24, 2026
Posted by Cornelia Biacsics in postgres-contrib.org on 2026-06-22 at 06:41

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
[...]

All Your GUCs in a Row: effective_io_concurrency
Posted by Christophe Pettus in pgExperts on 2026-06-22 at 01:00
`effective_io_concurrency` has changed what it means twice—from a harmonic-series spindle count to a direct request depth to a real async I/O control.

PostGIS Tiger Geocoder 2025.1
Posted by Regina Obe in PostGIS on 2026-06-22 at 00:00

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.

PostgreSQL Berlin May 2026 Meetup
Posted by Andreas Scherbaum on 2026-06-21 at 22:00
On 7th of May, 2026, we had the PostgreSQL May Meetup in Berlin. AWS hosted it again, this time we had two speakers from UK and US. The Meetup took place in the Amazontower (EDGE East Side Tower Berlin) in Berlin, across the Uber Arena and with a view at the railway station Warschauer Straße. Celeste Horgan: pg_lake: Unifying transactional and analytical data with Postgres Celeste is Sr.

Replacing pgAgent with pg_timetable: Part 1
Posted by Regina Obe in PostGIS on 2026-06-21 at 07:01

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
Continue reading "Replacing pgAgent with pg_timetable: Part 1"

All Your GUCs in a Row: effective_cache_size
Posted by Christophe Pettus in pgExperts on 2026-06-21 at 01:00
effective_cache_size doesn't allocate memory, reserve RAM, or control runtime behavior—it merely whispers a number to the query planner to make it smarter…

Optimising Polymorphic Associations in PostgreSQL
Posted by Andrei Lepikhov in pgEdge on 2026-06-20 at 19:30

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).

Efficiency of a LEFT JOIN on a discriminated key
[...]

All Your GUCs in a Row: dynamic_shared_memory_type
Posted by Christophe Pettus in pgExperts on 2026-06-20 at 01:00
Parallel queries in PostgreSQL need shared memory sized at runtime, not startup.

Looking Forward to Postgres 19: Logically Sequenced
Posted by Shaun Thomas in pgEdge on 2026-06-19 at 11:01

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

There’s a sequence hiding behind every  or  column ever created in a Postgres cluster. That sequence provides the next value to the related column when no default is supplied. It’s easy to take for granted because it’s always been there and everyone knows how they work.Any time a write happens in Postgres, the new row contents get written to the WAL. Logical replication works by essentially decoding the raw WAL tuple into an equivalent  or  statement and replaying it on the subscriber. When a row with  arrives on the subscriber, that's the value that gets written. Meanwhile, the subscriber's own sequence object (probably copied from the publisher at some point) remains unused. That’s great until the subscriber has to generate a value of its own.Consider the migration including a table t[...]

All Your GUCs in a Row: dynamic_library_path
Posted by Christophe Pettus in pgExperts on 2026-06-19 at 01:00
PostgreSQL 18 finally made extensions truly relocatable by adding `extension_control_path` to match the long-existing `dynamic_library_path`.

It's Not Magic, It's Method
Posted by Lætitia AVROT on 2026-06-19 at 00:00
At PGDay France (2018 or 2019, I honestly can’t remember which one), I shared my method for evaluating a Postgres extension: read the source code, and ask yourself whether you understand it well enough to fix a small bug. Someone in the audience replied: “That’s only valid for Lætitia Avrot.” I’ve been thinking about that comment ever since. It’s not magic. It’s not some rare gift. It’s a method. Read. Understand.

pgsql_tweaks Version 1.0.4 Released
Posted by Stefanie Janine Stölting on 2026-06-18 at 22:00
  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.

Introducing ColdFront: Seamlessly Uniting OLTP, Analytics and AI Workloads on PostgreSQL
Posted by Antony Pegg in pgEdge on 2026-06-18 at 10:40

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

A bank holds seven years of transaction history because regulators say it has to. The table is 4 TB, growing monthly, and the queries that actually touch it hit the last 90 days. Everything older sits in the same PostgreSQL heap, inflating the storage bill, stretching the backup window, and slowing down every VACUUM cycle. The team knows the old data should live somewhere cheaper. They also know that "somewhere cheaper" usually means "somewhere you can't query with the same SQL anymore."A SaaS platform's compliance team gets a GDPR deletion request. The customer's records span three years. The recent ones delete fine. The older ones were archived to a cold tier six months ago, and that cold tier is read-only. To delete a single customer's data, they have to restore the archived partition back to hot storage, run the delete, re-archive, and re-verify. A one-line SQL statement turned into a half-day ops project.An AI governance platform is logging every decision trace from autonomous agents across a financial services deployment. The traces are append-heavy and growing fast. Retention is mandatory for regulatory audit. Most of the data is never read again, but when an agent acts on stale information and makes a bad recommendation, the provenance team needs to trace the decision back to the specific source record, correct it, and confirm the correction propagated. If that source record is in a read-only archive, the correction loop breaks.A growing startup is paying RDS storage r[...]

All Your GUCs in a Row: default_transaction_isolation and default_transaction_read_only
Posted by Christophe Pettus in pgExperts on 2026-06-18 at 01:00
PostgreSQL's isolation levels hold surprises: read uncommitted silently becomes read committed, repeatable read forbids phantom reads the standard permits, and…

The AI Agent Layer: Architecture, Implementation, and the Future of Intelligent Enterprise Systems
Posted by Vibhor Kumar on 2026-06-17 at 12:16

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.

  Theme Topics Covered
Part 1 ★ Foundations Introduction · Memory Architecture · Planning Overview
Part 2 Capabilities Tool Use · Reflection · RAG · PostgreSQL + pgvector
Part 3 Scale & Control Multi-Agent Systems · State Management · Security & Governance
Part 4 Production Observability · Production Roadmap · Future 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,

[...]

Your AI App Works on Postgres. Now Make It Production-Ready Without Starting Over
Posted by Antony Pegg in pgEdge on 2026-06-17 at 11:27

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

The prototype is deceptively simple. You have a table with your data, pgvector is installed as an extension, and an  column stores vectors alongside your regular business data. Something like this:HNSW (Hierarchical Navigable Small World) is the index type that makes approximate nearest-neighbor search fast enough for real workloads. You don't need to understand the algorithm. You need to know it works, and that it lives inside Postgres as a regular index on a regular table.Querying it is just SQL:That's the entire search layer. Your product catalog, your embeddings, your similarity search, all in one database, all queryable with standard SQL. This is the part that makes people fall in love with the Postgres approach to AI: you don't need a second database, a separate vector store, or a different query language. It's just Postgres.The problem is that "just Postgres" gets you through the proto[...]

All Your GUCs in a Row: default_transaction_deferrable
Posted by Christophe Pettus in pgExperts on 2026-06-17 at 01:00
PostgreSQL's `DEFERRABLE` transaction mode only works with `SERIALIZABLE READ ONLY` transactions, where it waits for a safe snapshot to eliminate…

Releasing pg_ducklake v1.0
Posted by Qiaosheng Liu on 2026-06-17 at 00:00
pg_ducklake 1.0 is production-ready: a standalone extension built on a reusable kernel, with broad DuckLake coverage and the fastest ingestion path for the lakehouse.

pgstream v1.1.0: Steps towards turning it into a service
Posted by Noémi Ványi in Xata on 2026-06-16 at 17:00
What's new in pgstream v1.1.0: env vars in YAML config, JSON logs, Kubernetes health probes, custom TLS for OpenSearch, and fixes for composite primary keys.

Scaling Rails at Aura Frames: Splitting to 8 Primary DBs and Reaching #1 in the App Store
Posted by Andrew Atkinson on 2026-06-16 at 13:15
📌 Overview

Ruby on Rails has helped make it possible to scale out the database layer, meeting the demands of millions of Aura Frames customers enjoying their digital photo frames.

In late 2025, the team added additional primary databases to expand capacity for peak write and read load ahead of Christmas Day, the busiest day of the year for the company. Rails manages queries and schema changes for each primary database within the same codebase, and now with the additional capacity of many primary databases.

With 8 primary databases in total, each server instance can be vertically scaled ahead of peak load. When load returns to normal levels, instances are scaled down for cost savings.

The team leveraged native support for Multiple Databases and the disable_joins: true feature in Active Record, the ORM for Ruby on Rails. The disable_joins feature replaces SQL joins, issuing multiple SELECT statements to combine data in the application from different databases.

This post looks back at the technical details of that plan, as well as a variety of additional data layer scaling tactics, that culminated in a successful Christmas 2025 season, with peak U.S. and Canadian Apple App Store and Google Play Store rankings of #1.

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

[...]

From Christmas Outage to #1 App Store Ranking: An Aura Frames Postgres Scaling Retrospective
Posted by Andrew Atkinson on 2026-06-16 at 13:15
📌 Overview

On Christmas Day 2024, Postgres infrastructure powering the Aura Frames API had problems under peak load, being unavailable for three hours and disrupting the experience for new customers. The team knew it would need improvements to handle the surge for Christmas 2025 and beyond.

One year later, much of the resource intensive data access was reworked, the Postgres infrastructure was upsized, and this approach not only survived, but thrived, providing reliable service through the holiday season.

The sum of Transactions Per Second (TPS) across the DBs peaked at 226,000, with more than 100K TPS sustained for 10 hours and repeating on multiple days after Christmas, with an average query time of 25 microseconds.

The improved reliability meant customers could smoothly set up new frames and add photos, and they did it more than ever, with the Aura Frames app reaching #1 in U.S. and Canadian Apple and Android App Stores on Christmas Day.

In this post we’ll look back at the months of planning and execution that went into achieving that outcome!

A second post in this series will dig into the Ruby on Rails side, while this one will focus on Postgres.

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

[...]

Postgres War Stories Part 2: multixact wraparound, TOAST corruption, and torn pages
Posted by Payal Singh in Instaclustr on 2026-06-16 at 13:00

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

[...]

British Columbia, Time Zones, and Postgres
Posted by Christopher Winslett in Crunchy Data on 2026-06-16 at 12:00

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!

Diagram of change in calculation

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

[...]

How To Run Logical Replication On A Production PostgreSQL Cluster
Posted by semab tariq in Stormatics on 2026-06-16 at 11:24

Upgrading PostgreSQL in production is not fun. You have real data, real users, and a version of Postgres that’s probably several major releases behind. The obvious approach (dump, upgrade, restore) works fine on a 10GB database on a Saturday night. It doesn’t work when you have TBs of data, applications that can’t afford hours of downtime, and a team watching the monitoring dashboard at 2am.

Logical replication solves this. I’ve run it in production, and it works well, but there are a few practical considerations that are easier to appreciate once you’ve been through the process yourself.

Why Logical Replication 

You keep your source cluster running (old Postgres version, production traffic, and all), bring up a destination cluster with the new version, and replicate all data changes while both run in parallel. When the destination has caught up, you cut over. Downtime is seconds to minutes, not hours.

Source is your existing cluster (say, Postgres 14). Destination is your new cluster running Postgres 17. 

The thing most guides skim over is that logical replication does not copy schema, it only copies data. PostgreSQL replicates INSERT, UPDATE, and DELETE operations, but not your table definitions, indexes, roles, or anything structural. An example would be if logical replication is setup then truncate operation on a single source table would also be replicated, but table1, table2, and table3, etc. won’t be replicated. You have to handle that yourself before replication starts. Miss this, and your subscription will either fail silently or throw confusing errors that don’t point you back to the actual problem.

Step 1: The Primary Key Check

Logical replication requires every replicated table to have a replica identity to replicate UPDATE and DELETE operations. By default, that’s the primary key. Tables without one will still replicate INSERTs, but any UPDATE or DELETE will error on the publ

[...]

Top posters

Number of posts in the past two months

Top teams

Number of posts in the past two months

Feeds

Planet

  • Policy for being listed on Planet PostgreSQL.
  • Add your blog to Planet PostgreSQL.
  • List of all subscribed blogs.
  • Manage your registration.

Contact

Get in touch with the Planet PostgreSQL administrators at planet at postgresql.org.