#​481 — November 16, 2022

Web Version

Together with  Polyscale

Postgres Weekly

Reducing Replication Lag with I/O Concurrency in Postgres 15 — Postgres 15 comes with key replication improvements, but the work done to get there was deep. This post explains how IO concurrency affects replication lag but also tells the story of how Postgres’s IO story is improving over time and teases a future where data will be asynchronously transferred into Postgres’s buffers avoiding the kernel entirely. “We’ve only scratched the surface,” says Thomas.

Thomas Munro (Microsoft)

Using Postgres with Deno Deploy — Accessing Postgres data at the edge presents new challenges with multi-region connectivity and latencies. Learn how to solve these in minutes with PolyScale’s plug-and-play database edge cache.

PolyScale.ai sponsor

PostgreSQL 15.1, 14.6, 13.9, 12.13, 11.18, and 10.23 Released — A bevy of bug fix releases for maintained release lines. None of the bugs particularly stand out, but are listed in this post. We also get time zone data updates due to DST changes in various countries. It also marks the final v10 release.

PostgreSQL Global Development Group

A Tribute to 2017's Postgres 10 — Five years after each major Postgres release, the tide heads out and that version hits its ‘end of life’ (EOL) after a final farewell release (more info about this in the versioning policy document.) Jonathan indulges in some nostalgia for what was a ‘transformative’ version of Postgres that introduced logical replication, declarative partitioning, and SCRAM authentication.

Jonathan Katz

IN BRIEF:

  • AWS RDS now supports gp3 storage volumes. This gives you more flexibility about defining the performance of your storage independent of its size, so you can tweak max IOPS and MB/sec throughput to your heart's content.

  • 🟥 🟩
    🟦 🟨  Windows XP? If you've ever doubted the core team's commitment to testing Postgres on older platforms, consider that Andrew Dunstan has only just decommissioned his Windows XP machine as part of Postgres 10's end of life – yes, Postgres 10 supported it! 

  • Amazon Aurora has upgraded to Postgres 14.5, 13.8, 12.12, and 11.17 (these are all one minor version behind the latest releases mentioned above). There's also a logical replication performance improvement.

EXPLAIN That Parameterized Statement — The output of EXPLAIN (ANALYZE, BUFFERS) is always useful, but can be hard to get for a parameterized statement. Unless you have some of Laurenz’s tips in your back pocket..

Laurenz Albe

What is an 'Upsert' and When Should You Use One? — This comes from CockroachDB but covers the use of the same idea in Postgres by way of INSERT ON CONFLICT. (Postgres 15’s MERGE provides another approach.)

Charlie Custer

Free eBook: How to Create the Best Postgres Index for Your Queries

pganalyze sponsor

Read Before You Upgrade: Best Practices for Choosing a Postgres Version — Reflections on when to consider upgrading your Postgres version and questions to ask before you start doing so. (As a Timescale article, Timescale’s own managed service does get recommended, but at the end.)

Laurence, Roybal and Godeke (Timescale)

JSON in PostgreSQL: The Ultimate Guide — I encountered this when putting together some JSONB queries this week and found it useful.

Ben Brumm

Postgres at Scale: Running Multiple PgBouncers — Connection pooling with pgBouncer is great, but at particularly high loads, there are benefits to be had with running multiple pgBouncers.

Elizabeth Christensen

How to Recreate ROW CHANGE TIMESTAMP from IBM Db2 on Amazon RDS or Aurora — In Db2, a column created with ROW CHANGE TIMESTAMP always contains a timestamp of when the row was last modified. Can you do this in Postgres too? Triggers to the rescue.

Amazon Web Services

How to Schedule Automatic Backups with pgAgent in pgAdmin
Horcasitas and Tran

🛠 Code and Tools

Improved Aarch64 (arm64) Support Now Available in the Postgres RPM Repository — You’ve been able to install aarch64/ARM64 architecture Postgres rpm packages for a while, but some focus has been put into full RHEL 9 support, improved support and fixes for RHEL 7 and 8, and more.

David Detter

And last, but not least: