Postgres Weekly Issue 588: February 13, 2025

#588 — February 13, 2025

Together with

Scaling Postgres without Boiling the Ocean — Postgres scales pretty far out of the box with the default settings, but once a server starts getting some serious load ‘weird issues’ can start to rear their head. Shayon addresses some common issues of this type and the solutions for them.

Shayon Mukherjee

How About Trailing Commas in SQL? — Could this be the simplest yet most requested SQL feature? It's implemented in some dialects, but could it be implemented in Postgres's dialect? And should it? It’s far trickier than it sounds.

Peter Eisentraut

Find Your Flow with Heroku Postgres — The world’s most advanced open source database is easier to use than ever. With Heroku’s PostgreSQL database service, you can get the most out of your data and leave the admin to us. So go ahead and focus on your data-driven apps – we got you.

Heroku Postgres sponsor

QUICK BITS:

🤖 Is it possible to learn anything about Postgres by using a local, free LLM (Phi 4 in this case) and have it help with building JSONB queries? Surprisingly, yes.
Postgres 17 is now available on DigitalOcean's managed Postgres service.
The Timescale team rounds up eight essential Postgres extensions, along with a primer on how to install and use extensions generally. Surprisingly, TimescaleDB isn't in the eight! :-)
🎤 Robert Haas went on the Talking Postgres podcast to talk about the importance of mentoring developers who want to work on the Postgres project.

Dealing With "found xmin ... from before relfrozenxid" — A look into a commonly reported error with easily misunderstood wording. It occurs during (and breaks) the vacuuming process when an unfrozen tuple’s xmin predates the table’s relfrozenxid, signaling data corruption. Luckily, there are solutions.

Laurenz Albe

📄 Working with the Postgres Protocol From Rust – From the creator of the PgDog sharding proxy. Lev Kokotov

▶️ The Early History of Spatial Databases and PostGIS Paul Ramsey

🛠 Code and Tools

Citus 13: Now with Postgres 17 Support — Citus is a long standing open source extension that makes it easier to horizontally scale Postgres. Bought by Microsoft some years ago, it continues to get releases, including this new one with Postgres 17 support, including for distributed queries using JSON_TABLE() and improved support for distributed partitioned tables.

Naisila Puka (Citus Data)

Locksmith: Detect SQL Migration Issues Quickly — A new Rust-powered tool to detect table locks, table rewrites, and modified tables, columns and indexes during a schema migration.

Tom Forbes

pgroll 0.9.0 Released with New Schema Migration Features — pgroll is a tool for performing reversible schema migrations on Postgres without taking the database down. v0.9 supports adding table-level constraints into the table creation process, as well as generated columns.

Andrew Farries

Pig v0.1.4: Now with 400 Postgres Extensions — Pig (short for Postgres Install Genius) is one of the newer attempts to manage Postgres extensions in a package-based fashion. The latest version adds support for Microsoft’s DocumentDB, pg_tracing, pg_cooldown, VectorChord-bm25, and more.

Pigsty

📰 Classifieds

Making the move from Oracle to Postgres? Read this in-depth guide to replacing Oracle hints with pg_hint_plan equivalents & when not to use hints.

📑 Start tracking vital resource and activity metrics from your PostgreSQL databases with Datadog's handy cheatsheet.

PeerDB 0.25 – Stream data from Postgres to data warehouses, queues and storage engines.
pgxmock 4.5 – Mock driver to test Postgres interactions in Go.
Bytebase 3.4.0 – A collaborative database devops platform.
RisingWave 2.2 – Postgres-compatible streaming database.
postgres-meta 0.86 – A RESTful API for managing Postgres.
DoltgreSQL 0.17 – Version-controlled Postgres-a-like.