#​558 — June 12, 2024

Web Version
Together with  pgAnalyze

Postgres Weekly

How to Get the Most Out of Postgres' Memory Settings — Samay Sharma said it better than I could: “My colleague wrote one of the most comprehensive memory optimization guides for Postgres that I’ve seen! It talks about the different components, what they are for, how to optimize them and how to put them together.”

Shaun Thomas (Tembo)

Register Now: Optimizing Slow Queries with EXPLAIN to Fix Bad Query Plans — The focus of this webinar is to teach the essential skills of optimizing slow queries with EXPLAIN ANALYZE, teach you about anti-patterns that you will encounter when debugging queries, and leave you with practical advice on how to address common problems.

pganalyze sponsor

Boosting Postgres's Vector Performance with pgvectorscale — How? It’s thanks to a new open source Postgres extension out of Timescale called pgvectorscale which enhances pgvector with StreamingDiskANN indexes and statistical binary quantization. The results are quite significant, even compared to dedicated vector storage systems.

Sewrathan, Pruitt, and Arye (Timescale)

🤖 It's quite a week for Timescale, as they've also announced pgai, a more generic extension for bringing AI-powered workflows into Postgres.

QUICK BITS:

How Much Speed Are You Leaving at the Table if You Use the Default Locale? — A benchmark of several setups using different collation providers and collations. It’s surprising how big a difference it can make.

Hubert depesz Lubaczewski

RAG on Structured Data with Postgres — Pamela shows off a Python, React and Postgres-based approach for using data retrieved from Postgres tables to create hybrid search systems.

Pamela Fox

Benchmarking Amazon RDS for PostgreSQL Deployment Options — A performance comparison between RDS for PostgreSQL Single-AZ DB instance, Multi-AZ DB instance, and Amazon RDS Multi-AZ DB Cluster deployments.

Andrea Caldarone (AWS)

🔈 Sponsoring the Community – A podcast interview with Microsoft’s Claire Giordano focusing on ways people can contribute to the Postgres community. Postgres FM

📄 The Perf Impact of Using ORDER BY with LIMIT Semab Tariq

📄 Tuning Postgres to Reduce SSD Wearout Yellow Rubber Duck

📰 Classifieds

🆙 Handle all your data via seamless admin panels for Postgres databases and more. Try the no-coding back office solution from Rocketadmin.

🤖 Big pgvector fan? The AI Engineer World's Fair takes place in San Francisco this June 25-27 and lots of ML, AI, and vector storage folks will be there. You can get 30% off tickets here or register for the free remote ticket/livestream at the bottom of the homepage.

🙋 More on the AI Engineer World's Fair, above, the editor of Postgres Weekly, Peter Cooper, will be there, so if you attend, go and say hi!

🛠 Code and Tools

PG Ferret: An All-in-One Tracing Toolkit for Postgres — A new project that shows a lot of promise. Powered by eBPF (so Linux only), it lets you observe traces of queries running inside Postgres, correlate Postgres activity to that in your app, and certainly ups Postgres’s tracing game.

Chris Bellew

pgtt 4.0: Manage and Use Oracle-Style Global Temporary Tables — For those situations where you want to reproduce Oracle’s behavior. This post explains why you might want that and some of the alternative approaches, but also why pgtt is the best option.

Gilles Darold

  • PostgREST 12.2 – A REST API for any Postgres database.

  • DBMate 2.17 – Lightweight, framework-agnostic database migration tool.

  • wal2mongo 1.0.7 – Replicate database changes into MongoDB.

  • pgxmock 4.1 – Mock driver to test database interactions in Go.