~postgresweekly | Bookmarks (119)

Don't Do This (in Postgres) — An interesting page on the official Postgres wiki that rounds up a variety of advice around ‘common mistakes’ in using Postgres and what not to do, such as “Don’t use char(n)” and “Don’t use serial“. Some of these will be contentious, but are well backed up with reasons. The page is updated, but it’d be nice to see...

Ruby Active Record Adapter for AWS Aurora DSQL — The “very beginnings” of an Active Record (Ruby on Rails) connection adapter for Amazon’s new Aurora DSQL database (essentially a Postgres-compatible serverless, distributed database service).

🐕 pgDog: A New Rust-Powered Postgres Query Router and Proxy — The main developer of the PgCat Postgres pooler has shifted to working on this dog-themed ‘spiritual successor’ that offers similar pooling, load balancing, and proxying, but with a plugin system and an AGPL license (PgCat is MIT).

pg_mooncake: Fast Columnstore Tables in Postgres for Analytics — A Postgres extension that adds columnar storage and vectorized execution (using DuckDB) for fast analytics within Postgres. Columnstore tables are stored as Iceberg or Delta Lake tables on the local file system or cloud storage. v0.1 just landed.

Idle Transactions Cause Table Bloat? Wait, What? — “Yup, you read it right. Idle transactions can cause massive table bloat that the vacuum process may not be able to address.”

The Anatomy of Table-Level Locks — A look at the meaning and motivation behind locking in database systems, before more closely looking at the various ways tables can be locked in Postgres and how conflicts between these locks are resolved.

Find the Best Postgres Monitoring & Tuning Vendor for 2025 — Download our free Vendor Evaluation Template to swiftly compare features, score demos, and confidently pick the best Postgres performance & monitoring solution to meet your 2025 budget needs.

The Power of Open Source in Postgres — Laurenz demonstrates how Postgres’ open source nature is invaluable when it comes to answering questions about how it operates, in case by tracking down the reason for the default setting for autovacuum_vacuum_scale_factor. Knowing how to do what he does in this post is a valuable skill.

Postgres 18 to Get Backend-Level Statistics — Barring any unforeseen problems, Postgres 18 (likely due late in 2025) will be gaining some improved backend statistics, including a new pg_stat_get_backend_io function to see the I/O activity in each server backend.

PIG: A New Package Manager for Postgres — Postgres Install Genius is a new package manager built to make it easier to get hundreds of Postgres extensions up and running on a mainstream Linux-based Postgres install. It’s from the Pigsty folks, but doesn’t require using their specialized Postgres distribution. Note that this is just one of a variety of similar efforts and is not...

How to Fix a Common Cause of Slow Queries in Postgres — A database engineer at the Render cloud platform shows off a commonly encountered, but trivially fixed, performance issue caused by missing indexes on foreign keys.

POSETTE 2025: Call for Proposals (CFP) is Open! — POSETTE: An Event for Postgres, a free & virtual developer event organized by the Postgres team at Microsoft, is happening June 10-12. The CFP is open until February 9. New and experienced speakers are encouraged to submit! Get the details.

PostgreSQL Anonymizer 2.0: Data Masking for Postgres — Rather than simply anonymizing dumps, this extension takes anonymization directly to your database by having you declare masking rules directly against your schema. Version 2.0 is a complete rewrite using Rust and PGRX and features improved fake data generation, role-based transparent dynamic masking, anonymized exports with pg_dump, and more.

SQLite Foreign Data Wrapper for Postgres v2.5 — Requires a Linux or POSIX-compliant system, but now supports Postgres 17 as well as some extra data types like uuid, bit and varbit.

Sqitch 1.5: Database Change Management Tool — A database and framework independent (Postgres 8.4+ is supported) system for managing database and schema changes via SQL scripts. Here’s how it works with Postgres.

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.

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.

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.

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.

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