Latest Blog Posts

Why PostgreSQL needs an AI usage policy
Posted by Jan Wieremjewicz in Percona on 2026-06-26 at 08:15

We often hear that open source is about people.

People who contribute their time and, in a way, parts of their lives to work on software that is available for everyone without limitations and without licensing costs.

The more popular a project becomes, the more often we also hear about the need for sustainable open source. Nothing surprising here. Often projects start off as “scratching ones itch” and it’s very appreciated when others notice the work done. The more time passes and the more the work becomes appreciated, the higher the chances that there will be a need to spend more time on the project.

Looking Forward to Postgres 19: Split Personality
Posted by Shaun Thomas in pgEdge on 2026-06-26 at 07:17

Postgres has had native support for declarative partitions since version 10, and every release since has filed off another rough edge. We got partition-wise joins, default partitions, hash partitioning, and the ability to attach and detach partitions concurrently. By any reasonable measure, declarative partitioning is one of the great success stories of modern Postgres.Despite the power here, it's always been a kind of one-way ratchet. Creating or dropping partitions was easy. But reorganizing existing ones was a different beast entirely. There's no syntax or tool to assist in the event we want to convert a table to a partition set, or revise an existing design. Instead, it's just a long series of manual statements to carve the table up into partitions, or perhaps leveraging pg_partman to do it instead.Well, Postgres 19 finally has an answer to that. Let's talk about the current state of the art, and how the new  and  syntax for ALTER TABLE may change the story.

The Way Things Were

Suppose we have a busy partition and want to break it into smaller pieces. The current procedure works something like this:
  • Create new partitions as standalone tables.
  • Copy the relevant rows into each one.
  • Detach the old partition.
  • Attach the new ones.
  • Drop the empty table husk.
It's serviceable, but also tedious, error-prone, and subject to various locks that can delay the process. Postgres 19 collapses that whole routine into one statement.Before diving into how, let's create something realistic to play with. How about an analytics pipeline containing a stream of events partitioned by time? The initial state will be one partition per quarter for 2026:Now let's inject a single event into every day of the year so each partition has something to hold:Let's assume that a quarter felt reasonable when we drew it up, but traffic in Q1 turned out to be heavier than expected. Now, querying three months of data to find a single morning's worth of clicks is wasteful. We'd much rather have monthly partiti[...]

All Your GUCs in a Row: enable_gathermerge
Posted by Christophe Pettus in pgExperts on 2026-06-26 at 01:00
Disable `enable_gathermerge` to diagnose whether a slow parallel query's bottleneck is the leader-side merge step or something deeper—like worker memory…

PostgreSQL Disaster Recovery with pgBackRest TLS Transport
Posted by SHRIDHAR KHANAL in Stormatics on 2026-06-25 at 09:45

The Night When Things Almost Went Down

Have you ever left for home on a Friday evening feeling confident about your work for the day, at peace knowing your system would survive the coming weekend? We’ve all felt that way at some point.

Meanwhile, the disk on the server had quietly reached 90% utilization. Write-Ahead Log (WAL) files had accumulated enormously, one long-running query had been running for over an hour, and nobody noticed because, some time earlier, the dashboard had looked fine.

After a while, writes to the database started to fail, and you received messages like, “Hey, the DB seems a bit slow?” In reality, the database was no longer slow; it had already gone down.

However, this wasn’t anything new. The signs of trouble had been there the entire time. The monitoring simply wasn’t connected to the right signals to raise the alarm.

And that’s what this post is about.

Dashboards vs Alerts

Let’s be honest. Dashboards are great for screenshots.

However, they make one risky assumption that someone will make this check before it’s too late. Alerts make no such assumption.

  • Dashboards = “Here’s what’s happening.”
  • Alerts = “There is something wrong. Need to address it.”

A dashboard gently shows an increasing CPU. But an alert rings, saying: “This has been bad for 30 minutes, wake up.”

Dashboards are waiting for us. But alerts find us. After a few incidents, we no longer rely on the dashboard to keep us safe while asleep. It’s through alerts that it happens.

Configuring Alerts in PMM

These rules are set up by going to Alerting → Alert Rules in the PMM interface and creating a New Alert Rule. For every alert, we need to configure three things:

Duration: How long the condition must hold before the alert fires. This filters out temporary sp

[...]

Heterogeneous Graphs in SQL/PGQ on PostgreSQL 19
Posted by Hans-Juergen Schoenig in Cybertec on 2026-06-25 at 05:00

In the previous post, we discussed the basic syntax of graph queries in PostgreSQL 19 and discussed some simple SQL/PGQ examples. However, more often than not applications are more complex and need more than just two basic tables. Therefore, we want to dig a little deeper and see how we can write more complex queries and model some more sophisticated graphs based on real world scenarios.

