Postgres Weekly

#578 — November 20, 2024

Together with

Postgres 17.1, 16.5, 15.9, 14.14, 13.17, and 12.21 Released — A roundup of releases for every maintained line of Postgres including bug fixes and fixes for four security issues. 12.21 also marks the final release of Postgres 12. Be careful with upgrading to Postgres 17.1, though, as there are some potential compatibility issues.. see the next item!

PostgreSQL Global Development Group

A Change to ResultRelInfo: A Near Miss with Postgres 17.1 — A seemingly minor change in the Postgres 17.1 release led to compatibility issues with some extensions (such as TimescaleDB and Apache AGE – see what TimeScale’s CTO had to say here). Craig explains what the issue is and why it arose here, reminding us that developing extensions robust to such changes is a full time job.

Craig Kerstiens

Postgres Reimagined for Data Warehousing — Crunchy Data Warehouse combines managed storage, a powerful analytics engine, and unified data pipeline. Seamlessly query data using familiar Postgres tools with the added benefit of columnar storage and performance.

Crunchy Data sponsor

Boosting INSERT Performance by 50% with UNNEST — You probably know that using COPY is usually the fastest way to ingest data into Postgres at scale, but INSERT retains some advantages due to its extra features. Can INSERT be sped up? UNNEST arrives to shake things up..

James Blackwood-Sewell

QUICK BITS:

📅 Microsoft's virtual POSETTE Postgres conference is back in 2025 and it has a call for proposals now open if you'd like to speak. It takes place in June 2025, but you have until February 9 to get your proposals sent in.
📅 The CFP for PGConf.dev 2025, taking place in Montreal next May, is also open right now until January 1.
🗣️ EDB's Ayse Bilge Ince is this week's PostgreSQL Person of the Week interviewee.
🧊 Crunchy Data has unveiled Crunchy Data Warehouse, a managed service that brings a high performance analytics database into Postgres.

Easy Totals and Subtotals in Postgres with Rollup and Cube — Handy additions for performing more analytical queries. ROLLUP can create hierarchical subtotals along with a grand total, while CUBE generates subtotals for every possible combination of grouping columns.

Elizabeth Christensen

Loading the World: An OpenStreetMap Import in Under 4 Hours — Did you know that OpenStreetMap’s database is a whopping 750GB in size, and you can grab it and set it up for yourself as a way to stress-test Postgres? Greg looks at how long it takes to ingest, how to make it faster, and performance improvements over the years. (Minor spoiler: Postgres 17 is 3% faster than Postgres 16 on this benchmark.)

Greg Smith

Running Postgres on Spot VMs? 🤯 — Building on a talk from PGConf Europe, Kaarel looks at how viable it is to run Postgres on instances that could, well, disappear at almost any time. pg-spot-operator is a utility he’s built to make it work.

Kaarel Moppel

The Doom That Came To Postgres: When Collations Change — What can happen when the library that provides locales changes out from under an existing Postgres database? It’s not pretty..

Christophe Pettus

📄 What Happens Behind the Scenes When You Modify a Row in Postgres? Semab Tariq

📄 Grouping Data into Arrays of Sums – Fun with Custom Aggregates Hubert depesz Lubaczewski

📄 Accessing Large Language Models from Postgres – A look at a few approaches. Paul Ramsey

🛠 Code and Tools

pglite-fusion: Embed SQLite Databases in Postgres Tables — An extension that gives you the ability to embed SQLite databases into Postgres tables by way of a special new SQLITE column type (so, yes, every row in a table could itself have an embedded SQLite database..)

frectonz

DBngin: Simple Postgres, MySQL and Redis on Mac and Windows — You could use Docker, you could use Postgres.app.. but if you’d like to spin up different versions of several common databases using a simple UI, this could fit the bill.

DBngin