Something which has been happening quietly in the background all the time, but we didn’t notice until we looked close enough to see it.

After a lot of work I discovered this was partly a case where someone had dropped an email to the postgres hackers list years ago “hey what about this” and everyone thought “nah it can’t be that bad” … and now here we are seeing production outages!

Exhibit A: Feb 2021, while the feature was being developed, a year and a half before it shipped, Stephen Frost

• Issuing unnecessary prefetches for blocks we’ve already sent a prefetch for is arguably a concern even if FPWs are off but the benefit of doing the prefetching almost certainly will outweight that and mean that finding a way to address it is something we could certainly do later as a future improvement. I wouldn’t have any issue with that. Just doesn’t seem as clear-cut to me when thinking about the FPW-enabled case. Ultimately, if you, Andres and Munro are all not concerned about it and no one else speaks up then I’m not going to pitch a fuss over it being committed, but, as you said above, it seemed like a good point to raise for everyone to consider.

https://www.postgresql.org/message-id/20210214231815.GO27507@tamriel.snowman.net

Exhibit B: Mar 2025, clear report from the field, Michael Zhilin (Postgres Pro)

• One customer complained that physical standby has significant replay lag (few hours) on modern powerful bare metal servers. According to output of “top”, startup process and few kernel workers utilized CPU, mostly in kernel space. The incident has been resolved by disabling recovery_prefetch after quick analysis of perf and strace outputs. Actual XLog prefetching code doesn’t consider that shared buffer may be loaded from WAL FPI rather than from disk and makes fadvise calls in such cases.

https://www.postgresql.org/message-id/9bdb428a-fc8e-4801-8fff-1ad0bc6fa76b%40postgrespro.ru

Both of these emails were on the right track, but didn’t quite get the complete picture yet.

First: FPI/FPW is ubiquitous today. While it’s very expensive in terms of performance, it’s the only way Postgres has of protecting from torn writes on OS restarts. As a result, the majority of production Postgres installations today (community oss version) enable this.

Second: synchronous replication is becoming more common. There are really only two ways to completely protect community open source postgres against data loss on hardware failures: (1) storage layer replication with oss tech like DRBD or expensive proprietary storage & clustering technology or (2) database layer protection with postgres synchronous replication. The second option is the normal solution with kubernetes and commodity hardware.

Third: something that hasn’t previously been diagnosed/reported – the problem with recovery_prefetch isn’t just about FPIs, it’s also about the fact that when the primary instance does large sequential I/O, recovery_prefetch converts those operations into 8k random reads. For the same number of bytes read, this amplifies IOPS as much as 16x on replicas. This can easily exhaust available IOPS, causing replicas to significantly lag after write activity.

The read IOPS amplification combined with Postgres design for synchronous replication is what turns recovery_prefetch into an actual outage-inducing killer. And it turns out this is trivial to reproduce once you know the steps.

1. Start up postgres with synchronous replication to a standby, on small servers with inexpensive hard drives (for example: cloud disk with 500 IOPS, 2 CPUs, 8GB memory)
2. Initialize pgbench scale 640 for about 10GB data
3. Disable autovac on pgbench_accounts just so it doesn’t interfere with the experiment.
4. Run pgbench at 5 TPS fixed in a terminal you can watch.
5. Update 5% of the table. (Checkpoint and update pgbench_accounts where aid%20 = 0.) This generates a burst of write activity. Pgbench will slow down until it finishes. Wait for this to complete.
6. Kill the walreceiver process to emulate network/system hang or instability that disrupts this connection.
7. TPS drops to zero and all writes hang for over 30 minutes (can be hours with larger pgbench table).

It really is this easy to consistently reproduce this outage. https://github.com/ardentperf-labs/walreceiver-outage-repro

And for what it’s worth, VACUUM itself is quite capable of producing write activity too. The recipe for an outage is simply “enough bad luck”: a network glitch that interrupts streaming replication at the same time as a burst of write activity.

To understand why the whole system hangs, it really comes down to two simple things: (1) synchronous replication and (2) the core “state machine” that defines a Postgres Standby Server.

Remember that with synchronous replication, we’re depending on at least one healthy standby server – otherwise we can’t take writes. (We want to guarantee durability to clients and ensure every transaction is on a remote machine before commit.)

The core standby server state machine is defined in src/backend/access/transam/xlogrecovery.c:

