log_replication_commands is log_statement = all for walsenders, with the one difference that you should turn it on. A replication connection (replication=1 for physical, replication=database for logical) speaks a small language of its own: IDENTIFY_SYSTEM, CREATE_REPLICATION_SLOT, START_REPLICATI…

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

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

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

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

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

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

Three-Valued Logic with OR and AND

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

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

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

[...]

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

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

Step 1

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

This file has all you need.

Step 2

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

Opening the northwind.sql file

Step 3

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

The contents of the northwind.sql file

Step 4

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

Use Alt + X to execute the northwind.sql file

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

Step 6

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

[...]

Aggregate query statistics hide the forest of cheap queries.

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

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

pg_stat_lock {#pg_stat_lock}

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

Your repository contains two version control systems.

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

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

The control group: declarative schema files

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

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

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

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

Editing is forbidden

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

[...]

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

Imagine an AI agent handling a customer refund.

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

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

So, did the AI successfully issue the refund?

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

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

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

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

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

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

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

[...]

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

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

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

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

Here is the event recording:

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

Our next meetup is on September 22! Register here!

Control what bind parameters PostgreSQL logs alongside statements and errors.

Your API accepts the change. It returns 201 Created or 200 OK, the system has saved the user's change, and the moment the user clicks, the change disappears from the app, only to resurface seconds later. That's if you are lucky. There's no error. The change simply ceased to exist for a while.

In the era of server-side-rendered applications this was a non-issue: either the state was managed as part of a single request, or the user was too slow to outrun the system. The modern application changed that. It fires off the mutation, invalidates the cache, and wakes up the state management, all in parallel and milliseconds after the write. The snappier your frontend feels, the more reliably it outruns your replica.

Throw a collaborative product into the mix and it gets worse, because every change sent over a websocket invalidates state on every teammate's open tab and device, and all of them go back to the same endpoints on the same replicas.

Some of the common workarounds are:

• pin reads to the primary (which you really want to avoid)
• add sleep delays
• set flags in Redis

Your application has to act like a traffic conductor. It guesses the replication lag using timeouts and Redis flags. The replica already knows exactly where it stands; your code just has no way to ask.

PostgreSQL 19 adds a way to ask. On the standby:

WAIT FOR LSN '0/554D1B78';

The standby blocks until it has replayed that position, then returns and lets the next statement run. That is all a reader needs to know to follow the numbers below.

There is a great deal more to it, and my friend Gülçin Yıldırım Jelínek wrote it up last week: why it has to be a top-level command rather than a function, the self-deadlock that rule prevents, and the 2016 proposal it grew out of. Read hers for that; it is the account I kept failing to write, and it saved me a lot of work. Everything below is what happened when I put the statement in front of traffic and measured it.

What makes a replica lag

Replica lag is a comb

[...]

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

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

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

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

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

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

Three-Valued Logic with OR and AND

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

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

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

[...]

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

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

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

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

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

[...]

Did you know: PG19 is on track to contain the most changes to pg_catalog in history

Introduction

pg_catalog is one of the most important interfaces PostgreSQL gives us: it exposes the metadata that describes database structure – tables, columns, indexes, constraints, types, and dependencies – alongside views into what the server is doing right now. When diagnosing replication lag, long-running or blocked queries, idle transactions, lock contention, vacuum activity, and other real-time performance problems, pg_catalog is often where I’m spending my time poking around.

Every so often, working with those catalogs leads me to a question that sounds like it should have a quick answer:

I wonder when that changed.

When was leader_pid added to pg_stat_activity? Has pg_locks always had waitstart? Which catalogs and views will be new in PostgreSQL 19?

Those answers are available in the PostgreSQL documentation, tracking changes over time isn’t very trivial. Opening the documentation for several releases, comparing tables, and then checking release notes to understand what happened – this can get tedious very quickly.

I wanted something simpler, and pg-catalog-almanac, a browsable representation of the PostgreSQL documentation for pg_catalog hopefully accomplishes that.

---

Inspiration

postgresqlco.nf is my favorite PostgreSQL reference outside of the official documentation. It makes the history of configuration parameters easy to explore: choose a setting, see the versions, and quickly understand how it evolved. And then there are useful links to articles as well.

I wanted that same experience for PostgreSQL’s system catalogs, system views, and statistics views. pg-catalog-almanac may not be as feature-rich as postgresqlco.nf but I hope it gets close – it currently covers all 143 documented relations across PostgreSQL 9.6 through the upcoming PostgreSQL 19.

---

Some Interesting Discoveries

Once I got the versions placed next to each other, I found these observations

[...]

Three parameters control what reaches your server log: a severity floor, whether to attach the triggering SQL, and how many fields each message prints.

A log line has two parts: the message, which PostgreSQL decides, and everything in front of it, which you do. log_line_prefix is the join key. Every fact that lets you connect a line to a session, a transaction, a client, or a moment in another system’s logs has to be in the prefix, because the m…

I finished patch that integrates Lua language to psql client https://github.com/okbob/lua-psql I wrote two examples. Second is a wrapping VACUUM command. Target is a more readable output in verbose mode. One bonus is printing progress - any second the pg_stat_progress_vacuum is selected and result is printed.

function vacuum(args)
  local options = {}
  local tables = {}

  if args == "help" then
    print "\\lua vacuum {verbose=true}"
    return
  end

  if args then
    local transfx = load("return " .. args)
    options = transfx()
  end

  local query = 
[[SELECT quote_ident(n.nspname) AS nspname,
         quote_ident(c.relname) AS relname
  FROM pg_class c, pg_namespace n
 WHERE n.oid = c.relnamespace
  AND n.nspname 

 'information_schema'
  AND n.nspname !~ '^pg_toast'
  AND c.relkind IN ('r','s','n')]]

  local rs, err = psql.exec(query)
  local data = rs:fetch()
  while data do
    table.insert(tables, { nspname = data.nspname, relname = data.relname } )
    data = rs:fetch()
  end
  rs:clear()

  local n = 0

  local mycon = psql.connect():clone()
  rs, err = mycon:exec("select pg_backend_pid()");
  local data = rs:fetch()
  local pid = data.pg_backend_pid

  for _, tbl in ipairs(tables) do
    if options.verbose then
      print(string.format("\27[7mVACUUM %s.%s \27[27m", tbl.nspname, tbl.relname))
      mycon:sendquery("vacuum verbose " .. tbl.nspname .. "." .. tbl.relname)
      mycon:sendquery("select pg_sleep(20)")
    else
      print(tbl.nspname .. "." .. tbl.relname)
      mycon:sendquery("vacuum " .. tbl.nspname .. "." .. tbl.relname)
    end

    mycon:consumeinput()
    isbusy = mycon:isbusy()
    while isbusy do
      n = n + 1
      if mycon:resultwait(1) == 0 then
        rs, err = psql.connect():exec(string.format("SELECT * FROM pg_stat_progress_vacuum WHERE pid = %d", pid))
        if err then
          error(err)
        end
        data = rs:fetch()
        if data then
          io.write(string.format("\rphase: %s, blks total: %d, blks scanned: %d, indexes total: %d, indexes processed

[...]

PostgreSQL's log rotation has three parameters that only make sense together: one rotates at clock boundaries, one at file sizes, and one decides whether to…

[...]

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!

One of the blessings and curses of the internet is that anything you say within its confines lives in perpetuity. Or perhaps in my case it's better to describe it as infamy. Now, before I start incriminating myself, I actually contributed to Postgres in a tiny way as early as 2002 with Postgres 7.3 when I submitted a  utility.The actual story behind that utility is something else entirely. It all actually started in this thread where I adamantly defended Oracle (lol) and then moaned constantly about how much  sucks. Here's a fun excerpt:Yes, I've always been sort of obnoxious. Yes, I've mellowed out immensely over the years. And yes, it's hard not to giggle looking back, given that all of this started over 2GB of bloat. But thanks to that thread, I started this one regarding .Essentially what happened is that there was a bug in  way back in the 7.x days that prevented it from cleaning up indexes. So even though a table may have been reduced from 2GB to 100MB through a , the index would continue to grow. To get around this, I took the  script, renamed it to , re-tooled it a bit to rebuild indexes, and ran it on occasion to reindex every table in the database. And by "on occasion", I mean:Yes, this particular system was vastly improved by reindexing every table in the database every two hours. I can't stress enough just how bad Postgres was back then. Up until 7.1, every  required an exclusive table lock, for example. Can you imagine? Not , just regular . An exclusive lock. Ridiculous.Eventually I pushed to remove Postgres from ou[...]

In the first post we covered the headline feature of v6.0.0-beta: Prometheus exporters as a native pgwatch source. This second post covers everything that shipped alongside it: a full dashboard overhaul, a reaper core that's noticeably harder to kill, four new metrics, and a security fix worth knowing about even if you never touch Prometheus.

Dashboards, reimagined

Every bundled dashboard has been rebuilt on Grafana's new v13 schema (dashboard.grafana.app/v2), with a unified row layout and a shared "colophon" footer across the board. That's the boring-sounding part. The visible part is that Prometheus sources — the ones from post one — now get first-class dashboard parity with Postgres, not an afterthought.

New Prometheus-side dashboards in this release include:

• Lock Details mirrors the Postgres v13 "Locks (PG19+)" dashboard: lock waits/sec by type, average wait time per acquisition, fast-path lock pool exceeded counts, and time since the last pg_stat_lock reset.
• Server Log Events displays server log events by database and by whole instance, plus "Top ERROR generating DBs" and "Top FATAL generating instances" panels that link straight into the per-database overview dashboard.
• Postgres Version Overview is a single "Monitored DBs by version" panel driven off the settings metric, for fleets where "which Postgres version is running where" is a recurring question.
• Stored Procedures dashboards, matching the existing Postgres sproc views.

A brand new Patroni Cluster Overview, now with per-cluster health summaries, node role and leader-lock panels, DCS-last-seen age, and WAL write/received/replayed location. All this is fed by the new patroni Prometheus preset from post one.

If you're running a Patroni cluster, that last one is worth a look on its own: "Clusters Without Leader" and "Paused Clusters" panels turn split-brain risk into a single number that should read zero.

A reaper that doesn't choke under load

The other half of this release is entire

[...]

All three of these do nothing unless logging_collector is on; they describe the files the collector writes. Where, what they’re called, and who can read them. The defaults for all three are fine for a laptop and wrong for a server, and the reasons are more mechanical than the documentation lets o…

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

[...]

[...]

PostgreSQL's logging collector is a small pipe-based daemon that prevents message loss and garbling—but turning it on requires a restart, which you'll want on…

Ten years ago, I attempted to enhance the \dt+ command to sort results by size. There were perhaps a hundred discussions, yet no consensus was reached on a new syntax. Eventually, I created the pspg tool, which allows results to be sorted by any column based on the vertical cursor position. Now, I have prepared a set of patches that integrates Lua into psql. Thanks to these modifications, anyone can write their own \dt command with the desired behavior:

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

[...]

PostgreSQL 18 adds granular connection logging.

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

[...]

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.