#​541 — February 7, 2024

Web Version
Together with  pgAnalyze

Postgres Weekly

Postgres is Enough: Doing Everything with Postgres? — It’s just a GitHub gist full of links, but leans on the idea communicated in Stephan Schmidt’s Just Use Postgres for Everything to show off the various projects that let you do everything, adding features like background jobs, mapping, audit logging, vector search, API serving, and more directly to our favorite database.

Chase Pursley

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.

pganalyze sponsor

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!

Jean Niklas hyPiRion L'orange

IN BRIEF:

Benchmarking Vector Search Approaches: pgvector vs Lantern
Binidxaba (Tembo)

Changing a Column from Integer to Boolean in One Transaction
Luca Ferrari

📰 Classifieds

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

Read about PostgreSQL on Redgate’s Simple Talk journal – featuring a range of authors sharing their expertise on different technical topics.

🐲 Dragonfly is a modern Redis replacement, delivering better performance and simpler operations at a lower cost. Start your free trial today.

🛠 Code and Tools

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.)

markmeeus

💡 If you ever need a random plan to play with, explain.depesz.com's public history collection may be helpful!

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.

all about apps GmbH

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.

Frankie Primerano

pgenv: Postgres Binary Manager 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.

Ferrari, Wheeler, et al.

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.

Ibis Developers

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.)

Monospace, Inc.