~postgresweekly | Bookmarks (121)

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

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.

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

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.

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.

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

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

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.

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

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

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.

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

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

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.

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.