#358 — June 3, 2020

Read on the Web

🖤 It's time to speak up, donate or even just stream a fundraising video, because Black Lives Matter. Our thoughts are with everyone in the US and beyond fighting for these causes.

Postgres Weekly

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.

Noah Doersing

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.

Егор Рогов

Postgres Vision 2020 - Free Online Conference (June 23-24) — 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 — 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.

Hans-Jürgen Schönig

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.

Robert Bernier

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.

Oliver Tan

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.

Bruce Momjian

Understanding Time Datatypes in PostgreSQL — 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 — Short and sweet at only 4 minutes.

Adrian Twarog

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

Craig Kerstiens

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.

Paul Ramsey

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.

Richard Yen

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.

PostGIS Developers

🗓 Upcoming Online Events

  • 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 on June 23-24. A full attempt at an online Postgres conference across multiple days with multiple tracks.