#​404 — May 5, 2021

Web Version

Postgres Weekly

Creating Test Data with SQL, PL/pgSQL, and Python — Greg posits that creating test data on the fly can encourage you to think more deeply about your data model (a good thing) and more easily work at the scale of your choice. An interesting aspect to this tutorial is you can follow along using Docker (and I did).

Greg Schafer

An Operations-Free, Scalable & Flexible Postgres Alternative — Fauna combines the operational integrity and relational modeling of Postgres with an interface and architecture that fits better with modern app development in the cloud. The goodness of Postgres without its operational bottlenecks - Learn more about Fauna.

Fauna sponsor

Supabase: Listen to Database Changes in Real Time via WebSockets — We first mentioned this project over a year ago and it’s continuing to get updates (here’s their latest news). They’ve also released a system to continuously sync a Stripe account to a Postgres database.


An Interview with Dave Page — The latest in the PostgreSQL Person of the Week series of interviews is a substantial one with a prominent member of the community (he’s on the core team and board of directors and has been working on Postgres for over 20 years).

Andreas Scherbaum

JSONB Multi-Column Type Casting — I’ve been doing a lot of work with JSONB columns lately and this neat way to type cast multiple values at once totally passed me by. Useful.

Bruce Momjian

TLS: Demystifying Communication Encryption in Postgres — TLS (Transport Layer Security) is a widely used technology to encrypt client/server connections, including with Postgres. Here’s a high level look at the main concepts involved.

Julian Markwort

Finding the Root Cause of Slow Postgres Queries Using EXPLAIN — An in-depth guide on how to use Postgres EXPLAIN to find and improve problematic query plans and how to use auto_explain.

pganalyze sponsor

Implementing Incremental View Maintenance for Postgres
Yugo Nagata

Citus Tips for Postgres: How to Alter Distribution Key, Shard Count, and More
Halil Ozan Akgul

🔧 Tools and Code

usql 0.9: A Universal CLI for Databases — A CLI tool (written in Go) for working with Postgres, SQL Server, MySQL, SQLite3, Oracle Database, CockroachDB, and many more. A database Swiss Army knife, if you will. v0.9.0 adds command autocomplete, a \copy command to move data between databases, and more.

Kenneth Shaw

Datanymizer: A Database Anonymizer with Flexible Rules — Written in Rust, this tool offers in-flight template-driven data anonymization for Postgres (only, for now - MySQL support is on the way). GitHub repo.


sqlc 1.8: Generate Type Safe Go(lang) from SQL — You write SQL queries, run sqlc to generate code and interfaces for those queries, then write Go code that calls the aforementioned code. 1.8.0 adds support for Postgres 12 and 13 features by upgrading an underlying dependency.

Kyle Conroy