Hello fellow Postgres Enjoyer. I'd like to do something a little different this week and talk a bit about how I got ensnared by the Postgres ecosystem, and what I've done with it over the years. Maybe you have something better to do with your Friday than listen to some old guy reminiscing about Postgres, but I promise you it'll be worth the read.There's a reason I've been a dedicated Postgres zealot for over 20 years now, and it definitely isn't because of the name.

Postgres Sucks!

A pg_dump data-only restore into a fresh, empty copy of the schema is the safest-sounding restore in PostgreSQL, since there is not a single row in the target for the incoming data to collide with. It stops anyway, on a table sitting plainly in \dt.

psql:/tmp/data.sql:56: ERROR:  relation "book_audit" does not exist
LINE 1: INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'inser...

book_audit exists. The statement that cannot see it never appears in the dump, because it lives inside an AFTER INSERT trigger on books, and the reason a bare table name stops resolving is one line the dump wrote at the top of itself before any data moved.

SELECT pg_catalog.set_config('search_path', '', false);

That pins search_path to the empty string for the rest of the session, and every unqualified table reference inside a trigger body goes down with it. The dump breaks its own restore.

Folklore warns about a different failure here, the one where the trigger fires successfully during the books load and collides with the audit rows the dump is putting back. That failure is real. You meet it second, and only in a session whose triggers can still resolve their own tables.

Reach for one of three levers at this point. --disable-triggers bakes trigger suspension into the file, one transaction with SET CONSTRAINTS ALL DEFERRED fixes load order, and session_replication_role = replica switches enforcement off for the session. Two of the three bill you in a currency nobody mentions until the restore is half done.

Key Takeaways

• The dump's own preamble pins search_path to the empty string, so a trigger function that names its own tables without a schema breaks the restore with relation ... does not exist, even into a target holding zero rows.
• A trigger error rolls back the entire COPY it interrupted, leaving that table at zero rows while every other table in the file loads normally around it.
• The trailing setval calls fire whether or not the matching table loaded, so a sequence can sit at 5 abov

\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

We're excited to unveil pgwatch v6.0.0-beta — the biggest release since v5. It's a big one, so I'm splitting the tour into two posts. This first post is about the headline feature: pgwatch can now scrape a Prometheus exporter directly, as a first-class source, side by side with your regular PostgreSQL sources.

Why a Prometheus source at all?

pgwatch's whole reason for existing is turning SQL queries against pg_stat_* views into stored measurements. That works great for anything Postgres itself can tell you. It falls apart the moment you need something Postgres can't see about itself, e.g. is this node the primary or a replica right now, according to the cluster manager? How far behind is replication as Patroni sees it, not as pg_stat_replication sees it? What's node_exporter saying about disk I/O on this box?

Until now the answer was "run something else next to pgwatch." Starting with v6.0.0-beta, pgwatch can just scrape that something else itself.

Adding a Prometheus source

A prometheus source looks almost exactly like a Postgres source, just with a URL instead of a DSN:

- name: patroni-prod-node1
  kind: prometheus
  conn_str: "http://patroni-node1:8008/metrics"
  preset_metrics: patroni
  is_enabled: true

Point conn_str at any endpoint that speaks the Prometheus text exposition format, pick a preset (or list custom_metrics families and intervals yourself), and pgwatch takes it from there. The built-in patroni preset alone covers twelve metric families — patroni_primary, patroni_replica, patroni_xlog_location, patroni_dcs_last_seen, patroni_pending_restart, and friends.

Basic Auth and TLS work exactly the way you'd expect from a URL:

conn_str: "https://user:secret@patroni-node1:8008/metrics?tlsskipverify=true"

tlsrootcert= and tlsskipverify=true are stripped before the request goes out, so they never leak to the exporter. And yes, the password is redacted from every log line, that means no secrets showing up in your journal because someone grepped for the source name.

Your Deployment Is a PostgreSQL Program#

Every migration tool is a program that executes your SQL. Invert the control flow and deployment policy becomes SQL the project owns.

By Alexey Evlampiev

Abstract. Every team that adopts a migration tool eventually goes looking for a flag. The release needs one thing the tool did not anticipate — a check that must run after the schema change but before the commit, a concurrent index built in the middle of an otherwise transactional deployment, an ordering rule that filenames cannot express — and the search begins: through the configuration reference, then the issue tracker, then the changelog of a version that has not shipped. The flag is the visible symptom of an invisible arrangement. A migration tool is a program that executes your SQL, which means every deployment semantic — what runs, in what order, inside which transaction, and whether the result is allowed to commit — belongs to the tool’s vocabulary. Anything outside that vocabulary is a feature request. This article describes the inversion: a tool that prepares one PostgreSQL session, materializes the project as relations inside it, and hands deployment policy to a SQL program the project owns. The tool keeps the execution mechanism; the project takes the policy. What used to require a tool feature becomes SQL the project owns. The scope is the database semantics — choosing and ordering the work, controlling its transaction boundaries, validating the result, and deciding whether it may commit — and not the cloud APIs, secret stores, and approval gates around them. The costs are specific too, and the last part names them.

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

Deploying a database is the easy day. The interesting days are the ones after: it needs more memory, more read capacity, or a version bump, and you would rather not take an outage for any of them. With the CYBERTEC PG Operator (CPO), all three are one-line changes to a manifest, and the operator rolls them out while the database stays up.

Every command and output below is from tutorials we ran end-to-end on a fresh minikube (Kubernetes 1.30, CPO 0.9.2).

Resize CPU/memory with an automatic switchover

Bump the resources:

kubectl -n cpo patch pg pg-cluster --type=merge -p '{
  "spec": { "resources": {
    "requests": { "cpu": "500m", "memory": "1Gi" },
    "limits":   { "cpu": "1",    "memory": "1Gi"   } } } }'

The operator doesn't restart everything at once. It rolls the change out the careful way and you can watch it happen:

NAME           READY   STATUS        ROLE
pg-cluster-0   1/1     Running       master
pg-cluster-1   1/1     Terminating   replica   # replica updated FIRST
pg-cluster-1   1/1     Running       (none)
pg-cluster-0   1/1     Terminating   (none)    # then a SWITCHOVER...
pg-cluster-1   1/1     Running       master    # ...pg-cluster-1 is now leader
pg-cluster-0   1/1     Running       replica   # old leader rejoins as replica

Replica first, then a switchover to the freshly-updated node, then the old leader, so there's no window where the cluster is fully down. We confirmed the new limits applied (" memory":"1Gi") and the cluster stayed healthy throughout.

Reconfiguring PostgreSQL itself is the same idea,  set a parameter and Patroni applies it cluster-wide:

kubectl -n cpo patch pg pg-cluster --type=merge \
  -p '{"spec":{"postgresql":{"parameters":{"max_connections":"200"}}}}'
# a few seconds later:
kubectl -n cpo exec pg-cluster-0 -- psql -U postgres -tAc 'show max_connections;'
# 200

Scale out (and back in) by changing a number

Need more read replicas? Change numberOfInstances:

kubectl -n cpo patch pg pg-cluster --type=merge -p {"spec":{"numb

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