Bookmarks (177)

Monitoring Distributed Postgres Activity with citus_stat_activity and citus_lock_waits — One for Citus users only: ”.. some new monitoring tools introduced in Citus 11 that’ll help you track and take control of your distributed queries”

Manage Long-Running Read Queries on Amazon Aurora — Goes over “the potential conflicts” and shares "best practices for managing long-running read queries on Aurora PostgreSQL.”

Dexter: The Automatic Indexer for Postgres — Collects your queries and generates indexes to suit them. Here’s an article introducing the idea.

Extending Postgres with C: A Simple Example — I’ve seen a few public discussions recently where someone has commented just how pleasant it is building extensions for Postgres. There are now numerous options for doing so (such as using Rust with pgx) but using good old fashioned C works just fine and isn’t as difficult as you might expect. (This tutorial is also helpful...

pg-boss 8.0: Postgres + Node.js Job Queueing System — A job queue for background processing and reliable asynchronous execution. It uses Postgres specific features (like SKIP LOCKED) to guarantee safety. v8 makes Node 14 the minimum supported version and enables reuse of existing DB connections on certain operations.

Debugging Postgres Autovacuum Problems: 13 Tips — Samay asks us to imagine seeing our database is larger than expected with high I/O load and sluggish performance. We spot a lot of bloat in our tables and run VACUUM. But then we might ask “why didn’t Postgres autovacuum clean up the bloat in the first place…?” — It’s a good question and this post covers...

📊 The Full State of Postgres 2022 Results — They’ve been teasing us with bits and pieces in recent weeks, but now we can see the full results (or download a dump of the results to analyze yourself). There’s not a lot that’s surprising, though. PostGIS came in as the most popular extension. Python as the most popular non-SQL language.

Improved Performance for ORDER BY / DISTINCT Aggregates — Technical item from the Postgres repo incoming! “Performance of ORDER BY / DISTINCT aggregates has just been terrible for too long in Postgres,” says David Rowley, but that will no longer be the case after implementation of an optimization that sounds simple in theory but was undoubtedly tricky to get right in practice.

Better SQL Functions in Postgres v14 — Postgres 14 introduced a new way to define SQL functions and it’s caught the eye of at least two developers this week as we have two good posts about it. In essence, “the body of SQL functions and procedures need no longer be a string constant” and this provides some advantages as we see in Laurenz’s demo.

BEGIN ATOMIC: A Better Way to Create Functions in Postgres 14 — Yes, it’s the same story as above, but focused less around explanation and more around a larger code example. It’s well worth skimming both if this area interests you at all.

Shipping Multi-Tenant SaaS using Row-Level Security — Explorations and thoughts on database-level (rather than app level) ‘secure by default’ security in single-schema multi-tenant databases. The extensive discussion about this topic on Hacker News proved to be quite valuable too.

Partitioning with Native Postgres and pg_partman — Sometimes premature optimization makes no sense, but if you know your database is likely to rapidly enter the world of terabytes (maybe you’re storing a lot of time series data, say) then being proactive about partitioning is a wise idea and this is a particularly gentle introduction.

Using BUFFERS for Query Optimization — You know EXPLAIN ANALYZE but what about BUFFERS? It provides info about how Postgres’s buffers are used in queries and can help you narrow in on I/O related issues in particular.

13 Postgres-Related Tools That Aren't psql — Timescale continues to analyze the results of its recent State of PostgreSQL 2022 survey, this time looking at tools. We cover Postgres tools every week ourselves, but this post has the numbers for what people are actually using.

AlloyDB Versus Postgres: A Performance Review — As is always the case, go into benchmarks with a cynical but open mind; benchmarking is hard! Nonetheless, it’s cool to see someone putting Google’s Aurora-a-like AlloyDB for PostgreSQL through its paces.

pg_jsonschema: JSON Schema Validation Extension — Postgres’s support for JSON / JSONB columns is now pretty much second to none, but what if you want to validate the structure of your JSON data? JSON Schema provides a way to define JSON-based formats and this extension lets you verify JSON documents using such schemas. (A fun aside is just how simple the code is since...

PostgreSQL 14 Internals: The Book — A book for those “who will not settle for a black-box approach when working with a database” from one of our favorite authors of technical deep dives into Postgres features. Part one is available now as a 160+ page PDF.

How Postgres's Views and Materialized Views Work — ..and, importantly to note, “how they influenced TimescaleDB continuous aggregates.” Whether you use Timescale or not, this is a useful introduction to views that gracefully moves into why Timescale had to create extra mechanisms suited for time series use cases.

When Does BRIN Indexing Win? — An easily digestible comparison of BRIN and BTree indexes and an explanation of when and why BRIN can make the most sense despite their relative simplicity (very large, insert-only tables, for instance).

Working with DISTINCT in Postgres 15 — Using DISTINCT with SELECT eliminates 'duplicate' rows from results by way of specified expressions. We’re still in the beta phase of Postgres 15, but this post shows off how the performance of SELECT DISTINCT has been improved through parallelization.

First Insights from the State of PostgreSQL 2022 Report — The State of PostgreSQL survey was recently closed. While we keep digging into the results, read the first insights in our new blog post, and share your email to get notified when the full report will be published.

\gexec in psql: A Postgres Power User Practice? — If you’ve used eval in languages like Python or JavaScript, \gexec provides a similar mechanism in psql and opens some interesting ways to dynamically generate and run queries en masse. (If you like history, here’s some background to the feature from its original announcement on the pgsql-hackers list.)