Loading sample data for graphs

To demonstrate a couple queries, which happen to be increasingly advanced, we have compiled some simple test data. The idea of the following data structure is to store friendship as well as work relations: 

-- People (six rows, three cities)
CREATE TABLE person (
    id          int  PRIMARY KEY,
    name        text NOT NULL,
    age         int  NOT NULL,
    city        text NOT NULL
);
INSERT INTO person VALUES
    (1, 'Alice', 30, 'Berlin'),
    (2, 'Bob',   25, 'Berlin'),
    (3, 'Carol', 35, 'Paris'),
    (4, 'Dan',   28, 'Paris'),
    (5, 'Eve',   40, 'London'),
    (6, 'Frank', 33, 'London');

-- "Knows" edges between people (nine rows, two mutual pairs)
CREATE TABLE knows (
    a     int NOT NULL REFERENCES person(id),
    b     int NOT NULL REFERENCES person(id),
    since int NOT NULL,
    PRIMARY KEY (a, b)
);
INSERT INTO knows VALUES
    (1, 2, 2018), (2, 1, 2019),
    (1, 3, 2020), (2, 3, 2020), (3, 2, 2021),
    (3, 4, 2021), (4, 5, 2022),
    (5, 6, 2019), (6, 1, 2023);

-- Companies (the new vertex type for this post)
CREATE TABLE company (
    id          int  PRIMARY KEY,
    name        text NOT NULL,
    industry    text NOT NULL
);
INSERT INTO company VALUES
    (10, 'Acme',    'manufacturing'),
    (20, 'Globex',  'finance'),
    (30, 'Initech', 'software');

-- "Works at" edges (the new edge type for this post)
CREATE TABLE works_at (
    pid                 int  NOT NULL REFERENCES person(id),
    cid                 int  NOT NULL REFERENCES company(id),
    role                text NOT NULL,
    since               int  NOT NULL,
    PRIMARY KEY (pid, cid)
);
[...]

All Your GUCs in a Row: enable_distinct_reordering and enable_group_by_reordering
Posted by Christophe Pettus in pgExperts on 2026-06-25 at 01:00
Reorder GROUP BY and DISTINCT keys to cut comparison costs and skip sorts—new optimizations in PostgreSQL 17 and 18 that usually stay invisible but…

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

Part 2: Planning, Tool Use, and Reflection

Key takeaways

  • Memory gives an agent continuity. It does not give it judgment. Planning, tool use, and reflection are the three capabilities that turn a well-informed model into a system that can act on a real business process.
  • Treat the execution plan as an artifact, not a transcript. If you can’t persist it, version it, and pause it for human approval, it isn’t a plan — it’s improvisation with extra steps.
  • A tool named ‘run a database query’ is not a tool. Slapping a JSON schema on it doesn’t make it one – it just makes the liability look documented.
  • Never let a model generate and execute arbitrary SQL against production data. The agent proposes; the platform decides.
  • Reflection only works if “good” and “bad” are defined before the agent runs — not adjudicated after the fact by asking a second model “does this look right?”

Abstract

Memory gives an AI agent continuity, but memory alone does not enable useful action. An enterprise agent must also determine what needs to be done, select the right systems and tools, execute each step safely, inspect the results, and revise its approach when reality doesn’t match its assumptions.

That’s three capabilities: planning, tool use, and reflection.

Planning translates a business goal into a sequence of executable steps. Tool use connects probabilistic model reasoning to deterministic systems — databases, APIs, workflow engines, code execution. Reflection lets the agent evaluate its own output, catch incomplete or unsafe results, and decide whether to retry, revise, or escalate.

Together, these move an agent past question-answering and into real business process participation. This installment uses PostgreSQL throughout to show how enterprise agents can query operational data, validate generated SQL, preserve execution state, and leave an auditable trail behind every action.

Memory Is Not Enough

In Part 1, we covered why the model is not the system, and why memory is

[...]

cygnet: A small but fierce ORM
Posted by Christophe Pettus in pgExperts on 2026-06-24 at 15:00
Cygnet is a PostgreSQL ORM for async Python that refuses to hide the SQL.

MCP For PostgreSQL: Automated Health Checks & Performance Analysis
Posted by warda bibi in Stormatics on 2026-06-24 at 11:32

AI agents are becoming increasingly capable at operational tasks: summarizing logs, analyzing query plans, identifying anomalies, and assisting with incident response. For databases in particular, this creates an obvious opportunity. Much of day-to-day troubleshooting follows repeatable workflows that lend themselves well to automation.

As someone who spends most of my time working with PostgreSQL, I don’t think the interesting question is whether an LLM can help analyze a slow database. It can. The harder question is how to make that useful in production without making it unsafe.

