Latest Blog Posts

more...

I am writing this post over the weekend but scheduling it to be published on Tuesday, after the PG DATA CfP closes, because I do not want to distract anyone, including myself, from the submission process.

A couple of months ago, I created a placeholder in my GitHub, promising to publish pg_acm before the end of the year. The actual day I pushed the initial commit was January 3, but it still counts, right? At least, it happened before the first Monday of 2026!

It has been about two years since I first spoke publicly about additional options I would love to see in PostgreSQL privileges management. Now I know it was not the most brilliant idea to frame it as “what’s wrong with Postgres permissions,” and this time I am much better with naming.

pg_acm stands for “Postgres Access Control Management.” The key feature of this framework is that each schema is created with a set of predefined roles and default privileges, which makes it easy to achieve complete isolation between different projects sharing the same database, and allows authorized users to manage access to their data without having superuser privileges.

Please take a look, give it a try, and let me know what’s wrong with my framework

I do a fair amount of benchmarks as part of development, both on my own patches and while reviewing patches by others. That often requires dealing with noise, particularly for small optimizations. Here’s an overview of ways I use to filter out random variations / noise.

Most of the time it’s easy - the benefits are large and obvious. Great! But sometimes we need to care about cases when the changes are small (think less than 5%).

-- invalid page in a table:
pg_dump: error: query failed: ERROR: invalid page in block 0 of relation base/16384/66427
pg_dump: error: query was: SELECT last_value, is_called FROM public.test_table_bytea_id_seq

-- damaged system columns in a tuple:
pg_dump: error: Dumping the contents of table "test_table_bytea" failed: PQgetResult() failed.
pg_dump: error: Error message from server: ERROR: could not access status of transaction 3353862211
DETAIL: Could not open file "pg_xact/0C7E": No such file or directory.
pg_dump: error: The command was: COPY publ

Introduction

PostgreSQL has built-in support for logical replication. Unlike streaming replication, which works at the block level, logical replication replicates data changes based on replica-identities, usually primary keys, rather than exact block addresses or byte-by-byte copies.

PostgreSQL logical replication follows a publish–subscribe model. One or more subscribers can subscribe to one or more publications defined on a publisher node. Subscribers pull data changes from the publications they are subscribed to, and they can also act as publishers themselves, enabling cascading logical replication.

Logical replication has many use cases, such as;

• Nearly zero downtime PostgreSQL upgrades
• Data migrations
• Consolidating multiple databases for reporting purposes
• Real time analytics
• Replication between PostgreSQL instances on different platforms

Other than these cases, if your PostgreSQL instance is running on RHEL and you want to migrate to CNPG, streaming replication can be used. However, since CNPG images are based on Debian, the locale configuration must be compatible, and when using libc-based collations, differences in glibc versions can affect collation behavior. That is why we will practice logical replication setup to avoid these limitations.

Preparation of The Source Cluster

The specifications of the source PostgreSQL instance:

cat /etc/os-release | head -5
NAME="Rocky Linux"
VERSION="10.1 (Red Quartz)"
ID="rocky"
ID_LIKE="rhel centos fedora"
VERSION_ID="10.1"

psql -c "select version();"
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20250617 (Red Hat 14.3.1-2), 64-bit
(1 row)

Populating some test data using pgbench and checking pgbench_accounts table:

/usr/pgsql-18/bin/pgbench -i -s 10
dropping old tables...

PostgreSQL 18 has arrived with some fantastic improvements, and among them, the RETURNING clause enhancements stand out as a feature that every PostgreSQL developer and DBA should be excited about. In this blog, I'll explore these enhancements, with particular focus on the MERGE RETURNING clause enhancement, and demonstrate how they can simplify your application architecture and improve data tracking capabilities.

Background: The RETURNING Clause Evolution

What's New in PostgreSQL 18?

The Problem Before PostgreSQL 18

• INSERT and UPDATE
•  could only return new/current values.

• DELETE
•  could only return old values

• MERGE
•  would return values based on the internal action executed (
• INSERT
• ,
• UPDATE
• , or
• DELETE
• ).

In this post, I describe experiments on the write-versus-read costs of PostgreSQL's temporary buffers. For the sake of accuracy, the PostgreSQL functions set is extended with tools to measure buffer flush operations. The measurements show that writes are approximately 30% slower than reads. Based on these results, the cost estimation formula for the optimiser has been proposed:
flush_cost = 1.30 × dirtied_bufs + 0.01 × allocated_bufs.

Introduction

Temporary tables in PostgreSQL have always been parallel restricted. From my perspective, the reasoning is straightforward: temporary tables exist primarily to compensate for the absence of relational variables, and for performance reasons, they should remain as simple as possible. Since PostgreSQL parallel workers behave like separate backends, they don't have access to the leader process's local state, where temporary tables reside. Supporting parallel operations on temporary tables would significantly increase the complexity of this machinery.

However, we now have at least two working implementations of parallel temporary table support: Postgres Pro and Tantor. One more reason: identification of temporary tables within a UTILITY command is an essential step toward auto DDL in logical replication. So, maybe it is time to propose such a feature for PostgreSQL core.

