#​414 — July 14, 2021

Web Version

Postgres Weekly

Understanding LATERAL Joins in Postgres — You can think of LATERAL joins as being a bit like a for-each loop where a subquery is run for each row of an initial result set. This can, of course, be very useful!

Hans-Jürgen Schönig

Timescale Adds 'Hyperfunctions' to Improve Time Series QueriesTimescaleDB is a time-series oriented extension/distribution for Postgres and they’ve extended Postgres’s SQL support with a variety of ‘hyperfunctions’ for working with time series data (things like time-weighted averages, hyperloglog counting, and dataset smoothing).

Lockerman, Kohn, and Rowe

An Operations-Free, Scalable & Flexible Postgres Alternative — Fauna combines the operational integrity and relational modeling of Postgres with an interface and architecture that fits better with modern app development in the cloud. The goodness of Postgres without its operational bottlenecks - Learn more about Fauna.

Fauna sponsor

LIMIT vs FETCH FIRST ROWS ... WITH TIES — I still remember how surprised I was when I first learnt that LIMIT/OFFSET is not standard SQL (though many systems support it). FETCH FIRST may be more portable, but there are some things to consider about its use.

Hans-Jürgen Schönig

DBCritic: Constructive Criticism for Your Postgres Schema — The team at Channable built DBCritic as a way to find problems in schemas due to running into a variety of problems in their own (explained in this article). The tool itself is interestingly written in the little seen Idris language.

Radek Slupik (Channable)

What Happens to Logical Replication After Running pg_upgrade? — Be careful about your replication slots.

Michał Mackiewicz

Using GDB To Trace Into a Parallel Worker Spawned By Postmaster During a Large Query — ‘Postmaster’, by the way, is referring to the part of Postgres that handles incoming client connections and routes them to a server process (though the term itself is now deprecated).

Cary Huang

Crunchy Bridge: Fully Managed Multi-Cloud Postgres

Crunchy Bridge sponsor

Heroku Database Connection Calculator — Optimizing your database pool configuration relies on quite a few variables (number of dynos, workers, processes, threads) so this guide does the math for you while also explaining each calculation.

Rails Autoscale

pgSCV 0.7.0 Released — pgSCV is a Postgres metrics collector that exposes said metrics in Prometheus format. GitHub repo.

Alexey Lesovsky

rails-pg-extras 2.0: Postgres Performance Insights for Ruby and Rails — Ruby on Rails developer? This plugin lets you get quick info about locks, index usage, buffer cache hit ratios, vacuum stats and more. There’s a pure Ruby (non Rails) version too.

Paweł Urbanek

db-to-sqlite 1.4: A CLI Tool for Exporting Tables or Queries from any SQL Database to SQLite

Simon Willison