~postgresweekly | Bookmarks (106)

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.

sqldef 1.0: Idempotent Schema Management for Databases — A Go-powered CLI tool and WASM library for diffing SQL schemas (demo here). Supports MySQL, Postgres, SQLite and SQL Server. Built by one of Ruby’s core team members, too.

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

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.

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.

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.

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.

While Postgres Redlined, Robinhood Sharded to Scale — A short write up, from an engineer who used to work at Robinhood, about Postgres scaling problems the company encountered and how sharding was their savior.

(Ab)using pgRouting to Use Postgres as a Graph Database — pgRouting is an extension for providing geospatial routing functionality, commonly along with PostGIS. But, surprisingly, its graph and path searching mechanisms can be used to navigate any data structured in a graph-like form.

Postgres 17.4, 16.8, 15.12, 14.17, and 13.20 Released — This makes two bumps to all maintained release lines in as many weeks (this is an 'out of cycle' release – explained here). As before, the focus is bug fixes. Postgres 17 gets fixes for a memory leak in pg_createsubscriber and a tweak to quoting function behavior in libpq.

Don’t Let Postgres Maintenance Slip Through the Cracks & Let Performance Fall Off a Cliff — Secure your spot to learn the proactive maintenance & monitoring practices to uncover hidden performance risks in your database, such as slow queries, checkpoint behavior, and connection issues, before they escalate, & ensure your database reliability.

EdgeDB is Now Gel (and Postgres is the Future) — Gel (formerly EdgeDB) is an ‘integrated data platform on top of Postgres’ that provides its own query language in an attempt to work around SQL’s perceived flaws (better explanation here).

pg_activity 3.6: top-Like Activity Monitoring Tool — Much as you might use top or htop to monitor processes and CPU usage, pg_activity gives you a similar look behind the scenes of Postgres with views over running queries, up to date performance stats, and more.

BemiDB: Zero-ETL Data Warehouse for Postgres — Open source (GitHub repo) but with a commercial angle for more enterprise-y features, BemiDB, written in Go, acts as a Postgres read replica (so all your data is synced in automatically) optimized for analytics workloads.

Postgres in the Time of Monster Hardware — You might think your M4 Max is pretty fast, but imagine having an AMD EPYC with 192 cores per socket and 10 terabytes of RAM that benchmarks at 160x faster than a Xeon-powered server from 15 years ago! Modern CPU power (not to mention faster storage) invites us to ask questions about how we scale database...

Expanding pgai Vectorizer — pgai is Timescale’s suite of tools for making it easier to use AI capabilities from Postgres, including automatically creating and syncing vector embeddings for data. Now they’ve upped the ante with SQLAlchemy support and support for more embedding models via LiteLLM (more detail here).

Incremental Archival from Postgres to Parquet — Crunchy Data’s pg_parquet and pg_incremental extensions can be used to set up a reliable pipeline to export ranges of rows to the columnar Parquet format on S3.

Representing Graphs in Postgres — Postgres isn’t a graph database, but you can emulate the concepts involved. Alternatively, you could use an extension like Apache AGE if you need Cypher-like graph querying support.

A Look at Virtual Generated Columns in Postgres 18 — Postgres 18 is gaining the ability to have ‘virtual generated columns’ where the expression for a virtual column is computed at read-time so they’re not stored on disk as all generated columns currently are.

A PostgreSQL Compatibility Index to Compare Implementations — Postgres has found itself in an envious, though complex, situation of becoming a lingua franca among databases where databases that contain no Postgres code at all attempt to be “compatible” with it in various ways. But how compatible? The Postgres Compatibility Index is an attempt to test and monitor numerous aspects.

PostgreSQL 17.3, 16.7, 15.11, 14.16, and 13.19 Released — A raft of updates to all maintained lines of Postgres to tackle one security vulnerability and lots of smaller bugs. As minor updates, the upgrade process is simple and doesn’t require any dumping and reloading.

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.