Ardent Performance Computing

Latest Post

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: