--- title: "A deep dive into various types of locks in Postgres" publication: "Postgres Weekly" issue: 358 published_at: "2020-06-03" canonical_url: "https://postgresweekly.com/issues/358" --- This week's Postgres news, views, and tools. #358 — June 3, 2020 [Read on the Web](https://postgresweekly.com/issues/358) 🖤 It's [time to speak up](https://auth0.com/blog/its-time-to-speak-up/), [donate](https://pitchfork.com/thepitch/resources-in-the-fight-against-police-brutality-and-systemic-racism/) or even just [stream a fundraising video](https://www.youtube.com/watch?v=bCgLa25fDHM), because Black Lives Matter. Our thoughts are with everyone in the US and beyond fighting for these causes. Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/zpny9hxugkgvphan1tns.jpg)](https://excessivelyadequate.com/posts/sierpinksy.html) [Drawing the Sierpiński Triangle with Recursive SQL and SVG](https://excessivelyadequate.com/posts/sierpinksy.html) — OK, this is pretty impressive: _“In this post, I’ll present a surprisingly simple recursive CTE1 that implements an iterated function system for generating a variant of the Sierpiński triangle..”_ A fractal, basically. There’s a lot to enjoy here. Noah Doersing [Deadlocks, Page Locks, Advisory Locks, and Predicate Locks](https://habr.com/en/company/postgrespro/blog/504498/) — Welcome to another of Egor Rogov’s amazing Postgres deep dives.. this time digging into less commonly considered types of locks. Lots to learn here. Егор Рогов [![\](https://copm.s3.amazonaws.com/e4c1c62c.png)](https://www.postgresvision.com/?utm_source=Cooperpress&utm_medium=ad) [Postgres Vision 2020 - Free Online Conference (June 23-24)](https://www.postgresvision.com/?utm_source=Cooperpress&utm_medium=ad) — This conference will feature real-world stories of how customers are leveraging Postgres to reduce cost and increase innovation; and the tools and technology PostgreSQL experts are using for deployment models. EnterpriseDB sponsor [TimescaleDB, A Large Scale Postgres-Based Time-Series Database, Now Free](https://blog.timescale.com/blog/multi-node-petabyte-scale-time-series-database-postgresql-free-tsdb/) — Timescale are making their multi-node time-series database on PostgreSQL available for free (it’s ‘source available’). This post digs into why. Timescale [Speeding Up Recursive Queries on Hierarchic Data](https://www.cybertec-postgresql.com/en/postgresql-speeding-up-recursive-queries-and-hierarchic-data/) — A hierarchical query is an SQL query that handles hierarchical model data such a tree. Postgres’s standard `ltree` module can be used to speed up some common use cases. Hans-Jürgen Schönig [Removing Postgres Bottlenecks Caused by High Traffic](https://www.percona.com/blog/2020/05/29/removing-postgresql-bottlenecks-caused-by-high-traffic/) — Covers a variety of ideas from tuning configuration parameters to managing connections, autovacuuming, and what server hardware is being used. Robert Bernier [The Intricacies of Time and Related Data Types in Postgres](https://www.cockroachlabs.com/blog/time-data-types-postgresql/) — As part of Cockroach Labs work in making CockroachDB even more compatible with Postgres (the goal is to be a drop-in replacement) they’ve spent a lot of _time_ getting _time_ right.. and have learnt a lot along the way. Oliver Tan [Visualizing Collations](https://momjian.us/main/blogs/pgblog/2020.html#May_29_2020) — Collations define how values (nearly always strings) get sorted, and different collations can have rather different outcomes when sorting strings. Two queries here show off this concept. Bruce Momjian [Understanding Time Datatypes in PostgreSQL](https://www.cockroachlabs.com/blog/time-data-types-postgresql/?utm_campaign=cooperpress-2020-Q2&utm_source=postgresweekly&utm_medium=newsletter&utm_content=sponsored-link-datatypes-postgres) — While improving CockroachDB’s Postgres compatibility for our recent release, we learned a lot about the intricacies of time in PostgreSQL. Cockroach Labs sponsor ▶[An Example of Using Postgres from Deno](https://www.youtube.com/watch?v=gaN4VghWhNk) — Short and sweet at only 4 minutes. Adrian Twarog [Using Composite Types in Postgres](https://info.crunchydata.com/blog/composite-types) — Composite types are data types formed of other, existing data types (like a `struct` in C, say). Craig Kerstiens [Defining Spatial Constraints with PostGIS](https://info.crunchydata.com/blog/spatial-constraints-with-postgis-part-1) — Constraints are used to ensure that data in the database reflects the assumptions of the data model and this can be just as true with _spatial_ data, as manipulated with PostGIS. Paul Ramsey [Don't Leave Me Hanging: Another Type of Transaction to Monitor](https://richyen.com/postgres/2020/05/25/dont-leave-me-hanging.html) — Prepared transactions make two-phase commits possible but they have some dangers when it comes to holding locks and are not recommended in any situations that don’t truly need them. Richard Yen [PostGIS 2.3.11 Released](http://postgis.net/2020/05/31/postgis-2.3.11/) — The final bug fix release for 2.3 – upgrading to 2.4 or higher (3.0 is the latest version, but [requires a minimum](https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS) of Postgres 9.5) is now strongly suggested. PostGIS Developers 🗓**Upcoming Online Events** - [Postgres Pulse](https://www.enterprisedb.com/postgres-pulse) - weekly at 11am ET each Monday. Weekly Zoom-based sessions with folks like Bruce Momjian, Vibhor Kumar, and other people at EnterpriseDB. - [Postgres Vision 2020](https://www.postgresvision.com/) on June 23-24. A full attempt at an online Postgres conference across multiple days with multiple tracks. Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), and [Craig Kerstiens](https://twitter.com/craigkerstiens). [Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) © Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom