#​486 — December 21, 2022

Web Version

Together with  Datadog

Postgres Weekly

The Best of Postgres Weekly in 2022

This is the final issue of the year (we're back on January 4, 2023) so we're revisiting the most popular items of 2022. We've covered the main top items but with some breakdowns of other content too (e.g. videos). We hope you have a fantastic holiday season.
Peter Cooper, your editor

1: 😱 A Hairy Postgres Incident — A team upgraded to Postgres 11 and their app began experiencing problems. The author came to the rescue in a rather significant way. This is a fantastic tale of problem solving and an example of how complex seemingly simple issues can get, or as one commenter said: "Very very interesting write up. Great story telling as well."

Jeremy Schneider

2: Postgres 15, A Billion Transactions Later — The author put Postgres 10 up against Postgres 15 with a billion transaction stress test and made some observations, including v15’s improved storage efficiency.

Kaarel Moppel

The release of Postgres 15 in October was arguably this year's biggest Postgres news, but release announcements don't tend to be our most popular links :-)

Visualize Key PostgreSQL Performance Metrics with Context in Datadog — Quickly identify slow-running queries, bottlenecks and errors with granular Postgres analytics alongside the rest of your stack to resolve performance issues. Build custom, drag-and-drop dashboards to quickly view analytics on any Postgres metric.

Datadog sponsor

3: Postgres 15's MERGE Feature — It’s always good to see a fresh new page in the Postgres docs and in October we got one for Postgres 15's new MERGE (a SQL statement you may have seen in SQL Server or Oracle) support. The basic idea is data from a query or a source table can be merged en masse into a target table under definable conditions.

PostgreSQL Documentation

4: How Postgres Chooses Which Index to Use for a Query — The publication date of April 1 worried us at first, but it turned out Lukas wasn't fooling about in his direct look at how Postgres’s query planner gets to work. It goes right down into Postgres’s source code but also covers a practical example of why this is useful knowledge to have.

Lukas Fittl

5: Getting a Query 'Progress Bar' in Postgres — This has our favorite lead image of any post we linked this year, and the end result was neat too. The idea is to use a sequence as a sort of counter to track the progress of a query. It’s a bit of a hack but you’ll learn something here.

Brian Likes Postgres

6: How Retool Upgraded Its 4TB Postgres Database to v13 — 4TB might not be “big data” but it’s in a common zone for SaaS primary database size, so Retool’s story and tips learned along the way during an upgrade from Postgres 9.6 to 13 may prove of interest.

Peter Johnston (Retool)

7: 'How We Optimized Our Postgres Queries 100x' — When it comes to optimizing Postgres queries “the rabbit hole is deep”, says Vadim who ran into some very specific pain points. This was a good look at how he tackled a few performance problems and got some reasonably easy wins.

Vadim Markovtsev

📺 Top Videos of 2022

🛠 Top Code and Tools of 2022

1: pg_activity 3.0: htop-Like Activity Monitoring Tool — Much as you might use top or htop to monitor processes and CPU usage, pg_activity gives you a similar look behind the scenes of Postgres with views over running queries, up to date performance stats, and more.


RDS to Crunchy Bridge: A Customer Case Study on Migration

Crunchy Bridge sponsor

2: Pagila 3.0: A Sample Database for Postgres — Originally a port of the ‘Sakila’ example database for MySQL, Pagila provides a complete and realistic schema and data that could be used in tutorials, demos, for learning SQL, or testing. The DVD rental store metaphor from Sakila stays, but numerous extra features have been brought into play, including JSONB columns and triggers.

Devrim Gündüz

A new release just dropped yesterday, too.

3: OrioleDB: A New Cloud-Native Storage Engine for Postgres — This project remains in public alpha, but the idea is it's an extension aimed at bringing some modern ideas to how tables are accessed, including undo logs, row-level WAL, and lock-less page reading.

Alexander Korotkov et al.

4: Citus 11 for Postgres Went Fully Open Source — Citus, a Postgres extension for horizontal scaling, was first open sourced in 2016 with the business model of an extended ‘enterprise’ paid version. As of June 2022, however, there’s only one Citus version and it’s all open source.

Marco Slot (Citus Data)

5: Awesome Postgres: A Curated List of Libraries, Tools, and Resources — OK, we're cheating as this is its own entire list. This occasionally updated repository covers all areas from backups to GUI clients, managed services to Docker images, blogs, and more. You can also contribute your own suggestions via the repo.

Asad Dhamani and Contributors

Merry Christmas, Happy Hanukkah, Happy New Year, or simply.. we'll see you next year, have a good one ;-)