Production databases sit behind layers of controls, processes, and accountability. Access is granted carefully because mistakes are expensive. When an engineer investigates an incident, that trust comes from experience and clearly defined responsibilities. Extending those capabilities to an AI agent raises a different challenge: how do you give it enough access to be useful without giving it enough access to be dangerous?

That problem is exactly what Model Context Protocol (MCP) attempts to address. Rather than exposing a database directly to an LLM, MCP introduces a layer of controlled capabilities. Instead of unrestricted access, the model receives a set of predefined tools with well-defined boundaries.

The LLM performs the reasoning. The server performs the measurements.

I’ve been experimenting with AI-assisted PostgreSQL troubleshooting for some time, and one project that stands out is postgres-mcp. It combines deterministic diagnostics, workload analysis, and access controls in a way that makes AI assistance practical without removing the human from the loop.

Before diving into the implementation, consider a familiar scenario. A production database starts running slowly, and the investigation usually begins with the same checklist:

  • Active queries
  • Wait events
  • Locks
  • Buffer cache hit rate
[...]

The Long Road to Bottomless Postgres
Posted by Shaun Thomas in pgEdge on 2026-06-24 at 10:05

Every database eventually runs into the same wall: storage costs money, and the data nobody queries anymore costs exactly as much as the data everyone does. A five-year-old row occupies the same expensive block storage as the order that came in thirty seconds ago. Postgres doesn't know the difference, and why would it? That's honestly a common refrain among most database engines.As a result, many have dreamed of fixing this by decoupling compute from storage. Push the cold, ancient data down to cheap object storage like S3, keep the hot data local and fast, and let a single query span both. Better yet, store that cold data in an open columnar format for optimal analytics. It's a vision that launched an entire cottage industry of extensions, forks, and re-architectures, each one tackling the problem in its own particular way. But there are so many approaches now, and none have "the" definitive solution. Each contender seems to demand a different sacrifice, whether that's a forked binary, an extra daemon, a duplicate copy of table data, or eschewing writes entirely. Let's explore the external storage ecosystem and see what we find.

Perchance to Dream

So what does "bottomless" actually mean, and how do we get there?Postgres declarative partitioning offers the first potential tool in our arsenal. The ability to split data into definitive chunks means the planner can leverage pruning to focus on the most relevant data based on our predicates. From there, we can consider purposefully tiering data based on storage cost and demands. It's not a huge stretch of the imagination to use tablespaces to further federate "hot" and "cold" storage this way. No longer would our critical transaction-sensitive data cost the same as accumulated metrics useful only to analytics.It's possible to take that idea even further. In the most extreme cases, innovative users utilize Postgres Foreign Data Wrappers to present entirely remote content as local data. Some wrappers are better than others, but this method of decoupling further [...]

Happiness Hint: Alarm on Checkpoint Time
Posted by Jeremy Schneider on 2026-06-24 at 08:20

Before starting, I want to put a few things at the top:

  1. You probably should not change the checkpoint_timeout setting from its default of 5 minutes. Users who read tuning advice on the internet (or get bad AI advice) about increasing this setting usually don’t understand the risk and significance of trading for RTO/availability. Let’s be honest: many of us don’t scrutinize RTO until we have a real incident and suddenly realize that there is no way to get our application back online. Turns out it does matter to your boss if you’re down! Increasing this parameter can turn a short outage into a long multi-hour outage.
  2. Always make sure log_checkpoints is enabled. This has been the default since Postgres v15.
  3. With a default checkpoint_timeout setting of 5 minutes, I’m leaning toward a default alarming threshold of 15 minutes on total checkpoint time. You may need to increase disk hardware specs or optimize database workload, if checkpoints take too long.
    • There are two ways this alarm can be built – either by parsing checkpoint messages after they appear in the log, or by looking at the current time and checking how long since the last checkpoint message appeared. The latter is a more robust solution because it will alarm even if no checkpoint message is ever produced.
  4. Don’t forget to also monitor for “checkpoints are occurring too frequently” messages in the Postgres log. In some cases, you might consider increasing max_wal_size if checkpoints are frequent. Remember that it’s ok if there are short occasional bursts of write activity. The thing to watch for is real user & application impact due to extended throttling.

Checkpoint is the heart of your database. It’s buried deep inside. It’s not something everyone talks about, like well-tuned autovacuum or fast queries. But if checkpointer stops beating, then you’re dead.

In addition to its well-understood job of getting dirty pages written from cache to disk in the background, it also has many smaller jobs that

[...]

All Your GUCs in a Row: enable_indexscan and enable_bitmapscan
Posted by Christophe Pettus in pgExperts on 2026-06-24 at 01:00
Diagnose index scan performance problems by temporarily disabling index scans or bitmap scans and measuring what the planner chooses instead.

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.

Image

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.

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.