#​520 — September 6, 2023

Web Version

👋 We're back from vacation. If you didn't get an issue of Postgres Weekly recently, it's because I was away, but we should be good all the way through to Christmas now 😁 There's a lot to cover from the past few weeks and it'll take a couple of issues to catch up, so let's go!
__
Peter Cooper and the Cooperpress team

Together with  pgAnalyze

Postgres Weekly

pgvector 0.5 Released: The Highlights — pgvector is an extension that provides vector similarity search capabilities and is being increasingly used in LLM/ML use cases. v0.5 introduces a new index type (shown off in detail by Christopher Winslett in HNSW Indexes with Postgres and pgvector), parallelization of ivfflat index builds, and improved performance of its distance functions. Changelog and GitHub repo.

Jonathan Katz

Postgres 16 Release Candidate 1 Released — There’s not much to add beyond the recent betas, but the final release is due next week, and you can peruse the draft release notes for the complete roundup till then.

PostgreSQL Global Development Group

Finding the Root Cause of Slow Postgres Queries Using EXPLAIN — In this in-depth guide you will learn our approach to using Postgres EXPLAIN to find and improve problematic query plans, how to analyze and improve these query plans, and how to use auto_explain to automatically start collecting EXPLAIN plans.

pganalyze sponsor

Optimizing Autovacuum for High-Churn Tables — Due to Postgres’s data storage approach, an 'autovacuuming' process is necessary to clean up and optimize table storage. The more UPDATEs and DELETEs a table is subjected to, the more important this vacuuming process becomes, so tuning how it works can yield significant results.

Adam Hendel

What’s New with Postgres at Microsoft in 2023 — Whether via Citus, Azure, or elsewhere, Microsoft has a significant presence in the Postgres world and maintains a variety of well known tools, extensions, and services. Claire does a great job of walking through the various things Microsoft has done in the Postgres space in the year so far.

Claire Giordano (Microsoft)

IN BRIEF:

▶  UPDATE Considered Harmful — “It is a clickbait title,” says Jeremy, but it helps make the point that UPDATE isn’t all it seems and how assuming it does merely ‘update’ data can lead to problems. (In reality, an UPDATE inserts new data and deletes the old data - or marks it as stale - rather than ‘updating’ anything at a physical level.) Bitemporality then also comes into play.

Jeremy Taylor

[Blog] Securing Your PostgreSQL Database

Teleport | goteleport․com sponsor

Making Postgres Backups 100x Faster via EBS Snapshots and pgBackRest — pgBackrest is a handy tool for handling backups, but it can get slow for large databases. Here’s how Timescale mitigated the problem by incorporating EBS snapshots.

Godeke and Blackwood-Sewell (Timescale)

What’s New in Oracle to Postgres Migrations with Google DMS
Google Cloud Blog

Code and Tools

Introducing pg_later: Asynchronous Queries for Postgres — Execute SQL now and get the results later. Built atop the pgmq Postgres message queue extension. GitHub repo.

Adam Hendel

pg_ivm 1.6: Incremental View Maintenance Extension — IVM can update materialized views more efficiently than recomputation when only small parts of the view are changed. v1.6 adds support for the EXISTS clause.

SRA OSS LLC