#​428 — October 27, 2021

Web Version

Postgres Weekly

Strings, Arrays, Recursion, and Parsing JSON: Exploring PL/pgSQL — If you’ve not yet worked with Postgres’s built-in procedural language, PL/pgSQL, this is a neat primer that works up from basic operations through to recursive ones and even a little parsing. The goal is to make PL/pgSQL “a little less scary to you” and I think it works.

Phil Eaton

StackGres 1.0.0: Open Source 'Postgres-aaS' for Kubernetes — StackGres provides a way to run Postgres on Kubernetes with the goal being to make it one of the easiest ways to deploy Postgres in production. This also requires extensions to be packaged in a way that they can be dynamically loaded and over 100 extensions are already available.

Álvaro Hernández

A Better Way to Index Your Postgres Database: pganalyze Index Advisor — We all love explain.depesz.com for sharing EXPLAIN plans. So we thought: Why not make a similar free tool, specifically for getting index advice on a query? Check out our free pganalyze Index Advisor and learn what the best index for your query is.

pganalyze sponsor

CockroachDB Launches New Serverless Database Platform — CockroachDB is a long standing, distributed, resiliency-oriented SQL-based database system and its parent company has unveiled CockroachDB Serverless, a managed offering priced entirely around total data stored and how much work queries do. Boasting Postgres tool and client compatibility, this is one to watch.

Nate Stewart (CockroachDB)

How to Scale Postgres for Time Series Data with Citus — TimescaleDB has done a good job of being associated with time series workloads on Postgres, but it’s not the only way! Citus offers an interesting approach by offering distributed time-partitioned tables - and here’s how to make it work.

Burak Velioglu

Why It's Hard to Automatically Suggest What Indexes to Create — The creator of the ever-useful explain.depesz.com EXPLAIN ANALYZE tool explains why he hasn’t added index suggestions to the tool, and how he feels index suggestion tools generally only help so far and that you also need to add experience to the mix.

Hubert depesz Lubaczewski

Improve Performance and Manageability of Large Tables by Migrating to Partitioned Tables on Amazon Aurora and RDS — That title is a bit of a mouthful, but this is the latest in a series on using partitioning to improve performance when using managed Postgres services on AWS.

Peter Celentano and Chirag Dave (AWS)

Memory Tuning for Workloads in PostgreSQL Flexible Server on Azure — An Azure-focused introduction to tweaking Postgres server configuration options.

Gennady Kostinsky

🏆  The Surprising Impact of Medium-Size Texts on Performance — A “golden oldie” I’ve seen doing the rounds again this week :-) You’ve got your small text (usernames, emails), large text (entire documents), and your ‘medium’ text (comments, descriptions), and while TOAST introduces efficiencies in storing larger documents, medium-sized text columns can make rows very wide and affect performance disproportionately.

Haki Benita

Encrypting Postgres Data at Rest in Kubernetes
Jonathan S. Katz

Tools & Code

pg_statement_rollback v1.3: Server Side Rollback at Statement Level — Add server side transaction rollback at statement level like in Oracle or DB2. This version adds Postgres 14 support.

Gilles Darold

Why Do Customers Choose Percona for PostgreSQL? Find Out Now

Percona sponsor

postguard: A Rust Library to Test Postgres Queries Against CORS-Like Rules — Aims to protect your database against malicious or invalid queries by testing them before they’re sent to your database.

Alex Pearson

pgexporter 0.2.0: A Prometheus Exporter for Postgres
Red Hat