#​462 — July 6, 2022

Web Version

Postgres Weekly

Understanding pg_stat_activitypg_stat_activity provides an easy way to look at what different Postgres backend processes are up to. Hubert digs into what its various values actually represent.

Hubert depesz Lubaczewski

PostgreSQL 15 Beta 2 Released — It’s to Postgres’s huge credit that a lot of effort is put into getting major releases just right so it’s reassuring to see a second beta five weeks after the first with plenty of fixes. You’re encouraged to continue to help testing and the updated (beta) release notes remain here.

PostgreSQL Global Development Group

With Retool, You Can Join Data from All Your Sources into One CRUD App — Retool connects to your PostgreSQL database in minutes and makes it easy to build admin panels, dashboards, and utilities on top of your PostgreSQL data. Also, with Retool you can connect to your other data sources with a REST or GraphQL API — all in one internal app.

Retool sponsor

How to Log Selects From a Specific Table? — A practical introduction to using PGAudit. It’s nice to have access to this sort of feature.

Hubert depesz Lubaczewski

Transaction Anomalies with SELECT FOR UPDATE“I was recently confronted with a nice example of how adding FOR UPDATE to a query can introduce transaction anomalies.” Luckily Laurenz has some tips for handling this situation. (If you’re not aware, FOR UPDATE locks selected rows against concurrent updates, as you might want to do in a transaction.)

Laurenz Albe


Queries in PostgreSQL: Nested Loops — Egor’s detailed and highly technical Postgres posts are always welcome and he’s moved on to digging into join methods and how Postgres 14 introduced a handy technique to speed up repeated loops in particular.

Egor Rogov

How to Reason About Indexing Your Postgres Database (On‑Demand Webinar)

pganalyze sponsor

Handling 'Bonus Programs' in SQL — It’s always interesting to see how to bring logic into the database. This example orients around using window functions to collect data across time ranges, such as may be used by a corporate rewards scheme.

Hans-Jürgen Schönig

▶  Tuning the Postgres Statistics Target and Understanding Selectivity for Spatial Indexes
Lukas Fittl

🔧 Code and Tools:

Antares SQL: A New(ish) Open Source SQL Client — A cross-platform Electron-based client with some ambitious goals. Supports MySQL/MariaDB, Postgres, and SQLite so far, with more on the way. Looks promising!

Fabio Di Stasio

OctoSQL: Join, Analyze, and Transform Data From Multiple Sources using SQL — A Go powered CLI tool for querying a ‘plethora’ of sources (including CSV files, JSON, MySQL, Postgres, Redis and Parquet) and even doing joins between them.

Jacob Martin

The EverSQL Online SQL Query Optimization Tool — This is far from the first such tool, and it’s certainly not the simplest, but this index advisor covers numerous databases, including Postgres, and is worth a look.



PostGIS 3.3.0beta1
PGSync 2.3 – Postgres to Elasticsearch sync.
node-pg-migrate 6.2.2 – Node.js-based migration management.
data-diff 0.2.2 – Efficiently diff rows across two different databases.
Citus 11.0.3 – Distributed Postgres as an extension.
SeaQuery 0.26 – Dynamic SQL query builder for Rust.
pspg 5.5.5 – Unix pager for working with tables.