~postgresweekly | Issue 541 - Doing everything with Postgres (9)

Directus 10.9: Wrap an SQL Database with a Real-Time GraphQL and REST API — A Node.js-based system that can act as a frontend to Postgres, SQLite, MySQL, Oracle, and other databases, providing a modern dashboard, client, and REST and GraphQL APIs. (Note the unusual license that limits production use to below a certain revenue level then reverts to GPL in three years.)

Implementing System-Versioned Tables in Postgres — There’s no official support for system-versioned tables in Postgres, though there is the temporal_tables extension, but what if you want to implement the idea yourself? Three triggers and an index to the rescue!

PlanSplainer: A New Way to Visualize Query Plans? — explain.depesz.com is perhaps the best known Web-based tool for this task, but this is an interesting alternative that presents the plan in a more visual way. (The creator admits it is still ‘very experimental’ so your mileage may vary.)

IntegreSQL: API to Manage Isolated Postgres Databases for Testing — Provides a RESTful JSON API for managing Postgres templates and spinning up (and managing the pool of) databases for integration testing purposes.

PGTera: Extension to Render Tera HTML Templates — Tera is a Jinja2-inspired template engine for Rust, and if you need to return complete, rendered HTML views from your database, it's another option.

pgenv: Postgres Binary Manger for the Shell — Makes it simpler to build and run different Postgres releases and switching between them to test, etc. It's just gained a status command for getting the status of a specific instance.

Ibis 8.0: A Dataframe Framework for Python — Provides a universal interface for data wrangling in Python with a focus on tabular data (using dataframes) and support for all the main database systems, including Postgres and SQLite. GitHub repo.

eBook: Tuning Autovacuum for Best Postgres Performance — Learn about tuning settings for scheduling and performance, reducing autovacuum overhead during business hours, the xmin horizon and how it blocks vacuums, why to avoid anti-wraparound vacuums and how to reduce bloat by tuning dead tuple thresholds.