#​576 — November 6, 2024

Web Version

Together with  Blacksmith

Postgres Weekly

Plugging the Postgres Upgrade Hole? — The author claims that community discussions often raise the point that Postgres is difficult to upgrade – but is that a fair point? Shaun looks at how Postgres approaches upgrades, limitations in the pg_upgrade approach, and considers how things could be made better.

Shaun Thomas (Tembo)

Migrating In-Place from Postgres to MySQL – (!) — Not the typical migration we’d be talking about here! But Yelp’s status as a heavy user of MySQL played a big role when needing to integrate a Postgres-powered site they acquired. They had to make some compromises due to Postgres-specific functionality, but with few Postgres skills in-house, they deemed it a necessary sacrifice.

Alex Toumazis (Yelp)

Run GitHub Actions Up to 2x Faster at Half the Cost — Blacksmith runs your GitHub Actions substantially faster by running them on modern gaming CPUs. Integrating Blacksmith is a one-line code change. 100+ companies like Ashby, Superblocks, and Slope use Blacksmith to help developers merge code faster.

Blacksmith sponsor

Amazon Aurora PostgreSQL Limitless Database Generally Available — A new way to horizontally scale/shard Amazon Aurora to extend a database beyond Aurora’s typical storage and write throughput limits while still looking and feeling like a single database. It’s now GA with Postgres 16.4 compatibility.

Channy Yun (AWS)

QUICK BITS:

  • postgres-contrib.org is a blog rounding up contributions made to the Postgres project on a mostly weekly basis.

  • 🗓️ The PostgreSQL Event Calendar is a site keeping track of Postgres-oriented events and also hosts an ICS / iCalendar file you can add to your own calendar app.

  • EDB's Rushabh Lathia is this week's PostgreSQL Person of the Week interviewee.

Vector Databases are the Wrong Abstraction — When it comes to semantic search, Timescale thinks that externally producing embedding vectors and then putting them into a database is the wrong approach, and that embeddings should be treated more like database indexes and handled by the database. Pgai Vectorizer is their attempt at making this a reality.

Arye and Sewrathan (Timescale)

Benchmarking Postgres vs MariaDB and MySQL — Using a custom-built benchmarking tool, Reserva, the author found Postgres leads the way, performance-wise. Usual disclaimers about the troubles of benchmarking and getting accurate results, etc. etc..

Cal Mitchell

📄 Poking Around Postgres Full-Text Search: A Primer – Focuses on the classic tsvector/tsquery approach and associated functions. Rémi Mercier

📄 Avoid Capital Letters in Postgres Names – It just saves a lot of headaches.. Wei Yen

📄 The Top 5 Key Differences Between SQL Server and Postgres Klaus Aschenbrenner

📄 Why You Do Not Want to Use the C/POSIX Locale Christophe Pettus

📄 Why pg_dump is Amazing Robert Haas

🛠 Code and Tools

pg_flo: Stream, Transform, and Route Postgres Data in Real-time — Offers a set of filters and transformations that can make moving data between, say, production and staging databases simple using one of two modes: copy-and-stream or stream-only. Uses NATS and Postgres’s replication.

Shayon Mukherjee

OneSparse: Accelerated Sparse Linear Algebra for Postgres — Like JSON/JSONB types did for unstructured data, OneSparse wants to make dense and sparse matrices and vectors fully native Postgres types along with optimized parallel matrix multiplication and other operations like reduction, selection, assignment, extraction, element-wise union, and intersection.

Michel Pelletier

pgCompare: A Java App to Check Post-Replication Data Consistency — If you’re moving data from one database to another, say, you can use this to perform a comparison to check the integrity of the migration.

Crunchy Data

ProxySQL Expands Database Support to PostgresProxySQL is an open source MySQL proxy that has, now, introduced Postgres support in anticipation of its v3.0 release. v3.0.0-alpha is now available for testing.

ProxySQL LLC

📰 Classifieds

🧐 Struggling with slow Postgres queries? Join our Nov. 13 webinar to see how pganalyze simplifies EXPLAIN plan comparisons to debug common issues.

pg-dump-parser: Parses Postgres Dump Files into Arrays of Schema Objects — Takes a Postgres database dump, splits it up, and turns the table and view structures into a format you can more easily work on from code (or just use as a reference or check into version control).

Gajus Kuizinas

rusty_psql: A psql Wrapper for Getting Credentials from Azure Key Vault
Claus Guttesen