Latest Blog Posts

Way back in 2015, I opened a PGXN API issue to allow links between documents rendered by PGXN to work. In 2024 I followed up with another issue to enable relative links to images to work. I mean, everyone wants this, right? It’s how your favorite source code sit works.

Now so does PGXN. As of today, links between documents and to images work. Check it out in the newly-released chdb extension docs, which both link to the chdb and chdb_hook docs and to some nice benchmark graphs.

Or see the Apache Age docs, which includes not only documentation images but also decorative SVGs for the headers.

I’m gradually reindexing all of the extensions so that any other documentation with such links work work; it should be done by tomorrow. Then, at along last, your extensions can look their very best.

We're happy to announce a new Postgres extension: chdb. This extension expands Postgres import and export features via the chDB library, an in-process ClickHouse engine, providing efficient, flexible conversion to and from a wide array of data formats living on your favorite cloud storage systems.

Benchmark {#benchmark}

And boy howdy do we mean efficient! We compared chdb's performance importing the NYC Taxi dataset (1m rows, wide table) in a number of data formats to three other Postgres extensions, all reading from a regionally-colocated AWS S3 bucket. To the chart!

In order to minimize differences and to optimize for measurement of extension performance rather than infrastructure, the chdb, pg_lake, and pg_duckdb benchmarks ran on r8id.xlarge ClickHouse Managed Postgres services with 4 vCPUs and 32 GB RAM; the aws_s3 benchmark ran on a db.r8g.xlarge AWS RDS host, also with 4 vCPUs and 32 GB RAM. Results average three runs for each import. See the benchmark source code for details.

Implementing a zero-trust network model in Kubernetes requires shifting from the default-allow behavior to explicit, label-driven microsegmentation. This hands-on lab walks through securing a standard three-tier architecture (Frontend ⭢ Backend ⭢ Database) using Kubernetes NetworkPolicies, validating both ingress and egress restrictions.

Because standard Kubernetes requires a network plugin to actually enforce these rules, this lab environment uses Calico as the Container Network Interface (CNI). While the YAML manifests are standard Kubernetes API objects, it is the Calico CNI operating under the hood that intercepts the traffic and enforces both our ingress and egress restrictions.

The Lab Environment

We begin by establishing a baseline three-tier architecture in a dedicated namespace, leveraging specific labels to identify our workloads.

kubectl create namespace production-app
# CREATE FRONTEND POD
kubectl run frontend --image=nginx --labels=tier=frontend -n  production-app
# CREATE BACKEND POD
kubectl run backend --image=nginx --labels=tier=backend -n  production-app
# CREATE DATABASE POD
kubectl run database --image=postgres:18 --labels=tier=database -n  production-app \
  --env="POSTGRES_DB=myapp" --env="POSTGRES_USER=appuser" --env="POSTGRES_PASSWORD=securepass123"

Verify the pods and labels:

kubectl get pods -n production-app --show-labels

NAME       READY   STATUS    RESTARTS   AGE     LABELS
backend    1/1     Running   0          3h6m    tier=backend
database   1/1     Running   0          3h12m   tier=database
frontend   1/1     Running   0          3h6m    tier=frontend

Expose the pods so they can communicate via ClusterIP:

kubectl expose pod frontend --port=80 --target-port=80 -n production-app
kubectl expose pod backend --port=80 --target-port=80 -n production-app
kubectl expose pod database --port=5432 --target-port=5432 -n production-app

Establishing the Baseline

The foundation of Kubernetes network security is a namespace-wide d

The PostGIS Team is pleased to release PostGIS 3.7.0rc2! Best Served with PostgreSQL 19 Beta 3 , GEOS 3.15.0 , postgis_tiger_geocoder 2025.2 , and address_standardizer.

This version requires PostgreSQL 14 - 19beta3, GEOS 3.10 or higher, Proj 6.1+, and libgmp. To take advantage of all features, GEOS 3.15+ is needed. To take advantage of all SFCGAL features SFCGAL 2.3.0+ is needed. To use postgis_raster extension GDAL 3+ is required.

This release contains fixes since 3.7.0rc1 release.

• source download md5

• NEWS

• HTML Online en ja zh_Hans fr

• PDF docs: en ja, zh_Hans, fr

• Cheat Sheets:

  • postgis: en ja zh_Hans fr
  • postgis_raster: en ja zh_Hans fr
  • postgis_topology: en ja zh_Hans fr
  • postgis_sfcgal: en ja zh_Hans fr

This release is a release candidate of a major release, it includes bug fixes since PostGIS 3.6.4 and new features.

Context

At Swiss PG Day 2026, I had the privilege of moderating a Birds of a Feather (BoF) session. A BoF session is a community-driven event designed for focused, interactive discussion among participants with shared interests.

I gave a brief introduction to the topic "How about being a speaker", taking from my brief experience of giving talks at Postgres community conferences. This was as much about me learning from others as it was to encourage aspiring speakers to submit to the next Call for Papers.

The room filled up with around 20 participants, an ideal group size to keep the dialogue

On 26 August 2026, the Adelaide PostgreSQL User Group met, organized by Robins Tharakan. Shadab Mohammad and Robins Tharakan delivered a talk.

On 26 August, the Sydney PostgreSQL User Group met, organized by Shadab Mohammad. Rajesh Kandasamy and Shadab Mohammad delivered a talk.

On 3 September, the PostgreSQL Istanbul Meetup group met, organized by Devrim Gündüz, Gülçin Yıldırım Jelínek & Bilge Korkmaz Erdim. Volkan Çetin and Önder Kalacı delivered a talk.

PGConf Brazil happened from 2-4 September 2026.

Organized by:

• Charly Batista,
• Euler Taveira,
• Fábio Telles, Fabrizio Mello,
• Francisco Porfírio,
• João Foltran,
• William Ivansky

Talk Selection Committee:

• Barbara Leidens,
• Cammila Martins,
• Francisco Omar,
• Francisco Porfírio,
• Jean Pierre

Speakers:

• Alfredo Rodriguez,
• Andreas Scherbaum,
• Barbara Soares Lara,
• Breno de Araújo Baima,
• Bruce Momjian,
• Carlos Correa Silva Alves,
• Charly Batista,
• Dani Monteiro,
• Daniel Moya,
• Davy Alvarenga Machado,
• Dickson S. Guedes,
• Emanuel Carneiro,
• Emerson dos Santos Queiroz,
• Fabrízio de Royes Mello,
• Fernando Franquini,
• Fernando Laudares Camargos,
• Francisco Porfírio Ribeiro Neto,
• Fábio Telles Rodriguez,
• Gabriel Batista Menezes,
• Gabriele Fedi,
• Guilherme Barreto,
• Gustavo Lemos,
• Gustavo Oliveira,
• Iago Passos,
• Israel Barth Rubio,
• Joao Cosme,
• Joelcio Rizelo,
• Josiel Lemos Santos,
• João Marcelo Detomini,
• Jônatas Davi Paganini,
• Karin Keller,
• Marcelo Fernandes,
• Martín Marqués,
• Matheus Alcantara,
• Maurilio Pereira,
• Nathan Aguiar,
• Ozano Neto,
• Patrick de Alvarenga Siqueira,
• Pedro Luís Teixeira,
• Rafael de Alencar Segura,
• Raphael Max Brettas Vieira,
• Raul Oliveira,
• Robert Treat,
• Roberto Mello,
• Rodrigo Ibraim Teixeira, Shane Borden,
• William Ivanski,
• William Lino Oliveira,

An overnight load runs under session_replication_role = replica, the setting most of the popular answers describe as switching enforcement off for the session. It writes an order for customer 999, who does not exist, and then rejects the next row for having a negative amount.

SET session_replication_role = replica;
SET
INSERT INTO orders (customer_id, amount, tenant_id) VALUES (999, 10, 1);
INSERT 0 1
INSERT INTO orders (customer_id, amount, tenant_id) VALUES (1, -5, 1);
ERROR:  new row for relation "orders" violates check constraint "orders_amount_check"

Both statements ran one line apart in the same session, against the same table. The parameter governs which triggers and rules fire, and a foreign key is enforced by a pair of internal triggers that pg_trigger names RI_ConstraintTrigger_c_*, so the foreign key falls silent along with them. No trigger implements a CHECK constraint, which is why that one carries on rejecting rows.

Two more levers get recommended for the same job. ALTER TABLE ... DISABLE TRIGGER USER (or ALL) works on the table instead of the session, and PostgreSQL 18 added ALTER TABLE ... ALTER CONSTRAINT ... NOT ENFORCED, which works on a single constraint. All three went through one identical probe set below, on a schema built to carry every kind of rule at once.

Key Takeaways

• Under session_replication_role = replica a load still meets CHECK, NOT NULL, UNIQUE, identity columns and row-level security. Foreign keys, ON DELETE CASCADE, rules and event triggers go quiet, and a trigger marked ENABLE REPLICA fires for the first time in its life.
• Neither RESET nor ENABLE TRIGGER ALL reads a row on the way back, and VALIDATE CONSTRAINT against a foreign key the catalog already believes is valid answers ALTER TABLE while orphans sit in the table.
• NOT ENFORCED, added for foreign keys in PostgreSQL 18 and extended to CHECK constraints in 19, is the only lever that scans the table when you switch it back on, and a forced row-level security policy can hide rows from t

If you run PostgreSQL on EC2, you've had two real choices until now: build from source, or fall back to whatever version Amazon Linux itself carries in its base repos. Neither matches what the rest of the PostgreSQL community gets from yum.postgresql.org — the full extension ecosystem, day-one minor releases, and a consistent layout across distributions.



That gap is closed. Amazon Linux 2023 is now a first-class target of the PGDG YUM repository, with its own build root and its own package tree, right next to Enterprise Linux, Fedora, and SUSE.

Summary first:

The PostgreSQL documentation on numeric contains two statements that don't sit well together:"especially recommended for storing monetary amounts and other quantities where exactness is required" — and right away: "calculations on numeric values are very slow compared to the integer types, or to the floating-point types". So the type is recommended for storing monetary amounts, and in the same breath admitted to be rather expensive.For me, as a DBMS developer, that reads as a call to action. If operations on a type are noticeably slower than on bigint, a temptation arises: couldn't we store monetary amounts as an integer number of cents and round by the standard rule? That would save a fair amount of computing resources on our database servers, wouldn't it? And what if we went all the way and used double precision?But before optimizing the type or swapping it for an integer, it's worth understanding what is actually demanded of it: by law, by data interchange formats, by application platforms. Is the exact decimal type really the standard for financial applications, if only a de-facto one? Or is it engineering folklore that can safely be worked around?Rather than rely on survey literature, let's dig into the primary sources. This task has never been a simple one, but AI agents have made it much easier. So let's roll up our sleeves and get started. If the text feels overly dry or boring — well, that's because it is. Which is why there's a table of contents, so you can quickly jump to whatever you need.

Table of contents

• What the SQL standard says

• What law and regulators require

• Financial data interchange formats

• Payment systems: scale as an attribute of the currency

• What the TPC benchmarks require

• What vendors, authorities, and practitioners say

• What's going on in ERP systems

• Where the exact decimal type is heading

• Conclusions

1. What the SQL standard says

PostgreSQL 19 Beta 3 shipped on August 13, 2026, and the release notes have been filled in as of 2026-07-18 — still marked subject to change, and the GA date isn’t announced yet, but following the project’s usual September/October cadence general availability should land within the next few weeks. That makes now the right time to read through what’s changing, the same way I did for PostgreSQL 11 through 18 a few weeks ago.

This is not a changelog dump. It’s the subset of PG 19 I think is worth knowing about before you upgrade: a handful of compatibility breaks that will bite people who don’t read release notes, and the SQL-level additions I found genuinely useful once I started poking at them. Every query below ran against a real PostgreSQL 19 Beta 3 instance — no hand-waving about syntax that might work.

When dividing by zero, Postgres fails and declares you ran an illegal operation. Cast 'abc' to an integer, and get an error. Divide by NULL and the query still runs. NULL isn't even a value. It is a marker for “unknown.” When using NULL, the concept of being unknown propagates through comparisons, arithmetic, concatenation, aggregates, window functions, and WHERE clauses. The result is well-defined, but it may not be the result you had in mind.

The unofficial subtitle of this post could be: why NOT NULL constraints are serious business. One way to dodge the complications below is to never store NULL in the first place. If a column should always have a value, say so in the schema.

Let's start with a quiz: what does this return?

SELECT (NULL = NULL) = (NULL != NULL);

If you said NULL, you are right. Both NULL = NULL and NULL != NULL are unknown, so the outer = is comparing unknown to unknown, which is also unknown. Comparison operators (=, <>, and the rest) return NULL when either side is unknown. That is why SQL has IS NULL instead of = NULL: you cannot know whether two unknowns are equal, but you can test whether a value is unknown. (There is a legacy caveat! It is at the bottom.)

Keep that in mind as we walk through the rest. NULL is not a value. It is unknown.

Three-Valued Logic with OR and AND

Boolean expressions in SQL are not limited to TRUE and FALSE. Every predicate can also be NULL, meaning unknown. WHERE and HAVING keep only rows where the expression is true. Unknown is discarded the same way false is.

SELECT
  NULL = NULL              AS null_eq_null,    -- NULL
  TRUE  OR  NULL           AS true_or_null,    -- t
  FALSE OR  NULL           AS false_or_null,   -- NULL
  TRUE  AND NULL           AS true_and_null,   -- NULL
  FALSE AND NULL           AS false_and_null,  -- f
  NOT NULL::boolean        AS not_null,        -- NULL
  NULL::boolean IS UNKNOWN AS is_unknown;      -- t

OR can still be true if the other side is true. AND can still be false if the other side is fal

 Note: I am presenting a tutorial for those who want to learn SQL at this year's Texas Linuxfest (https://pretalx.com/txlf2026/talk/DQ3XW7/) on November 6th.  This is a great opportunity at a fantastic community event and I encourage you to attend.

Demo databases are good way to develop skills with Structured Query Language. Some are associate with one data store more than another, like Sakila and World with MySQL, or DVD with PostgreSQL. The Northwind database originated in the Microsoft sphere of influence but it is easy to obtain for PostgreSQL.

Step 1

Go to https://github.com/pthom/northwind_psql and download https://github.com/pthom/northwind_psql/blob/master/northwind.sql

This file has all you need.

Step 2

I am using DBeaver Enterprise Edition 26.1.0 and open the northwind.sql file. DBeaver is an amazing data tool and makes this type of project simple. 

Opening the northwind.sql file

Step 3

This is that northwind.sql file in all its glory. If you are new to SQL, take a moment and scroll through the file. This is a well structed example that you could use to model your future work (hint, hint).

The contents of the northwind.sql file

Step 4

Now we can execute the northwind.sql file to load the structure and data. 

Use Alt + X to execute the northwind.sql file

How did it go? Did it load properly. If it did, you should see something like the following report on the script's execution. 

Step 6

Try a sample query! What, you're new to SQL and don't have a sample handy? Try this:

Back on Friday, July 17th, I joined Courtney from Manning for another LinkedIn Live, this time on what to do when you inherit a bad database. The recording and the slides are now up, with a Q&A at the end.

The session was based on Chapter 11 of my book, PostgreSQL Mistakes and How to Avoid Them (Manning). This one was more of a fireside chat than a technical presentation, and if you know me, I tend to give the latter. It's a situation most people who work with data run into sooner or later, and the first thing worth saying about it is that you are not alone.

These databases come about for what we politely call historical reasons: no DBA on the team when the thing was built, rushed deadlines, organic growth, and various other reasons. There's also a thing I call architect disease, which is architectural arrogance: a data or software architect joins the team and says forget the best practices everyone keeps talking about, I know the perfect way to do this. What they build might work fine for the use case at the moment it rolls out, but people usually have trouble maintaining such designs afterwards. The symptoms are recognizable: improper database encodings, tables with a hundred columns because they were once spreadsheets, missing indexes, no constraints so the data is inconsistent, etc.

The part that matters is that assigning blame is not a strategy. What we covered instead:

• Don't panic. These are solvable problems, and never let a good crisis go to waste: you have effectively been given permission to fix things.
• Ask the humans what hurts. People are keen to complain once they find an outlet, so let them. Beware the XY problem while you listen: when someone asks for partitioning or a Kafka pipeline, find out what they are actually trying to solve before you build it.
• Examine everything: the schema with pg_dump, pgAdmin or DBeaver, and the data with exploratory queries. The configuration, and the behavior, through logs, pg_stat_activity and pg_stat_statements.

While writing about the monitoring improvements in PostgreSQL 19 and preparing my new talk on Postgres observability for PostgreSQL Conference Europe in October, I noticed the system views got their own section in this release. The last time they were similarly highlighted was in PG13 and PG14. So I decided the system views need a blog of their own to go through what has changed. PostgreSQL 19 adds four new views, pg_stat_lock, pg_stat_recovery, pg_stat_autovacuum_scores, and pg_dsm_registry_allocations, each deserving more than the one-line mention they got in my monitoring blog, so here is the tour.

*Disclaimer: PostgreSQL 19 is still in beta as I write this and this area has already seen columns renamed mid-cycle; things can still change or get reverted before GA. The release notes will be the final word.*

pg_stat_lock {#pg_stat_lock}

Locks are a special interest of mine 😀 Last year, I spoke at 16 conferences with a talk called "Anatomy of Table-Level Locks in PostgreSQL". If you're interested, some of those talks were recorded and are available on YouTube. So, you can imagine how excited I was to see a new locks view in PostgreSQL 19\.

Your repository contains two version control systems.

One is Git. The other is your migration directory — a timestamped, append-only sequence of schema changes with its own applied-state tracking in the database. Two systems, two timelines, living in the same repo. And they do not synchronize.

Because migrations are files, it’s natural to assume they inherit the protections Git gives files: reviewable diffs, merge conflicts where work collides, meaningful revert, meaningful checkout. This post walks through those protections one by one and shows that, for migrations, each is quietly absent — not because Git is deficient, but because being stored in Git is not the same as being managed by Git. I think the pattern is underdiagnosed: a lot of “weird database problems” in day-to-day development are really this one mismatch wearing different costumes.

The control group: declarative schema files

To see what’s missing, look at a kind of file Git genuinely manages. A declarative schema file — schema.prisma, models.py, schema.rb, an Ent or sqlc definition — cooperates with Git almost perfectly:

• Diffs are readable. Open the PR and you see which columns changed, in place, in context.
• Conflicts happen when they should. Two people touch the same table, they touch the same lines, Git flags it. A human gets involved.
• Revert means something. Revert the commit and the declared state is restored.
• Checkout means something. Switch branches and you’re looking at that branch’s schema.

This is no accident. Git is built to manage stateful files evolving over time. A declarative schema is exactly that.

A migration directory is not that. It’s an event log wearing a file system costume. The costume is convincing — text files, in a directory, in the repo — and every Git operation degrades the moment it touches what’s underneath.

Editing is forbidden

Git’s core verb is “change this file.” Applied migrations must never change — edit one and every environment that alr

Why transactional evidence matters as AI moves from generating answers to changing business state

Imagine an AI agent handling a customer refund.

It receives the request, retrieves the order, evaluates the relevant information, decides the refund is allowed, and invokes the appropriate service. From the AI system’s perspective, the workflow succeeded because the trace shows that a decision was made and a tool was called.

The business system tells a different story. The payment operation failed. The transaction rolled back. No refund was actually committed.

So, did the AI successfully issue the refund?

That question points to a larger challenge enterprises will face as AI systems gain the authority to act.

For most of the generative AI era, we have focused on what models produce. We measure accuracy, latency, token consumption, and trace quality. That is the right focus when an interaction ends with an answer. It is not enough when the interaction ends with a business action.

The fact that an AI agent called a tool and the fact that a business transaction committed are two different facts.

As AI moves from answering questions to changing enterprise systems, we need architectures that preserve that distinction.

Observability tells you what the AI did. It does not prove what changed.

AI observability — prompts, tool calls, latency, evaluation traces — is necessary infrastructure, and enterprises are right to invest in it. An execution trace can establish that an agent invoked approve_claim() at 10:42:17. Only the authoritative claims system can establish whether claim 84721 actually moved from pending to approved.

The first describes execution. The second describes business state. A reliable AI architecture has to connect them, yet those two records often live in different systems with different identifiers, retention policies, and notions of success — which is exactly where things go wrong. A timeout might cause an agent to retry an action that actually succeeded

That was an experimental meetup: we never, ever scheduled meetups in August, especially during the last week of August! Still, when Keanya Phelps came up with the idea to have a Postgres meetup during the Djangocon.US conference, I couldn’t say no!

Until the last week before the meetup, I was unsure how many people would register and, more importantly, how many would come, but we had a full house (and yes, I didn’t take enough pictures because we had a Zoom setup crisis, so you have to trust me!)

Once again, I can’t even describe how proud I am of the Prairie Postgres community we built during these less than two years of our existence! I am so thankful to everyone who comes, listens, asks questions, and participates in discussions. I have to remind the attendees multiple times that we are about to close the house because people keep talking :). And if you were there and you can’t believe it was ever different, trust me, it was!

Nothing feels as rewarding as seeing genuine interest from listeners and hearing them thank you for organizing the event. That’s when I feel that I am doing something good

Here is the event recording:

If you’ve never been to our meetups, please consider coming! We love our new venue, and we have the same Giordano’s pizza! And we are family-friendly: we have room for kids just by our meeting room, and if you notify me in advance, we will provide childcare!

Our next meetup is on September 22! Register here!