#298 — March 27, 2019

Read on the Web

Postgres Weekly

Benchmarking Postgres Cloud Solutions: Amazon RDS — Two weeks ago they benchmarked Amazon Aurora, now it’s RDS’s turn. Spoiler: Aurora clearly beats RDS performance-wise when it comes to Postgres, although Aurora is quite a different beast.

Viorel Tabara

PostgreSQL 11 Generally Available on Heroku (and AWS) — Heroku, the cloud application platform, have been huge champions of Postgres over the years and now they’ve made Postgres 11 support generally available across their entire service. Hurrah. In related news, Postgres 11 is supported on AWS RDS too!

Becky Jaimes (Heroku)

Free eBook: How to Get A 3x Performance Improvement On Your Postgres Database — Learn our best practices for optimizing Postgres query performance for customers like Atlassian and how to reduce data loaded from disk by 500x.

pganalyze sponsor

Speeding Up GROUP BY — SQL’s GROUP BY is used to group records in a result set together often for summary/aggregation purposes and there’s a way to speed them up and gain some ‘free’ extra performance, claims the author!

Hans-Jürgen Schönig

Indexes in Postgres: A Look at B-Trees — The latest in a series of extensive posts digging into how indexes work in Postgres. There’s more here than you absolutely need to know but it’ll help you understand why indexes sometimes behave the way they do.

Egor Rogov

Pivotal Launches Its Own Supported Version of Postgres — Pivotal is a well known cloud development company and they’ve created their own commercially supported package of Postgres 11 (for RHEL 7) along with enterprise-style add-ons like a replication manager.

Pivotal

The Best Way to Count Distinct Indexed Things — It’s significantly more efficient to do a count of a subquery’s results than to try to do a count with DISTINCT in a single query.

Peter Bengtsson

Monitoring PostgreSQL WAL Files — Monitoring Postgres WAL files can lead to a more performant PostgreSQL deployment. Find out why & how to go about it.

pgDash sponsor

Comparing Postgres Clients: SQLPro vs Table Plus vs Postico — One developer’s very brief overview of what she liked and disliked about three different GUI apps for working with Postgres and why you might pick each.

Meredith Lind

Barman V2.7 Released: The Backup and Recovery Management Tool — Use this to implement disaster recovery solutions for those all critical production databases. Project homepage.

2ndQuadrant

wal2json: A JSON Output Plugin for Changeset Extraction — A JSON object is produced per transaction containing both the new and old tuples for updates.

Euler Taveira de Oliveira

supported by

💡 Tip of the Week

Improve your psql experience with .psqlrc

Unless you've found a GUI tool you really love, you're likely to be using Postgres's default psql client a lot while working with your Postgres databases.

.psqlrc is a user-level configuration file (though there is a system-wide psqlrc file too) you can create in your home directory to set a variety of default options for psql and make it behave in a way that better suits you.

Create a .psqlrc file in your home directory and paste in these commands merely to get started:

\timing
\pset null 'ø'

We're keeping it very simple here, just to show off the basic concept!

\timing turns on query timing which shows you how long a query took to run (it was our tip in issue 292).

\pset null 'ø' makes psql show null values as the ø character (similar to the 'empty set' symbol) instead of as an easily missed blank space.

Want to learn a little more? Check out An Explained psqlrc and Customizing My Postgres Shell for some more ideas.

This week's tip is sponsored by strongDM. Teams at Hearst, SoFi, and Peloton trust strongDM to manage engineers’ access to everything.

🗓 Upcoming Postgres Events