~postgresweekly | Issue 539 - Let's talk pgvector (11)

One PID to Lock Them All: Finding the Source of the Lock — One process can lock your database, blocking other processes and queries from doing their jobs. How can you find which process? Jesse goes down the rabbit hole and shares some useful queries along the way.

▶  Jonathan Katz on pgvector — Postgres core team member Jonathan Katz went on the Postgres.fm podcast to talk about the increasingly popular pgvector extension, its use cases, indexing approaches, and how TOAST gets involved, along with host Michael Christofides.

eBook: Tuning Autovacuum for Best Postgres Performance — Learn about tuning settings for scheduling and performance, reducing autovacuum overhead during business hours, the xmin horizon and how it blocks vacuums, why to avoid anti-wraparound vacuums and how to reduce bloat by tuning dead tuple thresholds.

POSETTE: Citus Con Renames for 2024 — Microsoft has run the virtual Citus Con event for two years now, but in 2024 it becomes POSETTE (standing for the less-than-euphonic “Postgres Open Source Ecosystem Talks Training & Education”). It's on June 11-12 and the CFP is open if you'd like to speak.

Apache AGE 1.5rc: A Graph Extension for Postgres — AGE provides graph database functionality (by way of openCypher) to Postgres and was inspired by AgensGraph. v1.5 is now in the release candidate stage and adds Postgres 16 support while otherwise making minor enhancements. Main repo.

A 'Fairly Full Featured' Ansible Role for Postgres — An Ansible 2.4+ role that installs and configures Postgres 12 upwards along with extensions, and can set up databases and users too. Postgres 16 support landed this week.

pgmoneta: A Backup / Restore Solution for Postgres — Formerly a Red Hat project, but now independent, pgmoneta is a complete database backup and restore toolkit, with support for encryption, compression, retention policies, Prometheus, and more. GitHub repo.

supa_audit: Generic Table Auditing for Postgres — A generic solution for tracking changes to tables’ data over time by storing history in a separate audit table.

oracle_fdw: A Foreign Data Wrapper for Oracle Database — Provides efficient access to Oracle databases, including pushdown of WHERE conditions and required columns as well as comprehensive EXPLAIN support.

Reshape 0.7: A Zero-Downtime Schema Migration Tool — During a migration, Reshape ensures both the old and new schema are available at the same time, allowing you to gradually roll out your app.