---
title: "A periodic table of `psql` commands"
publication: "Postgres Weekly"
issue: 308
published_at: "2019-06-05"
canonical_url: "https://postgresweekly.com/issues/308"
---

The latest Postgres news, articles, tools, and projects.       

#308 — June 5, 2019 

[Read on the Web](https://postgresweekly.com/issues/308)   

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1559735604/pwkj4s4p7kakrzulztte.png)](https://paquier.xyz/postgresql-2/postgres-12-table-am-blackhole/)   

[Table Access Methods and Blackholes in Postgres 12](https://paquier.xyz/postgresql-2/postgres-12-table-am-blackhole/) — 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](https://github.com/michaelpq/pg_plugins/tree/master/blackhole_am) (which essentially acts as a `/dev/null` approach to storage). 

Michael Paquier   [![\](https://copm.s3.amazonaws.com/009a5d55.png)](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyP) 

[Real-Time PostgreSQL Performance Monitoring with Datadog](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyP) — 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](https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover/) — If you’re feeling a little _déjà vu_, we first linked this project [a month ago](https://postgresweekly.com/issues/304) 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](https://severalnines.com/blog/benchmarking-managed-postgresql-cloud-solutions-part-four-microsoft-azure) — The last in a series of managed Postgres service benchmarks moves on to Azure’s offering. 

Severalnines   

[PostgresOpen Call for Papers](https://2019.postgresopen.org/callforpapers/) — 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](https://2019.pgconf.eu/callforpapers/) is open too and takes place in October. 

United States PostgreSQL Association   

[How I Decimated Postgres's Response Times for My SaaS](https://blog.checklyhq.com/how-i-decimated-postgres-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](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklySecondary) — 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](https://www.percona.com/blog/2019/05/24/an-overview-of-sharding-in-postgresql-and-how-it-relates-to-mongodbs/) 

Fernando Laudares Camargos   

▶[Installing, Setting Up and Using PostGIS](https://www.youtube.com/watch?v=EBfjZgjyZmM) — 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](https://github.com/sqitchers/sqitch) — 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.](https://github.com/sqitchers/sqitch/blob/develop/lib/sqitchtutorial.pod) 

Sqitch       supported by [![GitPrime\](https://res.cloudinary.com/cpress/image/upload/v1554813939/kwagarrsergds4sxgjr9.png)](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)  

💡 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](https://en.wikipedia.org/wiki/GNU_Readline) (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](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)_. Get your copy of their new field guide **['20 Patterns to Watch for in Engineering Teams'](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)**, filled with actionable insights to help debug your development process with data.    

🔬 A Periodic Table of `psql` Commands  

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1559733987/y74anviavcgujchlknif.png)](https://www.cybertec-postgresql.com/en/) 

Thanks to of Marcelo Diaz of [Cybertec](https://www.cybertec-postgresql.com/en/) for sending this in.     

🗓 Upcoming Postgres Events 

- [PostgreSQL Ibiza](https://www.pgibz.io/index.html) (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](https://postgresvision.com/) (June 24-26 in Boston, MA) — Sir Tim Berners-Lee, the inventor of the Web, is delivering the keynote.
- [PostgresLondon 2019](https://postgreslondon.org/) (July 2-3 in London, UK)
- [PostgresConf Beijing 2019](https://postgresconf.org/conferences/Beijing) (July 3-6 in Beijing, China)          

Curated by [Cooperpress](https://cooperpress.com/) and [Craig Kerstiens](https://twitter.com/craigkerstiens). 

[Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) 

© Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom