#​513 — July 5, 2023

Web Version

Together with  pgAnalyze

Postgres Weekly

Thirteen Ways of Looking at a Join — Joining tables is a fundamental operation in both SQL and relational databases generally and there’s a lot of ways to describe and think about them. Justin covers thirteen such ways, from them being a “solution to N+1” or “a flatMap” through to more arcane options like being “paths through a graph” or “a ring product.”

Justin Jaffray

🗣️ Hacker News particularly enjoyed this post and came up with a couple more ways, plus an idea for a title which we adopted.. thanks Nick Peterson 😉

Postgres 16 Beta 2 Released — The countdown to the eventual release of Postgres 16 continues with beta 2 including fixes for a variety of bugs picked up in beta 1. As always, the exhaustive list of release notes is available.

PostgreSQL News

Webinar: Advanced Autovacuum Tuning and the Upcoming pganalyze VACUUM Advisor — Learn about the most important VACUUM metrics, concepts like xmin horizon, how to prevent transaction ID wraparound, estimating bloat, and more. We walk through the upcoming pganalyze VACUUM advisor and showcase its per-table tuning recommendations.

pganalyze sponsor

Google Hops Aboard the (pg)Vector Train — Just last week, Jonathan Katz told us vectors are the new JSON in Postgres and right on the tail of that, Google has added pgvector support to both AlloyDB and Google Cloud SQL. You’re encouraged to use it alongside Google's vector-based Vertex AI Matching Engine database.

Ghai and Narasimhan (Google Cloud)

Heroku Introduces New Postgres Plans — As one of the world’s largest caretakers of Postgres databases, it’s nice to see good news relating to Heroku. They’re helping users scale a little further with new plans offering up to 6TB of space, 768GB of memory, and 16K IOPS. If you need to ask the price.. 😅

Jonathan K. Brown (Heroku)


Nearest Neighbor Indexes: What are pgvector's 'ivfflat' Indexes? — pgvector uses the Inverted File Flat (ivfflat) algorithm for speeding up the approximate nearest neighbor search of the vectors you’re storing. This post explains the concept in a very accessible way, even if you’re new to the vector space.

Arye and Sewrathan (Timescale)

🧊  Fun with Postgres Puzzles: Surface Area and 3D Slices — Greg’s journey of completing the 2022 Advent of Code challenges with SQL and Postgres continues with more mind bending SQL, PL/pgSQL, and even a 3D visualization of sorts. Now if only ChatGPT could write this level of code, I’d be shocked..

Greg Mullane

Goodbye etcd, Hello Postgres: Running Kubernetes with an SQL Database?Could you replace etc with Postgres at the heart of Kubernetes? Yes. Why? When the author says “why not”, it’s clear you’re enjoying the results of someone’s experimentation! But it’s certainly possible if you need to..

Martin Heinz

Epsio's PostgreSQL Incremental Materialized View

Epsio sponsor

Differential vs Incremental Backups in pgBackRestpgBackRest is a popular backup and restore solution that can make full, incremental, and differential backups. Incremental backups are those containing everything that changed since the last full or incremental backup, but differential backups can provide more resiliency by being based upon the latest full backup alone.

Stefan Fercot

What Happens When a Postgres Data File Goes Missing — If a data file associated with a table goes ‘missing’, perhaps due to an OS or hardware problem, you’re probably going to have a bad time, but here are a few things to consider doing.

Abhishek Deb

Code and Tools