#​531 — November 22, 2023

Web Version

Together with  Epsio

Postgres Weekly

Exploring a Postgres Query Plan — Phil continues his spelunking into the technicalities of how Postgres works by looking at how the query execution process can be intercepted and what you can learn by doing so. Admittedly, most Postgres users will never need to dig this deep but it’s worth a skim if you want to at least get a feel for what goes on under the hood.

Phil Eaton

Boosting Query Performance with Incremental Materialized Views! — Boost the performance of your most complex queries by plugging Epsio into your database. Epsio constantly prepares and updates results for queries you define whenever the underlying data changes, without ever recalculating the entire dataset.

Epsio sponsor


Designing Your Database for Multi-Tenancy — Craig digs into multi-tenancy apps, sharding, and the role the Citus extension can play to manage such situations. He gives several approaches to data design so you’ll be better prepared to scale if, and when, the time comes.

Craig Kerstiens (Crunchy Data)

Running Postgres on s390x (i.e. IBM Mainframes) — Postgres is gaining support for a not-so-new architecture in the shape of IBM’s Z-series mainframe architecture, known as s390x in Debian. A build machine has been provided by Marist College’s LinuxOne cloud.

Christoph Berg

How to Use pgvector for Similarity Search on Heroku — Heroku Postgres announced support for pgvector a few weeks ago, and here’s the obligatory ‘how to use it’ post.

Valerie Woolard (Heroku)

Experiencing WAL REDO in Postgres — A technical look at the steps involved in adding a piece of customized information as an XLOG record for use during WAL REDO.

David Zhang

PostgreSQL 101 Webinar: Database Monitoring and Optimization

Redgate sponsor

Citus Live Query Inspection with citus_stat_activity — How to use citus_stat_activity to get all the information about your shards in one go.

Hans-Jürgen Schönig

Teaching Postgres New Tricks: SIMD Vectorization for Faster Analytical Queries
James Blackwood-Sewell (Timescale)

Fast Switchovers with PgBouncer on Amazon RDS Multi-AZ Deployments with Two Readable Standbys
Khodorkovskiy and Jain (AWS)

Code and Tools

River: A Fast, Robust Job Queue for Go and Postgres — A new in-beta, open-source job queue “for building fast, airtight applications” that’s written in Go and takes advantage of generics. It uses pgx to interface with Postgres.

Brandur Leach

pgcopydb: Copy a Postgres Database to a Target Postgres Server — Automates and provides a little structure around the running of pg_dump | pg_restore between two running Postgres servers.

Dimitri Fontaine

PGSync 3.0: A Postgres to Elasticsearch Syncing Tool — Middleware for syncing Postgres to Elasticsearch so you can expose denormalized documents in Elasticsearch for querying. Now MIT licensed.

Tolu Aina