#​403 — April 28, 2021

Web Version

Postgres Weekly

Understanding Deadlocks — One of the more straightforward demonstrations of what a deadlock actually is. Essentially it’s when two transactions are waiting for each other to give up their locks and there’s a catch-22 of sorts.

Hans-Jürgen Schönig

pgvector: A Vector Similarity Search Extension for Postgres — Supports L2 distance, inner product, and cosine distance.

Andrew Kane

Cockroach University: Learn to Build Cloud-Native Apps — Cockroach University is a free online learning platform in which you’ll gain distributed database skills & modern application development experience (useful skills for the resume 😉).

Cockroach Labs sponsor

When to Use NUMERIC/DECIMAL vs. Other Numeric Types — For Postgres users looking for any incremental performance improvement possible, managing the layout and use of every byte of a row can be worthwhile and numeric types provide a great example. (Last week’s Tip of the Week touched on this also.)

David Youatt

Can You Get a Value From a Dynamic Column in pl/PgSQL Triggers? — There’s a workaround (with a cost), demonstrates Hubert.

Hubert depesz Lubaczewski

Getting Started with Set Operations — For example, UNION for bringing result sets together and INTERSECT for only returning rows in common between multiple SELECT result sets.

Paul Odhiambo

A CHAR(1) to Boolean Transformation While Migrating to Postgres — Did you know Oracle Database doesn’t have a boolean type? If you’re pondering an Oracle to Postgres migration, you probably do, and here’s how to approach migrating from a CHAR(1) approach to a boolean.

Dileep Kumar

The Internals of Hash Indexes — B-trees are the most popular type of index in Postgres and hash indexes are actually ‘discouraged’ in the docs. Hamid takes a quick look at how hash indexes work, however, revealing why they still have much room for improvement.

Hamid Akhtar

[Guide] The Truth About Developer Productivity Metrics — What you need to know to avoid weaponizing metrics for performance reviews, and use them to accelerate releases instead.

Sleuth sponsor

On Database Software Bundles — No strong point here but Bruce ponders on how commercial database vendors offer a ‘full package’ but for Postgres you need to weigh up the offerings from various providers. Should the Postgres project itself provide more?

Bruce Momjian

Creating Custom Postgres Data Types in Rails
Josh Alletto

Using ON vs WHERE to Combine and Filter Data on Joins
Jacek Trociński

What to Return from a Row Level Trigger?
Laurenz Albe