~postgresweekly | Bookmarks (130)

Some of My Favorite Things – Postgres Queries — There’s not really any big Postgres news this week, so why not enjoy this festive gift of some (reasonably complex) SQL queries Shane uses to track database usage in various ways, to see unused indexes, to generate a vacuuming report, and more.

5. Hacking on PostgreSQL is Really Hard — Robert has consistently been in the top ten committers to Postgres for many years, but this year he reflected on just how intimidating the process of contributing to Postgres can be, especially for newcomers.

4. pgPedia: A Postgres Encyclopedia of Sorts — An interesting, cleanly formatted wiki-esque reference guide to Postgres’ features and settings that complements the official documentation well. Well worth poking around a bit.

3. Can Postgres Replace Redis as a Cache? — Redis is a popular, high performance in-memory data storage server commonly used for caching, but if we can just use Postgres for everything, could Postgres take on the job instead?

1. SQL Query Optimization: A Comprehensive Developer's Guide — A bumper packed post digging into the optimization of SELECT, INSERT, and DELETE queries, covering areas like using indexes, paginating results, avoiding joins, and how window functions can help (or not). Not Postgres-only but written with Postgres in mind.

pg_incremental: Incremental Data Processing in Postgres — Marco introduces a new Postgres extension offering automated, incremental batch processing for creating reliable processing pipelines. GitHub repo.

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.

▶  7+ Million Postgres Tables — Firmly in the space of “you might not wanna do this, but it’s certainly interesting”, an engineer shares an interesting approach to large scale analytics that involves creating a colossal amount of tables (which Postgres has no problem with).

🤯 Explaining the ABI Breakage in Postgres 17.1 — A few weeks ago, Postgres 17.1 was released and some changes caused problems for a few popular extensions. Postgres 17.2 was rapidly released to resolve the problem, but here we get a bit more of the story and technical detail behind what happened. Certainly stuff to be aware of if you’re an extension developer.

Smarter Postgres LLM with Retrieval Augmented Generation — A quick, easy to digest introduction to the concept of ‘RAG’ where the results from large language models are improved by providing added context sourced from extra data sources.

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.

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.

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!

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

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

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.

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.

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

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.

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.