#364 — July 15, 2020

Web Version

Postgres Weekly

PGX: Build Postgres Extensions with Rust — A framework for building Postgres extensions with Rust rather than the C or C++ you might be more used to. Why is this a good thing? Rust is becoming pretty popular in more cutting edge circles lately and the language’s safety features make it a compelling choice.


JSONB: A Container of Types — Bruce reminds us that JSONB columns don’t just have to hold collections like arrays or maps/hashes, you can directly store strings, numbers and booleans too.

Bruce Momjian

Achieve High Availability with PostgreSQL [Whitepaper] — Critical business applications require availability of their backend database cluster. Discover how to setup & deploy enterprise-grade High Availability of up to four 9s. Learn the features and benefits in the Highly Available Postgres Clusters Whitepaper.

2ndQuadrant PostgreSQL Products sponsor

How to Find a Postgres Bug Born in 1997 — The tale of discovering a bug hidden away inside Postgres’s documentation or circle type (depending on your point of view) for the past 23 years.

David Zhang

⚡️ Quick bytes:

How to SCRAM with pgBouncer — Not only can you see how pgBouncer (the connection pooler) uses SCRAM authentication but also what SCRAM authentication is and how it works.

Jonathan S. Katz

What Are Postgres Templates? — We’ve covered this in one of our ‘tips of the week’ before, but if you’re unaware of what template databases are and how they’re used, this is a quick primer.

Angelico de los Reyes

Foreign Data Wrappers: Postgres's Secret Weapon? — Foreign data wrappers (often called FDWs, for short) lets you query remote databases directly from your PostgreSQL instance and here’s how that works alongside Splitgraph, a toolkit for building and working with “data images”.

Artjoms Iškovs (Splitgraph)

Distributed SQL Beats Polyglot Persistence for Building Microservices — Polyglot persistence is the silent killer of release agility; distributed SQL is the answer for today’s multicloud era.

YugabyteDB sponsor

PgTyped: Typesafe SQL in TypeScript — Use raw SQL in TypeScript with guaranteed type-safety.

Adel Salakh

pgsodium: A Postgres Extension for Using libsodiumlibsodium is a crypto library for encryption, decryption, signatures, password hashing and more. We first linked when it was in alpha but it’s now 1.0 :-)

Michel Pelletier

supported by PopSQL

💡 Tip of the Week

RANK and DENSE_RANK for ranking orders

Let's say we've been taking votes of people's favorite database systems. Here's our schema:

CREATE TABLE databases (name TEXT, votes INT);
  ('Postgres', 10000),
  ('MySQL', 4522),
  ('SQLite', 9500),
  ('MongoDB', 4522),
  ('Oracle', 4580),
  ('Redis', 9500);

We want to get the results in order along with a rank of where each database stands:

SELECT row_number() OVER (ORDER by votes DESC), * FROM databases;

It doesn't seem very fair to me that Redis gets ranked #3 despite having the same number of votes as SQLite! Ranks in the real world often take note of equivalent/tied scores and give the same rank to similarly scored items.

Enter RANK and DENSE_RANK, two of several window functions:

SELECT DENSE_RANK() OVER (ORDER by votes DESC), * FROM databases;
SELECT RANK() OVER (ORDER by votes DESC), * FROM databases;

The difference is subtle but RANK gives us a more traditional rank approach where if there are two second place entries, there's no longer a third place entry. DENSE_RANK, however, ensures there are continguous rankings even if there are duplicates.

Try out this tip in PopSQL. PopSQL is a SQL editor built for teams. Think “Figma for SQL”. A free Premium trial awaits Postgres Weekly readers (no credit card required).