#​440 — February 2, 2022

Web Version

Postgres Weekly

Timestamps and Timezones: What You Do and Don't Need to Know — An attempt to ‘demystify’ Postgres’s date and time data types that goes into far more depth than you might imagine (and it’s only the first of a two part series – the second part moves on to durations/intervals.) It’s about time ;-)

Bryn Llewellyn (YugabyteDB)

Creating a Foreign Data Wrapper for Postgres — One of Postgres’s great strengths lies in its extensibility by way of extensions – a mechanism so powerful that entire companies (e.g. Citus Data or Timescale) have even been founded upon them. A common use for extensions is foreign data wrappers which allow Postgres to interact with non-native data formats — this post covers what you need to know to make your own (beware: plenty of C ahead - if you prefer Rust, consider pgx).

Aaron Son (DoltHub)

The pganalyze eBook Library: Guides on Postgres Optimization and More — Over 10,000 people have downloaded our eBooks so far. Check out our guides on Postgres query optimization, effective indexing, finding the root cause of slow Postgres queries using EXPLAIN, efficient search, and log monitoring.

pganalyze sponsor

In brief:

PG 14 and Recent SCRAM Auth Changes: Should You Migrate to SCRAM? — If you’ve run into password authentication related failures after switching to Postgres 14, here you’ll learn why. In short, the default for password encryption in newer versions of Postgres uses SCRAM (available since Postgres 10). This post explains what it is and why you’ll want to be using it going forward.

Jobin Augustine (Percona)

Working with JSON in Postgres 14+ — Postgres’s native JSON support goes all the way back to version 9.2, but what’s available in PG14 is a huge leap ahead from those early days. Aaron looks at some of the more modern features.

Aaron Bos

How to Use Regular Expression Group Quantifiers — Regular expressions provide a powerful and flexible way to parse text, even if they’re not always the most robust way to do so. Nonetheless, the author had some HL7 V2 data (a format used in healthcare) to parse and managed to use regexes in Postgres for the job.

Selvakumar Arumugam

🔧 Code and Tools:

Procrastinate: A Postgres-based Task Queue for Python — It feels like a lot of care and thought has gone into this. Nice API too.


🐘 The Developer Experience You Always Wanted for Postgres

Crunchy Bridge sponsor

What's New in Pgpool-II 4.3? — v4.3 of the popular connection pooling (and more) middleware actually dropped in December, but this post takes a specific look at a couple of the changes.

Tatsuo Ishii

PostGIS 3.1.5 Releasedv3.2 is out, but this is a bug fix release of the older branch.

PostGIS Developers