It’s hard to say anything new about query optimization. On the one hand, each new Postgres release includes multiple query planner improvements, and it feels like there is something for any problem that can possibly arise. On the other hand, the fundamental principles of optimization do not change: if your query is highly selective, meaning the result is a small percentage of the original data set, you need to build indexes that would support this particular search. If you are optimizing an analytical query, you are looking for the way to execute it in parallel and aggregate early.
There is only one “but” – it’s not like you can build an index on any table at any time. If that’s the case, what can you do?
Recently, I had to find a way to speed up a production query that suddenly started performing significantly slower than it used to. Yes, it reached the tipping point, but nevertheless, I had to find a way to make it fast again. Or at least not terribly slow.
Here is a problem I had to solve.
Given
And there is a query I needed to optimize. Yes, it looks simple/obvious, but wait till I get to the details!
SELECT * FROM t
WHERE a=? AND b=? AND c=?
AND start_date <='2026-08-16' AND end_date >='2026-08-16'
Date could be any date; I used August 16 for illustration (and no, it’s not “yesterday” or “today”; the query could run for any date in the past). Basically, what you need is to find all records in which the interval from
start_date to end_date includes that date in question (and satisfies other selection criteria). The query was running from several seconds to several minutes.
Yes, we know that we need: we need to build a daterange from start_date to end_date, and then build a GIST index on that range. All good, except we all know how long it takes to build any ind
On 19 August 2026, the Postgres Meetup for All - Group met online, organized by Elizabeth Christensen. James Nelson and Philip Johnston delivered a talk
Hyderabad PGDays 2026 took place from 20-21 August 2026
Organizers:
Program Selection Committee:
Code of Conduct Committee:
Volunteers:
Speakers:
Beyond observability: building the synchronized, simulatable model AI agents
need before they touch production
The digital twin you are building may depend on one you have not built yet.
Most digital-twin programs begin with a physical asset: a building, factory, vehicle, power grid, or machine. An insurer, for example, might maintain a digital representation of a commercial property using sensor readings, inspection results, maintenance records, weather conditions, occupancy patterns, and claims history. The purpose is not simply to display the building. It is to understand its present condition, forecast how its risk may
change, and test possible interventions before acting.
But the building twin rests on another system. Its data must be captured, validated, replicated, governed, stored, queried, and kept current. Schemas must evolve without breaking downstream models. Pipelines must surface missing events. Historical state must remain traceable. If the data platform beneath the twin drifts from its actual operating condition, the asset model inherits that distortion.
This raises a question that deserves more attention:
Should the data platform itself have a digital twin?
I am using the term deliberately. The National Institute of Standards and Technology
describes a digital twin as a computer model of a physical system and treats forecasting—through simulation, monitoring, optimization, or decision support—as foundational. A data platform is not a physical asset in the same sense as a turbine or building. The idea here is an architectural extension: apply the same discipline of synchronized state, relationships, history, and simulation to the platform that produces the digital representation.
For PostgreSQL, that extension is both practical and timely.
PostgreSQL already exposes unusually rich evidence about its internal state. What it does not provide automatically is the coherent, continuously updated, simulatable model that would turn that evidence into a platfo
[...]PostgreSQL 19 introduces a new SQL command, WAIT FOR, that lets a session block until WAL has reached a specific position. This gives us read-your-writes consistency on asynchronous replicas without paying the synchronous replication tax.
WAIT FOR LSN 'lsn' WITH ( option [, ...] ) ];
where option can be:
MODE 'mode' TIMEOUT 'timeout' NO_THROW
Express the branching scenarios of your business logic as a directory tree; walk it with savepoints so each branch inherits its history instead of rebuilding it; and let the walk decide whether your deployment commits.
By Alexey Evlampiev
Abstract. Database tests often repeat the same state-building work, because several scenarios share the same prefix: a device may be provisioned before testing its configuration paths; an order may be paid before testing shipment and refund; a workflow may be approved before testing its downstream outcomes. The running example throughout is an order lifecycle — chosen only because its branching states are easy to see, and standing in for whatever lifecycle your own database implements. To test both placed → paid → shipped and placed → paid → refunded, a conventional suite constructs placed → paid twice. This article develops the alternative: express the scenarios as a directory tree and walk it with PostgreSQL savepoints — execute the shared prefix once, test one branch, roll back to the branch point, and test its sibling from the same inherited state. Every scenario then runs against the accumulated state it actually depends on, without rebuilding that state and without seeing a sibling’s changes. A lifecycle’s reachable histories branch like a multiverse, far beyond what a practical suite can cover, so the tree is authored, not exhaustive: you choose the critical paths, and the walk proves each one from the exact parent state it depends on — proof here meaning execution plus declared-invariant checks, not formal verification. And because most PostgreSQL DDL is transactional, the whole walk can run inside a still-uncommitted deployment: apply the migration, run the tree, discard the test state, and commit only if every authored scenario passes.
COSCUP 2026 was held on August 8–9 at National Taiwan University of Science and Technology (NTUST) in Taipei. As one of Taiwan’s largest annual open-source gatherings, COSCUP brings together developers, users, communities, and open-source advocates from Taiwan and around the world.
This year was particularly international. COSCUP was co-hosted alongside UbuCon Asia 2026 that feature more than 20 tracks and dozens of community booths. Among them, the PostgreSQL community had a much stronger international presence this year.
I was honored to represent the international PostgreSQL community at COSCUP this year, together with Bruce Momjian, Robert Treat, and Grant Zhou from HighGo, joined by Julien Rouhaud and Mr. Ku from the local community. Together, we brought PostgreSQL to Taipei through a series of talks and a dedicated PostgreSQL community booth, where we had the opportunity to meet and connect with Taiwan’s open-source community face-to-face.
It was also the first time visiting Taiwan for Bruce, Robert, and Grant. I was glad to see them enjoy the people, food, and atmosphere of Taiwan. As a bonus, we also got to experience a little bit of Taiwan’s summer tradition, a typhoon.
Overall, it was a great conference to be part of, filled with meaningful conversations, new connections, and plenty of PostgreSQL. In this post, I’d like to look back at COSCUP 2026 from my own perspective and share some of the highlights from our time in Taipei.
The COSCUP experience actually started the evening before the conference with the Welcome Party at Hua Shan Ding Bistro in Taipei. It was a casual gathering that brought together people from many different open-source communities and tracks—including Ubuntu, Python, and many others—to have a drink, meet new people, and talk about all things open source before the busy conference weekend began.
There were not many PostgreSQL folks at
[...]The PostGIS Team is pleased to release PostGIS 3.7.0rc1! Best Served with PostgreSQL 19 Beta 3 , GEOS 3.15.0rc1 , postgis_tiger_geocoder 2025.2 , and address_standardizer.
This version requires PostgreSQL 14 - 19rc1, GEOS 3.10 or higher, and Proj 6.1+. To take advantage of all features, GEOS 3.15+ is needed. To take advantage of all SFCGAL features SFCGAL 2.3.0+ is needed.
This release contains fixes since 3.7.0beta2 release.
Cheat Sheets:
This release is a release candidate of a major release, it includes bug fixes since PostGIS 3.6.4 and new features.
The PostGIS development team is pleased to provide postgis_tiger_geocoder extension. This is the very second 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.
postgis_tiger_geocoder has its own dedicated repo at OSGeo Gitea postgis_tiger_geocoder under the PostGIS org.
The versioning model is versioned based on the year of the Census US Tiger dataset that is current at time of it’s release.
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!
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.
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.
setval(seq, max(id)) recipe quietly does nothing at all, since setval handed a NULL returns without acting.
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.
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.
snapshot_conf.
It also introduces a new blocking locks analysis module:
relation, transactionid, tuple, and so on).
pg_blocking_pids(), so even soft blocks (sessions that are just ahead in the lock wait queue) are counted, not just hard conflicts.
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.
OPENAI_BASE_URL and OPENAI_MODEL environment variables.
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.
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:
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:
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:
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.
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:
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.
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.
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.
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.
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?
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
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:
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
[...]Number of posts in the past two months
Number of posts in the past two months
Get in touch with the Planet PostgreSQL administrators at planet at postgresql.org.