#​427 — October 20, 2021

Web Version

Postgres Weekly

Function Pipelines: Building Functional Programming into Postgres? — The latest release from the Timescale folks (who are behind the time-series focused TimescaleDB extension) provides a way to write more functional SQL for analyzing data through composing functions together. The first example given shows off the idea pretty well.

David Kohn (Timescale)

Postgres Text Search: Balancing Query Time and Relevancy — Postgres’s pg_trgm provides ways to query and search text for specific strings of characters (as opposed to FTS which is more word oriented). Stephen demonstrates some of the performance concerns when using pg_trgm in particular and why tradeoffs are needed.

Stephen Gutekanst (Sourcegraph)

A Better Way to Index Your Postgres Database: pganalyze Index Advisor — We all love explain.depesz.com for sharing EXPLAIN plans. So we thought: Why not make a similar free tool, specifically for getting index advice on a query? Check out our free pganalyze Index Advisor and learn what the best index for your query is.

pganalyze sponsor

OtterTune: An Automated Database Tuning Service for RDS Postgres — I’ve linked to the Hacker News post as it has more content, but you could go to OtterTune’s homepage direct, if you prefer. OtterTune is a database optimization service built on concepts explained in this 2017 paper and it’s now available for the public to try (on RDS deployments only). Here’s a five-minute explanation.

Andy Pavlo et al.

Upgrading Amazon RDS and Aurora Postgres v9.6 Databases — If you use either of AWS’s managed Postgres services and are using Postgres 9.6, you want to continue your upgrade story ASAP. Otherwise, AWS will automatically upgrade you in early 2022 which could cause problems if you’re not prepared.

Chandra sekhar Pathivada (Amazon Web Services)

Projecting Monthly Revenue Run Rate in Postgres — If you’ve ever run a business or done reports for one, you’ll know how important those monthly recurring revenue (MRR) and annual recurring revenue (ARR) rates are, and you can calculate them from data stored with Postgres using the right SQL queries.

Jonathan S. Katz

📈 How to Perform Data Evaluation Tasks with Postgres & TimescaleDB

Timescale sponsor

WITH HOLD Cursors and Transactions — This article describes how cursors and transactions interact, how WITH HOLD can overcome the limitations, plus some caveats and tricks.

Laurenz Albe

Does QGIS Work with Postgres 14? — In short, yes.

Elephant Tamer

Tools & Code

pgmetrics 1.12: Collect and Display Stats from Running Postgres Servers — Written in Go, and here’s an example of its output. v1.12 adds Postgres 14 and PgBouncer 1.16 support, including collecting new PG14 specific stats. GitHub repo.


pg_timetable 4.2 Released: Advanced Job Scheduling for Postgres — The latest 4.2.0 release fixes a bug in logging functionality and any users are encouraged to upgrade immediately.

CYBERTEC PostgreSQL International GmbH

Visualizing Postgres Vacuum Progress — This is a ‘golden oldie’ post I wanted to highlight again! If your database’s vacuum procedure is taking an extensive amount of time (as it is here) being able to visualize its progress is surely desirable and in this case a picture truly is worth a thousand words.

Dave Pacheco