Debugging Postgres Autovacuum Problems: 13 Tips — Samay asks us to imagine seeing our database is larger than expected with high I/O load and sluggish performance. We spot a lot of bloat in our tables and run VACUUM. But then we might ask “why didn’t Postgres autovacuum clean up the bloat in the first place…?” — It’s a good question and this post covers a variety of stumbling blocks and things to do if your trusty vacuum isn’t cleaning things up as well as it used to.

Samay Sharma (Citus Data)

📊 The Full State of Postgres 2022 Results — They’ve been teasing us with bits and pieces in recent weeks, but now we can see the full results (or download a dump of the results to analyze yourself). There’s not a lot that’s surprising, though. PostGIS came in as the most popular extension. Python as the most popular non-SQL language.

Timescale

Postgres Access From the Edge — Scale your PostgreSQL database globally in minutes for low latency edge access. PolyScale automatically caches data and executes queries closer to your users for low latency, serverless functions at the edge.

PolyScale.ai sponsor

Improved Performance for ORDER BY / DISTINCT Aggregates — Technical item from the Postgres repo incoming! “Performance of ORDER BY / DISTINCT aggregates has just been terrible for too long in Postgres,” says David Rowley, but that will no longer be the case after implementation of an optimization that sounds simple in theory but was undoubtedly tricky to get right in practice.

David Rowley

Extending Postgres with C: A Simple Example — I’ve seen a few public discussions recently where someone has commented just how pleasant it is building extensions for Postgres. There are now numerous options for doing so (such as using Rust with pgx) but using good old fashioned C works just fine and isn’t as difficult as you might expect. (This tutorial is also helpful for establishing the necessary pieces.)

Eric Radman

Manage Long-Running Read Queries on Amazon Aurora — Goes over “the potential conflicts” and shares "best practices for managing long-running read queries on Aurora PostgreSQL.”

Wanda He and Avi Jain (AWS)

Monitoring Distributed Postgres Activity with citus_stat_activity and citus_lock_waits — One for Citus users only: ”.. some new monitoring tools introduced in Citus 11 that’ll help you track and take control of your distributed queries”

Halil Ozan Akgul (Citus Data)

pg-boss 8.0: Postgres + Node.js Job Queueing System — A job queue for background processing and reliable asynchronous execution. It uses Postgres specific features (like SKIP LOCKED) to guarantee safety. v8 makes Node 14 the minimum supported version and enables reuse of existing DB connections on certain operations.

Tim Jones

Simplify Database Management. Free Your Team. Control Your Data

EDB BigAnimal sponsor

Dexter: The Automatic Indexer for Postgres — Collects your queries and generates indexes to suit them. Here’s an article introducing the idea.

Andrew Kane