After numerous code improvements over the years, AFAICS, only one fundamental problem remains: temporary buffer pages are local to the leader process. If these pages don't match the on-disk table state, parallel workers cannot access the data.

A comment in the code (80558c1) made by Robert Haas in 2015 clarifies the state of the art:

/*
 * Currently, parallel workers can't access the leader's temporary
 * tables.  We could possibly relax this if we wrote all of its
 * local buffers at the start of the query and made no changes
 * thereafter (maybe we could allow hint bit changes), and if we
 * taught the workers to read them.  Writing a large number of
 * temporary buffers could be 

Recently during a post-migration activity, we had to populate a very large table with a new UUID column (NOT NULL with a default) and backfill it for all existing rows.

Instead of doing a straight:

ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL;

we chose the commonly recommended performance approach:

• Create a new table (optionally UNLOGGED),
• Copy the data,
• Rename/swap the tables.

This approach is widely used to avoid long-running locks and table rewrites but it comes with hidden gotchas. This post is about one such gotcha: object dependencies, especially views, and how PostgreSQL tracks them internally using OIDs.

The Scenario: A Common Performance Optimization

Picture this: You’ve got a massive table with millions of rows, and you need to add a column with unique UUID default value and not null constraint values. The naive approach? Just run ALTER TABLE ADD COLUMN. But wait for large tables, this can lock your table while PostgreSQL rewrites every single row and can incur considerable time.

So what do we do? We get clever. We use the intermediate table(we can also use unlogged table) with rename trick, below is an sample created to show the scenario’s.

drop table test1;
create table test1
(col1 integer, col2 text, col3 timestamp(0));

insert into test1 
select col1, col1::text , (now() - (col1||' hour')::interval) 
from generate_series(1,1000000) as col1;

create view vw_test1 as 
select * from test1;


CREATE TABLE test1_new 
(like test1 including all);
alte

more...

2025 marked a historic turning point for CloudNativePG, headlined by its acceptance into the CNCF sandbox and a subsequent application for incubation. Throughout the year, the project transitioned from a high-performance operator to a strategic architectural partner within the cloud-native ecosystem, collaborating with projects like Cilium and Keycloak. Key milestones included the co-development of the extension_control_path feature for PostgreSQL 18, revolutionising extension management via OCI images, and the General Availability of the Barman Cloud Plugin. With nearly 880 commits (marking five consecutive years of high-velocity development) and over 132 million downloads, CloudNativePG has solidified its position as the standard for declarative, resilient, and sovereign PostgreSQL on Kubernetes.

Modern databases must know how to handle failures gracefully, whether they are system failures, power failures, or software bugs, while also ensuring that committed data is not lost. PostgreSQL achieves this with its recovery mechanism; it allows the recreation of a valid functioning system state from a failed one. The core component that makes this possible is Write-Ahead Logging (WAL); this means PostgreSQL records all the changes before they are applied to the data files. This way, WAL makes the recovery smooth and robust.

In this article, we are going to look at the under-the-hood mechanism for how PostgreSQL undergoes recovery and stays consistent and how the same mechanism powers different parts of the database. We will see the recovery lifecycle, recovery type selection, initialization and execution, how consistent states are determined, and reading WAL segment files for the replay.

We will show how PostgreSQL achieves durability (the "D" in ACID), as database recovery and the WAL mechanism together ensure that all the committed transactions are preserved. This plays a fundamental role in making PostgreSQL fully ACID compliant so that users can trust that their data is safe at all times.

Note: The recovery internals described in this article are based on the PostgreSQL version 18.1.

Overview

PostgreSQL recovery involves replaying the WAL records on the server to restore the database to a consistent state. This process ensures data integrity and protects against data loss in the event of system failures. In such scenarios, PostgreSQL efficiently manages its recovery processes, returning the system to a healthy operational state. Furthermore, in addition to addressing system failures and crashes, PostgreSQL's core recovery mechanism performs several other critical functions.

The recovery mechanism, powered by WAL and involving the replay of records until a consistent state is achieved (WAL → Redo → Consistency), facilitates several advanced database capabilities:

• R

Free and Open Source for Geospatial North America (FOSS4GNA) 2025 was running November 3-5th 2025 and I think it was one of the better FOSS4GNAs we've had. I was on the programming and workshop committees and we were worried with the government shutdown that things could go badly since we started getting people withdrawing their talks and workshops very close to curtain time. Despite our attendance being lower than prior years, it felt crowded enough and on the bright side, people weren't fighting for chairs to sit even in the most crowded talks. The FOSS4G 2025 International happened 2 weeks after, in Auckland, New Zealand, and that I heard had a fairly decent turn-out too.

Emma Sayoran organized a PUG Armenia speed networking meetup on December 25 2025.

FOSDEM PGDay 2026 Schedule announced on Dec 23 2025. Call for Paper Committee:

• Teresa Lopes
• Stefan Fercot
• Flavio Gurgel

Community Blog Posts:

• Chiira B. Mwangi I joined a community!

Recent changes in the software bundled in PgOSM Flex resulted in unexpected improvements when using OpenStreetMap roads data for routing. The short story: routing with PgOSM Flex 1.2.0 is faster, easier, and produces higher quality data for routing! I came to this conclusion after completing a variety of testing with the old and new versions of PgOSM Flex. This post outlines my testing and findings.

The concern I had before this testing was that the variety of changes involved in preparing data for routing in PgOSM Flex 1.2.0 might have degraded routing quality. I am beyond thrilled with what I found instead. Quality of the generated network didn't suffer at all, it was a major win!

What Changed?

The changes started with PgOSM Flex 1.1.1 by bumping internal versions used in PgOSM Flex to Postgres 18, PostGIS 3.6, osm2pgsql 2.2.0, and Debian 13. There was not expected to be any significant changes bundled in that release. After v1.1.1 was released, it came to my attention that pgRouting 4.0 had been released and that update broke the routing instructions in PgOSM Flex's documentation. This was thankfully reported by Travis Hathaway who also helped verify the updates to the process.

pgRouting 4 removed the pgr_nodeNetwork, pgr_createTopology, and pgr_analyzeGraph functions. Removing these functions was the catalyst for the changes made in PgOSM Flex 1.2.0. I had used those pgr_* functions as part of my core process in data preparation for routing for as long as I have used pgRouting.

After adjusting the documentation it became clear there were performance issues using the replacement functions in pgRouting 4.0, namely in pgr_separateTouching(). The performance issue in the pgRouting function is reported as pgrouting#3010. Working through the performance challenges resulted in PgOSM Flex 1.1.2 and ultimately PgOSM Flex 1.2.0 that now uses a custom procedure to prepare the edge network far better suited to OpenStreetMap data.

Graph databases have become increasingly popular for modeling complex relationships in data. But what if you could leverage graph capabilities within the familiar PostgreSQL environment you already know and love? In this article, I’ll explore how PostgreSQL can serve as a graph database using the Apache AGE extension, demonstrated through a fun use case: analyzing social connections in the craft beer community using Untappd data.

This article is based on my presentation at PgConf.EU 2025 in Riga, Latvia. Special thanks to Pavlo Golub, my co-founder of the PostgreSQL Ukraine community, whose Untappd account served as the perfect example for this demonstration.

Pavlo Golub’s Untappd profile - the starting point for our graph analysis

Why Graph Databases?

Traditional relational databases excel at storing structured data in tables, but they can struggle when dealing with highly interconnected data. Consider a social network where you want to find the shortest path between two users through their mutual connections—this requires recursive queries with CTEs, joining multiple tables, and becomes increasingly complex as the depth of relationships grows.

You might say: “But I can do this with relational tables!” And yes, you would be right in some cases. But graphs offer a different approach that makes certain operations much more intuitive and efficient.

Graph databases model data as nodes (vertices) and edges (relationships), making them ideal for:

• Social networks
• Recommendation engines
• Fraud detection
• Knowledge graphs
• Network topology analysis

Basic Terms in Graph Theory

Before diving into implementation, let’s establish some fundamental concepts:

Vertices (Nodes) are the fundamental units or points in a graph. You can think of them like tables in relational databases. They represent entities, objects, or data items—for example, individuals in a social network.

Edges (Links/Relationships) are the connections between nodes that indicate relationships

Introduction

I started contributing to PostgreSQL around 2020. This year I wanted to work harder, so I will explain the PostgreSQL features I developed and committed in 2025.

I also committed some other patches, but they were bug fixes or small document changes. Here I explain the ones that seem most useful.

These are mainly features in PostgreSQL 19, now in development. They may be reverted before the final release.

Added a document that recommends default psql settings when restoring pg_dump backups

• Title: Doc: recommend "psql -X" for restoring pg_dump scripts.
• Committer: Tom Lane
• Date: Sat, 25 Jan 2025
• Link: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d83a108c10a3ec886a24c620a915aa2c5bc023aa

When you restore a dump file made by pg_dump with psql, you may get errors if psql is using non-default settings (I saw it with AUTOCOMMIT=off). The change is only in the docs. It recommends using the psql option -X (--no-psqlrc) to avoid reading the psql config file.

For psql config file psqlrc, see my past blog:
https://zenn.dev/shinyakato/articles/543dae5d2825ee

Here is a test with \set AUTOCOMMIT off:

-- create a test database
$ createdb test1

-- dump all databases to an SQL script file
-- -c issues DROP for databases, roles, and tablespaces before recreating them
$ pg_dumpall -c -f test1.sql

-- restore with psql
$ psql -f test1.sql
~snip~
psql:test1.sql:14: ERROR:  DROP DATABASE cannot run inside a transaction block
psql:test1.sql:23: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test1.sql:30: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test1.sql:31: ERROR:  current transaction is aborted, commands ignored until end of transaction block
~snip~

DROP DATABASE from -c cannot run inside a transaction block, so you get these errors. Also, by default, when a statement fails inside a transaction block, the whole transaction aborts, so later statem