If your COMMIT is slow, Laurenz Albe gives you a few places to start troubleshooting, including deferred constraints and sync replication.

Writing Postgres code is hard. Robert Haas’s post isn’t talking about writing queries, either – it’s about writing the database engine code itself. As somebody who maintained a relatively microscopic open source project, Robert’s observations ring true for me as well.

I haven’t seen a start-to-finish Postgres tuning guide yet, but EDB has updated their PostgreSQL on Linux tuning guide, and that’s as good a place to start as any.

The free online POSETTE conference is this week, and yes, there are Microsoft marketing sessions in there, but there are also a ton of good-looking best practices sessions. The 25-minute talks will be published on YouTube after the conference ends.

Technique of the Week:
Listening for Changes

Most applications have a couple of configuration tables holding things like application parameters, global values, and feature flags. This data doesn’t change often, but when it does change, we want it to take effect everywhere, quickly.

It sucks to repeatedly query the database when we know the data rarely changes. However, if we cache the data in the application layer, how do we know when it changes?

One solution is LISTEN/NOTIFY, as Brandur explains in delightful detail, including simple code examples for both queries and app code:

Brandur’s post explains the common gotchas of this technique, including the fact that you’ll need to use session pooling (not transaction pooling) in PgBouncer because notifications are sent to the original session that started listening for updates.