#​417 — August 4, 2021

Web Version

Postgres Weekly

Working Around a Case Where the Postgres Planner is 'Not Very Smart'? — Postgres’ query planner is usually fantastic but I’ve run into a few edge cases over the years, and so it goes here with a workaround resulting in a 2x performance improvement.

Matt DuPress

Improve Your Query Performance Insights with pg_stat_monitorpg_stat_monitor is an extension, built by Percona, that’s trying to be a more advanced replacement of the usual pg_stat_statements.

Ibrar Ahmed (Percona)

Free eBook: Effective Indexing in Postgres — Learn how to create the best Postgres index for your queries. We provide a deep dive into index types, operators, data types and more. Creating the right indexes can often improve your query performance by 10x or even 100x.

pganalyze sponsor

pg_timetable 4.0 Released: Advanced Job Scheduling for Postgres — The 4.0 release is backwards incompatible so a new timetable table will need to be built. There’s a lot of new documentation to enjoy too.

CYBERTEC PostgreSQL International GmbH

TimescaleDB 2.4 Introduces an 'Experimental Schema' — A new release for the increasingly popular time series extensions for Postgres. The big feature this time is the addition of a specific ‘experimental’ schema you can use to try out the edgier parts of what’s to come.

Timescale, Inc.

What’s New in the Citus 10.1 Extension to Postgres — As a list of enhancements, nothing in 10.1 jumps out, but collectively it’s still a strong step forward for the popular ‘turn Postgres into a distributed database’ extension.

Claire Giordano (Microsoft)

Postgres Security Problems to Avoid — Twelve recommendations covering areas like not listening on all network devices, not using MD5 hashed passwords, avoiding SQL injection problems (including in your PL/pgSQL functions), and more.

Hans-Jürgen Schönig

Postgres 14 Logical Decoding of Two-Phase Commits Explained — Two phase commits weren’t properly supported by logical replication but that’s changing in PG 14. This post deftly explains what they are, why this matters, and how PG 14 will change things. (This is a good explainer generally, even if you don't think you need the feature.)

Ajin Cherian

Using SQL Triggers with AWS Lambda — Did you know that Postgres triggers can interact with Lambda-hosted functions directly? A Python specific example of bringing it all together is shown here.

Liav Yona

Hasura Now Supports Citus / Hyperscale | Instant Realtime GraphQL on Your Data in 30 Seconds

Hasura sponsor

Looking into Memory Locks — Egor looks at spinlocks, lightweight locks and buffer pins, all of which involve locking memory in one way or another.

Egor Rogov

GoodJob: A Multithreaded, Postgres-Based, ActiveJob Backend for Ruby on Rails — One for the Rubyists among you! Complete support for async, queues, delays, priorities, timeouts, and retries with near-zero configuration. Some recently added features include a cron-esque replacement for recurring jobs and improved concurrency controls.

Ben Sheldon