Postgres Weekly Issue 453: May 4, 2022

#453 — May 4, 2022

A Look at (Future) Parallel Server-Side Backup Compression — Postgres 15 will, fingers crossed, support server-side backup compression and Robert did a little digging around to see how well it might work (in terms of both speed and disk space saved) using different compression algorithms. Very promising!

Robert Haas

▶ IO in Postgres: Past, Present, and Future — A technical talk given just two days ago digging deep into Postgres’s IO story including the current limitations, how those limitations are being worked around, and progress being made on improvements. (1 hour 6 minutes.)

Andres Freund

Is Your Distributed Database Really PostgreSQL Compatible? — How do you evaluate the PostgreSQL compatibility of cloud native databases? This video provides a framework for comparing PostgreSQL compatibility of modern databases, including Google Spanner, Amazon Aurora, CockroachDB, and YugabyteDB.

YugabyteDB sponsor

Working with Large Postgres Databases — What is a small, medium, or large database when Postgres can handle up to an unlimited size (though tables are limited to 32TB)? Addressing potential issues for a ‘large’ database by anticipating future conditions is the goal for every DBA, says Robert.

Robert Bernier

Apache AGE 1.0: A Graph Extension for Postgres — An extension that provides graph database functionality (via openCypher) to Postgres and was inspired by AgensGraph. More info on the release.

Apache Foundation

In brief:

A look into the details of a cross-account database vulnerability that was found in Azure PostgreSQL.
Amazon RDS for PostgreSQL now supports new M6i and R6i instances that offer up to 128 vCPUs and 1TB of RAM. Could your startup now keep all its data in RAM on AWS? Possibly, if you have a few thousand dollars a month for it.

Analyzing Historical Flight Data with MobilityDB — MobilityDB sits atop Postgres and PostGIS for providing geospatial trajectory analysis.. so it’s perfect for analyzing historical air traffic data, as demonstrated here.

Florian Nadler

Using SQL's MERGE in Postgres 15 — Last month, we mentioned that MERGE support is coming to Postgres 15 and this post presents a basic example of its use.

Hans-Jürgen Schönig

▶ Advanced Postgres Schema Design — A talk from PGConf NYC 2021 on going “beyond CREATE TABLE and CREATE VIEW with functional indexes, exclusion constraints, array types, transactional DDL, and more.”

Sehrope Sarkuni

Automating Interval Partitioning Maintenance on Amazon RDS or Aurora — Very AWS specific but there’s a lot of tying together ideas here for creating robust partitioned databases.

Shetty, Gudivada, et al. (AWS)

Tuning Auto-Vacuuming Parameters — The most perennial Postgres topic there is, perhaps.

Michael Aboagye

How to Upgrade Postgres from 13 to 14 on Ubuntu 22.04
Paolo Melchiorre

🔧 Code and Tools:

pgwatch2 v1.9 Final Released — pgwatch2 is a Postgres monitoring system using Grafana dashboards for visualization of a wide array of Postgres metrics.

Pavlo Golub

Custom Dashboards, Admin Panels, CRUD Apps — Build an Internal Tool on Any SQL Database 10x Faster

Retool sponsor

pg_ivm 1.0: IVM (Incremental View Maintenance) Extension — Incremental View Maintenance (IVM) keeps materialized views up to date in a way where incremental changes are computed and applied rather than recomputing everything. pg_ivm provides a kind of ‘immediate maintenance’ where materialized views are updated immediately after a base table is modified.

SRA OSS, Inc. Japan