1: 📗 The PostgreSQL 14 Internals Book — It might seem odd that a book about Postgres 14 was the most popular link in 2023, but it was completed after a long period of development, remains highly relevant to more modern Postgres versions, and is available as a free PDF. It goes into so much depth in its 600+ pages and is packed with code-driven examples and diagrams that make it easy to narrow down to the topics that interest you, like locks, indexes, and query processing.

Egor Rogov and Liudmila Mantrova

2: Nine Ways to Slip Up with Postgres — A quick tour of some common mistakes and pitfalls that you might encounter, from the overuse of triggers to not adding indexes to foreign keys.

Phil Booth

3: Vectors are the New JSON in Postgres — Jonathan's assertion is only becoming more true in 2024 than it was in June 2023. Machine learning and LLM embeddings have made vectors the data structure du jour, and Postgres's support for them is growing by the month (though mostly by way of extensions, for now, such as pgvector).

Jonathan Katz

4: 152 psql Tips — A perennially popular selection of bite-sized tips for the tried and tested psql client. If you have a psql session coming up any time soon, this is worth a browse, as you might end saving more time in the long run. It’s also possible to load a single tip and hop through randomly.

Lætitia Avrot

5: A Postgres JSONB Cheatsheet — A handy cheatsheet for the various JSON functions in Postgres with quick examples of each, along with related operators. Here's a direct link to the PDF.

Aiven

1: Top 8 Postgres Extensions to Know About — Timescale's engineers shared a selection of extensions they use and are excited by. Perhaps a little bit of cheating on our part, but this roundup did get the most clicks for a tool-related post ;-)

Sewrathan and Clark (Timescale)

2: PGMQ: Simple Message Queues built on Postgres — Imagine AWS SQS but running within your Postgres database. PGMQ is written in Rust and provides ‘exactly once’ delivery semantics with a visibility timeout.

Adam Hendel (Tembo)

3: pgroll: Zero-Downtime, Reversible Schema Migrations — The folks at Xata think database migrations should be easy, risk-free, and reversible, so they built a tool to make it the case. pgroll includes numerous approaches, such as using the ‘expand and contract’ pattern to split migrations into numerous reversible chunks and by performing operations only under short locks (with timeouts).

Carlos Pérez-Aradros Herce (Xata)

4: Mathesar: A Spreadsheet Database Interface — An open source (GPL) tool that offers a grid-style interface to a Postgres database of your choice with the aim being to enable developers to open up databases more directly to end users. It’s written in Python and is also on GitHub.

Mathesar

5: PL/Rust 1.0: Write Postgres Functions in Rust — This was a big release for an exciting project which compiles Rust to native machine code to then be run as a function within Postgres. This promises extension-like levels of performance, but in a more dynamic form. 1.2.7 is the latest version. We expect to see Rust's use for building Postgres extensions skyrocket further in 2024.

Technology Concepts and Design, Inc.

6: pg_bm25: Elastic-Quality Full Text Search Inside Postgres — If you’ve run into any limitations with Postgres’s built-in full text search, this is a Rust-based extension that introduces the BM25 index type which allegedly “indexes 50 seconds faster than tsvector and ranks results 20x faster.”

ParadeDB