~postgresweekly | Bookmarks (128)

Top 3 Features in Postgres 17 — As we continue our final approach towards Postgres 17, likely to land later this month, Neon reflects on improvements to MERGE, improved JSON functions, and performance improvements. EDB wrote up its own list of Postgres 17 enhancements too, with incremental backup support topping their list.

pg_easy_replicate Now Supports Schema Change Tracking During Logical Replication — pg_easy_replicate makes it easy to set up logical replication and perform minimal downtime switchovers between primary database servers. It now supports DDL change tracking too.

PgCat 1.2: Modern Postgres Pooler and Proxy — Built in Rust, PgCat offers support for sharding (including, experimentally, based upon SQL syntax), load balancing and failover support.

pg_hint_plan 1.7: Tweak Query Execution Plans with 'Hints' — Postgres’ query planner usually does a good job of figuring out how to best execute a query against your data, but when it needs a little extra push in the right direction, this lets you drop some hints. With v1.7, Postgres 17 support has been added.

Pigsty v3.0: An Extension-Rich Postgres Distribution — Pigsty is a Postgres distribution that promises ‘batteries included’. It offers an RDS-like platform but to deploy as you wish. It now lets you easily install 336 further extensions as well.

pg_stat_monitor 2.1: Query Performance Monitoring Tool — Along similar lines to pg_stat_statements but promises to be a ‘more advanced replacement.’ One particularly neat feature is the bucketing of statistics by time, enabling more finessed use of the data collected. GitHub repo.

Free Guide: Boost Postgres Performance by 3x — Drawing from proven practices honed tuning thousands of databases, we walk you through the process to achieve a 3x performance boost in your Postgres databases while reducing disk data load by 500x.

Six Degrees of Kevin Bacon - Postgres Style — Given two random actors, could you join those actors within six steps through other actors that have co-starred in movies together? That’s the game of Six Degrees of Kevin Bacon and Paul sets out to see if Postgres can tackle the task of finding such connections.

Why I Always Use Postgres Functions For Everything — There are numerous schools of thought on the roles of functions in production databases, so many of you may disagree with the author. Nonetheless, he explains why he appreciates the encapsulation and abstraction that user defined functions provide.

How to Create a Postgres FDW with WebAssembly — Foreign data wrappers (FDWs) provide a way to give Postgres powers to query external data sources as if they were local tables. Using the Rust-based Wrappers FDW framework, you can create WebAssembly powered FDWs which can be dynamically deployed at runtime.

Postgres Webhooks with pgstream — A look at a use case for the pgstream change data capture tool: calling webhooks based upon Postgres data and schema changes.

Understanding pgvector's HNSW Index Storage — pgvector is widely used for implementing vector similarity search features in Postgres, but how are its indexes stored under the hood?

Prisma 5.19.0: The JS ORM Now with 'Typed SQL' — Prisma is a popular declaratively-driven ORM in the Node.js / TypeScript world and its new version makes it possible to write raw SQL queries in a type-safe way.

pgMonitor: An Open Source Monitoring Extension — pgMonitor is the name of a Postgres metrics tracking tool Crunchy Data has maintained for years, but it’s now available as an open source extension too.

Using Postgres as a Search Engine — Folks have used Postgres within search systems for years, not least with its strong full text indexing features. You can, however, go further with vector based semantic search and fuzzy matching. Eric brings together some ideas, complete with queries, here.

How Postgres Stores Data on Disk (This One's a Page Turner) — A great overview of how Postgres stores data on disk, covering segments, pages, and more. More depth than you need to know, until one day, you suddenly do..

PostgreSQL Upgrades, Simplified — Streamline your PostgreSQL upgrade with zero downtime. Our expert DBAs ensure smooth transitions, addressing blockers and performance issues. Plan, test, set up, and upgrade with ease. Fill out the form to start your upgrade today!

Building a Custom HTTP Client in Amazon Aurora and RDS — Oracle Database users can use the UTL_HTTP package to communicate with HTTP servers from their procedures, but when they migrate to Postgres on Aurora or RDS, a new approach is required.

Pigsty v3.0 Beta 1 Released with a Focus on Extensions — Pigsty is an interesting Postgres distribution that promises ‘batteries included’. It’s basically an RDS-like platform but to deploy as you wish. It now lets you easily install 333 extensions as well as ‘pluggable kernels’ like Babelfish, IvorySQL, and PolarDB.

PostgREST 12.2 Released: Access Postgres RESTfully — Now including Prometheus compatibility and statement timeouts, PostgREST is a Haskell based system for serving up a fully RESTful API from any Postgres database. GitHub repo.

🦆🐘  Splicing Duck(DB) and Elephant DNA — DuckDB is a fast, in-process OLAP database system and a fantastic bit of engineering. It’s neat, then, to see an official pg_duckdb extension to bring DuckDB’s powers into the Postgres world, complete with backup from Microsoft, Hydra, and Neon.

Running Postgres Inside Node-RED via WASM and PGlite — Node-RED is a popular Node.js-based ‘low code’ programming environment where you build ‘flows’ in the browser to connect code together.

New Releases of Every Maintained Line of Postgres — Namely 16.4, 15.8, 14.13, 13.16, 12.20, and even a third beta of Postgres 17. These are mainly for rolling out bug fixes, but a pg_dump vulnerability has also been resolved. Postgres 17 final is (tentatively) expected to land in September.

Posting Your Patch on pgsql-hackers — Robert continues his efforts to encourage new Postgres contributors by giving some tips on how to best approach submitting patches, particularly if the pgsql-hackers list was all crickets when you’ve tried to do so before.