Every table needs a way to tell its rows apart, and auto-incrementing surrogate keys have been the go-to solution since practically the dawn of time. They're simple. They're fast. And perhaps most importantly, they're correct. But distributed systems demand unique values cluster-wide, preferably without some kind of consensus model or key-server bottleneck. The Smart Money is always on algorithmic generation.So along came the UUID. The standard has been through several iterations since its debut, but for the cost of 128-bits, it virtually guarantees algorithmically unique values. Unfortunately, UUIDs also tend to treat B-Tree indexes like particularly durable piñatas.Why would something so convenient cause so much grief? Is there a way out? I'm glad you asked!

Everything, Everywhere, All at Once

The Request Becomes a Transaction#

A transactional API has two halves: a network edge and a transactional operation. Keep the first at that edge; give the second to PostgreSQL, where its authority already lives — and every declared outcome of every operation becomes provable in the same transaction.

By Alexey Evlampiev

Abstract. The last five years consolidated storage into PostgreSQL: the queue, the cache, the search index, and the vector store moved in, one “just use Postgres” argument at a time. The API tier did not move — and the debate over whether it should is usually fought across the wrong boundary. A transactional API has two halves. The network edge authenticates the caller and adapts HTTP. The transactional boundary resolves the operation, validates its input, authorizes it against current state, executes the transition, and shapes the result. Convention puts the first half in a gateway and the second in an application framework — even though every authoritative decision in the second half already terminates in PostgreSQL. This article moves that boundary to where its authority lives, focusing on APIs whose valuable behavior is transactional decision-making over PostgreSQL state. The unit of design becomes the transactional operation: a named database operation with a typed contract, an authorization policy, a declared transaction, an implementation, and tests — and each protocol surface, starting with REST, becomes a binding to it. The payoff is one authority, one transaction, one executable proof: a test can invoke an operation end to end, assert on the response and the state transition in the same snapshot, and roll everything back.

The load finished without complaint, with row counts matching the fixture file and every foreign key resolving, but then the application inserts a row of its own, and Postgres refuses it:

ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(1) already exists.

Nothing is corrupt and nothing needs restoring. What you do have is a Postgres sequence out of sync with the table it feeds, the most common way a clean data load leaves a database broken, and the mechanism behind it is almost disappointingly plain, because writing an explicit id never tells the sequence that the value has been taken.

Everything below was run against PostgreSQL 18.6 in a throwaway container on 2026-08-21, and the outputs are pasted as they came back.

Key Takeaways

• Explicit ids and generated ids come from two different places, and loading the former doesn't move the latter.
• Moving from serial to an identity column changes nothing about this. GENERATED ALWAYS at least refuses the load outright, but add OVERRIDING SYSTEM VALUE to get past it and you inherit the same stale sequence.
• pg_get_serial_sequence() resolves the sequence behind a column for both serial and identity, which matters because a sequence keeps its original name when the table is renamed.
• On an empty table the popular setval(seq, max(id)) recipe quietly does nothing at all, since setval handed a NULL returns without acting.
• Whether the number you pass to setval is the next value or the last one used comes down to the is_called flag. Get it backwards and you lose exactly one id.

Why the sequence goes out of sync

A bigserial column is really a bigint carrying a default of nextval(''), so supplying your own value in the INSERT means that default is never evaluated at all, and the sequence sits where it was while the table fills up around it.

CREATE TABLE users (id bigserial PRIMARY KEY, email text NOT NULL UNIQUE);

