#​577 — November 13, 2024

Web Version
Together with  Redgate

Postgres Weekly

pgvector 0.8.0 Released — There’s a post about this release of the popular vector indexing and querying extension on the Postgres site, but I like this writeup as it digs into a change in index scans. With 0.8, we get improved performance, support for iterative index scans, and a way to cast arrays to sparsevec.

Gwen Shapira (Nile)

▶  The PostgreSQL Conference Europe 2024 Talks — Europe’s annual Postgres conference went down well in Athens last month and there are now a whopping sixty videos to enjoy. We haven’t had time to go through them yet, but will be featuring some in forthcoming issues.

PostgreSQL Conference

💡 If you want to start your watching somewhere, Claire Giordano's What's in a Postgres major release? and Lætitia Avrot's Untangling the Web of PostgreSQL Permissions were both highlights.

Learn PostgreSQL with Redgate — New to PostgreSQL? Check out Redgate’s PostgreSQL learning resources hub for easy-to-follow webinars, how-to articles, and a supportive community forum. Boost your skills and save time by learning tips, tricks and best practices from experts.

Redgate Software sponsor

8 Steps in Writing Analytical SQL Queries — If you’ve got a complex analytical query to write, start small and build from the ground up to avoid issues creeping in, says Christopher, who has an eight step rubric to doing just that.

Christopher Winslett

Understanding Volatility in PL/pgSQL Functions — Deepak shares a real-world example of the importance of understanding the different levels of function volatility you can specify for PL/pgSQL functions.

Deepak Mahto

QUICK BITS:

Text Identifiers in Postgres Database Design — A variety of things to consider if, rather than numbers or UUIDs, you’re working with textual identifiers for your records.

Radim Marek

From Backup to Integrity: Leveraging WAL-G — Making backups is one thing, ensuring their integrity in the face of hardware or network issues is another, but it’s possible to use checksums to detect integrity problems.

Data Egret

📄 Getting Comfortable with psql – If there’s one Postgres tool it pays to be fully up to speed with, it’s psql. Craig gives a few entry level/beginner tips. Craig Kerstiens

📄 Waiting for Postgres 18: The array_reverse() Function Hubert (depesz) Lubaczewski

📄 Scenarios That Trigger Autovacuum in Postgres Semab Tariq

📄 Transitioning from Oracle to Postgres: Indexes Umair Shahid

🛠 Code and Tools

PostgreSQL Workload Analyzer (PoWA) 5.0 Released — A performance tool for collecting and aggregating metrics from multiple Postgres instances which can then be viewed through real-time charts and used to help optimize your setup. v5 has a much improved Web UI and offers more data sources than before, including WAL stat views, an IO stats view, and info on background writers and replication slots.

PoWA Team

📰 Classifieds

🌐 PostGIS Day is November 21. Join us for an online event all about open-source geospatial tools and use cases. Registration and talk details.

You love Postgres but you’ve got more love to give? Try a taste of MongoDB for vector search. Nobody has to know.

Schemalint: A Linter for Postgres Schemas — A linter that lets you raise errors on a variety of common matters like name casing, enforcing RLS, or the presence of mandatory columns (so think more like ESLint than a basic formatter). You can also write your own custom rules.

Kristian Dupont

  • PGQueuer 0.15.0 – Python library that uses Postgres for efficient job queuing. You can now schedule recurring jobs with a cron-like syntax.

  • PGSync 3.3 – Postgres to Elasticsearch/OpenSearch sync.

  • DBMate 2.23 – Framework-agnostic, Go-powered database migration tool.

  • pg_flo 0.0.9 – Stream, transform, and route Postgres data in real-time.

  • DoltgreSQL 0.14 – Version-controlled Postgres-a-like.