Postgres Weekly Issue 400: April 7, 2021

#400 — April 7, 2021

100 psql Tips — You know how we occasionally include a Postgres “tip of the week”? This is a similar idea but bite-sized tips for psql in particular. If you have a psql session coming up any time soon, this is worth a browse! It’s also possible to load a single random tip if you don’t want to be overwhelmed.

Lætitia Avrot

Standing on the Shoulders of a Giant Elephant: Upgrading Discourse to Postgres 13 — A quick piece from the folks who run the Discourse forum system on their upgrade to Postgres 13. B-tree deduplication was the winning feature for them.

Rafael dos Santos Silva (Discourse)

Best-Practices on How to Speed Up Your Postgres Queries. Free eBook — We share our learnings from helping companies like Atlassian, CounterPath, and others speed up their queries.

pganalyze sponsor

Working with Roles and Permissions — Postgres’s extensive access permissions system is powerful but can be a stumbling block when compared to simpler schemes in other database systems. Here are a few queries to help you get a hold on things (the recursive query at the end is rather neat).

Hans-Jürgen Schönig

Quick bits:

pgBackRest, a backup and restore tool, has added Google Cloud Storage (GCS) support as another place to easily send your backups.
Hubert Lubaczewski has written a string of useful, though niche, posts on new things coming to Postgres 14 you might be interested in. He's covered the unistr function, the pg_database_owner default role, the date_bin function, and configurable LZ4 TOAST compression.
Rick Branson's "10 things I hate about Postgres" from 2020 has done the rounds on social media again. A few of these things are beginning to be mitigated now.
AWS has a new series of posts about converting from the NUMBER type (part 2) when moving from Oracle to Postgres. It's harder than it seems, and you can't just switch to NUMERIC without a second thought.

On the Many Upgrade Methods of Postgres — Bruce lists 8 possible methods for major major version upgrades and explains which suit certain requirements.

Bruce Momjian

[Report] Benchmarking the Cloud: AWS vs. GCP vs. Azure — Which cloud has the lowest latency? The best storage performance? The most throughput? This report digs into the tradeoffs each cloud makes - so you can choose the best option for your needs.

Cockroach Labs sponsor

Anything I Want With Sequel and Postgres — Janko demonstrates how Ruby's Sequel gem supports advanced features of Postgres, allowing him to bulk import CSV files into a partitioned set of tables that required some obscure PG knowledge.

Janko Marohnić

Managing the Logging of Pgpool-II on Kubernetes

Bo Peng

Preventing FreeBSD's OOM Mechanism from Killing Postgres

Luca Ferrari

🔧 Tools and Code

PostgREST: Serve a RESTful API from a Postgres Database — I was surprised to see I hadn’t linked to this useful project in over a year. It hasn’t seen any large updates recently but is still actively under development and could come in useful for spinning up an API in a hurry.

Joe Nelson and Steve Chavez

pgtt: PostgreSQL Global Temporary Tables — An extension to create, manage and use Oracle-style Global Temporary Tables.

Gilles Darold