---
title: "Upgrades, computed columns, and more Postgres 12 goodies"
publication: "Postgres Weekly"
issue: 329
published_at: "2019-10-30"
canonical_url: "https://postgresweekly.com/issues/329"
---

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

#329 — October 30, 2019 

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

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/bog6obt6hjzfyor3p6fz.jpg)](https://www.cybertec-postgresql.com/en/upgrading-postgres-major-versions-using-logical-replication/)   

[Performing a Major Version Upgrade with Logical Replication](https://www.cybertec-postgresql.com/en/upgrading-postgres-major-versions-using-logical-replication/) — If you’re just looking to upgrade to Postgres 12, [start here](https://www.cybertec-postgresql.com/en/a-primer-on-postgresql-upgrade-methods/), but this article focuses on a ‘new’ (since Postgres 10) upgrade approach based around _logical replication_. While it offers safety and a tiny amount of downtime, it’s perhaps more complex than other approaches. 

Kaarel Moppel   

[Fine Tuning Full Text Search with Postgres 12](https://rob.conery.io/2019/10/29/fine-tuning-full-text-search-with-postgresql-12/) — A fun and detailed walk through of making the results of a full text search scenario in Postgres more _relevant_ via a variety of tweaks and considerations. 

Rob Conery   [![\](https://copm.s3.amazonaws.com/67575c72.png)](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary) 

[Free eBook: How to Get a 3x Performance Improvement on Your Postgres Database](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary) — 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   

[Virtual, Computed Columns in Postgres 12](https://rob.conery.io/2019/10/24/virtual-computed-columns-in-postgresql-12/) — Rob is clearly enjoying his time with Postgres 12! Here, he digs into _generated_ columns, essentially _computed_, indexable columns that are stored like any other. _“They’re amazing and in this post I’ll show you how they work..”_ 

Rob Conery   

[Monitoring PostgreSQL Databases Using Percona](https://www.percona.com/blog/2019/10/29/monitoring-postgresql-databases-using-percona-monitoring-management/) — Percona is well known for its professional database services and tools and they have an open source monitoring tool called [PMM](https://www.percona.com/software/database-tools/percona-monitoring-and-management) you can use with Postgres. 

Avinash Vallarapu (Percona)   

[`CASE` Statement Basics by Example](https://blog.panoply.io/postgres-case-statement-basics-by-example) — In [issue 324](https://postgresweekly.com/issues/324) we included a tip about using `CASE` in `ORDER BY` expressions. This basic tutorial provides a slightly broader overview. 

Lori Brok   

[Seamless Application Failover using `libpq` Features in Postgres](https://www.percona.com/blog/2019/10/23/seamless-application-failover-using-libpq-features-in-postgresql/) 

Avinash Vallarapu    

📂**Code and Projects** 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1572434633/ppxtc583nr1t6xpefgvh.png)](http://okbob.blogspot.com/2019/10/watch-mode-for-pspg.html)  

[pspg 2.5.0: Now with 'Watch Mode'](http://okbob.blogspot.com/2019/10/watch-mode-for-pspg.html) — [pspg](https://github.com/okbob/pspg) started life as a simple ‘pager’ for query results output from `psql` but now it can work as a simple Postgres client in its own right and refresh results at a chosen interval. 

Pavel Stěhule   

[Real-Time Postgres Performance Monitoring](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyS) — Collect out-of-the-box and custom Postgres metrics and correlate them alongside traces and logs for full visibility. 

Datadog sponsor   

[`pg_checksums` 1.0 Released](https://www.credativ.com/blog/pgchecksums-10-released) — Data checksums are used to protect data against corruption by checking that checksums match before and after a shutdown, say. 

Michael Banck   

[Windows 64 Bit Binaries for `file_textarray_fdw` and `odbc_fdw`](http://www.postgresonline.com/journal/index.php?/archives/398-PostgreSQL-12-64-bit-for-Windows-FDWs.html#pgfdwindows) 

Leo Hsu and Regina Obe     

🗓**Upcoming Events** 

- [PG Down Under](https://pgdu.org/) (November 15 in Sydney, Australia) — The second outing for this annual, Australian Postgres conference.
- [2Q PGCONF 2019](https://www.2qpgconf.com/) (December 4-5, 2019 in Chicago) — A conference dedicated to exchanging knowledge about the world’s most advanced open source database: PostgreSQL
- [PgDaySF](https://2020.pgdaysf.org/) (January 21, 2020 in San Francisco) — Bringing the PostgreSQL international community to the heart of San Francisco and Silicon Valley.
- [PgConf.Russia](https://pgconf.ru/2020) (Febuary 3-5, 2020 in Moscow, Russia) — One day of tutorials and two days of talks in three parallel sessions.
- [PGConf India](https://pgconf.in/conferences/pgconfin2020) (Febuary 26-28, 2020 in Bengaluru, Maharashtra, India) — A dedicated training day and a multi-track two-day conference.
- [pgDay Paris 2020](https://2020.pgday.paris/#%2319) (March 26, 2020 in Paris, France) — Learn more about the world’s most advanced open source database among your peers.        supported by [![Digital Ocean\](https://res.cloudinary.com/cpress/image/upload/v1570809425/nlqv0ueejagpj7gkft55.png)](https://blog.digitalocean.com/how-we-launched-our-marketplace-using-digitalocean-kubernetes-part-1/?medium=display&utm_source=cooperpress&utm_campaign=MDB_Dev_Signup_Cold_Newsletter10-30-2019)  

💡**Tip of the Week** 

A quicker, more targeted, way to find specific queries in `psql` 

Back in [issue 308](https://postgresweekly.com/issues/308), we shared a tip about using `psql`'s `readline` support to search for old queries. 

For example, pressing `Ctrl+R` and typing `SEL` would bring up the previous `SELECT` query you'd ran, and pressing `Ctrl+R` again and again would go back through older matching examples. If you weren't aware of this, [that tip](https://postgresweekly.com/issues/308) is well worth reading. 

One problem with the `Ctrl+R` technique is that it relies upon searching for something that was present in the query. So if we wanted to add some more contextual information with which to find a query in future, what can we do? 

Comments! SQL lets you add comments on queries, like so:  

SELECT * FROM users; -- find all people   

Now if you do a `Ctrl+R` you could type "find all" (or "people", say) and the query above would appear. It's a simple, contrived example and in the real world you'd use this upon more complex queries which you might confuse with one another 

This week’s Tip of the Week is sponsored by [DigitalOcean](https://blog.digitalocean.com/how-we-launched-our-marketplace-using-digitalocean-kubernetes-part-1/?medium=display&utm_source=cooperpress&utm_campaign=MDB_Dev_Signup_Cold_Newsletter10-30-2019). Find out [how engineers at DigitalOcean built a scalable marketplace for developers](https://blog.digitalocean.com/how-we-launched-our-marketplace-using-digitalocean-kubernetes-part-1/?medium=display&utm_source=cooperpress&utm_campaign=MDB_Dev_Signup_Cold_Newsletter10-30-2019) on top of their managed Kubernetes service.          

Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), 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
