Issue 446 - Full of frammishes (11)

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.

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.

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.

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.

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.

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.

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.

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.

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.

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