Postgres Weekly Issue 329: October 30, 2019

#329 — October 30, 2019

Performing a Major Version Upgrade with Logical Replication — If you’re just looking to upgrade to Postgres 12, start here, 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 — 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

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

Virtual, Computed Columns in Postgres 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 — Percona is well known for its professional database services and tools and they have an open source monitoring tool called PMM you can use with Postgres.

Avinash Vallarapu (Percona)

CASE Statement Basics by Example — In issue 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

Avinash Vallarapu

📂 Code and Projects

pspg 2.5.0: Now with 'Watch Mode' — 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 — 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 — 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

Leo Hsu and Regina Obe

🗓 Upcoming Events

PG Down Under (November 15 in Sydney, Australia) — The second outing for this annual, Australian Postgres conference.
2Q PGCONF 2019 (December 4-5, 2019 in Chicago) — A conference dedicated to exchanging knowledge about the world’s most advanced open source database: PostgreSQL
PgDaySF (January 21, 2020 in San Francisco) — Bringing the PostgreSQL international community to the heart of San Francisco and Silicon Valley.
PgConf.Russia (Febuary 3-5, 2020 in Moscow, Russia) — One day of tutorials and two days of talks in three parallel sessions.
PGConf India (Febuary 26-28, 2020 in Bengaluru, Maharashtra, India) — A dedicated training day and a multi-track two-day conference.
pgDay Paris 2020 (March 26, 2020 in Paris, France) — Learn more about the world’s most advanced open source database among your peers.

supported by

💡 Tip of the Week

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

Back in issue 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 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. Find out how engineers at DigitalOcean built a scalable marketplace for developers on top of their managed Kubernetes service.