Issue 358 - A deep dive into various types of locks in Postgres (13)

PostGIS 2.3.11 Released — The final bug fix release for 2.3 – upgrading to 2.4 or higher (3.0 is the latest version, but requires a minimum of Postgres 9.5) is now strongly suggested.

Drawing the Sierpiński Triangle with Recursive SQL and SVG — 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.

Deadlocks, Page Locks, Advisory Locks, and Predicate Locks — 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.

TimescaleDB, A Large Scale Postgres-Based Time-Series Database, Now Free — Timescale are making their multi-node time-series database on PostgreSQL available for free (it’s ‘source available’). This post digs into why.

Speeding Up Recursive Queries on 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.

Removing Postgres 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.

The Intricacies of Time and Related Data Types in Postgres — 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.

Visualizing Collations — 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.

Using Composite Types in Postgres — Composite types are data types formed of other, existing data types (like a struct in C, say).

Defining Spatial Constraints with PostGIS — 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.

Don't Leave Me Hanging: Another Type of Transaction to Monitor — 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.

Postgres Vision 2020 - Get Your Early Bird Tickets — The leading Postgres conference will be back in Boston this year from June 22-24. Get a 2-day conference pass for $199.