#​538 — January 17, 2024

Web Version

Together with  Crunchydata

Postgres Weekly

Implementing GPT in 500 Lines of SQL — When an article pitches itself as demonstrating ‘a complete GPT2 implementation as a single SQL query in PostgreSQL’, you know you’re in for a serious ride. Can the algorithms involved in a large language model be reimplemented in SQL? (It's not going to replace ChatGPT any time soon, but the eventual query in this post is a sight to behold..)


Postgres 17's ALTER TABLE Command to Change Generation Expressions — When you CREATE a table, you can define generated columns that are automatically generated by way of an expression. If you want to change such expressions later on, Postgres 17 will have an answer in the form of ALTER COLUMN ... SET EXPRESSION.

Hubert depesz Lubaczewski

❤️ Postgres — You need a database provider that loves Postgres as much as you do. We'll take care of all the hassle - monitoring, backups, HA, disaster recovery so you don't have to. Want amazing support? We'll be there when you have questions.

Crunchy Bridge sponsor

Introducing pg_query for Postgres 16pg_query is a long standing standalone library (with Ruby, Rust and Go bindings) for turning SQL queries into parse trees. This new version is based on Postgres 16, supports SQL/JSON, and now works on Windows too.

Fittl & Mijalkovic (pganalyze)

Optimizing Postgres Functions with Preloaded Libraries — Thanks to extensions, it’s possible to write Postgres functions in numerous different languages, but those extensions need to be loaded when needed – the session_preload_library can be used to preload libraries at connection start, shaving off precious milliseconds.

Hans-Jürgen Schönig


▶  Waiting for Postgres 17: Incremental Base Backups — Walking through the incremental backup feature committed to the Postgres 17 development branch by Robert Haas. (6-minute video with transcript.)

Lukas Fittl

Password Encryption Without Leaking Credentials — Logs and proxies can sometimes open up unexpected holes into your system’s security, but you can work with user passwords over the wire using pre-hashed versions.

Sehrope Sarkuni

The postmaster.pid File Explained — A short file with a long history.

Greg Sabino Mullane

🛠 Code and Tools

Procrastinate 1.1: Postgres-Based Task Queue for Python — A distributed task processing library for Python 3.8+ that uses Postgres to store task definitions, manage locks, and dispatch tasks. It can be used in both sync and async code, as well as in Django apps. GitHub repo.

Jablon and Lemoine

Steampipe: Query Your Cloud Services with SQL — Use SQL to query and even join across over 100 APIs from providers like Airtable, AWS, Heroku, Slack and Stripe, as well as social media sites like Reddit and Hacker News. It’s open source (AGPL) and there are now extensions to use it from within Postgres.


pg_back 2.2.0: A Simple, Thorough Backup Tool for Postgres — A tool for dumping your databases to files, including roles, server parameters, and more, in the format of your choice. This new version adds support for public and private keys for encryption with AGE, some bugfixes and an example Dockerfile plus other configs to ease deployment on Kubernetes.

Nicolas Thauvin