--- title: "The internals of hash indexes" publication: "Postgres Weekly" issue: 403 published_at: "2021-04-28" canonical_url: "https://postgresweekly.com/issues/403" --- Plus understanding transactional deadlocks. | #​403 — April 28, 2021 [Web Version](https://postgresweekly.com/issues/403) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/mpmyll9qnwg4j5kyudqe.jpg)](https://www.cybertec-postgresql.com/en/postgresql-understanding-deadlocks/) [Understanding Deadlocks](https://www.cybertec-postgresql.com/en/postgresql-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](https://en.wikipedia.org/wiki/Catch-22_%28logic%29) of sorts. Hans-Jürgen Schönig [pgvector: A Vector Similarity Search Extension for Postgres](https://github.com/ankane/pgvector) — Supports L2 distance, inner product, and cosine distance. Andrew Kane [![\](https://copm.s3.amazonaws.com/a688285d.png)](https://www.cockroachlabs.com/cockroach-university/?utm_source=pgweekly&utm_medium=sponsor&utm_campaign=cu-general-learn-build&utm_content=postgresweekly-sponsoredlink&utm_term=postgres-weekly-0428) [Cockroach University: Learn to Build Cloud-Native Apps](https://www.cockroachlabs.com/cockroach-university/?utm_source=pgweekly&utm_medium=sponsor&utm_campaign=cu-general-learn-build&utm_content=postgresweekly-sponsoredlink&utm_term=postgres-weekly-0428) — 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](https://blog.crunchydata.com/blog/choice-of-table-column-types-and-order-when-migrating-to-postgresql) — 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_](https://postgresweekly.com/issues/402) touched on this also.) David Youatt [Can You Get a Value From a Dynamic Column in pl/PgSQL Triggers?](https://www.depesz.com/2021/04/21/getting-value-from-dynamic-column-in-pl-pgsql-triggers/) — There’s a workaround (with a cost), demonstrates Hubert. Hubert depesz Lubaczewski [Getting Started with Set Operations](https://arctype.com/blog/postgres-set-operators/) — 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](https://www.migops.com/blog/2021/04/25/char1-to-boolean-datatype-transformation-while-migrating-to-postgresql/) — 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](https://www.highgo.ca/2021/04/22/hash-index-internals/) — B-trees are the most popular type of index in Postgres and hash indexes are actually ‘discouraged’ [in the docs](https://www.postgresql.org/docs/9.2/indexes-types.html). 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](https://www.sleuth.io/developer-productivity-metrics?utm_source=cooperpress&utm_medium=email&utm_campaign=postgres&utm_content=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](https://momjian.us/main/blogs/pgblog/2021.html#April_23_2021) — 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](https://pganalyze.com/blog/custom-postgres-data-types-ruby-rails) Josh Alletto [Using `ON` vs `WHERE` to Combine and Filter Data on Joins](https://www.pluralsight.com/guides/using-on-versus-where-clauses-to-combine-and-filter-data-in-postgresql-joins) Jacek Trociński [What to Return from a Row Level Trigger?](https://www.cybertec-postgresql.com/en/what-to-return-from-a-postgresql-row-level-trigger/) Laurenz Albe **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- **Want more?** You might like [DB Weekly](https://dbweekly.com/?utm_source=pg&utm_medium=email&utm_campaign=footer-promo), our more general database newsletter covering data storage trends, new database types, and more. --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)