Postgres Checkpoint Followup and Collation Visualization and Codex Luna

My previous article talked about the checkpoint happiness hint: You probably should not change the checkpoint_timeout setting from its default of 5 minutes.

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!!