---
title: "New releases of Postgres to fix a security vulnerability"
publication: "Postgres Weekly"
issue: 311
published_at: "2019-06-26"
canonical_url: "https://postgresweekly.com/issues/311"
---

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

#311 — June 26, 2019 

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

Postgres Weekly  

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/ssanjh8nvme1bq9vcfog.jpg)](https://www.percona.com/blog/2019/06/21/hypothetical-indexes-in-postgresql/)   

[An Introduction to Hypothetical Indexes](https://www.percona.com/blog/2019/06/21/hypothetical-indexes-in-postgresql/) — Why _would_ you want to create imaginary indexes for Postgres’s optimizer to chew over? It’s a way to find out if an index would be useful before you go to the computational and storage expense of creating a real one. 

Avinash Vallarapu   

[Postgres 11.4, 10.9, 9.6.14, 9.5.18, 9.4.23, and 12 Beta 2 Released](https://www.postgresql.org/about/news/1949/) — A bumper set of releases for Postgres.. and it’s all down to fixing a single security vulnerability by which a user can cause a buffer overflow by changing their password to a specially crafted value. Some other minor bugfixes have been baked in too, of course. [Here’s more info on the vulnerability](https://info.crunchydata.com/blog/explaining-cve-2019-10164-with-postgresql-security-best-practices). 

PostgreSQL Global Development Group   [![\](https://copm.s3.amazonaws.com/1f883368.png)](https://www.youtube.com/watch?v=-cbT64D9KFk) 

[Demo: What Scaling Out Postgres Can Do for Your Analytics](https://www.youtube.com/watch?v=-cbT64D9KFk) — Short 4 minute video showing how analytics pipelines benefit from scaling out Postgres horizontally & parallelizing your workload, using a 10-node Hyperscale (Citus) database cluster on Azure Database for PostgreSQL, with 320 cores & 2.5 TB of memory. 

CITUS DATA, NOW PART OF MICROSOFT sponsor   

PDF 📄 [The Major Features in Postgres 12](https://momjian.us/main/writings/pgsql/features.pdf) — The much esteemed Postgres expert Bruce Momijan has released a simple slidedeck highlighting the most significant improvements and features coming to PostgreSQL 12, including JIT compilation and `REINDEX CONCURRENTLY`. It’ll only take you a minute to scan. 

Bruce Momijan   

[Another Postgres 12 Highlight: SQL/JSON Paths](https://paquier.xyz/postgresql-2/postgres-12-jsonpath/) — The next version of Postgres is shipping with support for the SQL/JSON path language which provides a more standards-compliant way to address parts of JSON documents (such as may be stored in a `JSONB` column). There’s [a fantastic slidedeck here](http://www.sai.msu.su/~megera/postgres/talks/jsonpath-pgday.it-2019.pdf) that digs into the specifics, too. 

Michael Paquier   

[Managing Multiple Postgres Instances on Ubuntu/Debian](https://www.percona.com/blog/2019/06/24/managing-multiple-postgresql-instances-on-ubuntu-debian/) — There are a variety of ways to do this, here’s one developer’s approach using the `postgresql-common` package and its suite of cluster management tools. 

Jobin Augustine   

[Free Whitepaper: How to Deploy Open Source Databases](https://severalnines.com/resources/whitepapers/how-deploy-open-source-databases?utm_campaign=MariaDB_Campaign_JUN19&utm_content=pg_weekly&utm_medium=Paid_Search&utm_source=banner) — Learn from the experts how to deploy a variety of open source DBs using proven methodologies to get battle-tested results. 

Severalnines sponsor   

[How to Set Up Postgres 9.6 with Transparent Data Encryption](https://www.cybertec-postgresql.com/en/install-postgresql-9-6-with-transparent-data-encryption/) — It relies upon [a patch by Cybertec](https://www.cybertec-postgresql.com/en/products/postgresql-instance-level-encryption/) that adds TDE support. 

Granthana Biswas   

[Swoop de Dupe: Handling Duplicate Rows in Tables](https://www.2ndquadrant.com/en/blog/swoop-de-dupe/) — Sometimes duplicate rows happen inadvertently in live and important tables and drop/recreate/reload is not an option.. 

Elein Mustain      supported by [![Percona\](https://res.cloudinary.com/cpress/image/upload/v1559053486/vmjp4kyjuomp5klwidlw.png)](https://www.percona.com/)  

💡 Tip of the Week 

The modern alternative to `SERIAL` columns 

When used in a table definition (e.g. with `CREATE TABLE`), `SERIAL` is not a data type but provides a convenient way to generate an integer sequence and associate it with a column in that table.  

CREATE TABLE people (
 id SERIAL PRIMARY KEY,
 name text
 );   

`SERIAL` continues to work fine, but since Postgres 10 we've been provided with a more SQL standards-compliant way of reaching the same outcome:  

CREATE TABLE people (
 id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
 name text
 );   

It's more verbose, but has better inter-database compatibility, you get to specify your preferred type, and you no longer need to work out the name of the associated sequence if you want to work with it at all (such as to set unique permissions on the sequence). 

For example, let's say you wanted to set a new start value for the sequence, you can now do:  

ALTER TABLE people6 ALTER COLUMN id RESTART WITH 10;   

There's [more information on this whole topic here](https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/), including how to 'upgrade' your existing tables to using generated columns instead of `SERIAL`. 

This week's tip is sponsored by [Percona](https://www.percona.com/). Build an enterprise-grade PostgreSQL environment using various open source tools and extensions. Topics covered: [security](https://www.percona.com/blog/2018/09/21/securing-postgresql-as-an-enterprise-grade-environment/), [backup](https://www.percona.com/blog/2018/09/25/postgresql-backup-strategy-enterprise-grade-environment/), [HA](https://www.percona.com/blog/2018/09/28/high-availability-for-enterprise-grade-postgresql-environments/), [connection pooling & load balancing](https://www.percona.com/blog/2018/10/02/scaling-postgresql-using-connection-poolers-and-load-balancers-for-an-enterprise-grade-environment/), [extensions](https://www.percona.com/blog/2018/10/05/postgresql-extensions-for-an-enterprise-grade-system/), and [detailed logging](https://www.percona.com/blog/2018/10/08/detailed-logging-for-enterprise-grade-postgresql/).      

🗓 Upcoming Postgres Events 

- [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)
- [PGConf.Brasil 2019](https://www.pgconf.com.br/2019/en/) (August 1-3 in São Paulo) — A three day PG conference with talks, tutorials, courses, Couch Clinic, and lightning talks.
- [PGDay Austria 2019](https://pgday.at/en/) (September 6 in Wiener Neustadt, Austria)          

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