~postgresweekly | Bookmarks (109)

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.

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.

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.

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

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.

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.

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

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.