~postgresweekly | Bookmarks (119)

Running Durable Workflows in Postgres using DBOS — A look at DBOS, a ‘durable workflow’ engine built by a team including none other than one of the founders of the Postgres project.

Electric: A Postgres Sync Engine, Now in Beta — It’s always nice to see a new project we casually mentioned a year ago come to fruition, and this real-time engine for syncing data between Postgres and numerous other services (over HTTP) has come a long way since then. GitHub repo.

pg_worker_pool: Extension to Create a Pool of Background Workers — A simple extension that leans on Postgres’s background worker process mechanism to make it easy to distribute queries across a number of workers.

pgBadger 13.0: A Postgres Log Analysis Tool — A performance-focused log analyzer that produces detailed usage reports. Here’s an example report to get a feel for it. GitHub repo.

What I Wish Someone Told Me About Postgres — A diverse grab-bag of practical tips covering areas from normalization and the use of different types to SQL ‘eccentricities’, locks, and the use of JSONB.

Transactional Schema Migration Across Tenant Databases with pg_karnak — If you run a setup where each tenant/user of your system has its own database, maintaining the schemas across them can rapidly become tricky. pg_karnak is Nile’s distributed DDL layer for coordinating schemas across tenants. It hasn’t been open sourced yet, but this post digs deep into how it works.

The substring Function, Regex Style — While you can use Postgres’ substring function to extract parts of strings using numeric indices, did you know it supports using regular expressions too?

Optimizing Postgres Performance and Compression: pglz vs. LZ4 — Did you know you can change the algorithm used to compress your TOAST data? It might be worth it if LZ4 is as fast as we see here.

Supabase Cron: Job Scheduling in Postgres — A module that uses the pg_cron extension to manage recurring jobs on the Supabase platform (both via the dashboard or SQL).

Amazon Aurora DSQL: A New Postgres-Compatible Database — In ‘preview’ for now, DSQL’s promise is ‘virtually unlimited scalability’ with high availability and strong consistency, while being Postgres compatible (kinda..? No views, triggers, JSON, or foreign key constraints..). AWS Hero AJ Stuyvenberg calls it “the single most exciting launch I’ve seen from AWS in a while”, so watch this space. (Official press release.)

Full-Stack Observability from Honeybadger — Honeybadger's new logging platform is a bit like Splunk, DataDog, or CloudWatch Logs, but with just the good parts and a reasonable price tag. Plus, it's easy to integrate with Crunchy Bridge, Journald, AWS, and more!

💎 pg_query 6.0: Ruby Library to Parse, Deparse and Normalize SQL Queries with Postgres' Own Parser — Uses the actual Postgres server source to parse SQL queries and return their internal Postgres parse tree. v6.0 upgrades to using the Postgres 17 parser and the new features it supports (e.g. new JSON functions).

pgspot: Spot Vulnerabilities in Postgres Extension Scripts — A security analysis tool for SQL scripts that checks for vulnerabilities and best practices. Created by a Timescale engineer to prevent the reoccurance of vulnerabilities found in TimescaleDB, it’s already resulted in finding an extension-related vulnerability which Postgres core has fixed.

Could GROUP BY Clause Reordering Improve Performance? — Could reordering the columns in a GROUP BY significantly enhance query performance? Small changes in query structure can indeed result in serious gains.

Benchmarking Batch Ingest with INSERT vs COPY — Two of the simplest approaches to loading data into Postgres en masse are by using INSERT and COPY. James explains each of their benefits, runs a benchmark of numerous configurations of both, and gives guidance on which approach to use in certain situations.

Why Postgres Major Version Upgrades Are Hard — Update the binaries and restart? No, no, no. Tweaks and updates to the underlying data format required a more structured approach – Peter explains why.

▶  The PGConf.EU 2024 Lightning Talks — A handy roundup of twelve brief talks given at the recent PGConf EU event, along with links to slides and recordings. Topics covered include BM25, an update on the Postgres Performance Farm, pg_duckdb, and WAL-G.

DELETEs Are Difficult — Conceptually, deleting data from a table is easy to understand. In practice, it involves a lot of work under the hood and performance issues can quickly rear their head.

Pigsty v3.1: A 'Batteries Included' Postgres Distribution — Pigsty is a packaging up of Postgres along with a variety of extensions and tools to provide a sort of RDS-like experience on your own machines. v3.1 introduces Postgres 17 as the default version and includes a way to quickly spin up a self-hosted Supabase instance.

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!

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..)

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...

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..

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...