#311 — June 26, 2019

Read on the Web

Postgres Weekly

An Introduction to Hypothetical Indexes — 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 — 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.

PostgreSQL Global Development Group

Demo: What Scaling Out Postgres Can Do for Your Analytics — 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 — 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 — 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 that digs into the specifics, too.

Michael Paquier

Managing Multiple Postgres 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 — 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 — It relies upon a patch by Cybertec that adds TDE support.

Granthana Biswas

Swoop de Dupe: Handling Duplicate Rows in Tables — Sometimes duplicate rows happen inadvertently in live and important tables and drop/recreate/reload is not an option..

Elein Mustain

supported by Percona

💡 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, including how to 'upgrade' your existing tables to using generated columns instead of SERIAL.

This week's tip is sponsored by Percona. Build an enterprise-grade PostgreSQL environment using various open source tools and extensions. Topics covered: security, backup, HA, connection pooling & load balancing, extensions, and detailed logging.

🗓 Upcoming Postgres Events