#370 — August 26, 2020

Web Version

Postgres Weekly

Why Postgres 13 Will Be A 'Lucky' Release — See why the author thinks that why 13, often maligned as an ‘unlucky’ number, will be lucky for Postgres it adds features like incremental sort, parallel vacuum, and improves performance of B-tree indexes.

Jonathan S. Katz

synchronous_commit Options and Synchronous Standby Replicationsynchronous_commit lets you customize when a transaction commit can be acknowledged back to the client as successful which has implications in replicated systems.

Jobin Augustine

[Whitepaper] Best Practices to Harden your Database — Learn the fundamental concepts of Security for PostgreSQL Databases & develop a deep understanding of industry best practices. Discover how to secure your Postgres cluster & keep your data safe. Have confidence your database is adequately protected from attacks.

2ndQuadrant Whitepapers sponsor

Improvements for Handling Large Number of Connections Coming to Postgres 14 — Note that this is about Postgres 14 so you’re going to be waiting a while (as 13 isn’t even out yet!) but handling large numbers of connections is a perennial issue for many so these links to some discussion threads may be of interest.

Hubert depesz Lubaczewski

Query 40K+ Datasets Through a 'Data Delivery Network' With Any Postgres Client — Splitgraph offers what they call a ‘data delivery network’ that acts like a distributed SQL caching proxy compatible with the Postgres wire protocol. In this demo it lets you query over 40,000 datasets with SQL using the psql or other Postgres clients you know and love.


Calculating Standard Deviation with SQL — As is often the case, Postgres offers a function for this task: stddev().

Bruce Momjian

A Look at TLS Related Updates Coming in Postgres 13 — Supporting a minimum of TLS 1.2, Postgres 13 brings a few improvements when it comes to secure connections including support for channel binding.

Cary Huang

How to Setup Postgres on an IPv6 Enabled Network — If you’re confident with IPv6 already, this goes into more depth than you’ll need, but if not.. you may find some useful tips here.

David Zhang

Faster CI/CD for All Your Software Projects - Try Buildkite ✅ — See how Shopify scaled from 300 to 1800 engineers while keeping their build times under 5 minutes.

Buildkite sponsor

pg-costop: Vector Arithmetic and Weighted, Variably Randomized Cosine Similarity Search — Mathemetically this is a bit beyond me but it’s a set of PL/pgSQL functions for working with vectors to calculate cosine proximity / similarity rankings.


pg-shortkey: YouTube-like Short IDs as Postgres Primary Keys“This installs a trigger and type which allow you to use YouTube-like short IDs (e.g. 1TNhBqYo-6Q) as Postgres Primary Keys. Just like YouTube IDs, ‘SHORTKEY’ IDs are fixed length and URL-safe.”


QuestDB: A Performance-Focused 'NewSQL' Time-Series Database System — A relational database (that isn’t built on Postgres but does support its wire protocol) focused on fast time-series data processing. Built in Java.

QuestDB Limited

Pgpool-II 4.1.3, 4.0.10, 3.7.15, 3.6.22 and 3.5.26 Released — Bug fixes all round for all maintained versions of the popular connection pooling system.

PgPool Development Group

supported by YugabyteDB

💡 Tip of the Week

Checking for the existence of a row

You could check for the existence of a row on a Postgres table by just requesting that row and seeing if anything comes back, but depending on how you did this, the result may be ambiguous or inefficent.

The EXISTS subquery expression can be used to unambiguously determine if another query returns any rows or not, and it can therefore be used to detect if a particular row exists:

# INSERT INTO test(id) VALUES (13);
# SELECT EXISTS(SELECT 1 FROM test WHERE id=11) AS "exists";
(1 row)

# SELECT EXISTS(SELECT 1 FROM test WHERE id=13) AS "exists";
(1 row)

EXISTS always returns a boolean – true or false.

This week’s tip is sponsored by YugabyteDB, the high-performance, cloud native, open source distributed SQL database. YugabyteDB EXISTS to help power your business-critical apps at scale. Get started.