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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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

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.

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.

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

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.

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

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.

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?

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.

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.

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

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.

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

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.

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.

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

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

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

Running Postgres on Spot VMs? 🤯 — Building on a talk from PGConf Europe, Kaarel looks at how viable it is to run Postgres on instances that could, well, disappear at almost any time. pg-spot-operator is a utility he’s built to make it work.

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

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!

Easy Totals and Subtotals in Postgres with Rollup and Cube — Handy additions for performing more analytical queries. ROLLUP can create hierarchical subtotals along with a grand total, while CUBE generates subtotals for every possible combination of grouping columns.

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