INSERT INTO users (id, email)
VALUES (1, 'a@example.com'), (2, 'b@

Just in time for the PostgreSQL 19 betas, I'm excited to announce release 1.2 of pg_statviz, the minimalist extension and utility pair for time series analysis and visualization of PostgreSQL internal statistics.

This release adds support for the upcoming PostgreSQL 19:

• pg_statviz now captures the new wal_fpi_bytes counter from pg_stat_wal.
• The PG18/19 I/O worker, effective WAL level, and autovacuum scoring settings are captured in snapshot_conf.
• The release has been tested against 19 beta3, and across the whole PostgreSQL 13 to 19 range.

It also introduces a new blocking locks analysis module:

• Each snapshot now records the number of blocked and blocking sessions, along with a breakdown by lock type (relation, transactionid, tuple, and so on).
• Detection is built on pg_blocking_pids(), so even soft blocks (sessions that are just ahead in the lock wait queue) are counted, not just hard conflicts.
• Storage stays lightweight: table size is independent of how many sessions were involved in the blocking.
• The module produces charts and AI verdicts like every other module, and the deterministic severity floor applies here too: sustained blocking can never be reported as healthy.

Blocking locks by type, as captured by the new blocking module (click to enlarge).

Also new is the openai AI provider:

• --ai openai uses the OpenAI API, so the same flag works with OpenAI itself and with any other service or local server that implements that API.
• You can select the endpoint and model with the OPENAI_BASE_URL and OPENAI_MODEL environment variables.
• The openai package has been added to the [ai] extras, and zero-dependency installs remain unchange

Finally, this release also updates the default AI models to claude-sonnet-5 for Claude and gemini-3.7-flash for Gemini.

pg_statviz takes the view that everything should be light and minimal. Unlike commercial monitoring platforms, it doesn't require invasive agents or open connections to th

In this article written for experienced PostgreSQL engineers and core developers, I want to describe how we tested one hypothesis — whether a shared hash table can be used to speed up parallel aggregation by hashing. A recent paper claims that the shared hash table is an unfairly dismissed way of doing parallel aggregation, and that the key to success is moving the group lookup out from under the lock. We considered the idea of shared parallel aggregate, brought it to a working patch set for PostgreSQL, and ran measurements on a many-core instance in Google Cloud.

1. Does the hypothesis hold in PostgreSQL — and if it does, how exactly.
2. What we pay for it.
3. Is contention on LWLock really the only bottleneck?
4. Can this approach be reused to speed up other operations in a query plan?

The starting point

Back in February, I wrote about Hackorum, a forum style web view of the pg-hackers mailing list. If you missed that post, you can read it here first. It turns the mailing list into something that reads and navigates a bit more like a modern forum, while the mailing list itself stays the source of truth.

On 6 August, the Postgres Summit US 2026 Program Committee met to finalize the schedule:

• Chelsea Dole
• Jonathan Hinds
• Jonathan Katz

On 11 August, the San Francisco Bay Area PostgreSQL Meetup Group, organized by Katharine Saar, Stacey Haysler and Christophe Pettus. Kalyani Madipadiga and Stacey Haysler delivered a talk.

On 12 August, the Program Committee of PGConf.PL finished the talk selection:

• Andreas Scherbaum (non-voting chair)
• Adam Wołk
• Hubert "depesz" Lubaczewski
• Svitlana Lytvynenko

On 13 August, the PostgreSQL Edinburgh Meetup Group met, organized by Jimmy Angelakos. Torsten Förtsch and Paolo Guagliardo delivered a talk.

Claire Giordano and Aaron Wislang hosted and published a new podcast episode on 14 August, 2026 “How AI is changing software development with Simon Willison” from the Talking Postgres series.

Community Blog Posts:

• Mayuresh Bagayatkar about EXPLAIN ANALYZE the PGConf.EU CFP

Introduction

The purpose of this blog post is to introduce pg_shmemviz, a new tool to visualize PostgreSQL shared memory.

It follows the same approach as pg_walviz, bringing physical layout and byte level navigation to PostgreSQL shared memory instead of WAL segments.

Views such as pg_shmem_allocations, pg_buffercache and pg_shmem_allocations_numa are useful to inspect selected aspects of shared memory. However, sometimes we also want to see where allocations are physically located, which C structures they contain, their exact fields and padding, the regions reached through pointers and the corresponding raw bytes.

Welcome to pg_shmemviz

pg_shmemviz is a development and debugging tool that captures PostgreSQL’s main and dynamic shared memory segments into an offline snapshot and displays them in a local browser.

The interface combines a shared memory map, an allocation table, a structure inspector and a Physical Bytes view. They are synchronized: selecting an allocation, structure field or byte updates the other views. Pointer and history navigation can also cross captured segments.

As a picture is worth a thousand words, let’s have a look at it:

Shared memory overview

The map displays named allocations, allocator padding and unused ranges. Main shared memory, DSM control, DSM and DSA segments can be selected independently. One can filter the allocation table, select an allocation or zoom into a physical range.

Structure Fields and padding

The Structure Fields panel uses DWARF from the exact postgres executable to display nested C structures, field offsets, values, compiler padding and array stride padding.

Pointer targets with known bounds appear as referenced regions. Selecting one highlights its source pointer and opens the target bytes. Specialized discovery covers PostgreSQL statistics, WAL, process, SLRU, dynahash and DSM registry structures.

Physical Bytes

The Physical Bytes panel displays bounded byte windows classified by stru

Build a retry-safe HubSpot synchronization pipeline with atomic outbox events, signed QStash workers, call-level rate control, reconciliation, and Sentry.

A reliable HubSpot integration has to survive the worst possible success: HubSpot commits the change, but the worker loses the response before recording it. Retrying may repeat the call; refusing to retry may leave the local event unresolved. That ambiguity is why queues alone are not enough. The database, publisher, worker, and remote mutation all need explicit identities and recoverable state.

This is the delivery layer for the 40-site architecture and its versioned brand-routing plan. The application first accepts a desired state locally; this pipeline makes HubSpot converge on it without blocking the visitor.

Eliminate the database-and-queue dual write

Writing the subscription to PostgreSQL and then publishing to QStash creates two independent writes. If the process crashes between them, the subscription exists but no worker is scheduled. Publishing first has the opposite failure: the worker can observe an event whose business transaction later rolls back.

The transactional outbox pattern puts the desired subscription change and an immutable event in the same database transaction. A separate dispatcher publishes committed outbox rows. The dispatcher is allowed to publish more than once because the worker is idempotent.

CREATE TABLE subscription_requests (
  id uuid PRIMARY KEY,
  brand_id text NOT NULL REFERENCES brands(id),
  contact_key text NOT NULL,
  product_id text NOT NULL,
  desired_state text NOT NULL CHECK (desired_state IN ('subscribed', 'unsubscribed')),
  mapping_version integer NOT NULL,
  idempotency_key text NOT NULL,
  request_hash text NO

A data-driven model for mapping websites and brands to HubSpot subscription types, Brands IDs, segments, contact properties, and analytics destinations.

When brand A means six HubSpot segments, one communication subscription type, one HubSpot Brand ID, and several attribution properties, the mapping is part of the product. Hiding those IDs in conditionals turns every new website, campaign, and reorganization into a deployment—and makes it difficult to explain why a contact landed where they did.

This article expands the configuration layer introduced in the 40-site HubSpot architecture. The goal is to let every website express business intent while one versioned model resolves that intent into HubSpot targets.

Use one internal brand identity

Start with a stable internal brand key such as brand_a. Map every accepted hostname and form to that key. Do not use the hostname itself as the business identity: domains change, several domains can represent one brand, preview hosts must be rejected, and a single site can expose more than one subscription product.

CREATE TABLE brands (
  id text PRIMARY KEY,
  name text NOT NULL,
  hubspot_business_unit_id bigint,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE brand_hosts (
  hostname text PRIMARY KEY,
  brand_id text NOT NULL REFERENCES brands(id),
  environment text NOT NULL CHECK (environment IN ('production', 'preview')),
  accepts_subscriptions boolean NOT NULL DEFAULT false
);

CREATE TABLE subscription_products (
  id text PRIMARY KEY,
  name text NOT NULL,
  channel text NOT NULL DEFAULT 'EMAIL',
  active boolean NOT NULL DEFAULT true
);

CREATE TABLE brand_subscription_routes (
  brand_id text NOT NU

Databricks’ acquisition of Electric is noteworthy for several reasons. Electric developed PGlite, a WebAssembly build of PostgreSQL that can run inside a browser, application, serverless environment, or AI agent sandbox. It also developed synchronization technology intended to connect these distributed environments with a central PostgreSQL system.

The speed of adoption is striking. PGlite grew from one million to 13 million weekly downloads in approximately 12 months. The project began with foundational PostgreSQL-to-WASM work by Neon co-founder Stas Kelvich, which Electric subsequently turned into an embeddable database used across development tools, testing frameworks, browser sandboxes, and AI applications. (Neon announcement)

That is impressive execution. But the more important story is not the acquisition.

It is the new architectural role emerging for PostgreSQL.

For most of its history, PostgreSQL has been deployed as a server. Applications connect to it across a network, execute transactions, and depend on it as a durable system of record. PGlite introduces another possibility: PostgreSQL can run inside the application—or even inside the agent itself.

That brings the database closer to the agent’s execution loop and removes a network round trip from operations involving local context. But it also raises a much harder question:

If every agent has a database, which database owns the truth?

Why agents create a different state problem

Traditional applications generally have known execution paths. Their queries are designed in advance, their data-access patterns can be tested, and their infrastructure is relatively stable.

Agents behave differently.

An agent may decide at runtime which information it needs, which tools to invoke, and which intermediate results to retain. It may retrieve documents, generate embeddings, create a plan, call external systems, revise its assumptions, and coordinate with other agents.

All of that activity produces state.

Some s

Kubernetes metric container_memory_working_set_bytes is used for evicting/killing pods with too much memory use, especially if request < limit (don’t do this with Postgres). The metric is calculated from cgroups v2 memory.stat metrics as current-inactive_file [source].

You’d assume it’s a good metric for memory usage in kubernetes. But with Postgres, this metric is very inaccurate for memory utilization and doesn’t tell you at all if you’re going to OOM crash your database.

After having the same conversation so many times about Postgres on Kubernetes, I need to write it down so I can just send people here to read it.

I will show better metrics to watch.

We start with fundamentals.

Note: scripts to reproduce all tests and graphs are at https://github.com/ardentperf/cgroup-postgres-memtest

Kubernetes Node E2E Tests

This is ground-zero for what Kubernetes promises to be true. AI research is telling me make test-e2e-node has several memory-pressure eviction tests:

• MemoryAllocatableEviction [source]
• MemoryAllocatableEvictionPodLevelResources [source]
• PriorityMemoryEvictionOrdering [source]
• PriorityMemoryEvictionOrderingPodLevelResources [source]

I believe these tests all use a test kit called agnhost [source]. Lets fire it up in docker and grab a few cgroup v2 metrics

docker run --name graph-repro-run_1-1821100 \
    --memory 512m --memory-swap 512m --detach \
    registry.k8s.io/e2e-test-images/agnhost:2.47 \
    stress --mem-alloc-size 25Mi --mem-alloc-sleep 5s --mem-total 1Gi

container_memory_working_set_bytes is the yellow line: current-inactive_file. It tells current memory usage, excluding linux page cache contents on the “active” file LRUs. The blue line is my own metric, where I’ve excluded all LRUs (both active and inactive) – basically I’m saying “memory usage not including the page cache”.

Looking at the graph:

Anonymous memory ramp-up. As expected, OOM when memory usage hits the cgroup max (aka Pod Memory Limit). If you’re taki

Over the years we have written a lot about how data gets into Postgres, how it sits on disk, and how indexes help you find it again. Some of that advice was written against Postgres 10 or 11. A surprising amount of it is still exactly what we would tell you for the upcoming Postgres 19 release. Functionality described here is based on current betas; minor details may still change before GA.

This post revisits Crunchy posts in the “load, storage, indexes, and partitioning” bucket: what we wrote, which version moved the needle, and what we would tell you to do now. Along the way: async I/O, more resilient COPY, LZ4 by default, richer BRIN shapes, skip scan, and smoother partition operations.

Async I/O: faster scans and vacuum on modern storage

In 2019 we benchmarked a BRIN index against a B-tree and a parallel sequential scan on the same time-series table. Sometimes BRIN won. Sometimes the parallel seq scan won: four workers chewing through the heap beat a clever index. That was the right lesson for Postgres 11: indexes are a tradeoff against what the executor can already do in parallel.

Postgres 18 made those heap reads substantially faster.

Async I/O lets backends queue multiple disk reads instead of waiting on each one. Sequential scans, bitmap heap scans (the path BRIN and many bitmap index plans finish with), and vacuum all benefit. Community benchmarks have shown up to ~3× on cold, latency-bound storage, a big deal for cloud disks. Defaults matter here: io_method = worker is on out of the box; on Linux 5.1+ you can try io_method = io_uring. See Get Excited About Postgres 18 for the operator view.

Postgres 19 builds on that: I/O workers can autoscale (io_min_workers / io_max_workers), read-ahead scheduling improved, and EXPLAIN (ANALYZE, IO) can show what the async subsystem is doing. Parallel query is still there; each worker can queue several reads and keep making progress while some of them are still in flight, so you get more useful work between waits. Parallel autovacuum workers

I’m happy to share something new: YeSQL is live, a free set of 24 short PostgreSQL lessons — one concept, one runnable query, real data, no signup. It’s free to use today, and it’s also a prototype for something I’ve wanted to build for a while: making every query in The Art of PostgreSQL runnable, right on the page.

"Highly available PostgreSQL" usually means leader election, streaming replicas, automatic failover, health checks, and a lot of careful wiring. With the CYBERTEC PG Operator (CPO) it means a 14-line YAML file. Here's the whole thing, start to finish, on a laptop with minikube.

Every command and output below comes from a tutorial we ran end-to-end on a fresh minikube(Kubernetes 1.30, CPO 0.9.2, PostgreSQL 18.4).

A cluster and an operator

minikube start -p cpo-deploy --driver=docker --cpus=2 --memory=4096

helm repo add cpo https://cybertec-postgresql.github.io/CYBERTEC-operator-tutorials
helm repo update cpo
kubectl create namespace cpo
helm install cpo cpo/postgres-operator -n cpo --version 0.9.2 \
  --set configKubernetes.enable_pod_antiaffinity=false
kubectl -n cpo rollout status deploy/postgres-operator

One information worth knowing: enable_pod_antiaffinity=false flag. By default the operator spreads replicas across different Kubernetes nodes, exactly what you want in production. But minikube is a single node, so without this flag the replica would sit Pending forever. On a real multi-node cluster, leave anti-affinity on.

Describe the database you want

kubectl -n cpo apply -f - <<'EOF'
apiVersion: cpo.opensource.cybertec.at/v1
kind: postgresql
metadata:
  name: pg-cluster
spec:
  dockerImage: 'containers.cybertec.at/cybertec-pg-container/postgres:rocky9-18.4-1'
  numberOfInstances: 2
  postgresql:
    version: '18'
  resources:
    requests: { cpu: 250m, memory: 1Gi }
    limits:   { cpu: '1',  memory: 1Gi }
  teamId: acid
  volume:
    size: 1Gi
EOF

numberOfInstances: 2 is the whole HA story: one leader, one streaming replica. Wait for them:

bash
kubectl -n cpo wait --for=condition=Ready pod \
  -l cluster.cpo.opensource.cybertec.at/name=pg-cluster --timeout=360s
# pod/pg-cluster-0 condition met
# pod/pg-cluster-1 condition met

The "it's actually HA" moment

Ask Patroni, which runs inside every database pod, and what it sees:

kubectl -n cpo exec pg-cluster-0 -- patroni

A production-ready approach to preventing duplicate leads and side effects with stable idempotency keys, PostgreSQL constraints, transactional outbox events, safe retries, and reconciliation.

A visitor fills out a form, presses Submit, and sees nothing happen. They press it again. The first request actually succeeded, but its response was delayed. The result can be two leads, two confirmation emails, two CRM updates, and two analytics events from one human action.

This is easy to dismiss as a frontend problem, but duplicate submission is a distributed-systems problem in miniature. Browsers retry, mobile connections fail after the server has committed, serverless functions time out, queues redeliver work, and reconciliation jobs intentionally retry failures. The server cannot infer whether two matching requests represent one action or two deliberate actions unless the client gives both attempts the same identity.

Why disabling the submit button is not enough

Disabling the button is still worthwhile. It improves the interface, stops impatient double-clicks, and tells the visitor that work is in progress. It does not protect the system from a refreshed page, a second browser tab, an automatic client retry, a proxy retry, a function timeout after commit, or a worker processing the same event twice.

The browser guard and the server guarantee solve different problems. Use both, but treat the database guarantee as the source of truth. Anything that depends only on React state disappears when the page reloads and can be bypassed by any direct API client.

The idempotency contract

For every logical submission, the client generates one unpredictable key. Every retry of that submission reuses the key. A genuinely new submission gets a new key. The server scopes the key to

A walkthrough of running OpenBao on Kubernetes with CloudNativePG as its PostgreSQL storage backend. Every layer of this stack is open source, with no vendor lock-in: Kubernetes and CloudNativePG are both CNCF projects, authenticated entirely over TLS client certificates via the 1.30 DatabaseRole CRD, with no passwords anywhere in the stack. Co-authored with Rob Kenefeck from ControlPlane.