#​419 — August 25, 2021

Web Version

📋 We're back from a quick one week vacation and this is a very practical issue – learn about defining indexes, fixing broken sequences, upgrading RDS databases, speeding up data ingestion, and more :-)
Peter Cooper, Editor

Postgres Weekly

Using CREATE INDEX: Operator Classes, Index Types and More — Creating indexes is one thing, but creating indexes well quite another. Lukas looks at the practicalities of using CREATE INDEX and demonstrates how to analyze your queries to figure out the best indexes to create.

Lukas Fittl

pg_timetable v4 Releasedpg_timetable is an advanced scheduling system for Postgres that lets you execute functions or processes like vacuuming on the schedule of your choice. There’s now better logging, more performance, and a config file.

Pavlo Golub

Monitor Custom Postgres Metrics in Real-Time with Datadog — Monitor and visualize Postgres performance in context end-to-end alongside the rest of your stack. Create custom, drag-and-drop dashboards to quickly view analytics on any Postgres metric. Try Datadog free.

Datadog sponsor

Postgres vs Redis vs Memcached Performance — Each of these data stores has different use cases, but how would it look performance-wise, if you skipped the cache and hit the database directly? Kaarel runs some benchmarks and shares their thoughts.

Kaarel Moppel

Fixing Out-of-Sync Sequences — We recently linked to a story about Postgres ‘skipping’ values in sequences but did you know there’s an extension for fixing sequences that have gotten out of sync?

Hans-Jürgen Schönig

Apache AGE 0.5.0 Released — Apache AGE (A Graph Extension) is a Postgres extension that adds graph database functionality (complete with openCypher query support) and was inspired by AgensGraph.

The Apache Software Foundation

Comparing Upgrade Approaches for Amazon RDS Databases — A quick look at the pros and cons of four different ways to approach version upgrades when using AWS’s managed Postgres offerings.

Arumugam Petchimuthu

Using pglogical to Upgrade a RDS Database — A direct follow on to the post above demonstrating a ‘minimal downtime’ upgrade approach using pglogical for replication.

Arumugam Petchimuthu

How to See Inherited Permissions for a User — If we were running a Tip of the Week this week, this would be an ideal one!

Richard Yen

🐘 How and Why to Become a Postgres Contributor: Tips & Best Practices

Timescale sponsor

Speed Up Time Series Data Ingestion by Partitioning Tables on Amazon RDS — A look at how and why partitioned tables help with data ingestion. While this is focused on RDS, due to the authors working at Amazon, much of this has value generally too.

Vinicius Schmidt and Andy Katz

pgdiff: Compare What Data Changed Between Two Points in Time — Think git diff but for Postgres.

Denver Smith

sqlc 1.9: Generate Type Safe Go(lang) from SQL — You write SQL queries, run sqlc to generate code and interfaces for those queries, then write Go code that calls the aforementioned code. v1.9.0 adds support for views, pgx/v4, and subqueries in SELECTs.

Kyle Conroy