Bookmarks (114)

Dealing With "found xmin ... from before relfrozenxid" — A look into a commonly reported error with easily misunderstood wording. It occurs during (and breaks) the vacuuming process when an unfrozen tuple’s xmin predates the table’s relfrozenxid, signaling data corruption. Luckily, there are solutions.

Find Your Flow with Heroku Postgres — The world’s most advanced open source database is easier to use than ever. With Heroku’s PostgreSQL database service, you can get the most out of your data and leave the admin to us. So go ahead and focus on your data-driven apps – we got you.

How About Trailing Commas in SQL? — Could this be the simplest yet most requested SQL feature? It's implemented in some dialects, but could it be implemented in Postgres's dialect? And should it? It’s far trickier than it sounds.

Scaling Postgres without Boiling the Ocean — Postgres scales pretty far out of the box with the default settings, but once a server starts getting some serious load ‘weird issues’ can start to rear their head. Shayon addresses some common issues of this type and the solutions for them.

Pig v0.1.4: Now with 400 Postgres Extensions — Pig (short for Postgres Install Genius) is one of the newer attempts to manage Postgres extensions in a package-based fashion. The latest version adds support for Microsoft’s DocumentDB, pg_tracing, pg_cooldown, VectorChord-bm25, and more.

pgroll 0.9.0 Released with New Schema Migration Features — pgroll is a tool for performing reversible schema migrations on Postgres without taking the database down. v0.9 supports adding table-level constraints into the table creation process, as well as generated columns.

Locksmith: Detect SQL Migration Issues Quickly — A new Rust-powered tool to detect table locks, table rewrites, and modified tables, columns and indexes during a schema migration.

Citus 13: Now with Postgres 17 Support — Citus is a long standing open source extension that makes it easier to horizontally scale Postgres. Bought by Microsoft some years ago, it continues to get releases, including this new one with Postgres 17 support, including for distributed queries using JSON_TABLE() and improved support for distributed partitioned tables.

ChartDB: Open Source Database Schema Diagram Editor — Create a new schema from scratch or quickly import your existing one. Supports Postgres, MySQL, SQL Server, SQLite, and more, and is built in TypeScript. You can deploy it for yourself or try an online demo here. AGPL licensed.

DB Fiddle: An Online SQL Database Playground — It’s been a couple of years since I linked to this useful tool that offers numerous database systems in an online sandbox of sorts (including Postgres versions 9.4 through to 17). While a local database is always better for big experiments, if you want to check syntax changes before versions or test queries against older Postgres...

Replacing Oracle Hints: Best Practices with pg_hint_plan — Oracle Database users can use ‘hints’ to guide the query optimizer’s decision making process, and you can reproduce the idea in Postgres with pg_hint_plan. Lukas explains when you might use hints and how Oracle hints can be mapped to Postgres.

commit_delay for Better Performance: A Postgres Benchmark — Laurenz shares benchmark results highlighting the performance wins gained by using the commit_delay and commit_siblings parameters to group WAL flushes and ultimately optimize transactional workloads.

🤖 Automating Data Summarization in Postgres with Claude — Timescale shows how to bring pgvector together with their pgai tooling to have Postgres reach out to Anthropic’s Claude AI for summarization tasks without any external scripting.

DocumentDB: Microsoft Brings More NoSQL to Postgres — Not to be confused with the proprietary Amazon DocumentDB, this DocumentDB is an MIT-licensed document-based NoSQL engine built on top of Postgres that Microsoft use internally for Azure Cosmos DB for MongoDB.

Just Because You’re Getting an Index Scan, Doesn't Mean You Can’t Do Better — If you see index scans when reviewing query plans, you might think you’re on the right path to a high performing query, but there’s more juice left for the squeezing, says Michael. Useful tips here.

Mathesar: A Spreadsheet Interface to Postgres Databases — An open source (GPLv3) tool providing a table-style interface to a Postgres database of your choice with the aim being to enable developers to open up databases more directly to end users. We first linked to it two years ago but v0.2 has just landed. GitHub repo.

Logon Triggers in Postgres — In Postgres 17 you can create a trigger that occurs upon login which you could use for auditing or even securing databases in creative ways.

pg_squeeze 1.8: Extension to Automate Table Bloat Cleanup — While not a replacement for vacuuming, this extension goes a step further to gain even more space improvements in-situ and now supports Postgres 17.

VACUUM (INDEX_CLEANUP OFF) Considered Harmful — “Unless you are sure you know what you are doing (that is, you are in a wraparound-point emergency), please pretend this option doesn’t exist. Really.”

pgwatch 3.0: A Flexible Postgres Monitoring Solution — v3.0 adds support for Postgres 17 (including new metrics), Windows support, improved Docker support, updated docs, support for parallel sinks, and more. GitHub repo.

A Major Aurora Postgres Upgrade with Zero Downtime — Inspired by an earlier blog post from the Lyft database team, two engineers set out to upgrade their small but heavily used Aurora Postgres instance without affecting their app – here’s the full story along with the problems encountered. (Note that the database does go down for a few seconds, but their app didn’t need...

A New Postgres Block Storage Layout for Full Text Search — pg_search is a Postgres extension providing BM25-powered full text search but has required the use of external, non-Postgres files till now. No more. It’s now fully using Postgres block storage – here’s the why and how.

PostgreSQL Performance Monitoring Cheatsheet — Start tracking vital resource and activity metrics from your PostgreSQL databases with our handy cheatsheet. You’ll have useful psql commands at your fingertips, and get a quick-start guide on using Datadog to monitor PostgreSQL performance.

DBOS Transact v2: Lightweight Durable Execution in TypeScript — An opensource library for lightweight durable execution built on Postgres. Durable execution means persisting the execution state of a program while it runs, so if it's interrupted or crashes, it resumes from where it left off, ideal for long-running or business-critical workflows. Documentation.

Who Contributed to Postgres Development in 2024? — Each year, Robert compiles a list of those who contributed most to Postgres’ development over the past year. 229 folks were the principal authors of at least one commit, and 66% of new lines of code came from the top 18 contributors.

Anatomy of Table-Level Locks: Reducing Locking Impact — It’s easy to lock an entire table when performing an operation, but the performance and uptime impacts should quickly drive you to using less restrictive approaches. Gulcin explains some options.

VeilStream – Simple Data Access Management for Postgres — VeilStream is a secure, self-hosted, Postgres filter that sits in front of your production database to provide a powerful and simple GUI and API for setting up data redaction and anonymization rules. Try it for free today.

GROUP BY and Fixing Optimizer Estimates — Did you know the more columns a GROUP BY contains, the more likely the optimizer overestimates the row count? Postgres maintains separate statistics for each column and creates group estimates by multiplying them together. Creating extended statistics can help get things in order.

Implementing Thread-Safe Scanners and Parsers in Postgres — Unless you’re a Postgres implementer (like top 5 contributor Peter Eisentraut here) this probably won’t be directly useful to you, but it’s fascinating to see what’s involved in potentially moving Postgres’ scanners and parsers to be thread safe. Spoiler: It’s a lot.

Don't Forget About the Parallel Leader Participation Setting — Kaarel has his own TLDR: “You should actually look into disabling parallel_leader_participation if you’re working with larger out-of-cache datasets, have plenty of cores and especially when the tables are partitioned.”

HeidiSQL 12.10: A Native Windows App for Database Work — A handy, open source database management tool. Supports Postgres, MySQL, SQL Server, and SQLite. Written in Delphi and is open source.

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.

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.

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.