---
title: "How Postgres 12 improves performance"
publication: "Postgres Weekly"
issue: 321
published_at: "2019-09-04"
canonical_url: "https://postgresweekly.com/issues/321"
---

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

#321 — September 4, 2019 

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

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/nfgxq8b2htpbgfqcp0ue.jpg)](https://github.com/okbob/pspg)   

[pspg: A Unix Pager Designed for Postgres Tables](https://github.com/okbob/pspg) — If you use `psql` you probably use `less` as your pager, but it doesn’t directly support tabular data. This does! The latest master branch version also supports [sorting by column.](http://okbob.blogspot.com/2019/09/pspg-supports-sort-by-selected-column.html) 

Pavel Stehule   

[How to Compute How Many Working Hours in a Date Range with PL/pgSQL](https://fluca1978.github.io/2019/08/30/postgresql_working_hours.html) — How many working hours are there in a range of dates? A perfect, easily digested example of a situation where PL/pgSQL can be very handy! 

Luca Ferrari   [![\](https://copm.s3.amazonaws.com/4f40a0e3.png)](https://pgexperts.com) 

[Because Your Data Is Your Business](https://pgexperts.com) — PGX provides complete PostgreSQL support, from emergency services through daily operational support, to strategic planning. We’re here for your data ops. 

PostgreSQL Experts, Inc. sponsor   

[How PostgreSQL 12 Can Improve Your Performance](https://info.crunchydata.com/blog/just-upgrade-how-postgresql-12-can-improve-your-performance) — _“Just upgrade and take advantage of performance improvements,”_ says the author. Postgres 12 boosts CTE queries, has JIT compilation for queries by default, and indexing is also improved. 

Jonathan S. Katz   

[Why You Need `plpgsql_check` if You Write Procedures in PL/pgSQL](http://okbob.blogspot.com/2019/08/why-you-need-plpgsqlcheck-if-you-write.html) — PL/pgSQL lets you write and execute some pretty advanced logic directly within Postgres and SQL queries and `plpgsql_check` can help you with performance and SQL injection problems. 

Pavel Stěhule   

[Running PostgreSQL on Google Cloud Platform (GCP)](https://severalnines.com/database-blog/cloud-vendor-deep-dive-postgresql-google-cloud-platform-gcp) — A look at the features Google’s platform offers for Postgres users. 

Viorel Tabara   

[How to Set Up Postgres 12 Beta 3 for Testing on Amazon RDS](http://jkshah.blogspot.com/2019/08/setting-up-postgresql-12-beta-3-for.html) — AWS offers beta versions of Postgres for testing in a ‘preview environment’ in their us-east-2 region. 

Jignesh Shah   

[IT Monitoring in the Era of Containers: Tapping into eBPF Observability](https://www.influxdata.com/blog/it-monitoring-in-the-era-of-containers-tapping-into-ebpf-observability/) 

InfluxData sponsor   

[Postgres Connection Strings and `psql`](https://tapoueh.org/blog/2019/09/postgres-connection-strings-and-psql/) — Did you know there are _three_ different ways to establish a connection to the same database? A key/value approach, the popular ‘URL’ approach, and using command line options with `psql`. 

Dimitri Fontaine   

[Setting Up Remote Backup and Restore with pgBackRest](https://www.cybertec-postgresql.com/en/remote-backup-and-restore-with-pgbackrest/) 

Granthana Biswas   

[Retroactively Adding Binary I/O to a Type in Postgres](http://www.myrkraverk.com/blog/2019/08/postgresql-retroactively-adding-binary-i-o-to-a-type/) 

Johann 'Myrkraverk' Oskarsson   

[`PERIOD`s and `SYSTEM VERSIONING` for Postgres](https://github.com/xocolatl/periods) — _Periods_ are a SQL:2016 feature that are a bit like Postgres’s range types and this extension enables some basic support in Postgres ahead of an official implementation. 

Vik Fearing      supported by [![Percona\](https://res.cloudinary.com/cpress/image/upload/v1559053486/vmjp4kyjuomp5klwidlw.png)](https://learn.percona.com/cooperpress-upgrade-or-migrate-your-postgresql-database-through-a-lowest-possible-downtime)  

💡**Tip of the Week** 

A basic example of using `DISTINCT ON` 

[Last week](https://postgresweekly.com/issues/320), our tip was about `SELECT DISTINCT`, a way to easily select unique values from a column without using `GROUP BY`. This week, we take `DISTINCT` a step further! 

Where `SELECT DISTINCT` selects unique values from a column, `SELECT DISTINCT ON` selects the first rows that can _**distinctly** match a variety of conditions_. This is hard to explain in writing alone, so let's try the simplest of examples. 

Let's create a table to store temperatures in different locations. In the real world, you'd probably have a time-related column too, but we'll skip it for brevity:  

CREATE TABLE temperatures
 (location TEXT,
 temp INT);
 
 INSERT INTO temperatures
 (location, temp)
 VALUES
 ('Springfield', 71),
 ('Springfield', 74),
 ('Springfield', 64),
 ('Springfield', 56),
 ('Miami', 84),
 ('Miami', 77),
 ('Miami', 91);   

Now, let's say we want to only **get the highest temperature recorded in each location.** `SELECT DISTINCT ON` to the rescue:  

SELECT DISTINCT ON(location) * 
 FROM temperatures
 ORDER BY location, temp DESC;
 
 location | temp
 --------------------
 Miami | 91 
 Springfield | 74   

If we _hadn't_ used `DISTINCT ON` we would have got all of the locations and temperatures with the temperatures in descending order. `DISTINCT ON(location)`, however, stripped the results down to the first matching result for each location, netting us the highest temperature for each location. 

This Tip of the Week is sponsored by [Percona](https://learn.percona.com/cooperpress-upgrade-or-migrate-your-postgresql-database-through-a-lowest-possible-downtime). Learn how to [upgrade or migrate your PostgreSQL database through a lowest possible downtime](https://learn.percona.com/cooperpress-upgrade-or-migrate-your-postgresql-database-through-a-lowest-possible-downtime) with Percona.      

🗓**Upcoming Events** 

- [PostgreSQL Conference Asia 2019](http://2019.pgconf.asia/) (September 8-11 in Bali, Indonesia)
- [PostgresOpen 2019](https://2019.postgresopen.org/) (September 11-13, in Orlando, Florida) — Two days packed with tutorials and presentations about PostgreSQL and related tech.
- [PostgresConf Silicon Valley 2019](https://postgresconf.org/conferences/SV2019) (September 18-20 in San Jose) — The schedule (including training) has now been published.
- [PostgresConf South Africa 2019](https://postgresconf.org/conferences/SouthAfrica2019) (October 8-9 in Johannesburg) — An opportunity for the database administration and developer community who use Postgres to get to know each other.
- [PostgreSQL Conference Europe 2019](https://2019.pgconf.eu/) (October 15-18 in Milan, Italy)          

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