#​436 — December 22, 2021

Web Version

🎄 Many season's greetings to all of you – we'll be back on Wednesday, January 12 after a two week break. Thanks for all of your support this year!
Peter Cooper, your editor

Postgres Weekly

The Best of Postgres Weekly in 2021

What defines the best? We're not putting any editorial slant on it, it's entirely on popularity. But hopefully you'll see a few things that escaped your attention before :-)

1.  A Roundup of Postgres GUIs in 2021 — This post originally came from 2020 but got an update for 2021 covering some common database clients like pgAdmin, Navicat, DBeaver, HeidiSQL, Datagrip, and others. It'll be interesting to see if we get any newcomers in 2022.

Angel D'az

2.  125 psql Tips — This was originally 100 tips when we first included it, but now there’s 125 bite-sized tips for the tried and tested psql client. If you have a psql session coming up any time soon, this is worth a browse. It’s also possible to load a single tip and hop through randomly if you don’t want to be overwhelmed.

Lætitia Avrot

Free eBook: Effective Indexing in Postgres — Learn how to create the best Postgres index for your queries. We provide a deep dive into index types, operators, data types and more. Creating the right indexes can often improve your query performance.

pganalyze sponsor

3.  Lots of Lesser Known Postgres Features — Haki’s articles are often catnip to us as they cover a lot of ground, and so it goes with this roundup of practical examples of Postgres features you might not be familiar with. Perhaps my personal favorite of the year.

Haki Benita

4.  Working with Hierarchical Structures in Postgres — Trees and similar hierarchical structures are often required when modelling data and there are multiple ways to implement them in Postgres. This post looks at materialized views with recursive CTEs and using ltree columns.

Ana Hobden

5.  Postgres 14: It's The Little Things — Postgres 14 is now here, but just before it came out, Craig Kerstiens wrote how version 14 is made up of lots of smaller improvements rather than around any sort of 'big pillar' features.

Craig Kerstiens

6.  Do You Need Redis? Postgres Does Queuing, Locking, and Pub/Sub.. — I’m a huge Redis fan and while it’s common to have a mainstream database (like Postgres) and Redis running side by side, you can potentially just use Postgres for things like job queues or locks.

Chris Farber

Develop a Faster Way to Work. Build Internal Tools, Remarkably Fast

Retool sponsor

7.  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 ran some benchmarks and shared his thoughts.

Kaarel Moppel

8.  Is Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL a Better Choice for Me? — I think there’s a lot of confusion about when Aurora or straight RDS suit the situation. Here, Amazon attempts to clarify things.

Amazon Web Services

The best of the rest..

Better JSON in Postgres with Postgres 14
Craig Kerstiens

The Unexpected Find That Freed 20GB of Unused Index Space
Haki Benita

JSON in Postgres: How to Use It Right
Laurenz Albe

Cut Out the Middle Tier: Generating JSON Directly from Postgres
Paul Ramsey

Understanding LATERAL Joins in Postgres
Hans-Jürgen Schönig