more...

Due to an unfortunate recent visitation by the Virus of the Decade (so far), I have a backlog of these which I'm trying to work through, so in the remote chance anyone is waiting with bated breath for the newest editions, my apologies. Normal service will be resumed as soon as humanly possible.

more...

Almost a month late, but I hope you enjoy it!

In one of our earlier blogs, we explored how improper volatility settings in PL/pgSQL functions — namely using IMMUTABLE, STABLE, or VOLATILE — can lead to unexpected behavior and performance issues during migrations.

Today, let’s revisit that topic from a slightly different lens. This time, we’re not talking about your user-defined functions, but the ones PostgreSQL itself provides — and how their volatility can quietly shape your query performance, especially when you’re using partitioned tables.

When Partitioning Doesn’t Perform

When designing partitioning strategies, performance is usually the first thing that comes to mind.
But what if despite all your tuning the queries still fail to leverage partition pruning effectively?

The answer sometimes lies not in your schema or indexes, but in how your filters are evaluated.

Every function you use in a filter has its own volatility behavior that determines how it’s planned and executed. You might pick the right filter logically — but if the function is volatile, it can completely change how PostgreSQL builds and optimizes the plan.

Let’s say you’ve designed a table as daily partitions and use Date Functions as partitions filters and check its underlying executions plan.

CREATE TABLE IF NOT EXISTS events_daily (
    id bigint generated always as identity ,
    created_at timestamptz NOT NULL,
    created_date date NOT NULL,      
    user_id int NOT NULL,
    amount numeric(10,2) NOT NULL,
    status text,
    payload text
) PARTITION BY RANGE (created_date);

--create a year of partitions as daily
DO
$$
DECLARE
    start_date date := current_date - INTERVAL '364 days';
    d date;
    part_name text;
