#​430 — November 10, 2021

Web Version

Postgres Weekly

Lots of Lesser Known Postgres Features — Haki’s articles are often catnip to us as they cover a lot of ground, and so it goes with this roundup of practical examples of Postgres features you might not be familiar with. Worth a skim of the Table of Contents at the very least!

Haki Benita

A Look at the New LZ4 TOAST Compression in Postgres 14LZ4 is an extremely fast compression algorithm (and a relatively modern one too) and it’s available as an option for TOAST compression in Postgres (as used on very large values in columns, such as text fields) versus the older PGLZ option. LZ4 doesn’t compress quite as much as PGLZ but the performance gains are huge.

Haiying Tang

Build What You Dream. Never Worry About Your Database Again — Elastic scale doesn’t have to mean sacrificing consistency or familiarity. Get effortless scaling, automated ops, and guaranteed transactional consistency with CockroachDB Serverless, the world’s most evolved SQL database.

CockroachDB Serverless sponsor

Three Cases Against Using IF NOT EXISTS / IF EXISTS — Many DDL statements accept IF (NOT) EXISTS modifiers to let statements only run under certain conditions, but overuse “may lead to negative consequences” argues Nikolay.

Nikolay Samokhvalov

Postgres 14's enable_memoize Brings Improved Perf to Nested Loop Joins — A quick look at a Postgres 14 enhancement that you can play with quite easily by turning the enable_memoize setting on and off. It enables/disables the query planner’s use of memoization to cache results from parameterized scans inside nested-loop joins. Does this help in every day usage? Lukas’s findings say yes.

Lukas Eder

📣  Azure user? Hyperscale (Citus) for Postgres on Azure has introduced Private Link, for creating private endpoints on private IPs within your virtual network. Elsewhere, Sunil Agarwal and Anna Hoffman ▶️ talked about the dynamically scaling 'Flexible Server' option in Azure Database for PostgreSQL.

Message Queuing Using Native Postgres? — The author thinks through how to implement a queuing system using only what Postgres provides out of the box.

David Christensen

Developers+PostgreSQL+Percona = Better Together. Find Out How

Percona sponsor

Finding Revenue Accrued Per Day with SQL — Some helpful queries and examples for revenue accruing per day over the course of a month.

Jonathan S. Katz

pspg 5.5.0: The Postgres Data Pager — Some UI tweaks, plus support for a streaming mode for files on BSD.

Pavel Stěhule

Slonik v25: A Sophisticated Node Postgres Client Library — A ‘battle tested’ framework that abstracts repeating code patterns, protects against unsafe behavior, and provides a rich debugging experience.

Gajus Kuizinas

Avoiding LWLock:buffer_content Locks in Amazon Aurora: Tips and Best Practices
Biran and Imseih (AWS)