~postgresweekly | Issue 577 - Now you can enjoy Postgres Europe 2024 too (9)

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.

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

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.

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.

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.

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.

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.

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.

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.