đŸ‡ș🇩 #​446 — March 16, 2022

Web Version

Postgres Weekly

Postgres Auditing in 150 Lines of SQL — Auditing, in the sense of keeping track of changes over time, is a fundamental process in many circumstances and it’s possible to create an auditing system entirely within Postgres itself with a little care and judicious use of PL/pgSQL.

Oliver Rice (Supabase)

Hooks: The 'Secret' Feature Powering the Postgres Ecosystem — Hooks are an unofficially documented internal piece of Postgres that enable extensions to extend or change the behavior of Postgres. This post looks at how some common extensions use them to get Postgres to do things beyond its original design.

Everett Berry

Studio 3T Manages Your Data, While You Wrestle Your App into Shape — Studio 3T's full suite of MongoDB tools lets you query quicker, develop with data even faster and turn queries into code automatically. Try it FREE for 30 days - no credit card needed.

Studio 3T sponsor

In brief:

How to Avoid the Hidden Dangers of Duplicate Key Violations — If multiple clients attempt to insert the same data into the same table at the same time, constraints can be used to prevent duplication, but a variety of issues can crop up including wasted storage and unnecessary autovacuuming work.

Divya Sharma and Shawn McCoy (AWS)

Pipeline Mode for Better Performance on Slow Networks — Postgres 14 introduced a ‘pipeline mode’ in the libpq C API which can be used to improve performance over high latency connections by allowing multiple statements to be sent over the wire without waiting for a response each time.

Laurenz Albe

Is My Autovacuum Configured Properly? — Autovacuuming was introduced in Postgres 19 years ago(!) but still throws up the occasional problem for a database admin. Keeping an eye on what’s going on and tweaking some parameters can go a long way.

Hubert depesz Lubaczewski

PostGIS vs GPU for Performance and Spatial Joins — A practical comparison of a GPU-assisted spatial joins project with the PostGIS equivalent. While the GPU approach was faster, Postgres still had some significant advantages to consider.

Paul Ramsey

How to Benchmark Performance of Citus and Postgres with HammerDB on Azure — HammerDB is a well known benchmarking suite for databases so provides a good way to put a system under stress.

Jelte Fennema (Microsoft)

How to Run Postgres on Kubernetes with Percona Operator and Pulumi
Sergey Pronin

🔧 Code and Tools:

PostgreSQL Anonymizer 0.10 Released — An extension to mask or replace personally identifiable information (PII) or commercially sensitive data in Postgres with a variety of different approaches to masking on offer. There’s now also 📒 a 50-page PDF documenting how to use the tool in depth.

Dalibo Labs

🐘 The Developer Experience You’ve Always Wanted for Postgres

Crunchy Bridge sponsor

pg_dumpbinary: Dump a Database with Data Dumped in Binary Format — Using a binary format for a dump has some benefits in particular situations.


PGSync 0.7: Sync Postgres Data Between Databases — Speed and security are the order of the day, and you can sync partial tables or groups of tables. The README shows off some examples. Written in Ruby.

Andrew Kane

supa_audit: Generic Table Auditing for Postgres — Uses the same technique as outlined in the auditing article linked above.