~postgresweekly | Bookmarks (115)

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

Just Because You’re Getting an Index Scan, Doesn't Mean You Can’t Do Better — If you see index scans when reviewing query plans, you might think you’re on the right path to a high performing query, but there’s more juice left for the squeezing, says Michael. Useful tips here.

ChartDB: Open Source Database Schema Diagram Editor — Create a new schema from scratch or quickly import your existing one. Supports Postgres, MySQL, SQL Server, SQLite, and more, and is built in TypeScript. You can deploy it for yourself or try an online demo here. AGPL licensed.

DB Fiddle: An Online SQL Database Playground — It’s been a couple of years since I linked to this useful tool that offers numerous database systems in an online sandbox of sorts (including Postgres versions 9.4 through to 17). While a local database is always better for big experiments, if you want to check syntax changes before versions or test queries against older Postgres...

Replacing Oracle Hints: Best Practices with pg_hint_plan — Oracle Database users can use ‘hints’ to guide the query optimizer’s decision making process, and you can reproduce the idea in Postgres with pg_hint_plan. Lukas explains when you might use hints and how Oracle hints can be mapped to Postgres.

commit_delay for Better Performance: A Postgres Benchmark — Laurenz shares benchmark results highlighting the performance wins gained by using the commit_delay and commit_siblings parameters to group WAL flushes and ultimately optimize transactional workloads.

🤖 Automating Data Summarization in Postgres with Claude — Timescale shows how to bring pgvector together with their pgai tooling to have Postgres reach out to Anthropic’s Claude AI for summarization tasks without any external scripting.

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.

A Major Aurora Postgres Upgrade with Zero Downtime — Inspired by an earlier blog post from the Lyft database team, two engineers set out to upgrade their small but heavily used Aurora Postgres instance without affecting their app – here’s the full story along with the problems encountered. (Note that the database does go down for a few seconds, but their app didn’t need...