~postgresweekly | Bookmarks (109)

A Practical Guide to Taming Postgres Isolation Anomalies — A practical, Ruby-based (though SQL heavy) walkthrough of Postgres’ concurrency control, detailing real-world issues like lost updates, write skew, and other isolation anomalies. Even if you rarely run into conflicts, you’ll come away with valuable insights on managing transactions.

Why Postgres Needs a Better API for Alternative Table Engines? — Postgres has mechanisms for allowing extensions to provide their own index types and table access methods, but it’s still not as easy to roll out powerful alternative table engines as it could be. Alexander of OrioleDB explains the limitations and some potential ways forward.

You Can Make Postgres Scale — Newsflash: Postgres scales! After last week’s experiments with a 1 trillion row table using Citus, we know Postgres scales, but once you go beyond a certain point, bringing multiple machines into play becomes a potentially complex necessity and the PgDog horizontal scaling/sharding approach offers just one solution.

Think About MERGE in Terms of a RIGHT JOIN — RIGHT JOIN isn’t encountered too often in SQL as you can just write a more universally understood LEFT JOIN alternative instead. However, right join semantics play an important role with the MERGE statement.

Sure, You Can Roll Your Own Auth - If You Hate Free Time — Don’t sacrifice your control. FusionAuth is downloadable, and can be developed and tested locally. A single-tenant architecture provides better scale and security than a purely multi-tenant SaaS model, even if you deploy via the cloud. Try it for free!

Troubleshooting Duplicate Primary Key Rows — If a table has unwanted ‘duplicate rows’ (which can happen due to a variety of quirks causing corrupt unique indexes) Greg has a multi-step technique for getting things back under control.

Can Artificial Intelligence Create Better Tables Than You? — As someone guilty of leaning on LLMs for a little SQL grunt work lately, this was an interesting read. As ever, you need to stay on your toes and check anything produced before rolling it into production!

▶  🎤 Why Python Developers Just Use Postgres — Dawn Wages of the Python Software Foundation joins Claire Giordano to talk about why Postgres is particularly popular in the Python and Django communities.

Xata Agent: An 'AI Expert' in Postgres — An open source agent that monitors your database, finds root causes of issues, and suggests fixes and improvements. It’s early days in this space but it’s interesting to see different takes on the idea.

pgFormatter 5.6: A Tool to Format SQL Code — You can test it out online, or you can grab the source from GitHub (it’s written in Perl) and run it for yourself. What’s new in v5.6.

pg_ivm 1.10: Incremental View Maintenance (IVM) Extension — A more efficient way to update materialized views incrementally, applying only changes instead of fully recomputing the view like REFRESH MATERIALIZED VIEW does.

pgzx 0.3: Create Postgres Extensions using Zig — If you’re familiar with pgrx as a way to build Postgres extensions with Rust, this is a similar idea in the world of Zig, another modern systems language that’s a little more C-like than Rust.

1 Trillion Rows in Citus? — “Postgres scales,” but just how far does that go? Hans-Jürgen decided to find out, running a little (or is it big?) experiment to see if a 1 trillion row table is even achievable.

❌ Ways to Cancel Queries in Postgres — If you’ve got a query or two hogging Postgres’s resources, terminating them is an option, and there are several ways to go about it.

▶ Don’t Let Postgres Maintenance Slip Through the Cracks — Secure your spot to learn the proactive maintenance practices that uncover hidden database performance risks, like slow queries, checkpoint behavior, & connection issues, before they escalate.

Postgres Gets a Self-Join Elimination Optimization — Several years ago, someone suggested a join optimization where redundant inner self-joins (often added by ORMs) can be removed under certain conditions. This so-called Self-Join Elimination (SJE) optimization has now been committed to Postgres and is explained in this GitHub commit.

Introducing pgNow: A Point-in-Time Diagnostic Tool — A free (but not open – it’s in preview behind an email wall) cross-platform desktop tool created by Redgate to highlight and analyze live data about queries, indexes, configuration settings, and other important elements of a Postgres cluster’s operation. Even if you don’t grab it, Ryan gives a thorough overview of what it offers here.

New Random Functions in Postgres 17 — Postgres 17 introduced some new random number generation functions so getting an integer between 1 and 10 is now as easy as random(1,10).

FerretDB 2.0: An Open Source MongoDB Alternative — MongoDB!? What if I told you this is Postgres-powered MongoDB? FerretDB is an implementation of MongoDB built on top of Postgres and Microsoft’s DocumentDB extension and, unlike MongoDB, it’s Apache 2.0 licensed. GitHub repo.

pytest-postgresql 7.0: A Pytest Plugin for Testing Postgres-Using Python Apps — A plugin for the Pytest Python testing framework for managing fixtures when testing against a Postgres database.

A Roundup of Postgres Query Plan Visualization Tools — The EXPLAIN command, along with its many options, gives you a deep view into how Postgres plans to execute a query and is a great way to figure out why certain queries are slow. The raw output can be tricky to read, though, but there are several tools that can help break things down.

The Schedule is Out for POSETTE: An Event for Postgres! — POSETTE 2025, a free & virtual developer event, is happening June 10-12. The speakers and schedule were just announced! Join experts via 4 livestreams to learn about the world's most advanced open source relational database. Get the details.

PGConf.dev 2025: May 13-16 in Montréal, Canada — The successor event to PGCon, the PostgreSQL Development Conference is one of the community’s biggest annual get-togethers with a particular focus on bringing together Postgres’ contributors. Registration is open, but the big news this week is the schedule is now live and it’s a packed one.

Validating Data Types from Semi-Structured Data Loads — Postgres 16 and up offer the pg_input_is_valid function, a quick way to determine if a supplied value can be parsed into a specified type. Elizabeth shows how this can be used validation checks in a few situations.