WaitForWALToBecomeAvailable(XLogRecPtr RecPtr, bool randAccess,
							bool fetching_ckpt, XLogRecPtr tliRecPtr,
							TimeLineID replayTLI, XLogRecPtr replayLSN,
							bool nonblocking)
{
	static TimestampTz last_fail_time = 0;
	TimestampTz now;
	bool		streaming_reply_sent = false;

	/*-------
	 * Standby mode is implemented by a state machine:
	 *
	 * 1. Read from either archive or pg_wal (XLOG_FROM_ARCHIVE), or just
	 *	  pg_wal (XLOG_FROM_PG_WAL)
	 * 2. Check for promotion trigger request
	 * 3. Read from primary server via walreceiver (XLOG_FROM_STREAM)
	 * 4. Rescan timelines
	 * 5. Sleep wal_retrieve_retry_interval milliseconds, and loop back to 1.
	 *
	 * Failure to read from the current source advances the state machine to
	 * the next state.
...
...

Fundamentally it looks like this:

Basically, when a Postgres standby switches into WAL-restore mode, it will not go back to streaming until it’s caught up.

A burst of write activity combined with IOPS amplification on all the replicas that causes significant lag can easily mean the database won’t start streaming again for hours.

The IOPS amplification is very clear. Here are the disk stats for the primary (maybe 5 minutes around 300 IOPS):

Here are the disk status for the replica/standby during the same time period (40 minutes at the disk limit of 500 IOPS):

These charts don’t illustrate it, but the number of bytes read from disk is about the same.

What happens if we disable recovery_prefetch? (Note that FPWs remain enabled.)

https://github.com/ardentperf-labs/walreceiver-outage-repro/blob/main/variations/README.md#recovery_prefetchoff

There’s still the pause during UPDATE but we didn’t see the long pause after UPDATE completes.

There is no burst of read IOPS, and the standby server basically stays caught up – applying the 5% update in less than 10 minutes.

Right now, I’m leaning toward disabling the recovery_prefetch setting across the board but I want to see where the discussions go. I think its benefits are a little dubious with FPWs enabled by default.

Appendix: Postgres Standby Server Replay Process

Here’s a more detailed refresher on the various processes involved in the core restore and replay logic of a postgres standby server.

Importantly, note the direction of the arrow between the WAL sender and WAL receiver. With synchronous replication in Postgres this connection is critical – the primary can’t accept writes without it. It’s also very fragile, because if the connection disappears the primary can only sit and wait, blocking all writes, hoping a standby will reconnect soon!

This is a reason to consider two standbys and quorum commit (ANY 1) with postgres. While it does increase cost to run with three total replicas instead of two, it significantly increases the robustness of the database.

If you were wondering, it’s the SBOM thing that I mentioned the other day. [Misc Learnings: SBOMs, Provenance and Attestations]

Postgres Extensions in containers with full inventory, provenance and attestation. I’ve been using plenty of AI Agents to put this together. This blog is a little scattered (apologies) but as they say, I didn’t have time to write a short letter.

Here’s what I believe to be the exact structure of current CloudNativePG images:

I have a bunch of irons in the fire related to this project:

• A downstream fork of CloudNativePG/postgres-extensions-containers which can host a bunch of Open Source code which isn’t allowed by CNCF.
  • I track upstream build infra, design my changes to minimize merge conflicts
• A set of patches fixing issues I’ve found, which I’ve submitted upstream
  • Patches are applied on my fork so that I can get stuff working
  • Upstream often tweaks stuff, so then I need to deal with the merge
• This huge new feature – adding proper SBOMs – which is in a feature branch off my repo
  • Planning to submit this upstream
  • Stacked on top of my other fix PRs
• Another huge new feature – PGRX build support – which is stacked on this SBOM feature
  • Not submitted upstream, will only live in my fork
  • Still want to structure code to minimize merge conflicts from upstream

After working on this for like a week, I realized that the set of commands to validate the security and provenance info was going to be totally different for PGRX than for upstream.

I think this is too confusing to users. There needs to be one simple, consistent command to verify provenance and SBOM material. I didn’t have that at the beginning and the AI Agent enabled racing ahead with the code. I didn’t realize the issue until now.

One thing I had been focused on was not having a bunch of things to copy, if someone needs to mirror images to a private container registry. With that focus, this is what I had inadvertantly ended up with.

For Debian-based extensions:

On the PGRX side, builds were timing out because they ran under qemu emulation. (Which is fine if you’re just installing debian packages.) I refactored the PGRX build to use native GitHub Runners, so that it would use this new design:

But after that refactor, the Debian images would look the same as previously, while the PGRX images would now look like this:

Here’s how Codex summarized it for me:

The main issue is that now it’s a totally different set of commands for users to validate PGRX containers. Most users don’t know or care how I built the container. They just want to use a Postgres extension.

Well this was no good. Time to go back to the drawing board and start over on the design.

Docker has a neat feature where you can provide a custom SBOM generator. This approach didn’t get used in my original design.

So using the agents, I rebuilt everything to use this approach. I also included a plugin API which was designed to be used by my PGRX build process later. Codex was able to leverage the previous code for actually composing the SBOM when it did the refactor. So far, this re-design is looking far more elegant and clean.

Most of the logic ends up self-contained in the custom sbom-generator module. The required changes to the build pipelines were surprisingly minimal. After a lot of work, here’s what I ended up with:

This link will probably break once I’ve merged things and cleaned up working branches, but if anyone is curious: https://github.com/ardentperf/postgres-extensions-containers/tree/x-ai/ardentperf/cnpg-sbom-generator/sbom-generator

One thing that AI could not do: correctly tell me what the right design was. Because it relies on me to know the right questions to ask, telling it the goals & priorities of the design.

Next up: refactoring the PGRX branch on top of this one (without losing native build hosts), cleaning up docs & code here and submitting this PR upstream, continuing to chase my handful of other open PRs upstream.

In the meantime – if anyone is running CloudNativePG and you need extensions… the CNPG-Extensions Project is available for testing now! It’s got quite a bit more advanced automation than the upstream at this point. Renovate is fully automated so that when a new extension version drops, you should automatically see it here (and in the Image Catalogs) quickly.

PG-Cron, PG-Partman, PG-Hint-Plan, PG-Stat-KCache, PGSentinel, PLDebugger, PLProfiler, MySQL/MSSQL FDWs… and lots more!

https://github.com/cnpg-extensions/postgres-extensions-containers

And soon (once I merge the first big PR): with full detailed and attested SBOMs and Provenance.

and last but not least: updated white board photo so you can see progress I’ve made since the last SBOM blog :)

Backstory is that I decided to make CNPG-Extensions an actually serious project. The original name was “Not-CNPG” as a joke about CNCF’s restrictive licensing policies which forbid hosting open source software with licenses like GPL. https://github.com/cnpg-extensions/

As a “serious” project I wanted to provide provenance info so users can more have assurance about the contents of a container image, and so that scanners can accurately report licenses and compare software versions against vulnerability databases. This week I also started exploring support for pgrx extensions with full rust dependency graphs in the SBOM so that tools like trivy can flag RUSTSEC vulns even on packages buried in the dependency tree.

Example Trivy output for a Debian-based extension:

https://github.com/ardentperf/postgres-extensions-containers/blob/x-ai/ardentperf/final-payload-sbom/examples/trivy-sbom-examples.txt

Example Trivy output for a pgrx-based extension (this is not final):

https://github.com/ardentperf/postgres-extensions-containers/blob/x-ai/ardentperf/pgrx-implementation/pgrx/examples/trivy-sbom-examples.txt

A few things I’ve learned along the way:

• Renovate auto-update problem: some extensions (MySQL FDW, PL/Debugger) have a sql version that’s completely different from the package version. There’s no way to know the SQL version outside of manually inspecting source code or firing up a full test container.
• Renovate auto-update decision: I don’t want to promote “release candidate” or “beta” versions on channels that users consider to be stable releases. How this is reflected in a version string varies by extension; requires manual check before promotion. But I want stuff as automated as possible ~ generally I don’t want to have to be approving PRs all the time. I might do a little research and only disable auto-update for extensions that have had beta/rc versions in the past.
• Two ways to sign attestations: Cosign/Sigstore or GitHub Artifact Attestations. CNPG currently uses Cosign. I ended up trying out GitHub Artifact Attestations… until a few days later when I stumbled across a random GH Issue on the ParadeDB project which pointed out that GitHub might have paywalled some of the functionality here behind Enterprise subscriptions (I think for people mirroring repos). So then I went and migrated all my working branches back to Cosign, re-ran tests, etc.
• SBOM problem: docker/moby buildkit uses syft to generate an SBOM. latest version of syft still has open issues around debian packages and basically it can’t detect licenses for a huge number of them. so the default docker SBOM is always missing a bunch of license info.
  • my workaround: adding CodeScan. then CodeScan promptly crashed on the PL/R license file because the file was too big. so i split the file into chunks using a marker at the beginning of each license and scanned each file. this worked and gave me a super highly reliable license scan, though I had to manually re-assemble an SBOM.
• If you install the latest rust using the method where it compiles cargo-about and cargo-cyclonedx, those two packages take… a loooooong time. For two large pgrx builds (pg-parquet and pg-search), my GitHub action workflows hit the six hour timeout and got killed.
• Turns out trivy can also generate SBOMs including Cargo.lock parsing… but you need the latest version because even something as recent as 0.66.0 has bugs where it couldn’t parse Cargo.lock if the project used workspaces. https://github.com/aquasecurity/trivy/issues/10007
  • Even the latest version of Trivy has a bug where it misses git-only deps. AI told me there wasn’t an existing issue… hopefully was right; I filed a new one https://github.com/aquasecurity/trivy/discussions/11236
  • Trivy doesn’t seem to have any capability to emit CONTAINS records in the SPDX SBOM. So I still need syft because I want to work backwards from files actually copied to the extension scratch container, to figure out which packages are included.
• “Modern” software languages (um, like less than a couple decades old?) that handle dependencies are wonderful. But I wanted to add pg-duckdb which is in C++ and so now I need bespoke pg-duckdb dependency processing build scripts… at least this gets easier with AI agents to help do the coding… but yeah not wanting to commit myself to maintaining any custom build systems unless it’s really worthwhile (AI or not)
• GitHub CI being as powerful as it is – and offering free compute – is a much more significant contribution to Open Source than they get credit for. My project to build, test, host and distribute a bunch of kubernetes postgres extensions with strong provenance across two architectures, two major Debian OS base containers, three versions of CloudNativePG… this can trigger some rather impressive counts of GitHub jobs! Like hundreds! And I’m amazed just how much compute GitHub gives away for free, which supports Open Source projects like this.

There are still some big open questions in my mind around how to best manage Postgres Extensions with CloudNativePG. How high of a bar for contributions? How much review is needed? What level of commitment from a contributor is expected – do we want to avoid drive-by contributions of big chunks of code which could become a liability – and how to decide whether to trust someone?

Maybe my CNPG-Extensions project can be an option for a lower bar, in addition to the licensing concerns. But I’m not sure. How important is provenance? I’ve put a lot of effort into it this past week. I’m not sure about random debian packages downloaded from GitHub; how do we know they were built correctly? Do we care? If a package is in the official Debian or PGDG repositories then I tend to have a little more trust in it – is that justified?

And then there are the AI related topics: just because you can ask an AI agent to rewrite the operating system on your laptop, doesn’t actually change the fundamentals of computing that much. Decades ago I had fun running my own wordpress site. I learned a bit and it fueled my enthusiasm. But after the third time cleaning up a hack I decided I’d rather spend my time elsewhere, and I started paying someone else to manage that part. AI agents are getting lots of people excited about coding again which is great. But eventually it comes back to boring and well maintained platforms.

Anybody can throw some code on the internet. That doesn’t mean anybody will maintain the code – ensuring that things are rebuilt after Log4Shell happens again, two years from now. AI is great at writing code but you still better pay attention to what human is at the steering wheel and whether they seem like they are paying attention, or seem like they have any level of commitment to stick around. Or alternatively you need to be willing to take full ownership/responsibility yourself.

I like the OpenSSF scorecard for this; it’s worth a read. https://scorecard.dev/

Just a reminder that AI doesn’t change this – it’s more important than ever.

And a final random musing… here’s a picture of my whiteboard right now where I’m trying to track a hierarchy of five different work streams that I have going all at the same time! Each is a separate git branch, forked from the branch above it (and requiring a rebase whenever an upper branch changes something). Agents mean I can fire off long-running tests and come back 12 hours later to check results and make design decisions, but having five different work streams in parallel is a lot of mental overhead – it’s a little tiring!

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

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

I will show better metrics to watch.

We start with fundamentals.

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

Kubernetes Node E2E Tests

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

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

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

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

container_memory_working_set_bytes is the yellow line: current-inactive_file. It tells current memory usage, excluding linux page cache contents on the “active” file LRUs. The blue line is my own metric, where I’ve excluded all file 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 taking notes, remember that OOM will be a full database crash and restart for Postgres.

Simple. No shmem in the test, no active page cache in the test.

Postgres Simple Sort (ORDER BY)

Now Postgres.

docker run --name graph-repro-run_2-1821100 \
    --memory 512m --memory-swap 512m --detach \
    --env POSTGRES_PASSWORD=graphrepro \
    postgres:18 \
    -c shared_buffers=128MB

In a loop, let’s run a SQL query that sorts rows in memory. Add a half million rows each time until we OOM.

By default, Postgres limits itself to 4MB of working memory for sorts, and spills to temp files on disk after that. Tell Postgres to use more working memory. (Usually you’d decrease working memory if there are lots of concurrent connections all needing memory…)

SET work_mem = '1GB'; 
SET max_parallel_workers_per_gather = 0; 

SELECT count(*) FROM (
  SELECT md5(n::text) AS sort_key 
  FROM generate_series(1, $rows) AS input(n) 
  ORDER BY sort_key
) AS sorted_values;

Tracks pretty closely with Kubernetes agnhost. So far, so good. Postgres uses kernel anon memory to perform sorts. It can sort 4.5 million rows, but sorting 5 million rows crashes the database with OOM.

No active page cache.

Postgres Shared Buffers (database cache) are allocated as shmem by the Linux kernel. In this test, Postgres config has 128MB of memory for cache (cf. green line) but the memory has not been allocated by the kernel. This is because we didn’t create any tables.

Postgres with a Small Workload

Enter pgbench – the Postgres hackers best friend. Lets run it in the background while we test ORDER BY statements.

We’ll run the select-only workload and drop the PK from accounts to force full table scans on the accounts table (dropping the PK will also drop the index). We’re going for memory pressure, not TPS.

pgbench --initialize --scale=4

psql -c "ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey"

pgbench --select-only --client=2 --jobs=2

Scale 4 is about 70 MB.

The distance between yellow and blue lines is kernel page cache contents on active file LRUs. We are starting to see a little more.

The linux kernel allocated about half of the Postgres Shared Buffers. They are allocated on demand after startup.

Now the database crashes when sorting only 4M rows (rather than 5M).

We are also starting to see that the page cache has some active pages, not only inactive pages.

This raises a question: are shmem pages reclaimable? What if the database is completely idle and there’s no workload at all – can we release a few of those shared buffer pages to avoid a crash?

Postgres Shared Buffer Cache without any Workload

To answer that question, stop running pgbench and just create a single large table that we can read into the buffer cache before we start running sorts.

CREATE TABLE shared_buffer_filler AS
  SELECT n, repeat(md5(n::text), 8) AS payload 
  FROM generate_series(1, 440000) AS input(n);

CREATE EXTENSION pg_prewarm;

SELECT pg_prewarm('shared_buffer_filler'::regclass, 'buffer');

This 440,000 rows table works out to about 127 MB in size. The prewarm extension is a handy way to load a table into your buffer cache. (Postgres uses ring buffers for some bulk ops to avoid one operation evicting everyone else from the cache; I’m using pg_prewarm to explicitly ensure the table is fully loaded to the cache.)

No pgbench – we will simply do this prewarm and then run our sorts.

Now we see the full buffer cache has been allocated. The kernel can never again reclaim shmem – and that means our database will crash when we sort a mere 3.5M rows (rather than 4M).

If you’re running postgres in cgroups with memory.max (aka Kubernetes Memory Limit) then you might want to run with shared_buffers lower than what’s typically recommended.

So far, Kubernetes metric container_memory_working_set_bytes is a good indicator of memory use. Now that we’ve established all of our fundamentals lets look at a more interesting case.

Postgres with a Realistic Workload

After prewarm, start pgbench with scale 20 for 300 MB (after dropping the 50 MB PK index). Repeat the sort test.

pgbench --initialize --scale=20

psql -c "ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey"

pgbench --select-only --client=2 --jobs=2

Now we break Kubernetes. :)

Same database crash on the 3.5M row sort. But now, the kubernetes metric container_memory_working_set_bytes (yellow line) is not giving a useful indicator of memory use.

Directly inspecting cgroup metrics: shmem reflects Postgres Shared Buffers and anon reflects working memory of SQL queries. When these approach the container limit, we get a database crash (OOM).

Postgres with a Smaller Buffer Cache

Lets take a look at what happens if we reduce the size of the buffer cache.

docker run --name graph-repro-run_2-1821100 \
    --memory 512m --memory-swap 512m --detach \
    --env POSTGRES_PASSWORD=graphrepro \
    postgres:18 \
    -c shared_buffers=32MB

Run exactly the same test.

Kubernetes container_memory_working_set_bytes is actively misleading. The system looks like it has more memory pressure, but really it has less. Now we can sort 4M records without crashing (instead of 3M).

Active pages in the linux page cache are easily reclaimed under memory pressure. If we want a reliable metric, the simplest and best route is to ignore the page cache entirely (blue line), rather than only ignoring inactive pages (yellow line).

When I find a few more minutes, I’ll show how to add this corrected memory utilization metric with CloudNativePG. Basically, you simply add the pgnodemx extension (https://github.com/pgnodemx/pgnodemx/) then write a CNPG custom monitoring query that does the correct calculation from the cgroup metrics. I’ll also try to find time to run everything against a full kubernetes environment in a production configuration – confirming the patterns hold.

Appendix: MGLRU

Linux’s new Multi-Gen LRU changes everything. It seems to be enabled by default on the latest Debian and Ubuntu LTS releases. I’m not sure if people are enabling it in Kubernetes systems yet.

Here are the last two tests repeated with MGLRU enabled:

With MGLRU, Linux represents the two youngest generations as “active” and my system had min_gen=2 and max_gen=4. Linux seemed much less prone to have pages on “active” generations in these tests running on my laptop, but I haven’t spent enough time with MGLRU to know what workloads would make more pages appear as active to Kubernetes.

Memory pressure is a complex topic, especially once swap enters the picture. I think swap remains disabled on many Kubernetes systems, but this might change. PSI is also an important metric for linux memory pressure. This blog is more focused on utilization than pressure.

In Summary: current - (inactive_file+active_file) remains a very useful metric, and I think it should always be collected for Postgres when it’s running on Kubernetes. I would also collect shmem and anon, and maybe samples of top-N resident - shared from /proc/pid/statm which seems cheaper for frequent collection across a large number of processes than RssAnon from /proc/pid/Status.

Checkpoint Followup Questions

A good follow-up question was raised: can an HA replica can save you from downtime if you want to set a large checkpoint_timeout?

It’s true that Postgres allows promoting a replica without restarting, if there’s an unplanned primary restart and your primary is going to take an hour to come back online (after you increased checkpoint_timeout to 45 minutes). But this glosses over the fact that if the replica experiences a restart, then it will take an hour to start up too. Checkpoints on the primary directly translate into restartpoints on the replica (it’s the same WAL stream).

First case: everything is manually managed by a DBA and there’s little automation. Bugs in the tooling are a risk, but the biggest risk here is human error. As we often say in COE’s: people make mistakes. Hoping they won’t make a mistake is not a realistic plan for a reliable platform.

Second case: postgres is increasingly automated and we need to be careful that our automation doesn’t accidentally restart a replica while we’re promoting it.

Even with automation, common Postgres orchestration kits heavily rely on “the DBA knows how to configure it” (ie. you still can’t trust all of the defaults). One example: PG configuration changes require rolling restarts. Is the default behavior of common orchestration frameworks to continue a rolling restart even if the first node never comes back up? Are we back to the first case of relying on the DBAs to know the specific incantation of special commands they need to run, to ensure they never accidentally end up restarting both nodes? If the rolling restart can’t complete, will the DBA know how to address it without accidentally triggering a restart in any way?

And what if a query is triggering a postgres bug which causes a restart – like consuming enough memory to trigger OOM? This is rare, but it certainly isn’t unheard-of. In this case there’s really nothing we can do – the workload will trigger restarts of both nodes and we still have the extended outage, rather than getting online as soon as we stop the bad query.

Fundamentally, if checkpoint_timeout is being set to a large value, then we’re relying on a hope that whatever causes our primary to restart, doesn’t also cause our replica to restart after we promote it and move our application traffic over.

My opinion remains that it’s best to use database configurations which are as robust and safe as possible – even in the face of software bugs and operator mistakes. This isn’t Postgres-specific – this is how I think about checkpoints across the board with relational databases (SQL Server, Oracle, Db2, etc). The exact purpose of checkpoint tuning in a relational database is directly related to your availability SLOs – it’s for bounding the amount of log replay needed at startup (on both primaries and replicas). The actual startup/replay time can exceed checkpoint_timeout, but this remains the best setting for managing your availability SLO in Postgres.

Postgres Collation

I have a major update on the Postgres Collation front.

Background:

• In 2018 glibc 2.28 shipped with significant changes to sort ordering. As a result, a bunch of Postgres DBAs accidentally corrupted their databases by upgrading their operating system to RHEL8 / Debian10 / Ubuntu20.04 underneath existing databases. (Note that the same corruption happens when any postgres container image is updated with a newer base image.)
• I was working at AWS and became involved early-on with finding a solution for Amazon RDS and Aurora (AWS manages the infrastructure underneath RDS Postgres). The scale of Amazon’s customer base made this an incredible place to learn. I learned more about Linux and Postgres collation than I ever wanted to know <g> … which was shared in a 2024 pgconf.dev talk.
• Based on what I learned, I developed a specialized list of 25 million strings which could find changes in sort order across many languages and locales. The 91 patterns were shared in my glibc-unicode-sorting GitHub repository in 2021. We nicknamed it the “Collation Torture Test”
• Using that list of 25 million strings, I looked at 10 years of history across RHEL, Ubuntu and Debian and I discovered that changes in sort order had been happening for many years – largely unnoticed by Postgres Developers and DBAs. There were even a couple corruption reports on the mailing lists which hadn’t been fully root-caused. This was also shared in the 2024 pgconf.dev talk.

So what’s new? A few things:

1. Joe Conway built on this work, and he had the idea to generate a checksum on the sorted list as a fingerprint of the sort order for a platform. Initially, one driver was wanting to have a validation test running regularly in CI pipelines to give high confidence that sort orders are not changing. But a fingerprint can be useful for many things. Joe presented about this at a few conferences including PGCon 2023.
2. My RHEL/Ubuntu/Debian tests needed to be updated over time with new releases, and I am always behind. Magnus Hagander suggested the idea of leveraging containers to make it easier to run these tests and validate new OS releases faster.

Last week I finally found the time to sit down with this. The Collation Torture Test has now been fully ported to docker and GitHub Action workflows. Using Joe’s idea, I also pivoted from drill-down data to fingerprints – and I have generated fingerprints across more platform combinations than ever before. And my favorite part is an idea I had last week – using colors to visualize the fingerprints across a grid of languages and OS versions. It’s now possible to visually compare sort orders at a glance across all of the combinations.

It makes patterns a lot easier to see!

• ICU sort order changes for every language in every release
• GNU C Library sort order for Korean and C collation changed in Debian 12 and RHEL 9
• English and French have always had identical sorting
• Starting with Debian10/RHEL8, German and Russian match English and French. In Debian, Arabic also matches – but Arabic has some kind of special treatment in RHEL and it has never changed.
• Japanese sort order has somehow never changed on either Debian or RHEL

The performance is also very interesting. In the tables I also record how long the SELECT ... ORDER BY SQL statement took – which tells us how performant the sort is. Version 2.28+ of glibc is a performance disaster. German, English, French, Spanish, Russian, Arabic and Chinese all skyrocket to 2 hours for sorting these 25 million strings. Only Korean, Japanese and C sorting remain performant. (And RHEL’s special version of Arabic stays performant.)

Explore for yourself: https://github.com/ardentperf/glibc-unicode-sorting/

One final thing – I now have a GitHub Actions workflow which will automatically run every two weeks and tell me any time a checksum changes on Debian SID. This provides a real-time indicator of changes coming in the future. There is a badge above the tables and if the badge is green then you know the Debian SID columns in the tables are accurate.

OpenAI Codex and Luna

I used this Collation Update Project as an opportunity to experiment with Luna. A Seattle friend working at OpenAI told me I’m the only person he knows who’s going all-in with “Luna-Low” right now <lol>

(Sol is Codex’s most powerful model for complex tasks, Terra is its balanced everyday model, and Luna is its lightweight option. Think: Opus/Sonnet/Haiku. I’m also running with “Low” effort, which is the lowest effort setting available.)

I switched to Codex recently after I stopped trusting Anthropic. I was doing some Postgres work and suddenly all my sessions started hitting security guardrails and refused to continue working on the project. I wasn’t doing anything remotely related to security but I had a lot of postgres source code in the context window and something started tripping the guardrails. (I was reproducing a bug where postgres follows the wrong fork on a timeline change while replaying WAL.) I tried the buttons to ask for review but probably its just some AI agent reviewing anyway, and it never led anywhere. So I quit Claude and went to Codex. I don’t fully trust OpenAI either but for now they seem less likely to shut me down in the middle of a project for bogus reasons without any remediation.

With both Claude and Codex, I’ve had my share of “take my money” and I’ve had a couple expensive months doing cool projects. But I wanted to try out the other approach: how much mileage can I get without spending hundreds of dollars?

Enter Luna.

The pricing on Luna is insanely low. I run my agents YOLO on an isolated VM with their own creds. I think this collation update project was similar complexity to a few benchmarking projects I recently did on the higher-priced plans with models like sonnet & opus. With Luna-Low-Effort, my agent loops don’t run quite as long before coming back for discussion – but the flow worked for me during this project. I found Luna to be shockingly capable. It didn’t go off in weird directions or make any big mistakes to speak of (i’m sure my prompts play a role too).

End result: on a $20 plan with Luna, I completed a project of comparable complexity to what I previously spent hundreds of dollars to complete.

The per-token-pricing difference between Luna and Terra is massive. There were a couple times I jumped over to Terra or Sol for just one or two questions. I don’t think I needed Sol, and just a couple questions start consuming my quota noticably faster… but in hindsight I think I can probably avoid Sol and use Terra very rarely.

Right now, I’m 2 days in to my weekly quota. Token-monitor says today I have 71 million tokens to Luna for $1.99 and 5 million tokens to Terra for $1.78 (API rates don’t directly apply to subscriptions, but it’s hopefully an informative rough proxy for quota consumption rates). With a $20 Plus subscription, I still have 95% left on the 7-day limit. Shocking mileage out of a $20 subscription.

Example gpt-5.6-luna high Prompt: start a new branch based on latest gh main. we will now create one final set of tests named builtin. it will need a tsv, a dockerfile and a workflow. for this test we choose architecture and locale and version of postgres. (no engine selection, no os selection.) aarch64 and x86_64. pg versions 14 to 19. locales C, ucs_basic, pg_c_utf8 (17+), pg_unicode_fast (18+). double check that i have versions right for locales. use debian 13 as base container. model everything after existing debian and rhel scripts. test locally with act. run all combinations locally to populate the TSV file. continue debugging any issues until you have all TSV values successfully and have done a full matrix run in ACT that completed successfully with matching expected checksums. then add a table into the README after the rhel tables with postgres versions as colums and locales as rows.

First turn ran for about 98 minutes and used:

• 447 top-level calls – 319 exec orchestration calls, 128 wait calls
• 134 shell commands, 157 stdin polls, 20 patches, 5 web calls, and 4 plan updates
• 1 context compaction
• 66,150,735 input tokens and 78,522 output tokens, including 35,082 reasoning tokens

It delivered a complete, working solution correctly identifying core dimensions and main objective.

I forgot to prompt to offer aarch64 as an option, but not to run any tests – so it tried to test aarch64 (didn’t work locally on my x86_64 laptop). It incorrectly thought ucs_basic wasn’t available in oldest PG versions (easy fix). It made a small error in code conflating POSIX and C collation, which I would not have caught without careful review (these often give identical results – but not always – so just running some tests is not sufficient). I refined the way it built its matrix for a more maintainable approach – dynamic instead of static list. Originally the table had PG versions in ascending order, I switched to descending so that recent versions are more visible.

My $20 plus subscription weekly quota might have gone down by 1%

Overall, a resounding success. I’m excited about how much can be done on a low-cost subscription with the latest models!!

1. You probably should not change the checkpoint_timeout setting from its default of 5 minutes. Users who read tuning advice on the internet (or get bad AI advice) about increasing this setting usually don’t understand the risk and significance of trading for RTO/availability. Let’s be honest: many of us don’t scrutinize RTO until we have a real incident and suddenly realize that there is no way to get our application back online. Turns out it does matter to your boss if you’re down! Increasing this parameter can turn a short outage into a long multi-hour outage.
2. Always make sure log_checkpoints is enabled. This has been the default since Postgres v15.
3. With a default checkpoint_timeout setting of 5 minutes, I’m leaning toward a default alarming threshold of 15 minutes on total checkpoint time. You may need to increase disk hardware specs or optimize database workload, if checkpoints take too long.
   • There are two ways this alarm can be built – either by parsing checkpoint messages after they appear in the log, or by looking at the current time and checking how long since the last checkpoint message appeared. The latter is a more robust solution because it will alarm even if no checkpoint message is ever produced.
4. Don’t forget to also monitor for “checkpoints are occurring too frequently” messages in the Postgres log. In some cases, you might consider increasing max_wal_size if checkpoints are frequent. Remember that it’s ok if there are short occasional bursts of write activity. (It’s also completely ok – even healthy – if disks have bursts that hit the peak IOPS.) The thing to watch for is real user & application impact due to extended throttling.

Checkpoint is the heart of your database. It’s buried deep inside. It’s not something everyone talks about, like well-tuned autovacuum or fast queries. But if checkpointer stops beating, then you’re dead.

In addition to its well-understood job of getting dirty pages written from cache to disk in the background, it also has many smaller jobs that are less widely known. Management of a few shared-memory config settings like sync_standby_names and full_page_writes. Fsync batching. Deferred file unlinks. Enforcement of archive_timeout.

A few years ago, I added a happiness hint to have an alarm on the “time since latest checkpoint”. This was partly due to an incident I saw many years ago but which I never managed to blog about. I saw another checkpoint related incident recently, so I thought I’d gather these thoughts together. Both incidents reveal an important lesson in hindsight: it’s dangerous to restart a database when there are checkpoint problems.

The First Incident

Lets see what I can remember about that original incident. It was an ugly 40 hour production outage that happened back in the postgres version 12 era. Someone started getting errors and restarted their postgres database to try to remediate, and the database simply never came back up.

I remember that there were four different things which all combined to make this incident so bad:

1. The workload made heavy use of unlogged tables. It created and dropped tables at a high rate.
2. Somehow there had not been a successful checkpoint on this database for a week. There was insufficient data to figure out why, after we finally got the system back online.
3. Remember that one job of checkpointer is to handle deferred file unlinks. Over the course of the week, while checkpointer was stalled and never unlinked any of the underlying files for the DROP <relation> statements, the system accumulated 27 million files on the filesystem until it ran out of inodes (before the database was restarted).
4. The database was on a large server and had a reasonably large buffer cache. In Postgres, any time a relation is dropped, the entire buffer cache is scanned to remove blocks belonging to that relation. (Some other databases maintain an index type of structure for this but Postgres does not.) This includes unlogged tables and their indexes.
5. When Postgres starts, the first thing it needs to do is replay write-ahead logs starting at the last successful checkpoint. This database needed to replay more than twenty-five thousand WAL files.

The reason the database never came back up was that WAL replay proceeded at a crawl (due to buffer cache scans), and after running for many hours it would inevitably cause the system to run out of inodes again. It would crash before it could complete recovery. When it restarted again, it lost all progress and went back to the beginning.

Eventually we shrank the buffer cache to speed up replay, but recovery was still incredibly slow – and at the end of the day, as stated above, this ended up being almost two days of production downtime.

At the root of this was a severely lagging checkpointer.

The Second Incident

The next incident was more recent and involved a hot standby instance, and a multi-hour outage for that hot standby instance. This time it was a CloudNativePG database running on Kubernetes – but similarly to before, the application was experiencing problems and a database restart was triggered as an early remediation. The hot standby instance never came back up.

Again, a few different factors combined to cause the incident:

1. The workload on the primary experienced a significant increase in write activity. This was running on cloud infrastructure and the underlying disk flatlined at its maximum IOPS with 100% writes. Hot standby databases had identically configured disks and needed to apply the same write activity as the primary (because they are replicating writes).
2. Checkpoints on the primary began to fall significantly behind. Even with a checkpoint_timeout of 5 minutes, checkpoints are taking up to an hour to complete and spending the bulk of that time waiting for write IO. (eg. write=2550s, total=3130s).
3. From an initial pass of postgres recovery code, I think that a replica doesn’t write its first restartpoint until it encounters its second checkpoint record in the WAL stream. Reference CreateRestartPoint() in xlog.c where it says (!XLogRecPtrIsValid(lastCheckPointRecPtr) || lastCheckPoint.redo <= ControlFile->checkPointCopy.redo)
4. Because of the overloaded checkpointer, when the hot standby restarted, reaching the second checkpoint from the restartpoint required replaying ~328 GB total. The WAL records are generated by many connections in parallel, but crash recovery needs to replay the records single-threaded… so it usually takes longer to replay WAL logs than it took to generate them.
5. CloudNativePG was configured with a 60-minute startup probe timeout which would kill and restart the pod if it didn’t open for queries within that time. With the disk I/O still saturated, it was unreachable. This produced a loop: the standby was killed at 18:11, 19:11, 20:11, 21:11, 22:12, 23:12, 00:12, 01:12 — eight restarts. Every hourly session decoded the same 128.6 GB of WAL, preserved none of it across restarts, and terminated at the same position.

I’ve drafted a proposal for a CNPG fix, but again – at the root of this was a severely lagging checkpointer.

Illustrating a Lagging Checkpointer

What does a lagging checkpointer look like? Lets look at some real examples of healthy and unhealthy systems.

For these examples, I’m using the following LogQL query:

max_over_time({namespace="<my-namespace-with-single-cnpg-cluster>"}
   |~ `checkpoint complete`
   | regexp `write=([0-9]+\.[0-9]+) s, sync=([0-9]+\.[0-9]+) s, total=(?P<total_duration>[0-9]+\.[0-9]+) s`
   | unwrap total_duration [$__auto])

We’ll start with an example of a healthy system. Checkpoints complete quickly when it’s idle, and under load the checkpoints appear to use the full 5 minutes (between writes and syncs) – and the checkpoints are keeping up and not exceeding 5 minutes.

Here’s a LogQL query to cross-check the total write+sync time. In the healthy example below, I confirmed the total time was always the sum of write and sync – it’s not including any sleep time outside of those which is part of meeting checkpoint_completion_target:

max_over_time({namespace="<my-namespace-with-single-cnpg-cluster>"}
  |~ `checkpoint complete` 
  | regexp `write=(?P<write_duration>[0-9]+\.[0-9]+) s, sync=(?P<sync_duration>[0-9]+\.[0-9]+) s, total=([0-9]+\.[0-9]+) s` 
  | line_format "wait_duration={{addf .write_duration .sync_duration}}" 
  | logfmt 
  | drop write_duration,sync_duration 
  | unwrap wait_duration [$__auto])

I’d like to dig a little deeper into this; the flat line at checkpoint_completion_target seems suspect to me. I feel like there must be some kind of variable idle/sleep factor somewhere in there, because the load on the system is not a flat line. But I’ll save that for another day.

Here’s an example of an unhealthy system. Up until June 4, the checkpoints were completing within five minutes – but once the system gets overloaded, checkpoint times skyrocket up to an hour at times. This example is from the second incident above, where the lagging checkpoint was driven by increased write workload and storage that’s bottlenecked on write IOPS.

And finally, remember: if your database is experiencing a lagging or stalled checkpointer, don’t restart it!

Edit June 24: I figured out why checkpoint time goes to 5 minutes when there’s low activity: the answer is that checkpoint timings absolutely do include idle/sleep time. An email from Andres Freund back in 2019 explicitly described this behavior. Timed checkpoints are always going to have a duration around checkpoint_completion_target and the reason I saw shorter durations is simply because those were requested checkpoints rather than timed checkpoints. The following query makes it pretty clear by adding up the total checkpoint time in every 5 minute window:

sum_over_time({cluster_name="$cluster", namespace="$namespace"} 
  |~ `checkpoint complete` 
  | regexp `write=([0-9]+\.[0-9]+) s, sync=([0-9]+\.[0-9]+) s, total=(?P<total_duration>[0-9]+\.[0-9]+) s` 
  | unwrap total_duration [5m])

Updated Happiness Hints

First and foremost: after many years, the Happiness Hints have received a major update! Before the conference, I updated the hints based on all the feedback I’ve collected over the past few years. Then the hints were updated into a poster format and we printed it as part of the pgconf.dev poster session. Throughout the week, I continued collecting more feedback. I used a sharpie during the conference and marked up the poster with ideas. Special thanks to Laurenz Albe, David Rader, Sami Imseih, Ryan Booz and Nik Samokhvalov (Nik you weren’t at the conference but a happiness hint resulted from other discussions we’ve had). Of course I’m forgetting more people who gave feedback making the happiness hints better. After coming home from the conference, I incorporated all the notes I had – and the version that’s now published here at ardentperf.com is the latest & best version I’ve assembled so far.

Physical Replication and Postgres High Availability

An extraordinary number of postgres users rely on physical replication for high availability. It’s been around for a long time and it works well. Nonetheless, there are a few rough edges and over the years there have been various mailing list threads that haven’t fully been resolved.

I proposed a Friday unconference session on this topic, and the topic received enough votes to be selected. Notes from the unconference are available on the Postgres wiki. But the discussion extended far beyond the unconference; there were also hallway discussions over coffee (thanks Thomas Munro) and then continuing discussions over dinner at Joey Burrard and beers at Steamworks (thanks Ants Aasma).

The first question that everybody asks is “should postgres have more HA capabilities in core”? And a discussion starting along these lines consumed the first half of the unconference.

But I thought the most interesting train of thought was something more incremental – an idea that Postgres is missing a fundamental/overarching concept or first principle which could make a lot of problems easier to solve – a concept around cluster topology. There are a few ways this could look. A function or a view like pg_nodes or something? The ability on a hot standby to query for all of the replicas in the topology? How about a function that could be called on a hot standby when the primary is unreachable and return a list of potential candidates for promotion to be a new primary?

My own idea is to consider the set of nodes in synchronous_standby_names as the “cluster” or “herd” of instances. (Jeff didn’t like the name “herd” but the word “cluster” already means something else in postgres…) Maybe we can let people set the number to “0” if they want a cluster with async replication. Which brings us to another challenge – managing changes to this parameter. First, how do we know the exact moment when every single connection and session is aware of a new set of cluster members? Remember that individual connections are responsible to ensure transactions are replicated before acknowledging commits to clients. Second, how could we ensure that all of the replicas know about changes when adding or removing cluster members?

There are also challenges around logical replication slots (like losing them after two failovers in a row, or the inability to replicate them at all if decoding from standbys) – could a new cluster concept help? A new cluster concept also might help around managing backups of WAL across a cluster. Lots of interesting ideas!

Wait Events and Physical Reads and pg_stat_statements

A handful of short discussions with Sami Imseih and Lukas Fittl. First off, Sami has some patches for pg_stat_statements that I’m pretty excited about. Improving concurrency around the LWLock and looking for ways to optimize the situation with the query text file.

Second, I had a few chats around physical reads. Right now I’m using the pg_stat_kcache extension to get data on physical reads. Postgres itself only tells reads that happen from the OS page cache. There’s ongoing work around direct IO, and also Postgres 18 will get a new AIO feature… and I’m curious if pg_stat_kcache will be able to get data about the background IO workers in pg18. There was some concern around the overhead of calling getrusage() too frequently; some benchmarking would be good, to determine if the overhead is too high to get per-query physical reads from AIO workers. (I’m expecting io_uring to be unavailable in many containerized environments; I think that GKE servers and also Docker’s default seccomp profile disable it.) I wonder if some users will want to disable the IO workers purely so they can continue getting physical read stats.

Third were a few small side conversations on better observability around Wait Events and Locks. For wait events, I think that we should be able to add counters to keep track of the number of times every wait event is called and the total duration for each wait event. But what about LWLock Wait Events? Too much overhead? It turns out that LWLocks don’t register wait events if they can quickly acquire a lock. They only register a wait if they actually relinquish the CPU to wait on a semaphor – so I think the overhead of maintaining counters on waits might be acceptable (and essential for debugging). Separately from this, I think that we also might be able to find a way to count the total number of times each LWLock is acquired – but it would need to be very efficient to be enabled all the time. (Postgres has LWLOCK_STATS already as a build flag but it’s not typically enabled.) I suspect we might want counters that are local to each process, and only aggregate them to central stats at some conservative interval.

Collation

It would hardly be a postgres conference if Jeff Davis and I didn’t have at least one conversation about Collation where we both insist that we’re now retired from collation work, then spend an hour debating how to best move Postgres forward.

Amazing work was done. But also, there is still more work to do.

The big problem nobody’s talking about is that language changes. ICU needs to be upgraded. Linguistic sort order is like time zones. It’s rare, but it changes – and when the sort order changes, all your indexes become invalid. Postgres does not have any good story yet for ICU upgrades.

Postgres now has a builtin stable code-point-order collation (pg_c_utf8). It’s possible to set this as the database default and do your linguistic sorting at the expression or column level. (Which you should! And it’s a happiness hint!) But lets be real: users in non-english languages don’t want to go through their entire schema or application adding COLLATE "fr_FR.utf8" everywhere.

The million-dollar question is “what do users really want?”

Can we come up with some limited “client locale” concept that gives users default behavior according to their client locale, while the database itself (and all indexes) operate with pg_c_utf8 collation? Maybe users only really care about ordering of results? The ORDER BY matters to them, but they actually might not really expect or care about the less-than operator? I think the ideal behavior is somehow that indexes are always created with pg_c_utf8 collation, while users can have a good experience that doesn’t require adding COLLATE clauses everywhere. The challenge is how to figure a way that pg_c_utf8 indexes can be used most of the time.

FWIW, Oracle takes a very interesting (if pragmatic) approach here – they just list all the operators, and some default to binary/codepoint collation while others default to the client locale. (Of course collation can always be explicitly specificed; this is just for defaults.) Indexes are always created binary/codepoint and indexes are generally used by queries. In Postgres, could the bttextcmp() function in postgres be tweaked somehow so that it can use pg_c_utf8 indexes by default even when the user requests linguistic collation? Or could we look at query execution plans and only apply linguistic collation to top-level nodes somehow? Crazy ideas, not sure any of it works, we’re still brainstorming.

Lightning Talks and Dinner Groups

Two final brief mentions. This year, Masahiko Sawada and myself organized the Lightning Talks. First time I’ve done it. We mostly just followed the same process which had been used last year – there was a very helpful google doc which we followed (and updated). From 29 total submissions, we randomly chose 12. Four people used green cards to indicate new/inexperienced speaker and we made sure that 2 of those were included. Every speaker gets 5 minutes max!

I learned that originally, Lightning Talks at pgcon were first-come-first-serve. As the conference grew, the Lightning Talks switched to random selection. Submissions were done at the conference by putting a note card into a box with your name and topic. I overheard a little discussion on Friday around whether lightning talks should move to a model of online submissions in the future, maybe ahead of time, more like a real CFP with a selection process instead of purely random selection.

I’m new here, but I do think there’s something that feels a little more authentic when it’s a physical submission at the conference and a random selection. Fits with the theme of this conference – ample time for hallway discussions and impromptu topics. Online submission feels a bit different; there are pros and cons both ways.

One final thing this year was that Paul Ramsey organized dinner groups on Tuesday and Thursday. What a fantastic idea! I was part of dinner groups on both days and really enjoyed meeting new people and having some great conversations. I forget to get a picture on Thursday, but here’s the group from Tuesday.

I wasn’t originally planning to attend this conference – and I’m very glad that I decided to go. I hope I’m able to attend another pgconf.dev in the future!

I don’t have any reproducible real test results today. But I have a good story and a little real data.

Vacuum tuning in Postgres is considered by some to be a dark art. Few confidently say: “Yes I know the right value for autovacuum_vacuum_cost_delay.” The documentation gives guidance, blog posts give opinions. Eventually I thought, “Ok what’s the worst that could happen if this one was set to zero?”

The story starts with some unexplained, intermittent application performance problems. We were doing some internal benchmarking to see just how far we could push a particular stack and see how much throughput a specific application could get. Everything hums along fine until suddenly – latency would spike across the board and the application would choke, causing backlogs and work queues to blow up throughout the system.

Where do you start when you have application performance problems? Wait Events and Top SQL – always! I’m far from the first person to evangelize this idea; I’ve said many times that wait events and top SQL are almost always the fastest way to discover where the bottlenecks are when you see unexpected performance problems. My [2024 SCaLE talk about wait events] gets into this.

So naturally I dug into the wait events and top SQL – and I noticed these slowdowns lined up perfectly with spikes in COMMIT statements on IPC:SyncRep waits. This wait event is not well understood. Last October I published an article [Explaining IPC:SyncRep – Postgres Sync Replication is Not Actually Sync Replication] with more explanation – but essentially it means the replicas were lagging behind and the primary was blocking on commit acknowledgments.

Notice how there are periodic spikes of hundreds of connections waiting on IPC:SyncRep for this system during the test runs: (nb. the plain colon represents CPU time)

That led me to check network traffic, which showed corresponding bursts of traffic between the primary and replicas. Something was periodically creating giant spikes of WAL.

So, I went hunting in the WAL itself. Using pg_walinspect on Postgres 16, I broke down records by resource manager and found massive surges from XLOG; specifically from full-page image (FPI) writes. These weren’t steady; they came in waves and caused serious commit latency waiting for downstream replication.

Here’s a graph of the record_size and fpi_size bytes per resource type during two benchmark runs:

I dumped the WAL and in the first sample I see it’s dominated by FPI_FOR_HINT blocks in sequential order from a specific 40GB toast table. I only see INSERT in pg_stat_statements for this table.

This confused me. Looking through Postgres source code, two possible sources I saw were log_newpage*() and MarkBufferDirtyHint() and I thought: where are these hint updates coming from? A Postgres SELECT can dirty pages by setting tuple hint bits when it reads rows whose inserting or deleting transaction has committed but whose visibility status has not yet been cached in the tuple header, which commonly happens after recent inserts, updates, deletes, or other write activity. Some napkin math suggested that 20,000 tuple ins/upd/del per second can dirty 10GB in one minute with hints. (We were close to 20k tuples/s in the run on the left side; second workload is over 30k/s.) Maybe spikes in dirty buffers were triggering forced checkpoints?

But after taking a look, the problem here wasn’t checkpoints themselves. A lot more checkpoints were happening than WAL spikes and there was no correlation between the timings. (You should still read Shaun’s blog though!)

But this is where the trail leads me to start thinking about autovacuum. From autovacuum logs (always enable these) I could see that the timing of autovacuum runs aligned perfectly with each WAL storm.

And then I had another realization: autovacuum_vacuum_cost_delay was set to 0 on this system. Crazy theory number two: vacuum is setting hint bits on an append only table very fast, and maybe the longer the gap between checkpoint and vacuum, the worse the damage? Remember that the first time a page is modified after checkpoint, the full page is written into the WAL log to protect from torn writes during system failures (because the OS block size usually doesn’t align with the database block size). Even if the update is just setting a hint bit – the WAL record can include the full 8k database block.

Without any cost throttling, autovacuum was racing through large tables at full speed, dirtying pages with hint bit updates – writing the full pages to the WAL log faster than the system could replicate them. That triggered bursts of WAL traffic, replication lag, and the intermittent major performance hiccups that had started this whole chase.

We reverted autovacuum_vacuum_cost_delay to its default 2ms, reran the workload, and everything smoothed out beautifully. You can still see the XLOG records generated by autovacuum, but they were more spread out. The WAL volume didn’t swing as wildly, replication didn’t crash as dramatically, and application latency spikes no longer overwhelmed backlogs and work queues. There was still variance in the performance – but we could tune the application to handle it, and we got much higher overall throughput without tipping everything over.

In hindsight, I remember seeing that setting early on and thinking,

“it’s a big server & workload, cost delay 0 won’t do anything that bad, won’t completely burn down the server, so I can probably let that one stay where it is”

I was completely wrong.

Moral of the story:
Vacuum tuning may feel like a dark art, but the defaults exist for good reason. Even one millisecond of cost delay keeps autovacuum from overwhelming the system and flooding WAL. Checkpoints and pg_repack and materialized view refreshes aren’t the only things that cause write storms; autovacuum can cause them too.

In other words: resist the temptation to go full throttle – your replicas and your applications and your future-self will thank you.

Since this sort of thing is getting easier with AI tooling, I spent some time doing a survey across a bunch of recognizable multi-contributor open source projects to see how they do database schema change management.

Biggest takeaway: the framework provided by your programming language is the most common pattern. After that seems to be custom project-specific code. Even while Pramod Sadalage and Martin Fowler’s twenty-year-old general evolutionary pattern is followed, I was surprised to see very few occurrences of the specific tools they listed in their 2016 article about Evolutionary Database Design. Those tools might be used behind some corporate firewalls, but they aren’t showing up in collaborative open source projects.

Second takeaway: it should be obvious that we still have schema migrations with document databases and distributed NoSQL databases; but lots of interesting illustrations here of what it looks like in practice to deal with document models and NoSQL schemas as they change over time. My recent comment on an Adam Jacob LinkedIn post:“life is great as long as changing your schema can remain avoidable (ie. requiring some kind of migration).”

What about the method of triggering the schema migrations? The most common pattern is that the application process itself triggers schema migration. After that we have kubernetes jobs.

The rest of this blog post is the supporting data I generated with some AI tooling. I made sure to include links to source code, for verifying accuracy. I spot checked a few and they were all accurate – but I didn’t go through every single project.

If you spot errors, please let me know!! I’ll update the blog.

Update Mar 26: On LinkedIn, Elizabeth Christensen mentioned last year’s virtual meetup about this topic [recording available on YouTube]. And I hadn’t originally mentioned it in this post, but probably worth pointing out that I think the three broad categories of schema change management tools are: (1) app frameworks [which this blog is focused on], (2) DB-agnostic [liquibase, flyway, sqitch, atlasgo, etc] and (3) DB-specific [pgroll, oracle edition based redefinition, etc] – it’s a very interesting landscape!

A survey of how major open-source projects handle database schema migrations. Each project includes a real code example and how migrations are triggered during upgrades.

Kubernetes Migration Trigger Methods

Projects with no official Helm chart or k8s support (Mastodon, Discourse, Sentry, Zulip, NetBox, Metabase, Lemmy, MediaWiki, Matrix Synapse†, CHT Core, Signal Server, Firefox, Chromium, Signal Desktop, FDB Record Layer, RxDB) are omitted.

† Matrix Synapse has no official Helm chart from Element; the widely-used community chart (ananace/matrix-synapse) relies on in-process startup migration.

Part 1: Relational

1A. External Migration Frameworks

1B. Custom Migration Systems

Part 2: Non-Relational Only

2A. External Migration Frameworks

2B. Custom Migration Systems

1A. Relational Database Support: Using External Migration Frameworks

Rails ActiveRecord Migrations

GitLab

Background migrations, batched migrations, migration helpers, extensive written policies for safe migrations at scale. Uses a custom Gitlab::Database::Migration base class rather than stock ActiveRecord.

Trigger: Dedicated Kubernetes Job in the official Helm chart (charts/gitlab/charts/migrations/). The job runs /scripts/db-migrate and must complete before web/Sidekiq pods roll out. Migrations never run automatically on app startup. (Helm chart)

Example — creating a partitioned table with sparse indexes (source):

class CreateWorkItemTransitions < Gitlab::Database::Migration[2.3]
  milestone '18.3'

  def up
    create_table :work_item_transitions, id: false do |t|
      t.bigint :work_item_id, primary_key: true, default: nil
      t.bigint :namespace_id, null: false
      t.bigint :moved_to_id, null: true
      t.index :moved_to_id, where: 'moved_to_id IS NOT NULL',
        name: 'index_work_item_transitions_on_moved_to_id'
    end
  end
end

Mastodon

Federated: thousands of independently-upgraded instances, so migrations must be safe across version skew.

Trigger: NOT automatic. Admins run migrations manually in two phases — SKIP_POST_DEPLOYMENT_MIGRATIONS=true rails db:migrate (before restart) then rails db:migrate (after). No official Helm chart.

Example — data migration translating theme settings to new key/value pairs (source):

class MigrateUserTheme < ActiveRecord::Migration[8.0]
  disable_ddl_transaction!
  class User < ApplicationRecord; end

  def up
    User.where.not(settings: nil).find_each do |user|
      settings = JSON.parse(user.attributes_before_type_cast['settings'])
      case settings['theme']
      when 'default'
        settings['web.color_scheme'] = 'dark'
      when 'mastodon-light'
        settings['web.color_scheme'] = 'light'
      end
      user.update_column('settings', JSON.generate(settings))
    end
  end
end

Discourse

Mature self-hosted forum with a disciplined migration history over 10+ years.

Trigger: discourse_docker launcher runs bundle exec rake db:migrate during ./launcher rebuild app. Auto-migration on every boot is off by default, controlled by MIGRATE_ON_BOOT env var.

Example — DDL + data backfill in one migration (source):

class CreateCategoryApprovalGroups < ActiveRecord::Migration[8.0]
  def up
    create_table :category_posting_review_groups do |t|
      t.integer :post_type, null: false
      t.integer :category_id, null: false
      t.integer :group_id, null: false
      t.timestamps null: false
    end
    # Backfill from existing category_settings
    execute(<<~SQL)
      INSERT INTO category_posting_review_groups (post_type, permission, category_id, group_id, created_at, updated_at)
      SELECT 0, 1, cs.category_id, 0, NOW(), NOW()
      FROM category_settings cs WHERE cs.require_topic_approval = true
    SQL
  end
end

Django Migrations

Sentry

Operates at massive scale; has written extensively about the pain of running Django migrations on huge tables.

Trigger: sentry upgrade CLI (called via docker compose run --rm web upgrade in self-hosted). Acquires a distributed lock. Migrations marked is_post_deployment = True are skipped and must be run manually in a separate step.

Example — post-deployment data backfill with Redis progress checkpointing (source):

class Migration(CheckedMigration):
    is_post_deployment = True  # won't auto-run during deploy

    operations = [
        migrations.RunPython(
            backfill_group_open_periods,
            migrations.RunPython.noop,
            hints={"tables": ["sentry_groupopenperiod"]},
        ),
    ]

Zulip

Well-engineered chat server, known for thoughtful code quality and contributor documentation.

Trigger: scripts/upgrade-zulip → upgrade-zulip-stage-3 stops the server, runs manage.py migrate --noinput, then restarts. Not automatic on startup.

Example — data-repair migration using raw SQL lateral join across JSONB audit log (source):

class Migration(migrations.Migration):
    atomic = False  # outside a transaction for large repair work

    operations = [
        migrations.RunPython(
            recreate_missing_realmemoji,
            elidable=True,
        ),
    ]

NetBox

Network infrastructure management tool widely used by network teams. Large plugin ecosystem extends the schema.

Trigger: Auto on container startup. netbox-docker entrypoint checks manage.py migrate --check and runs manage.py migrate --no-input if unapplied migrations exist.

Example — AddField + RunPython backfill + RemoveField in one migration (source):

operations = [
    migrations.AddField(
        model_name='vminterface', name='primary_mac_address',
        field=models.OneToOneField(null=True, on_delete=SET_NULL, to='dcim.macaddress'),
    ),
    migrations.RunPython(code=populate_mac_addresses, reverse_code=migrations.RunPython.noop),
    migrations.RemoveField(model_name='vminterface', name='mac_address'),
]

Alembic (SQLAlchemy)

Airflow

Apache project, widely deployed in very different operator environments.

Trigger: Dedicated Kubernetes Job (migrate-database-job.yaml) as a Helm post-install/post-upgrade hook running airflow db migrate. All other pods have a wait-for-airflow-migrations init container that blocks until the job completes. (Helm chart)

Example (source):

revision = "53ff648b8a26"
down_revision = "a5a3e5eb9b8d"

def upgrade():
    op.create_table(
        "revoked_token",
        sa.Column("jti", sa.String(32), primary_key=True, nullable=False),
        sa.Column("exp", UtcDateTime, nullable=False, index=True),
    )

def downgrade():
    op.drop_table("revoked_token")

Apache Superset

Apache data visualization platform, large contributor base.

Trigger: Dedicated Kubernetes Job (init-job.yaml) as a Helm post-install/post-upgrade hook running superset db upgrade. (Helm chart)

Example (source):

def upgrade():
    op.add_column("dbs", sa.Column("password", sa.LargeBinary(), nullable=True))

def downgrade():
    op.drop_column("dbs", "password")

Knex.js Migrations

Ghost

Popular blogging platform. Uses knex-migrator (a wrapper around Knex).

Trigger: Auto on every startup. boot.js → DatabaseStateManager checks knexMigrator.isDatabaseOK() and calls knexMigrator.migrate() if needed. (source)

Example (source):

const {addTable} = require('../../utils');

module.exports = addTable('members_created_events', {
    id:               {type: 'string', maxlength: 24, nullable: false, primary: true},
    created_at:       {type: 'dateTime', nullable: false},
    member_id:        {type: 'string', maxlength: 24, nullable: false,
                       references: 'members.id', cascadeDelete: true},
    attribution_id:   {type: 'string', maxlength: 24, nullable: true},
    source:           {type: 'string', maxlength: 50, nullable: false}
});

Backstage

Spotify-created developer portal. Plugin architecture means migrations come from many independent teams.

Trigger: Auto on startup. Each plugin’s CatalogBuilder.build() calls applyDatabaseMigrations(dbClient) → knex.migrate.latest() before any database objects are constructed. (source)

Example (source):

exports.up = async function up(knex) {
  await knex.schema.createTable('entities_relations', table => {
    table.comment('All relations between entities in the catalog');
    table.uuid('originating_entity_id')
      .references('id').inTable('entities').onDelete('CASCADE').notNullable();
    table.string('type').notNullable();
    table.string('target_full_name').notNullable();
    table.primary(['source_full_name', 'type', 'target_full_name']);
  });
};

Liquibase (XML/YAML Changelogs)

Keycloak

Red Hat-backed identity server. Liquibase changelogs declared in XML.

Trigger: Auto on startup. DefaultJpaConnectionProviderFactory calls LiquibaseJpaUpdaterProvider.update() → liquibase.update(). No special Helm/Operator handling — pods auto-migrate. (source)

Example (source):

<changeSet author="keycloak" id="25.0.0-28265-tables">
    <addColumn tableName="OFFLINE_USER_SESSION">
        <column name="BROKER_SESSION_ID" type="VARCHAR(1024)" />
        <column name="VERSION" type="INT" defaultValueNumeric="0" />
    </addColumn>
    <addColumn tableName="OFFLINE_CLIENT_SESSION">
        <column name="VERSION" type="INT" defaultValueNumeric="0" />
    </addColumn>
</changeSet>

Metabase

BI tool written in Clojure. Uses Liquibase under the hood with custom Clojure macros (define-migration, define-reversible-migration) layered on top.

Trigger: Auto on startup. setup-db! → run-schema-migrations! → Liquibase’s migrate-up-if-needed!. (source)

Example — custom Clojure migration macro wrapping Liquibase (source):

(define-migration DeleteAbandonmentEmailTask
  (custom-migrations.util/with-temp-schedule! [scheduler]
    (qs/delete-trigger scheduler
      (triggers/key "metabase.task.abandonment-emails.trigger"))
    (qs/delete-job scheduler
      (jobs/key "metabase.task.abandonment-emails.job"))))

Diesel ORM Migrations (Rust)

Lemmy

Federated Reddit alternative. Diesel generates migration SQL files.

Trigger: Auto on every startup. build_db_pool() calls run_pending_migrations() synchronously before the pool is returned. A standalone lemmy_diesel_utils binary also allows running migrations offline. (source)

Example (source):

CREATE TABLE private_message (
    id serial PRIMARY KEY,
    creator_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    recipient_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
    content text NOT NULL,
    deleted boolean DEFAULT FALSE NOT NULL,
    read boolean DEFAULT FALSE NOT NULL,
    published timestamp NOT NULL DEFAULT now(),
    updated timestamp
);

XORM-Based Migrations (Go)

Gitea

Git hosting platform. Migrations are Go functions using XORM for cross-database compatibility.

Trigger: Auto on startup (unless AUTO_MIGRATION=false in app.ini). InitDBEngine() → Migrate() compares the Version table against ExpectedDBVersion(). (source)

Example — typical pattern: define minimal struct, call SyncWithOptions (source):

func AddExclusiveOrderColumnToLabelTable(x *xorm.Engine) error {
    type Label struct {
        ExclusiveOrder int `xorm:"DEFAULT 0"`
    }
    _, err := x.SyncWithOptions(xorm.SyncOptions{
        IgnoreConstrains: true,
        IgnoreIndices:    true,
    }, new(Label))
    return err
}

Doctrine DBAL-Based Migrations (PHP)

Nextcloud

Huge self-hosted user base. Plugin ecosystem means third-party apps also run their own migrations.

Trigger: occ upgrade CLI command or web-based updater. Updater::doUpgrade() instantiates MigrationService and calls migrate(). Not automatic on every page load. (source)

Example — using Doctrine’s schema abstraction to change a column type (source):

class Version34000Date20260318095645 extends SimpleMigrationStep {
    public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
        $schema = $schemaClosure();
        if ($schema->hasTable('jobs')) {
            $table = $schema->getTable('jobs');
            $argumentColumn = $table->getColumn('argument');
            if ($argumentColumn->getType() !== Type::getType(Types::TEXT)) {
                $argumentColumn->setType(Type::getType(Types::TEXT));
                return $schema;
            }
        }
        return null; // idempotency guard — no change needed
    }
}

1B. Relational Database Support: Custom / In-House Migration Systems

Custom Go Migration Systems

Grafana

Must keep migrations working across 3 DB backends (SQLite, PostgreSQL, MySQL). Uses a Go DSL with per-dialect SQL dispatch.

Trigger: Auto on every startup. ProvideService() calls s.Migrate() synchronously — the server won’t start if migration fails. Official Helm chart has no migration job; relies entirely on auto-migration. (source)

Example — per-dialect SQL in one migration (source):

mg.AddMigration("Update uid column values in alert_notification", new(RawSQLMigration).
    SQLite("UPDATE alert_notification SET uid=printf('%09d',id) WHERE uid IS NULL;").
    Postgres("UPDATE alert_notification SET uid=lpad('' || id::text,9,'0') WHERE uid IS NULL;").
    Mysql("UPDATE alert_notification SET uid=lpad(id,9,'0') WHERE uid IS NULL;"))

Mattermost

Enterprise messaging. Switched from a custom Go DSL to morph (their own migration engine) with embedded .up.sql/.down.sql files.

Trigger: Auto on startup by default. sqlstore.New() calls store.migrate(). Also has an offline mattermost db migrate CLI with --dry-run and --save-plan flags for zero-downtime deploys. Helm chart has no migration job. (source)

Example (source):

UPDATE AccessControlPolicies AS p
SET Name = LEFT(p.Name, 128 - LENGTH(' (' || p.ID || ')')) || ' (' || p.ID || ')'
FROM (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY CreateAt ASC) AS rn
    FROM AccessControlPolicies WHERE Type = 'parent'
) AS dupes
WHERE p.ID = dupes.ID AND dupes.rn > 1;

CREATE UNIQUE INDEX IF NOT EXISTS idx_accesscontrolpolicies_name_type
    ON AccessControlPolicies (Name, Type) WHERE Type = 'parent';

Custom PHP Systems

WordPress

Famously has no migration framework. Uses dbDelta() for schema and version-numbered upgrade functions for data. 40%+ of the web runs on this.

Trigger: Auto on first admin page load after update. wp-admin/admin.php checks get_option('db_version') vs $wp_db_version; if they differ, redirects to upgrade.php which calls each version-specific upgrade_NNN() function.

Example — version-specific data migration (source):

function upgrade_700() {
    global $wp_current_db_version, $wpdb;
    if ( $wp_current_db_version < 61644 ) {
        $wpdb->update(
            $wpdb->usermeta,
            array( 'meta_value' => 'modern' ),
            array( 'meta_key' => 'admin_color', 'meta_value' => 'fresh' )
        );
    }
}

MediaWiki

Powers Wikipedia. Has its own maintenance script system with separate SQL files per database engine.

Trigger: php maintenance/update.php must be run manually after deploying new code. Wikimedia runs this as a k8s Job in their deployment pipeline via the scap tool. Never auto-runs on web requests.

Example — batch data migration merging a temp table into the main table (source):

protected function doDBUpdates() {
    $dbw = $this->getDB( DB_PRIMARY );
    if ( !$dbw->tableExists( 'revision_comment_temp', __METHOD__ ) ) {
        $this->output( "revision_comment_temp does not exist, nothing to do.\n" );
        return true;
    }
    // batch-copies revcomment_comment_id → rev_comment_id
    $dbw->newUpdateQueryBuilder()
        ->update( 'revision' )
        ->set( [ 'rev_comment_id' => $row->revcomment_comment_id ] )
        ->where( [ 'rev_id' => $row->rev_id ] )
        ->caller( __METHOD__ )->execute();
}

Declarative / ORM-Diffing + Explicit Migration Scripts

Odoo

ERP with 10,000+ modules. ORM handles additive changes declaratively; renames, transforms, and restructuring require explicit pre/post/end migration scripts. Major version upgrades use Odoo SA’s proprietary upgrade service or the community OpenUpgrade project (~120 scripts per major version).

Trigger: odoo -u <module> or -u all. MigrationManager in loading.py discovers migrations/<version>/pre-*.py and post-*.py files via glob and exec_module()s each script’s migrate(cr, version) function.

Example — pre-migrate script changing FK constraints (source):

def migrate(cr, version):
    cr.execute("""
        SELECT value::int FROM ir_config_parameter WHERE key = 'analytic.project_plan'
    """)
    [project_plan_id] = cr.fetchone()
    cr.execute("SELECT id FROM account_analytic_plan WHERE id != %s AND parent_id IS NULL",
               [project_plan_id])
    plan_ids = [r[0] for r in cr.fetchall()]
    for column in [f"x_plan{id_}_id" for id_ in plan_ids]:
        sql.drop_constraint(cr, 'account_analytic_line', f'account_analytic_line_{column}_fkey')
        sql.add_foreign_key(cr, 'account_analytic_line', column,
                            'account_analytic_account', 'id', 'restrict')

Parse Server

Backend-as-a-Service (originally Facebook, 21k stars). No numbered migration scripts — declarative schema reconciliation at startup.

Trigger: Auto on startup. ParseServer.start() adds new DefinedSchemas(schema, config).execute() to startupPromises. Server won’t accept traffic until reconciliation completes (or process.exit(1) in production on failure). (source)

Example — schema reconciliation engine (source):

async executeMigrations() {
  await this.createDeleteSession();
  const schemaController = await this.config.database.loadSchema();
  this.allCloudSchemas = await schemaController.getAllClasses();
  await Promise.all(
    this.localSchemas.map(async localSchema => this.saveOrUpdate(localSchema))
  );
  this.checkForMissingSchemas();
  await this.enforceCLPForNonProvidedClass();
}

Custom Python (Delta Scripts)

Matrix Synapse

Federated messaging server. Numbered SQL/Python delta scripts. Federation means different homeservers run different versions simultaneously.

Trigger: Auto on startup (main process only). prepare_database() reads schema_version and applies all pending delta scripts. Worker processes refuse to start if schema is unmigrated — only the main process is permitted to apply changes. (source)

Example (source):

CREATE TABLE sliding_sync_connection_lazy_members (
    connection_key BIGINT NOT NULL
        REFERENCES sliding_sync_connections(connection_key) ON DELETE CASCADE,
    room_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    last_seen_ts BIGINT NOT NULL
);

CREATE UNIQUE INDEX sliding_sync_connection_lazy_members_idx
    ON sliding_sync_connection_lazy_members (connection_key, room_id, user_id);

Custom Versioned Scripts (Multi-DB, including non-relational)

Temporal

Workflow orchestration engine. Versioned SQL scripts per database backend.

Trigger: temporal-sql-tool update-schema CLI. In k8s, runs as a dedicated Kubernetes Job in the official Helm chart (charts/temporal/templates/server-job.yaml). (Helm chart)

Example (source):

CREATE TABLE visibility_tasks(
  shard_id INTEGER NOT NULL,
  task_id BIGINT NOT NULL,
  data BYTEA NOT NULL,
  data_encoding VARCHAR(16) NOT NULL,
  PRIMARY KEY (shard_id, task_id)
);

Kong

Popular API gateway (43k stars). Custom Lua migration framework. Deprecated Cassandra in 2.7 and removed it in 3.4. Now PostgreSQL-only.

Trigger: kong migrations bootstrap (fresh install) / kong migrations up + kong migrations finish (upgrades). In k8s, runs as a dedicated Kubernetes Job in the official Helm chart. (Helm chart)

Example (source):

return {
  postgres = {
    up = [[
      DO $$
      BEGIN
        ALTER TABLE IF EXISTS ONLY "plugins" ADD "protocols" TEXT[];
      EXCEPTION WHEN DUPLICATE_COLUMN THEN
        -- Do nothing, accept existing state
      END;
      $$;

      CREATE TABLE IF NOT EXISTS "tags" (
        entity_id    UUID    PRIMARY KEY,
        entity_name  TEXT,
        tags         TEXT[]
      );
    ]],
  },
}

Zipkin

The original distributed tracing system (17k stars, since 2012). Bundles versioned CQL and SQL schema files.

Trigger: Schema must be applied manually before running Zipkin — mysql < mysql.sql. Zipkin does not auto-apply schema on startup; it introspects existing tables but does not create or alter them. (docs)

Example (source):

CREATE TABLE IF NOT EXISTS zipkin_spans (
  `trace_id_high` BIGINT NOT NULL DEFAULT 0,
  `trace_id`      BIGINT NOT NULL,
  `id`            BIGINT NOT NULL,
  `name`          VARCHAR(255) NOT NULL,
  `start_ts`      BIGINT,
  `duration`      BIGINT,
  PRIMARY KEY (`trace_id_high`, `trace_id`, `id`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED CHARACTER SET=utf8;

ThingsBoard

IoT platform (21k stars). Uses Cassandra for time-series telemetry, PostgreSQL for relational data.

Trigger: upgrade.sh script invokes ThingsboardInstallApplication (a separate Spring Boot entry point, not the normal server) with --fromVersion flag. Docker: docker compose run --rm -e UPGRADE_TB=true. (source)

Example (source):

ALTER TABLE calculated_field
  ADD COLUMN IF NOT EXISTS additional_info varchar;

Desktop SQLite Migrations

These projects run migrations on end-user machines — across hundreds of millions of installations, with no DBA watching, no rollback capability, and users who may skip many versions between upgrades.

Firefox

Migrates bookmarks, history, cookies, permissions databases in C++/Rust.

Trigger: Auto on startup. InitSchema() reads GetSchemaVersion() and runs sequential MigrateVNUp() functions inside a transaction. Failure prevents Places from loading.

Example — adding a column and backfilling it (source):

nsresult Database::MigrateV54Up() {
  nsCOMPtr<mozIStorageStatement> stmt;
  nsresult rv = mMainConn->CreateStatement(
      "SELECT expire_ms FROM moz_icons_to_pages"_ns, getter_AddRefs(stmt));
  if (NS_FAILED(rv)) {
    rv = mMainConn->ExecuteSimpleSQL(
        "ALTER TABLE moz_icons_to_pages "
        "ADD COLUMN expire_ms INTEGER NOT NULL DEFAULT 0 "_ns);
    NS_ENSURE_SUCCESS(rv, rv);
  }
  rv = mMainConn->ExecuteSimpleSQL(
      "UPDATE moz_icons_to_pages SET expire_ms = "
      "strftime('%s','now','localtime','start of day','utc') * 1000 "
      "WHERE expire_ms = 0 "_ns);
  return NS_OK;
}

Chromium

Same problem as Firefox, different implementation. Sequential if (cur_version == N) blocks.

Trigger: Auto on startup. HistoryDatabase::Init() → EnsureCurrentVersion() runs each version block up to the current version (70+). Version too new → INIT_TOO_NEW; migration failure → INIT_FAILURE.

Example (source):

if (cur_version == 15) {
  if (!db_.Execute("DROP TABLE starred") || !DropStarredIDFromURLs())
    return LogMigrationFailure(15);
  ++cur_version;
  std::ignore = meta_table_.SetVersionNumber(cur_version);
  std::ignore = meta_table_.SetCompatibleVersionNumber(
      std::min(cur_version, kCompatibleVersionNumber));
}

Signal Desktop

Encrypted SQLite database (SQLCipher). Migrations in TypeScript.

Trigger: Auto on startup. ts/sql/Server.node.ts opens the encrypted DB, then calls updateSchema(db, logger) which iterates SCHEMA_VERSIONS and applies each pending migration in a transaction. Only the primary worker runs migrations. (source)

Example (source):

import type { Database } from '@signalapp/sqlcipher';

export default function updateToSchemaVersion1090(db: Database): void {
  db.exec(`
    CREATE INDEX reactions_messageId ON reactions (messageId);
    CREATE INDEX storyReads_storyId ON storyReads (storyId);
  `);
}

2A. No Relational Database Support: Using External Migration Frameworks

Very few non-relational projects use an external migration framework — the ecosystem of reusable tooling is much thinner than in the relational world.

Mongock (MongoDB Migration Framework for Java)

Appsmith (server-side)

Low-code platform. Server-side uses Mongock with @ChangeUnit annotations. Also has a separate client-side DSL migration system (see 2B).

Trigger: Auto on Spring Boot startup. Mongock runs as a MongockInitializingBeanRunner bean, scanning for @ChangeUnit classes and executing them in order. Helm chart has no init container for migrations — they run inside the main app container. (source)

Example — converting a policies array to a keyed policyMap across 22 collections (source):

@ChangeUnit(order = "059", id = "policy-set-to-policy-map")
public class Migration059PolicySetToPolicyMap {
    private final ReactiveMongoTemplate mongoTemplate;

    @Execution
    public void execute() {
        Mono.whenDelayError(CE_COLLECTION_NAMES.stream()
                .map(c -> executeForCollection(mongoTemplate, c))
                .toList())
            .block();
    }
    // Uses ArrayToObject aggregation to transform policies[] → policyMap{}
}

FoundationDB Record Layer (Protobuf Schema Evolution)

FoundationDB Record Layer

Apple’s Java library powering iCloud/CloudKit — billions of independent databases sharing thousands of schemas. SIGMOD 2019 paper.

Trigger: Programmatic. Library consumers call FDBRecordStore.Builder#open() or #checkVersion(). A UserVersionChecker callback compares the stored metadata version in the database header against the current code’s metadata version and decides how to proceed. (source)

Example — adding a field to a record type via MetaDataProtoEditor (source):

public static void addField(@Nonnull RecordMetaDataProto.MetaData.Builder metaDataBuilder,
                            @Nonnull String recordType,
                            @Nonnull DescriptorProtos.FieldDescriptorProto field) {
    DescriptorProtos.DescriptorProto.Builder messageType =
        findMessageTypeByName(metaDataBuilder.getRecordsBuilder(), recordType);
    if (messageType == null) {
        throw new MetaDataException("Record type " + recordType + " does not exist");
    }
    messageType.addField(field);
}

And the evolution validator (source):

public void validate(@Nonnull RecordMetaData oldMetaData, @Nonnull RecordMetaData newMetaData) {
    if (oldMetaData.getVersion() > newMetaData.getVersion()) {
        throw new MetaDataException("new meta-data does not have newer version");
    }
    validateUnion(oldMetaData.getUnionDescriptor(), newMetaData.getUnionDescriptor());
    validateRecordTypes(oldMetaData, newMetaData, getTypeRenames(...));
    validateCurrentAndFormerIndexes(oldMetaData, newMetaData, typeRenames);
}

Neo4j-Migrations (Flyway-inspired for Graph DBs)

Neo4j-Migrations

Canonical migration tool for the Neo4j ecosystem. Migrations are Cypher scripts or Java classes.

Trigger: Two paths: (1) neo4j-migrations migrate CLI, (2) Spring Boot auto-configuration — MigrationsInitializer implements InitializingBean and calls migrations.apply(true) in afterPropertiesSet(). (source)

Example — Cypher migration file, Flyway naming convention (source):

MATCH (n:BrokenData) DETACH DELETE n;

The migration runner (source):

private void apply0(List<Migration> migrations) {
    MigrationChain chain = this.chainBuilder.buildChain(this.context, migrations);
    for (Migration migration : IterableMigrations.of(this.config, migrations, optionalStop)) {
        migration.apply(this.context);
        recordApplication(chain.getUsername(), previousVersion, migration, executionTime);
    }
}

2B. No Relational Database Support: Custom / In-House Migration Systems

Almost every non-relational project has built its own migration infrastructure.

KV Store / Protobuf Schema Evolution (etcd)

Kubernetes

Objects stored as protobufs in etcd. When the storage version for a resource type changes, existing objects need re-encoding.

Trigger: Create a StorageVersionMigration CRD. The kube-storage-version-migrator controller watches for these CRDs and does a paginated no-op PUT on every object, causing the API server to re-serialize in the new storage version. Deployed as a standalone in-cluster controller.

Example — API version conversion function for Deployments (source):

func Convert_v1_Deployment_To_apps_Deployment(in *appsv1.Deployment, out *apps.Deployment, s conversion.Scope) error {
    if err := autoConvert_v1_Deployment_To_apps_Deployment(in, out, s); err != nil {
        return err
    }
    // Deprecated rollbackTo field → annotation for roundtrip
    if revision := in.Annotations[appsv1.DeprecatedRollbackTo]; revision != "" {
        revision64, _ := strconv.ParseInt(revision, 10, 64)
        out.Spec.RollbackTo = &apps.RollbackConfig{Revision: revision64}
        delete(out.Annotations, appsv1.DeprecatedRollbackTo)
    }
    return nil
}

Calico

Underwent a major data model overhaul from v2 to v3. Built a dedicated calico-upgrade migration tool.

Trigger: calico-upgrade start CLI. Operator-initiated one-time migration with four phases: dry-run, start (pauses networking, converts all v1 objects to v3), complete, abort. (source)

Example — policy name conversion for etcd storage (source):

func convertPolicyNameForStorage(name string) string {
    if strings.HasPrefix(name, "knp.") {
        return name // Kubernetes-native policies keep their prefix
    }
    return "default." + name // Calico policies stored under "default" tier
}

Vitess

CNCF Graduated MySQL clustering system (powers PlanetScale, Slack, GitHub). Stores topology metadata (keyspaces, shards, tablets, routing rules) as proto3 binary blobs in etcd. Schema evolution happens via standard protobuf rules — fields are only added, never removed or reordered — so stored objects remain readable across versions without any migration step. The topo2topo tool exists to copy topology between different backends (e.g., ZooKeeper → etcd) but this is a backend replacement, not a schema migration.

Trigger: No migration tooling needed. The protobuf encoding is forward- and backward-compatible by construction.

Example — protobuf-encoded topology object read from etcd (source):

func CopyKeyspaces(ctx context.Context, fromTS, toTS *topo.Server, parser *sqlparser.Parser) error {
    keyspaces, err := fromTS.GetKeyspaces(ctx)
    for _, keyspace := range keyspaces {
        ki, err := fromTS.GetKeyspace(ctx, keyspace)
        if err := toTS.CreateKeyspace(ctx, keyspace, ki.Keyspace); err != nil {
            if topo.IsErrType(err, topo.NodeExists) {
                log.Warn(fmt.Sprintf("keyspace %v already exists", keyspace))
            }
        }
    }
    return nil
}

Apache APISIX

Cloud-native API gateway. All dynamic runtime config (routes, upstreams, plugins, SSL certs) stored in etcd; static node config (listen ports, worker processes) remains in config.yaml on disk. The 2.x → 3.0 upgrade had incompatible etcd data structure changes with no automated migration.

Trigger: Entirely manual. etcdctl snapshot save, then either write custom scripts to transform JSON values in-place, or reconfigure from scratch via the 3.0 Admin API. No migration tooling provided. (docs)

Example — the breaking disable field relocation:

// 2.15.x — "disable" is top-level in each plugin
{ "plugins": { "limit-count": { "count": 2, "disable": true } } }

// 3.0.0 — "disable" must be nested under "_meta"
{ "plugins": { "limit-count": { "count": 2, "_meta": { "disable": true } } } }

Cassandra Schema Migrations (Cassandra-only projects)

Jaeger

CNCF Graduated distributed tracing. Versioned CQL templates parameterized by environment variables.

Trigger: create.sh shell script performs variable substitution and pipes CQL to cqlsh. In k8s, runs as a one-time Kubernetes Job using the jaegertracing/jaeger-cassandra-schema Docker image before deploying Jaeger. (k8s manifest)

Example (source):

CREATE TYPE IF NOT EXISTS ${keyspace}.keyvalue (
    key          text,
    value_type   text,
    value_string text,
    value_bool   boolean,
    value_long   bigint,
    value_double double,
    value_binary blob
);

CREATE TABLE IF NOT EXISTS ${keyspace}.traces (
    trace_id        blob,
    span_id         bigint,
    span_hash       bigint,
    operation_name  text,
    start_time      bigint,
    duration        bigint,
    PRIMARY KEY (trace_id, span_id, span_hash)
);

MongoDB Document Migrations

Rocket.Chat

Team chat platform (45k stars). 300+ migrations. Control document tracks version + lock state.

Trigger: Auto on every startup. xrun.ts calls performMigrationProcedure() → migrateDatabase('latest'). All versioned migration modules (v293–v335) are imported at startup. (source)

Example (source):

import { Settings } from '@rocket.chat/models';
import { addMigration } from '../../lib/migrations';

addMigration({
    version: 309,
    name: 'Remove unused UI_Click_Direct_Message setting',
    async up() {
        await Settings.removeById('UI_Click_Direct_Message');
    },
});

Appsmith (client-side DSL)

Per-document version stamps. 94 sequential migration functions for widget DSL. Runs in the browser, not the server.

Trigger: On every page load. extractCurrentDSL() calls migrateDSL(currentDSL), which runs every if (version === N) block from the stored version up through 94. The upgraded DSL is never written back — migrations re-execute on every load. (source)

Example — migrating legacy styling enums to CSS tokens (source):

enum ButtonBorderRadiusTypes { SHARP = "SHARP", ROUNDED = "ROUNDED", CIRCLE = "CIRCLE" }
const THEMING_BORDER_RADIUS = { none: "0px", rounded: "0.375rem", circle: "9999px" };

export const migrateStylingPropertiesForTheming = (currentDSL: DSLWidget) => {
  // walks every widget, rewrites legacy enum-style borderRadius / boxShadow
  // to CSS token strings used by the theming system
};

Graylog

Log management (since 2010). 91 timestamped Java migration classes. Leader-gated.

Trigger: Auto on startup via ServerBootstrap.runMigrations(). Only runs on the leader node (checked via configuration.isLeader()). Three phases: PREFLIGHT, STANDARD, and ENFORCED_ON_ALL_NODES. No separate k8s job. (source)

Example (source):

public class V20190705071400_AddEventIndexSetsMigration extends Migration {
    @Override
    public ZonedDateTime createdAt() {
        return ZonedDateTime.parse("2019-07-05T07:14:00Z");
    }

    @Override
    public void upgrade() {
        ensureEventsStreamAndIndexSet("Events",
            "Stores events created by event definitions.",
            elasticsearchConfiguration.getDefaultEventsIndexPrefix(),
            Stream.DEFAULT_EVENTS_STREAM_ID, "All events");
    }
}

CouchDB / Offline-First Migrations

RxDB

Reactive JavaScript database for client-side apps. Each collection carries a schema version with migrationStrategies functions.

Trigger: Auto when a collection is opened (if autoMigrate: true, the default). createRxCollection() detects a lower stored schema version and calls migratePromise(). Runs in the browser per-device; awaits leader election in multi-instance databases. (source)

Example — migration strategies and the core iteration loop (source):

// Defining strategies at collection creation
migrationStrategies: {
  1: function(oldDoc) {
    oldDoc.time = new Date(oldDoc.time).getTime(); // string → unix
    return oldDoc;
  },
  2: function(oldDoc) {
    if (oldDoc.time < 1486940585) return null; // deletes document
    return oldDoc;
  }
}

// Core iteration in migration-helpers.ts
let nextVersion = docSchemaVersion + 1;
while (nextVersion <= collection.schema.version) {
    currentPromise = currentPromise.then(docOrNull =>
        runStrategyIfNotNull(collection, nextVersion, docOrNull));
    nextVersion++;
}

CHT Core (Community Health Toolkit)

CouchDB-based offline-first health apps used by tens of thousands of health workers in dozens of countries.

Trigger: Two-track. App-level migrations (in api/src/migrations/) auto-run on API startup — the server is unavailable (502) until complete. Cluster-level migrations (3.x → 4.x) require manually running the couchdb-migration Docker tool before upgrading. (docs)

Example — removing a field from CouchDB documents via bulkDocs (source):

module.exports = {
  name: 'remove-enabled-from-translation-docs',
  created: new Date('2025-09-01'),
  run: async () => {
    const translationDocs = await translations.getTranslationDocs();
    translationDocs.forEach(doc => delete doc.enabled);
    await db.medic.bulkDocs(translationDocs);
  }
};

DynamoDB Schema Evolution

Cortex

CNCF Prometheus long-term storage. Time-partitioned schema versioning — you never migrate old data.

Trigger: No data migration. Append a new PeriodConfig block to the YAML config with a future from: date and new schema: version. At runtime, SchemaForTime(timestamp) selects the correct config for each chunk. Old and new schema tables coexist indefinitely. (original PR)

Example — the schema dispatch function (now maintained in Grafana Loki, same code) (source):

type PeriodConfig struct {
    From   DayTime `yaml:"from"`
    Schema string  `yaml:"schema"` // e.g. "v10", "v11"
}

func (cfg SchemaConfig) SchemaForTime(t model.Time) (PeriodConfig, error) {
    for i := range cfg.Configs {
        if t >= cfg.Configs[i].From.Time &&
            (i+1 == len(cfg.Configs) || t < cfg.Configs[i+1].From.Time) {
            return cfg.Configs[i], nil
        }
    }
    return PeriodConfig{}, fmt.Errorf("no schema config found for time %v", t)
}

Signal Server

Backend for Signal Private Messenger. Uses DynamoDB as primary store. Schema evolution is implicit — most data lives inside a JSON blob attribute.

Trigger: No schema migration. New fields are added to the Account POJO and written into the D (data) attribute on next update. A per-item V (version) attribute provides optimistic locking. Table/GSI changes are provisioned externally via infrastructure-as-code, not application code. (source)

Example — optimistic locking on DynamoDB writes (source):

static final String ATTR_VERSION = "V";

// Every update atomically increments version and checks the condition
updateExpressionBuilder.append(" ADD #version :version_increment");

return new UpdateAccountSpec(accountTableName,
    Map.of(KEY_ACCOUNT_UUID, AttributeValues.fromUUID(account.getUuid())),
    attrNames, attrValues,
    updateExpressionBuilder.toString(),
    "attribute_exists(#number) AND #version = :version");  // conditional write

Graph Database Schema Evolution

Dgraph

When deploying a new GraphQL schema, Dgraph updates the schema in memory immediately but does not alter existing data — index rebuilds run asynchronously in the background.

Trigger: POST /admin with an updateGQLSchema mutation, or dgraph live --schema. The change propagates to all cluster nodes via Raft. If a predicate’s tokenizer changed, a background goroutine iterates all existing postings in Badger and writes new index entries. (source)

Example — schema mutation with conditional async index rebuild (source):

rebuild := posting.IndexRebuild{
    Attr: su.Predicate, StartTs: startTs,
    OldSchema: &old, CurrentSchema: su,
}

// Write new schema to memory immediately (queries see it now)
schema.State().Set(su.Predicate, rebuild.GetQuerySchema())

if rebuild.NeedIndexRebuild() {
    go buildIndexes(su, rebuild, closer) // async background reindex
} else {
    updateSchema(su, rebuild.StartTs)    // write to Badger, done
}

Automated emails are spam. They always have been. Openclaw (and whatever new thing surfaces this summer) is no different.

Policies saying automated emails/messages are banned – including anything AI generated – are not only common-sense policies, they aren’t even a change from how we’ve always worked. This includes automated comments on github issues, automated PRs, automated patch submissions, and even any kind of automated review. Copilot automated reviews, snyk, etc – are ok if-and-only-if it’s configured by the owners of the repo/project. Common sense.

Enforcement of these policies – more than ever – depends on trust and relationships. I do think, for example, that non-native-english-speakers should be allowed to use AI to help them check their english. Used responsibly, AI tools can help a lot with language learning! Your grammar checker is probably based on some kind of LLM anyway. But I’m saying that a human always presses the “send” button on the message, and this human is responsible for the words they sent. If moderators suspect automated messages, every open source project should have a policy they can cite for blocking/banning the account.

Tomas Vondra’s article “the AI inversion” is the latest of many good and thought-provoking pieces I’ve read – it’s well worth the read – although he’s getting at deeper problems than what I’m writing about here – and he has very good reasons to have a much deeper level of concern for the impact of AI tooling on open source communities. These are interesting times and we don’t have all the answers yet.

A few more things I’ve recently read, which I think are good:

• CloudNativePG AI policy – https://github.com/cloudnative-pg/governance/blob/main/AI_POLICY.md
• Linux Foundation AI Policy – https://www.linuxfoundation.org/legal/generative-ai
• Bryan Cantrill, Oxide RFD – https://rfd.shared.oxide.computer/rfd/0576
• Russ Cox on GoLang and AI – https://groups.google.com/g/golang-dev/c/4Li4Ovd_ehE/m/8L9s_jq4BAAJ?pli=1
• Jordan Tigani about AI @ MotherDuck – [long painful URL for LinkedIn post]

.

I’ve also been writing bits and pieces of partial thoughts over the past week or two – my short blog post about the Scott Shambaugh situation (And thank you to Kim Bruning for the thoughtful email exchanges about this blog! Please continue to keep this old guy on his toes, reasoning through things, and challenging his thinking!)

There have been a bunch of LinkedIn messages too; capturing them here:

• Mischa van den Burg wrote a LinkedIn post about whether ChatGPT in interviews is a red flag
  • Brad Nicholson said “As someone that knows how to find that sort of info command line and has done so many, many times – I’d go to chat first, google second and the man pages last because the first two get me what I need faster than reading a man page.”
    
    .
  • Replying to Brad:
    
    “I do the same thing, but we also understand this is in descending order of hallucination likelihood
    
    one of my favorite ways to use agents is to write me a script that demonstrates a behavior they claim… by the time the script is working, the claim is often significantly revised – and at present i still usually have to prevent them from making the test script work by moving the goalposts”
    
    .
• Replying to Phil Eaton’s post about Russ Cox’s perspective on golang project approach (policy?) for AI:
  • Russ Cox’s message is here
  • i said “yes – the section here is a good excerpt” (referring to Phil’s excellent choice of what to screenshot)
    
    .
• Replying to Kelsey Hightower’s post “Generative AI is a slop generation machine by default. You have to put in a lot of work to get something of quality from it.”
  • It’s the same work I did before, just shifted left. I’m iterating on low-level detailed design spec and autogenerating code, rather than iterating on the code and trying to keep design docs in sync. I think of it as writing more of my code in detailed prose, flowcharts, sequence diagrams, and pseudocode – rather than writing it directly in the programming language and manually keeping the design docs in sync. But it’s the same work, minus time spent on syntax (which was never where the value was).
    
    .
  • Replying to Adam Jacob’s comment: I think it remains true that “you get out what you put in”
    
    .
• Replying to Jordan Tigani’s post about MotherDuck AI policy:
  • Tricky topic. I built a deeply detailed design for overhauling how auth works on a core platform…
    * 291 prompts across 15 sessions over 3 days, comprising ~1,226 lines of prompt text
    * final design document is 1,904 lines of markdown — a ratio of roughly 2 lines of human-written prompt for every 3 lines of design document output
    
    a review of the full transcript showed a number of interesting characteristics of my prompts, including:
    * Persistent effort to simplify the tool’s initial proposals
    * Directly contributing critical domain knowledge
    * Frequent insistence on precise terminology
    
    Overall I’m satisfied and I think it’s a good doc that would have taken me 10x longer otherwise (especially research portions) – but I acknowledge mixed feelings.
    
    one thing that’s clear: i obviously got the AI game backwards. i thought it was scored like golf, where a lower ratio of input-to-output is a better score
    
    .
• My own LinkedIn post: “We need to re-think OSS contribution attribution in light of AI. More than ever, it’s important for committers to give credit on where the ideas are coming from. A committer can copy/paste someone else’s ideas into their own prompts, and they need to give appropriate credit.”
  
  .
• Mentioning the Oxide RFD in my reply to Daniel Gustafsson:
  • my thought is around crediting someone who participates meaningfully in the discussion, even if they didn’t author the final patch. a wall of text email that nobody entirely reads is not a meaningful contribution – but there are lots of ways AI can be part of a well-written email. it’s hard to find the objective line though about what this means.
    
    email moderation is going to get harder. trust and relationships were always important, and now even more so. i think using AI for research or to assist with writing is a net positive – as long as the final written product is concise and well-communicated and understood by the author. AI is the tool, but it’s finally still a human relationship. oxide’s RFD is good https://rfd.shared.oxide.computer/rfd/0576 – responsibility, rigor and empathy remain fundamental. old-school email lists might have a small advantage here. and i hope we can stay open to new people who seem interested to join and contribute
    
    .
• Replying to Adam Jacob’s post: “If you’re thinking to yourself “this 10x increase in capability to create software doesn’t matter, because writing software was never the bottleneck”, you’re drawing the wrong conclusions from a true statement. … [skipping middle section, but go read the whole thing bc its good] … We will rebuild everything around this capability. Everything.”
  • what people miss: it doesn’t need to be 10x more code, it can be same code 10x faster (which often is very little code — but it would have taken much longer to get it right)
    
    but Adam why are you telling everyone? i’m having so much fun right now, and once everyone figures it out then we’ll be back to the usual drill…
    
    .
• I wrote a LinkedIn post about how I think moderation will get harder, then clarified a bit in reply to Andreas Scherbaum by pointing to Tomas Vondra’s blog because that’s much better than what I said.

.