#​548 — March 27, 2024

Web Version

Together with  pgAnalyze

Postgres Weekly

Recovering Deleted Data from Postgres Tables — Transactions and backups cover most situations, but can you do something similar to an ‘undelete’ on data otherwise considered deleted from a table? With pg_dirtyread, you can do quite a bit and, failing that, you could even go digging into the WAL..

Christoph Berg

Will Postgres Ever Change Its License? — The recent announcement that Redis will be dropping the BSD license in favor of a dual ‘source available’ and SSPL license has got people worried. Is Postgres vulnerable to a similar move? Certainly not.

Jonathan Katz

How to Automate Postgres Index Selection Using the CP-SAT Solver — Learn about the Constraint Programming model powering pganalyze Index Advisor 3.0. This technical deep dive (56min video) with Lukas Fittl & Philippe Olivier is a behind-the-scenes look at using the CP-SAT solver for recommending Postgres indexes.

pganalyze sponsor

Introducing pgzx: Create Postgres Extensions using Zig — Yes, we linked to pgzx last week, but it seems we jumped the gun as there’s now a proper launch post for it, complete with a C vs Zig example for creating a barebones extension and a run down of the advantage of using Zig to build extensions.

Golubenco and Siering (Xata)


Checking Your Privileges“If you are counting on the PostgreSQL privilege system to prevent roles from running functions (and accessing other objects), be sure you know what the default permissions are, and adjust them accordingly.”

Christophe Pettus

How to Identify Semantic Issues with plpgsql_checkplpgsql_check is a linter for statically analyzing Postgres PL/pgSQL procedure code.

Pattanayak, Rout, Kumar and Borse (AWS)

Inside PostGIS: Calculating Distance — Paul dives inside PostGIS’s distance calculation implementation.

Paul Ramsey

Transitioning from Heroku Postgres to AWS EC2: A Step-by-Step Approach
Muhammad Ali

Understand Postgres's Planner: Simple Scan Paths vs Plans
Cary Huang

📰 Classifieds

🥸 Use Neon's Postgres database branching and PostgreSQL Anonymizer to develop in production-like environments with masked PII.

📢 Free pgEdge distributed Postgres DB with 3 node active-active cluster and one-click provisioning across 3 regions. Free t-shirt to first 500.

🛠 Code and Tools

Slonik v38: The Sophisticated Postgres Client for Node.js — A ‘battle tested’ framework offering strict types, detailed logging, abstracts repeating code patterns, protects against unsafe behavior, as well as a rich debugging experience.

Gajus Kuizinas

MobilityDB 1.1: Geospatial Trajectory Data Management and Analysis Platform — Built upon Postgres and PostGIS, MobilityDB is designed for working with things that are on the move and therefore are providing geospatial trajectories that can be stored and queried. GitHub repo.

Université libre de Bruxelles