Postgres Weekly Issue 308: June 5, 2019

#308 — June 5, 2019

Table Access Methods and Blackholes in Postgres 12 — Table access methods (more commonly known as ‘pluggable storage’) are set to be a highlight feature in Postgres 12 and will make it easier to significantly extend how Postgres works. Michael shows off the basics with a basic ‘blackhole’ plugin (which essentially acts as a /dev/null approach to storage).

Michael Paquier

Real-Time PostgreSQL Performance Monitoring with Datadog — Collect and visualize out-of-the-box and custom Postgres metrics to troubleshoot performance issues faster. Correlate them with data from across your dynamic infrastructure, distributed traces, and logs. Start monitoring with Datadog free.

Datadog sponsor

Introducing pg_auto_failover (Again): A High Availability and Automated Failover Postgres Extension — If you’re feeling a little déjà vu, we first linked this project a month ago when Microsoft announced it. Now, the Citus Data team themselves have done a slightly more extensive writeup of what pg_auto_failover is all about.

Citus Data (Microsoft)

Benchmarking Managed Postgres Cloud Solutions: Microsoft Azure — The last in a series of managed Postgres service benchmarks moves on to Azure’s offering.

Severalnines

PostgresOpen Call for Papers — PostgresOpen, this year hosted in Orlando over September 11-13, has opened their call for papers. Have something to say about Postgres? Consider submitting. Rather speak in Europe? PGConf.EU 2019’s CFP is open too and takes place in October.

United States PostgreSQL Association

How I Decimated Postgres's Response Times for My SaaS — A tale of taking Postgres query response times from an average of ~100ms with peaks to 1 second to a steady 1-10ms.

Tim Nolet

eBook: Best Practices for Optimizing Postgres Query Performance — Learn how to get a 3x performance improvement on your Postgres database and 500x reduced data loaded from disk in this free pganalyze eBook.

pganalyze sponsor

An Overview of Sharding in Postgres and How it Relates to MongoDB’s

Fernando Laudares Camargos

▶ Installing, Setting Up and Using PostGIS — A gentle 30 minute screencast that walks through installing Postgres and PostGIS for working with geographical data.

Klas Karlsson

Sqitch 1.0: Database Change Management — A database and framework independent (Postgres 8.4+ is supported) system for managing database and schema changes via SQL scripts. Here’s how it works with Postgres.

Sqitch

supported by

💡 Tip of the Week

Searching for old queries in psql

The official (and always popular) psql terminal client for Postgres is packed with interesting features, but one I wasn't aware of until recently stems from its use of the Readline library (as commonly used by UNIX-based utilities, shells, etc.)

Readline brings a lot of useful text editing features to psql out of the box including tab completion (try it while typing queries, it works really well) and pressing Ctrl+W to delete the entire last word typed.

You also get access to history (usually stored in a .psql_history file in the home directory of the user running Postgres) where you can press ↑ or ↓ and navigate through the commands and queries you've recently entered, but did you know you can search the history on the fly?

Let's say you have a session like this:

    INSERT INTO people (name, age) VALUES ('Jim', 74);

SELECT * FROM people WHERE age > 18;

SELECT * FROM people ORDER BY name ASC LIMIT 1;

If you now press Ctrl+R and type SEL, you'll see the previous query appear again, as it ready to be autocompleted. If you keep typing, you can scope the search further or press Ctrl+C to break out of the search feature.

If you find a query using your search but it's not the right query, then if you press Ctrl+R again, it will cycle through other matches. So if you press Ctrl+R and type SEL and then keep pressing Ctrl+R it will cycle through other SELECT queries you have made that are stored in the history.

This week's tip is sponsored by GitPrime. Get your copy of their new field guide '20 Patterns to Watch for in Engineering Teams', filled with actionable insights to help debug your development process with data.

🔬 A Periodic Table of psql Commands

Thanks to of Marcelo Diaz of Cybertec for sending this in.

🗓 Upcoming Postgres Events

PostgreSQL Ibiza (Jun 19-21 on Ibiza) — Spain's famous party island promises you both "Postgres on the beach" while still having talks and training in a neat looking venue.
Postgres Vision 2019 (June 24-26 in Boston, MA) — Sir Tim Berners-Lee, the inventor of the Web, is delivering the keynote.
PostgresLondon 2019 (July 2-3 in London, UK)
PostgresConf Beijing 2019 (July 3-6 in Beijing, China)