#​557 — June 5, 2024

Web Version

🫣 We took a break last week, but forgot to notify you in advance - sorry! You didn't miss anything, and we're catching up with the past two weeks today. Luckily, as you'll see in a moment, it wasn't just us taking a break.. :-)
Your editor, Peter Cooper

Together with  Microsoft

Postgres Weekly

Why Upgrade Postgres? Here's Exactly Why — It’s been over two years since we linked to this neat resource that lets you pick ‘from’ and ‘to’ versions and see what Postgres gained in the interim. Even this 16 to 16.3 example is packed with all sorts of interesting details.

Hubert depesz Lubaczewski

 PostgreSQL 17 Beta 1 Released — Time wasn’t on our side with our break last week, as the first beta of Postgres 17 landed just after we sent the last issue! Nonetheless, this remains big news, and the post does a good job of rounding up what’s new here.

PostgreSQL Global Development Group

Don't Miss POSETTE: An Event for PostgresPOSETTE is almost here! Join us on June 11-13 for POSETTE, the largest free virtual Postgres event. Featuring 42 talks across 4 livestreams all about the world’s most advanced open source database. Brought to you by the Postgres team at Microsoft.

Microsoft sponsor

💡 Claire Giordano has also put together a fantastic guide to POSETTE and the various talks, given by folks across the entire Postgres community, and other activities taking place next week.

How Engaging Was PGConf.dev Really? — PGConf.dev took place last week and was a great success but did it truly engage core developers? Peter worked out it caused the longest hiatus in core commits in over 20 years!

Peter Eisentraut

Why Do I Have a Slow COMMIT? — When it comes to committing a transaction, the faster the better. But what can cause them to go slow?

Laurenz Albe

Controlling Resource Consumption with Linux's cgroup2 — Control groups constrain the resources allocated to processes and can be used to keep Postgres in check, too, particularly in ‘noisy neighbor’ multi-tenant scenarios.

Jobin Augustine

Circular Dependencies in Foreign Keys — A needs B and B needs A. Uh-oh! How can such circular dependencies occur and how can they be worked around? INITIALLY DEFERRED to the rescue.

Hans-Jürgen Schönig

Creating Custom Postgres Extensions with Rustpgrx is (probably) the way to go, and here’s some pointers to getting started.

Radim Marek

Building a RAG App with Postgres and pgvector — RAG (Retrieval Augmented Generation) is a technique for enhancing the output from large language models by augmenting queries with context retrieved from other datasets.

Gulcin Yildirim Jelinek (EDB)

📄 Taking Postgres for GRANTed – A quick look at predefined roles in Postgres. Shaun M. Thomas

📄 Setting Up a High Availability 3-Node Postgres Cluster with Patroni on Ubuntu 24.04 Semab Tariq (Stormatics)

📄 Data Encryption in Postgres: A Roundup of Methods Greg Nokes (Crunchy Data)

📄 Converting DMS to PostGIS Point Geometry Elizabeth Christensen (Crunchy Data)

📄 Carbon Footprint and Query Optimization? Henrietta Dombrovskay

📰 Classifieds

Create an admin panel from your database structure with Rocketadmin. Zero coding required. We have SAAS and self-hosted versions.

🔄 Local-first w/ Postgres: PowerSync uses Logical Replication to sync only a relevant subset of data to each user, with guaranteed consistency.

Your fastest path to production. With Render, you can build, deploy, and scale your apps with unparalleled ease – from your first user to your billionth.

🛠 Code and Tools

tbls: A CI-Friendly Tool for Documenting Databases — Document a database automatically in Markdown format with schemas rendered via DOT, PlantUML, Mermaid, or direct to images. By default you get documentation covering columns, indexes, relations, and other schema details (here’s a sample).

Ken’ichiro Oyama

Introducing pgCompare: A Multi-Database Comparison ToolpgCompare is an open source tool fresh out of Crunchy Data. It’s designed for when you’re replicating data from different sources and need to validate data consistency afterwards.

Brian Pace (Crunchy Data)

SQLFluff: A SQL Linter 'for Humans' — Built in Python, a SQL linter that covers a variety of dialects (including Postgres, of course).

Alan Cruickshank

📄 A Quick Look at Two Terminal Tools for PostGIS Dian M Fay

  • QuestDB 8.0 – Java-powered time-series database with Postgres wire protocol compatibility.

  • Bob 0.27 – SQL query builder and ORM/Factory generator for Go.

  • pgxmock 4.0 – Mock driver to test database interactions in Go.

  • pgweb 0.16 – Cross-platform client for Postgres databases.

  • Piccolo 1.7 – User friendly Python ORM and query builder.

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

  • pgvector 0.7.1