#​572 — October 2, 2024

Web Version

📰 Last week's issue, which dropped on Friday, was a big deal as we sent it just after Postgres 17 was released, so if you missed it due to the unusual sending time, you can check it out here. :-)
__
Peter Cooper, your editor

Together with  Blacksmith

Postgres Weekly

What's So Great About Postgres 17? — Finally, Postgres 17 is here and we’re starting to see some opinionated takes on its many enhancements. Laurenz covers the things that caught his eye here.

Laurenz Albe

Are You Qualified to Use NULL in SQL? — A fun(?) quiz designed to test your intuitions about the behavior of NULL and how it interacts with other types, predicates, aggregations, and more. It’s a real brainscratcher. (Note: My Postgres, at least, behaves differently to the expected answer on the final question, showing just how confusing it all is.)

AgentM

Run GitHub Actions Up to 2x Faster at Half the Cost — Blacksmith runs your GitHub Actions substantially faster by running them on modern gaming CPUs. Integrating Blacksmith is a one-line code change. 100+ companies like Ashby, Superblocks, and Slope use Blacksmith to help developers merge code faster.

Blacksmith sponsor

▶  Heroku's Glory Days and Postgres vs The World — Postgres legend Craig Kerstiens joined Aaron Francis (working on the soon to be launched Mastering Postgres course) to talk about his history with Postgres at Heroku, Citus Data, and now Crunchy Data. It’s not all about him, though; the interview is packed with insights about building ecosystems, scaling Postgres, and even DuckDB.

Aaron Francis

▶  SQL IN Clauses are Miles Faster in Postgres 17 — Hussein is so amazed by PG17’s enhancements to b-tree scanning performance when using IN that he’s recorded a screencast explaining how it works, compares it to SQL Server’s approach, and shows it off with a live demo.

Hussein Nasser

QUICK BITS:

Waiting for Postgres 18: Add Temporal PRIMARY KEY and UNIQUE Constraints — Postgres 17 may have just landed, but Hubert is looking ahead to Postgres 18, and a new way to enforce uniqueness across a time period, say, using WITHOUT OVERLAPS rather than just against static values.

Hubert depesz Lubaczewski

Exploring the Limits of Postgres: When Does It Break? — Postgres’ limits are well understood on paper, but how far could you push a single node Postgres server in the real world and where are the bottlenecks?

Andrew Atkinson

📄 Writing Raw SQL Easier with pgx and sqlc in Go remvn

📄 Upgrading Homebrew Postgres 16 to 17 on a Modern Mac Ian van der Linde

📄 Building an AI Image Gallery: Advanced RAG with pgvector and Claude Sonnet Haziqa Sajid (Timescale)

📰 Classifieds

Choosing PostgreSQL data types wisely can save you time and headache down the road. Join Redgate’s live webinar on October 8th to learn how.

Building AI apps on Postgres? pgai empowers devs with AI superpowers, enabling integration of AI workflows for tasks like embedding creation and model completion.

🛠 Code and Tools

pg_stat_kcache: Gather Statistics About Disk Access and CPU Consumption — Sits atop pg_stat_statements and creates views you can use to measure CPU time, page faults, swaps, bytes read and written, and more at both database and query level.

Dalibo and PoWA Team

pgsql-tweaks: A Grab Bag of Useful Postgres Functions — An interesting suite of functions that the author (a former PostgreSQL Person of the Week) uses in her day job as a Postgres user, covering areas like checking data types, gathering statistics, some aggregates, and conversion functions.

Stefanie Janine Stölting

pg_qualstats: Extension for Collecting Statistics About Predicates — Analyze the most often used predicates in queries against your database, perhaps as a way to create more effective indexes. Part of the POWA (Postgres Workload Analyzer) project.

Powa Team

  • pg_idkit 0.2.4 – An extension for generating UUIDs and similar IDs.

  • Pongo 0.15 – Postgres driver for Node.js that presents as a MongoDB-style API.

  • node-pg-migrate 7.7 – Database migration management from Node.js.

  • pgwire 0.25 – The Postgres wire protocol implemented in a Rust library.

  • DoltgreSQL 0.12 – Version-controlled Postgres-a-like.

  • pspg 5.8.7 – Unix terminal pager for tabular data.