\if :{?LUA_RELEASE}
\echo :LUA_RELEASE
\luacode
psql.registerCommand ( {
  name = "my.dt",
  help_syntax = "\\my.dt[+] [PATTERN] [-OPTION]",
  help_desc = "list tables possibly sorted by size",
  handler = function(ss, ab, cmd, verbose)
    local filter = "  AND n.nspname 

 'pg_catalog'\n" ..
        "  AND n.nspname !~ '^pg_toast'\n" ..
        "  AND n.nspname 

 'information_schema'\n" ..
        "  AND pg_catalog.pg_table_is_visible(c.oid)\n"

    local sort = "ORDER BY 1, 2";

    local opt = psql.scanSlashOption(ss, psql.OT_NORMAL, false)

    if opt == "-help" then
      print "my.dt[+] [PATTERN] [-OPTION]     list tables, possibly sorted"
      print ""
      print "Options:"
      print "  -asc-size         sorted by size in ascending order"
      print "  -desc-size        sorted by size in descending order"
      return psql.PSQL_CMD_SKIP_LINE;
    end

    if opt and string.sub(opt,1,1)  ~= "-" then
      local schema, tablename, dot
      if opt == "*" then
        filter = "  AND pg_catalog.pg_table_is_visible(c.oid)\n";
      else
        dot = string.find(opt, "%.")
        if dot then
          schema = string.sub(opt, 1, dot - 1)
          tablename = string.sub(opt, dot + 1)
        else
          tablename = opt;
        end
        if schema then
          if schema ~= "*" then
            filter = "  AND n.nspname = '" .. psql.connect():escape(schema) .. "'\n"
          else
            filter = ""
          end
        else
          filter = "  AND pg_catalog.pg_table_is_visible(c.oid)\n"
        end

        if tablename then
          if 

History and trade-offs with UUIDs

The system uses UUID primary keys throughout. I typically recommend starting with bigint and sequences over UUID v4 primary keys, although here uuid v1 was used. Insert performance is not as bad for v1 compared with v4.

Still though, v7 brings better performance than both for inserts and can also result in smaller indexes with fewer page splits meaning less CPU and IO.

What drives bad performance for v4 and to a lesser extent v1? Let’s do a quick refresher. As new table rows are inserted and a primary key is defined, primary key values are maintained in sorted order in a b-tree index. Just like table rows, index entries in Postgres are stored in fixed size 8kb pages.

Postgres needs to know in which page to place the new index entry. For sorted order, the first bytes of new uuid values are compared.

For v4 given new values are very random and not monotonically increasing (they lack “monotonicity”), values can be earlier or later, meaning they’re unlikely to be placed into the same recently accessed page. This is bad for caching!

When new values are monotonically increasing, the recently accessed page is “hot” in the Postgres buffer cache (in memory copy of the on-disk page).

When Postgres is not able to use the hot index page for the newly inserted value, that page could be outside the buffer cache, not in

DocumentDB is a MongoDB-compatible document database built on PostgreSQL. It adds the BSON data type and a full CRUD API to Postgres, and ships a gateway that speaks the MongoDB wire protocol -- so existing MongoDB clients (mongosh, pymongo, the Node.js driver) can connect to a PostgreSQL server as if it were MongoDB. It's the same engine behind Azure DocumentDB.

DocumentDB is included in the pglayers-azure profile image, which mirrors the open-source extensions available in Azure Database for PostgreSQL. This post walks through running the image and talking to it from a MongoDB client end to end -- including a password gotcha that trips people up.

1. Start the container

Run the pglayers-azure image, exposing PostgreSQL on 5432 and the DocumentDB gateway on 10260 (the port the wire protocol listens on):

docker run -d --name pglayers-docdb \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 -p 10260:10260 \
  ghcr.io/pglayers/pglayers-azure:18

The profile image auto-configures everything at boot: it sets shared_preload_libraries (including pg_documentdb_gw_host, the gateway worker), appends the required GUCs, and auto-creates the documentdb extension on first init. Within a second or two the log shows TCP listener(s) bound to port 10260 and the gateway is ready. You don't need to run CREATE EXTENSION yourself.

Use PG 18 (isolated layout) or PG 17. DocumentDB is built only for 17 and 18 -- not 19 -- so don't use pglayers-azure:19 for this.

2. Create a MongoDB user

The gateway uses native SCRAM authentication, and its configuration blocks a set of role name prefixes (documentdb, citus, pg, internal_role). That means you can't reuse the postgres superuser -- you need a fresh role with a password.

The intuitive approach is DocumentDB's own documentdb_api.create_user() function, but on a stock image it fails:

ERROR:  password type is not a plain text
CONTEXT:  ... CREATE ROLE mongoadmin WITH LOGIN PASSWORD 'SCRAM-SHA-256$...'

The reason: the server runs with password_encrypti

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

• Postgres version: 13.6
• A monolithic (non-partitioned) table, size 750 GB, 16 billion rows
• Several indexes, but none of them were super useful for this particular search

And there is a query I needed to optimize. Yes, it looks simple/obvious, but wait till I get to the details!

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:

• Ameen Abbas
• Hari Kiran P
• Rajesh Madiwale

Program Selection Committee:

• Deepak Mahto
• Gayathri Varadarajan
• HariKrishna B
• Jobin Augustine
• Pavlo Golub

Code of Conduct Committee:

• Shashidhar Dakuri
• Rumi Abbas
• Rushabh Lathia

Volunteers:

• Bikash Chandra Rout
• Deevena Ande
• Faisal Ashraf
• Keerthi Seetha
• Kushwanth Kumar Ganta
• Mandyam Lokesh
• Nithin Kumar
• Nashera Fatima
• Y Pavan Sai Nishith
• Pranav Salota
• Sai Krishna Namburu
• Sashikanta Pattanayak
• Shameer Bhupathi
• Syed Ayaan
• Pabbathi Varshene
• Vaishnavi Vadapalli
• Venkata Krishna Bandlamudi

Speakers:

• Ameen Abbas
• Anuradha Chintha
• Ashutosh Bapat
• Aswini Kumar Tummala
• Avi Vallarapu
• Bikash Chandra Rout
• Dinesh Salve
• Hari Kiran
• InduTeja Aligeto
• Jobin Augustine
• Kabilesh PR
• Kevin Biju
• Manan Gupta
• Suman Michael
• Pat Wright
• Pavan Deolasee
• Prafulla Ranadive
• Pranav Salota
• Purnima Kumari
• Rahul Singh
• Rajesh Madiwale
• Raj Verma
• Sai Krishna Namburu
• Sashikanta Pattanayak
• Shameer Bhupathi
• Shashikant Shakya
• Shreya Radhakrishna Aithal
• Sivasankar Prasad
• soqrabanu rumi
• Sravan Velagandula
• Subhani Shaik
• Veeranjaneyulu Grandhi
• Venkat Akhil
• VIKAS GUPTA
• Vinay Kumar Dumpa
• Vishnu Das
• Y V Ravi Kumar

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

Scenario-Tree Testing in PostgreSQL: Every Authored Branch, Shared History, Before COMMIT#

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.

Introduction

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.

Long post ahead

The Welcome Party

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.

• 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.

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!

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