BEGIN
    d := start_date;
    WHILE d <= current_date LOOP
        part_name := format('events_p_%s', to_char(d, 'YYYYMMDD'));
        EXECUTE format($sql$
            CREATE TABLE IF NOT EXISTS %I PARTITION OF events_daily
            FOR VALUES FROM (%L) TO (%L);
        $sql$, part_name, d::text, (d + INTERVAL '1 day')::da

As a database consulting company, we are often faced with analytics and reporting related tasks which seem to be easy on the surface but are in reality not that trivial. The number of those seemingly simple things is longer than one might think, especially in the area of reporting

The purpose of this post is not to share some fancy SQL techniques, some super cool magic AI tool that does whatever new hot thing or some crazy coding technique one can use in PostgreSQL. This is all about two simple things that are often forgotten or just neglected. We are of course talking about “awareness” and “clarity”.

Yes, it is that simple. If you don’t know what you have or if you don’t know what you want, all data is worthless (in a best case scenario) or even dangerous. 

Simple Things: Counting Customers

One of those seemingly simple things might be related to a trivial question: “How many customers do you have?”. Every CEO will proudly talk about this number but have you ever asked those guys a simple question: “What is a customer?”.

Well, just try. In my experience the process often starts with an arrogant smile that indicates “What stupid question is that?”. But just dig deeper and keep asking for details. I can assure you: In the majority of cases, people have no clue what they are talking about.

Here are some typical scenarios:

• If a person has not ordered for a long time, when do you stop counting him as a customer?
• If two customers merge into a single company and rebrand: How many customers do you have?
• If a customer has ordered an item and sent it back immediately without paying for it. Is it a customer?

One could continue this list forever and a day. In other words: It is not that simple and many "business people” tend to think that displaying things in a pie chart has the power to change the fundamental problem: “What do you actually count?”

Example: Counting Customers in Banking

The following data structure shows a simple scenario: A bank wants t

Why checksums matter

Beyond Start and End columns with PostgreSQL range types

One of the most read articles at boringSQL is Time to Better Know The Time in PostgreSQL where we dived into the complexities of storing and handling time operations in PostgreSQL. While the article introduced the range data types, there's so much more to them. And not only for handling time ranges. In this article we will cover why to consider range types and how to work with them.

Bug Not Invented Here

But before we can talk about the range types, let's try to understand why we should look at them in the first place. Let's imagine a booking platform for large flash sales of the seats, that goes live at 10pm and will be taken by storm by thousands of people who want to get their tickets.

CREATE TABLE seat_holds (
    hold_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    seat_id INTEGER NOT NULL REFERENCES seats(id),
    user_session_id UUID NOT NULL,
    -- define the hold period explicitly
    hold_started_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    hold_expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX seat_holds_on_seat_id ON seat_holds(seat_id);
CREATE INDEX seat_holds_on_expiration ON seat_holds(hold_expires_at);

While the table design looks perfectly reasonable, it has one serious flaw - there's no database-level atomicity guarantee there to prevent two holds for the same seat_id at the same time. The table design requires on application logic to check for the existing holds before inserting a new hold, and at the same time it does not provide any high-concurrency guarantee.

If all you have in your toolbelt are those two columns you will end up increasing the complexity to make it work. You started with one problem and soon your application developers might want to ask you to add caching layer (most likely external K/V store) to place the holds there and very soon you have N-problems when you will resort to building a complex, custom application

PostgreSQL received attention through the following contributions at Data Stack Conf 2025 on Oct 29, 2025:

Speaker

• Radoslav Stanoev
• Pavlo Golub
• Lætitia Avrot
• Valeria Bogatyreva
• Devrim Gündüz

PostgreSQL Booth Staff

• Devrim Gündüz
• Pavlo Golub

Gabriele Quaresima spoke at Cloud Native Bergen on Tuesday, October 28, 2025.

Community Blog Posts

• Mayuresh B: Slonik on the Catwalk: PGConf.EU 2025 Recap

• Floor Drees: My PGConf EU 2025 experience

• Cornelia Biacsics: The PostgreSQL Village and Head of Marketing experiences @PGConf.EU 2025 – A Riga Recap

• Jan Karremans: The Pulse of PostgreSQL: A Global Perspective from PGConf.EU 2025

• Rafia Sabih: A Glimpse of PostgreSQL Conference Europe 2025

• Valeria Kaplan: PostgreSQL — Blurring the Line Between Mine and Ours

 I wrote a post in my MySQL blog on migrating from MySQL to PostgreSQL using DBeaver. You can pass it along to your acquaintances who want to get off the Dolphin and on the Elephant. 

Not only will DBeaver move your tables and data, but you can compare them afterwards. In the post, I outline the process in five steps. DBeaver will let you do it in four. 

Please share this blog with your friends to promote PostgreSQL. 

High availability in PostgreSQL doesn't always require a globally distributed architecture. Sometimes you need reliable failover and replication within a single datacentre or region. pgEdge Enterprise Postgres handles this scenario with a production-ready PostgreSQL distribution that includes the tools you need for high availability out of the box.

What You Get

High Availability Through Physical and Logical Replication

Spock: Multi-Master Replication for High Availability

From time to time I get to investigate issues with some sort of a batch process. It’s getting more and more common that such processes use very high memory limits (maintenance_work_mem and work_mem). I suppose some DBAs follow the logic that “more is better”, not realizing it can hurt the performance quite a bit.

Let me demonstrate this using an example I ran across while testing a fix for parallel builds of GIN indexes. The bug is not particularly interesting or complex, but it required a fairly high value for maintenance_work_mem (the initial report used 20GB).

In #PostgresMarathon 2-009 and #PostgresMarathon 2-010, we explored why execution 6 causes a lock explosion when building a generic plan for partitioned tables — the planner must lock all 52 relations because it can't prune without parameter values.

Today we'll test what actually happens with different plan_cache_mode settings.

Let's test empirically on Postgres 18 with the 12-partition table from the previous posts. This time, we'll insert 1M rows into each partition to get realistic query plans with index scans:

-- Insert 1M rows into each partition
do $$
declare
  i int;
  start_date date;
  partition_name text;
begin
  for i in 0..11 loop
    start_date := '2024-01-01'::date + make_interval(months => i);
    partition_name := 'events_' || to_char(start_date, 'YYYY_MM');

    execute format(
      'insert into %I (event_id, event_time, event_data)
       select s, %L::timestamptz + (s * interval ''1 second''), ''data_'' || s
       from generate_series(1, 1000000) s',
      partition_name, start_date
    );
  end loop;
end $$;

vacuum analyze events;

Now let's see what different plan_cache_mode settings do.

Test 1: auto mode

As we already saw, with the default auto mode setting, Postgres decides whether to use custom or generic plans based on cost comparison. We saw, that first 5 runs it uses custom plan, then, on the important 6th call, it builds the generic plan (and we have the "lock explosion" we already studied), and then, on 7th call and further, it uses the generic plan.

But that was on empty tables. Here things will be different, because we have data. Let's see.

As before, prepare a statement and execute it multiple times, observing the number of relation-level locks and plan type counters:

prepare test (timestamptz) as
select event_id, event_data from events where event_time = $1;

-- Run this snippet to test each execution
begin;
explain (verbose) execute test(timestamptz '2024-06-06 00:00:00+00');

select
  count(*) as lock_count,
  array_agg(
    distinct relation::reg

How-to guide for installing PostgreSQL version 18 on Ubuntu, after a fresh installation of version 25.10 (Questing Quokka).

In #PostgresMarathon 2-009, we focused on Lock Manager's behavior when dealing with prepared statements and partitioned tables.

And observed a lock explosion in our simple synthetic example: from 8 locks (custom plans) during first 5 calls, to 52 locks (building generic plan) in the 6th call, to 13 locks (using cached generic plan) in the 7th and subsequent calls. We left with questions:

• this lock explosion at the 6th call – why is it so exactly and can it be avoided?
• why do we lock all 12 partitions even though runtime pruning removes 11 of them?

Let's dig deeper.

The 6th call: why 52 locks?​

In #PostgresMarathon 2-008, we studied the code flow for unpartitioned tables. The same pattern applies here, but with a critical difference: while for the first 5 calls we had very efficient planning-time partition pruning, it is not used during generic plan building in the 6th call.

Let's trace the execution (using PG18 sources; // comments are mine):

Step 1: Acquire planner locks

GetCachedPlan() starts by locking the Query tree via AcquirePlannerLocks():

AcquirePlannerLocks(plansource->query_list, true);

The Query tree (parser output) contains only the parent table reference. Result -- 4 locks acquired (parent table + 3 parent indexes). Partition locks will be acquired later during planning.

Step 2: plan type decision

Function choose_custom_plan() decides whether to use custom or generic plan:

if (plansource->num_custom_plans < 5)
    return true;  // NOT taken (num_custom_plans = 5)

if (plansource->generic_cost < avg_custom_cost)
    return false;  // TAKEN (generic_cost = -1, meaning "not yet calculated")

Result here: use generic plan.

Step 3: build generic plan

Since no cached plan exists yet (CheckCachedPlan() returns false), we build one:

plan = BuildCachedPlan(plansource, qlist, NULL, queryEnv);
                                          // ^^^^ NULL = no bound parameters

Insi

PostgreSQL Conference Europe 2025 in Riga has officially come to an end — and what remains are the impressions, emotions, moments, and the incredible vibe that remind me why we do what we do.

I met so many inspiring people, had conversations with some of my role models, and wore many hats throughout the conference.

Preparations and Looking Ahead

PostgreSQL Conference Europe is an annual event — and funnily enough, it was also the last time I had used my large suitcase for a trip abroad. So, in a way, this year’s conference experience began a day before I even left Austria, when I pulled out that same suitcase and noticed the old airport baggage tag still attached from PGConf.EU 2024 in Athens. That small detail instantly brought back a wave of memories — and the excitement for Riga began right there.

This time, I was traveling with two large bags — one for my personal items (six days in a country colder than Austria!) and one filled with CYBERTEC marketing swag, booth material, and a roll-up banner on my shoulder.

At the Vienna (Schwechat) departure gate, I met Pavlo Golub and Svitlana Lytvynenko, who were on the same flight. Other colleagues departed from different parts of the world — CYBERTEC truly is a global team. In total, 20 colleagues attended PGConf.EU 2025, and I was looking forward to reconnecting in person — without plastic screens between us. Which is true for all the other people in the community I got to know over the past years - it’s quite a challenge to catch up with everyone in just a few days!

Arrival in Riga

We flew with Air Baltic, and apart from one small scare — a colleague’s plane experienced an engine failure right on the runway before takeoff — everyone eventually made it safely to Riga.

My own travel day started early: I left home at 5 am., and by the time I arrived at the conference hotel around noon, I already felt like I’d been on the move forever. (For context, Riga is on GMT+3, while Austria is GMT+2 in summer — so there was a bit

In #PostgresMarathon 2-008, we discovered that prepared statements can dramatically reduce LWLock:LockManager contention by switching from planner locks (which lock everything) to executor locks (which lock only what's actually used). Starting with execution 7, we saw locks drop from 6 (table + 5 indexes) to just 1 (table only).

There we tested only a simple, unpartitioned table. What happens if the table is partitioned?

The following was tested on Postgres 18.0 with default settings:

• enable_partition_pruning = on
• plan_cache_mode = auto

Postgres behavior in this field might change in the future — there are WIP patches optimizing performance.

Let's create a simple partitioned table with multiple partitions:

create table events (
  event_id bigint,
  event_time timestamptz,
  event_data text
) partition by range (event_time);

-- Create 12 monthly partitions
do $$
declare
  i int;
  start_date date;
  end_date date;
begin
  for i in 0..11 loop
    start_date := '2024-01-01'::date + make_interval(months => i);
    end_date := start_date + interval '1 month';
    
    execute format(
      'create table events_%s partition of events for values from (%L) to (%L)',
      to_char(start_date, 'YYYY_MM'),
      start_date,
      end_date
    );
  end loop;
end $$;

Result:

test=# \d+ events
                                              Partitioned table "public.events"
   Column   |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 event_id   | bigint                   |           |          |         | plain    |             |              |
 event_time | timestamp with time zone |           |          |         | plain    |             |              |
 event_data | text                     |           |          |         | extended |             |              |
Partition key: RANGE (event_

For this second entry into our blog feature to celebrate 25 years of CYBERTEC, our CEO gave some interesting insights into what he expects of the future - for the company as well as PostgreSQL as a whole.

CYBERTEC in another 25 years

Interviewer: Let's talk about the future. Where do you think you see CYBERTEC in another 25 years? 

Hans: If I would make a prediction, it would be wrong for sure, but certainly… larger. Certainly, if this is even possible at this point, more international, and definitely the same technology, I would say, but with more ecosystem around it.

More locations, hopefully not more processes. Hopefully similar vibes and hopefully in the same place - physically, I mean.

Interviewer: Alright, so moving away from CYBERTEC a little, where do you see yourself personally in 25 years?

Hans: I don't know. Hopefully alive. [laughs] 

Interviewer: That's a great goal to have. [laughs] 

Hans: In 25 years I’m 72. I mean, hopefully alive, right?

Interviewer: Yeah. 

Hans: I don't know. Wouldn't it be damn boring if you would know?

Interviewer: That's true. 

Hans: Why would you wanna know? I mean, I believe that if you wanna know exactly what's gonna happen in 20 years, you have to become a bureaucrat. Otherwise, you don't want to know, I believe. I believe you don't. Even if everything is good, you don't wanna know because that would destroy the fun, I believe. 

Interviewer: Okay. Then, looking at a closer future, what would you still like to achieve?

Hans: It's not about achievement. It's not about achievement because achievement is an ego thing. “If I do this, I will be more happy,” whatever. No, achievement, as in big philosophy, I'm not sure if it's about achievement, it's about doing the right thing. Is doing the right thing an achievement? I don’t know, that's quite philosophical, I would say. 

But doing the right thing, I don't think that's an achievement. That's something you should do, you know? It’s not about achievements, like: “I ne

Postgres database-level “synchronous replication” does not actually mean the replication is synchronous. It’s a bit of a lie really. The replication is actually – always – asynchronous. What it actually means is “when the client issues a COMMIT then pause until we know the transaction is replicated.” In fact the primary writer database doesn’t need to wait for the replicas to catch up UNTIL the client issues a COMMIT …and even then it’s only a single individual connection which waits. This has many interesting properties.

One benefit is throughput and performance. It means that much of the database workload is actually asynchronous – which tends to work pretty well. The replication stream operates in parallel to the primary workload.

But an interesting drawback is that you can get into situation where the primary can speed ahead of the replica quite a bit before that COMMIT statement hits and then the specific client who issued the COMMIT will need to sit and wait for awhile. It also means that bulk operations like pg_repack or VACUUM FULL or REFRESH MATERIALIZED VIEW or COPY do not have anything to throttle them. They will generate WAL basically as fast as it can be written to the local disk. In the mean time, everybody else on the system will see their COMMIT operations start to exhibit dramatic hangs and will see apparent sudden performance drops – while they wait for their commit record to eventually get replicated by a lagging replication stream. It can be non-obvious that this performance degradation is completely unrelated to the queries that appear to be slowing down. This is the infamous IPC:SyncRep wait event.

Another drawback: as the replication stream begins to lag, the amount of disk needed for WAL storage balloons. This makes it challenging to predict the required size of a dedicated volume for WAL. A system might seem to have lots of headroom, and then a pg_repack on a large table might fill the WAL volume without warning.

This is a bit different from storage-level synchronous re

Creating an extension for Postgres is an experience worthy of immense satisfaction. You get to contribute to the extension ecosystem while providing valuable functionality to other Postgres users. It’s also an incredibly challenging exercise in many ways, so we’re glad you’ve returned to learn a bit more about building Postgres extensions.In the previous article in this series, we discussed creating an extension to block DDL. That sample extension was admittedly fairly trivial, in that it only added a single configuration parameter and utilized one callback hook. A more complete extension would provide a function or view to Postgres so users could interact with the extension itself. So let’s do just that!

Deciding on Data

Starting the Extension

In October 2025, PostgreSQL Conference Europe brought the community together in Riga, Latvia from the 21st to the 24th.

Organizers

• Andreas Scherbaum
• Chris Ellis
• Dave Page
• Ilya Kosmodemiansky
• Jimmy Angelakos
• Karen Jex
• Magnus Hagander
• Marc Linster
• Samed Yildirim
• Valeria Kaplan

Talk selection committee

• Karen Jex (non voting chair)

Application Developer and Community Subcommittee

• Dian Fay
• Hari P Kiran
• Johannes Paul
• Kai Wagner

Postgres Internals and DBA Subcommittee

• Anastasia Lubennikova
• Dirk Krautschick
• Samed Yildirim
• Teresa Lopes

Code of Conduct Committee

• Floor Drees
• Pavlo Golub
• Celeste Horgan

Community Events Day

Extensions Showcase - Organizers

• Alastair Turner
• David Wheeler
• Floor Drees
• Yurii Rashkovskii

PostgreSQL on Kubernetes Summit - Organizers

• Floor Drees
• Gülçin Yıldırım Jelinek

Speakers

• Gülçin Yıldırım Jelínek
• Floor Drees
• Oleksii Kliukin
• Polina Bungina
• Thomas Boussekey
• Pavel Lukasenko
• Danish Khan
• Esther Minano
• Gabriele Bartolini
• Andrew Farries
• Jonathan Gonzalez V.
• Jonathan Battiato

Establishing the PostgreSQL standard: What's Postgres compatible? - Organizers

• Jimmy Angelakos
• Henrietta Dombrovskaya
• Boriss Mejías

Community Summit – PGConf EU Edition - Organizers

• Teresa Giacomini
• Andreas Scherbaum

Crafty Slonik - Organized by

• Chris Ellis

PostgreSQL & AI Summit - Organizers

• Gülçin Yıldırım Jelinek
• Ellyne Phneah
• Torsten Steinbach

Speakers

• Ellyne Phneah
• Marc Linster
• Bruce Momjian
• Torsten Steinbach
• Gulcin Yildirim Jelinek
• Miguel Toscano
• Luigi Nardi
• Bertrand Hartwig-Peillon
• Daniel Krefl
• Adrien Obernesser
• Jimmy Angelakos
• Mohsin Ejaz
• Gleb Otochkin
• Erik Hellsten

Panel Discussion – Moderator: Torsten Steinbach, Panelist: Gul