Postgres Weekly Issue 582: December 18, 2024

#582 — December 18, 2024

🎄 This week we're covering a few recent items but will then head into a 2024 roundup of how things went in the Postgres world, as well as the most clicked items of the year, in case you missed them at the time.

We're taking a Christmas break for two weeks and will be back on January 9, 2025, so we hope you have a Merry Christmas and a Happy New Year!
__
Peter Cooper and the Cooperpress team

Together with

Some of My Favorite Things – Postgres Queries — There’s not really any big Postgres news this week, so why not enjoy this festive gift of some (reasonably complex) SQL queries Shane uses to track database usage in various ways, to see unused indexes, to generate a vacuuming report, and more.

Shane Borden

Building AI Apps on Postgres? Start with pgai — pgai is a PostgreSQL extension that brings more AI workflows to PostgreSQL, like embedding creation and model completion. pgai empowers developers with AI superpowers, making it easier to build search and retrieval-augmented generation (RAG) applications.

Timescale sponsor

pg_incremental: Incremental Data Processing in Postgres — Marco introduces a new Postgres extension offering automated, incremental batch processing for creating reliable processing pipelines. GitHub repo.

Marco Slot

📄 Exploring Postgres's Arena Allocator by Writing an HTTP Server from Scratch – An odyssey that's not for the faint of heart! Phil Eaton

📄 Quick Benchmark of Improvements to Large Object Dumping in Postgres 17 Michael Banck

📄 Shrinking a Postgres Table John Nunemaker

QUICK BITS:

🗓️ It might be too early to book your flights, but if you want to go to PostgreSQL Conference Europe 2025, you'll need to pencil in being in Riga, Latvia on October 21-24, 2025.
This year, Timescale's annual Postgres community survey attracted several hundred responses and they provide a sneak peek at the responses, along with 📄 a full (rather well designed) PDF report here.
🇩🇰 Registration for Nordic PGDay 2025 is now open. It takes place in Copenhagen next March and its CFP is open till December 31.
Regina Obe ponders the 'bus factor' problem (a slightly morbid way to measure resiliency of a project to 'losing' core contributors) and how to be prepared to add new core team members as needed.
🎧 The Postgres FM podcast just had jOOQ's Lukas Eder on for an interview.

🥇 Top Developments and Items of 2024

2024 was another great year for Postgres. Here are some of the highlights, plus one sad development, looking back:

The year started strong with DB Engines billing Postgres as 'DBMS of the Year 2023'. I wonder if we'll get it for 2024 too?
Tembo hired PGXN creator David Wheeler to work on ways to improve the Postgres extension ecosystem – work he has diligently been progressing all year. Here's the latest update.
Postgres 17 was released in September with overhauled memory management, incremental backup support, faster B-tree index scans, MERGE improvements, and more. It was a great release.
pgvector continued to find its way into everything and had a handful of significant releases (most lately v0.8.0) with performance going from strength to strength.
ElectricSQL unveiled PGLite, a way to run a complete Postgres database in WebAssembly environments (including the browser).
Postgres creator Mike Stonebraker unveiled his new company, DBOS.
🗓️ Microsoft's virtual Citus Con event rebranded to POSETTE and was a roaring success in 2024. The 2025 edition is in June and its CFP is open. PGConf.dev also went well and you can watch the talks here.
Amazon teased its new Aurora DSQL database just two weeks ago.
And, absolutely not a highlight but important to recognize and remember, we sadly said goodbye to Postgres maintainer Simon Riggs who passed away in March. May he rest in peace.

Now, for the top 5 links of the year based upon reader engagement:

1. SQL Query Optimization: A Comprehensive Developer's Guide — A bumper packed post digging into the optimization of SELECT, INSERT, and DELETE queries, covering areas like using indexes, paginating results, avoiding joins, and how window functions can help (or not). Not Postgres-only but written with Postgres in mind.

Francesco Tisiot

2. Optimizing Postgres Table Layout for Maximum Efficiency — The alignment of data within broader data structures has long been an important factor for optimization in low level programming, but it can affect your Postgres tables too. Renato looked at how table layout can impact both storage efficiency and query performance.

Renato's Runtime Reflections

3. Can Postgres Replace Redis as a Cache? — Redis is a popular, high performance in-memory data storage server commonly used for caching, but if we can just use Postgres for everything, could Postgres take on the job instead?

Raphael De Lio

4. pgPedia: A Postgres Encyclopedia of Sorts — An interesting, cleanly formatted wiki-esque reference guide to Postgres’ features and settings that complements the official documentation well. Well worth poking around a bit.

Postgrespedia

5. Hacking on PostgreSQL is Really Hard — Robert has consistently been in the top ten committers to Postgres for many years, but this year he reflected on just how intimidating the process of contributing to Postgres can be, especially for newcomers.

Robert Haas

👋 We are now off for two weeks for our Christmas break. Merry Christmas if you celebrate too. We return on Thursday, January 9, 2025 (yes, we're changing the day Postgres Weekly goes out in the new year – keeps things interesting!)