Postgres Weekly Issue 592: March 13, 2025

#592 — March 13, 2025

Together with

1 Trillion Rows in Citus? — “Postgres scales,” but just how far does that go? Hans-Jürgen decided to find out, running a little (or is it big?) experiment to see if a 1 trillion row table is even achievable.

Hans-Jürgen Schönig

❌ Ways to Cancel Queries in Postgres — If you’ve got a query or two hogging Postgres’s resources, terminating them is an option, and there are several ways to go about it.

Petr Kada

▶ Don’t Let Postgres Maintenance Slip Through the Cracks — Secure your spot to learn the proactive maintenance practices that uncover hidden database performance risks, like slow queries, checkpoint behavior, & connection issues, before they escalate.

pganalyze sponsor

QUICK BITS:

🤔 Microsoft's Postgres team has reflected on what they achieved in 2024. If you've ever wondered who's in their team and what they do, this is a good introduction.
🗣️ After a break of a few months, the PostgreSQL Person of the Week interviews are back with Doug Ortiz in the hot seat.
🎤 EDB's Jeremy Kellway went on the Stack Overflow podcast to explain how Postgres is a great fit for AI use cases.
⛓️‍💥 The Neon Postgres platform has implemented an 'Outage Simulator' to demo its 'instant restore' feature.
🇨🇦 If you'd like to speak at Postgres Extensions Day Montreal 2025, its call for speakers is open until April 1.

Postgres Gets a Self-Join Elimination Optimization — Several years ago, someone suggested a join optimization where redundant inner self-joins (often added by ORMs) can be removed under certain conditions. This so-called Self-Join Elimination (SJE) optimization has now been committed to Postgres and is explained in this GitHub commit.

Postgres Repo

📄 Waiting for Postgres 18: Allow Parallel CREATE INDEX for GIN Indexes Hubert depesz Lubaczewski

📄 Waiting for Postgres 18: Allow json{b}_strip_nulls to Remove Null Array Elements Hubert depesz Lubaczewski

📄 When HASH Partitioning Works Better Than RANGE Umair Shahid

🛠 Code and Tools

Introducing pgNow: A Point-in-Time Diagnostic Tool — A free (but not open – it’s in preview behind an email wall) cross-platform desktop tool created by Redgate to highlight and analyze live data about queries, indexes, configuration settings, and other important elements of a Postgres cluster’s operation. Even if you don’t grab it, Ryan gives a thorough overview of what it offers here.

Ryan Booz

PGSync 4.0 – Middleware for syncing data from Postgres to Elasticsearch or OpenSearch.
📊 PEV2 v1.14 – Vue component for visualization of Postgres execution plans.
DoltgreSQL 0.18 – Version controlled Postgres-a-like with Git-style functionality.
pgenv 1.4 – Postgres binary manager.