#291 — February 6, 2019

Read on the Web

Postgres Weekly

▶  PostgreSQL Goes to Eleven — Joe Conway’s talk from FOSDEM not only highlights all the great new features included within Postgres 11 but digs into some of the history and looks forward to Postgres 12 too. 48 minutes.

Joe Conway

PostgreSQL at Scale: Database Schema Changes Without Downtime — Braintree Payments, a division of PayPal, heavily uses Postgres and due to offering payment processing services, zero downtime is a must. Here’s a deep dive into all the things involved in making schema updates without downtime.

James Coleman

Get a Machine Learning Job, Guaranteed — Take advantage of the first online machine learning course with 1:1 mentorship from machine learning experts and a job guarantee. Get a machine learning job or your money back.

Springboard sponsor

MailChimp's Mandrill Service Experiences Outage Due to Transaction ID Wraparound — This is something you really need to be aware of if using Postgres at scale and aren't fully in tune with your autovacuuming setup. Sentry experienced a similar outage in 2015 and did a full writeup.

Hacker News

Using Postgres with Passphrase-Protected SSL Keys Under systemd — A lot of people don’t use passphrases with their keys but if you want to, how can you get it working when Postgres is started with systemd?

Peter Eisentraut

Implementing “AS OF”-queries in Postgres — Many databases such as Oracle provide “AS OF” queries (a.k.a. ‘time travel’). This blog explains how to mimic a similar thing in Postgres.

Hans-Jürgen Schönig

Now Available: Gartner's 'Definitive Guide for Database Platforms as a Service' — Get your copy of the report.

EnterpriseDB sponsor

How Long Would a 64 Bit Transaction-ID Last in Postgres? — Postgres's MVCC implementation means it can keep multiple versions of data at a time allowing for concurrent transactions to take place. This requires maintaining a set of active transaction IDs. But means TXID wrap-around can happen (see Mandrill story above), so what if Postgres used a 64-bit id instead of 32-bit? Spoiler: it's not likely to change though.

Andreas 'ads' Scherbaum

The Life of a SQL Query — What happens when you run a SQL statement? We follow a Postgres query transformation by transformation as a query is processed and results are returned.

Eric Feng

Using pg_repack to Rebuild Postgres Database Objects Online

Avinash Vallarapu

supported by

💡 Tip of the Week

Local cross-database SELECT and INSERT
A tip from Alan Au

Have you ever wanted to SELECT from one local database and INSERT the results into another? You can do this with the dblink extension that comes with Postgres itself.

First, you need to set up the dblink extension on both databases:

CREATE EXTENSION dblink;

Then run a query such as this from your local (or 'destination') database:

INSERT INTO my_table
SELECT * FROM dblink(
'dbname=my_other_db options=-csearch_path=',
'SELECT some_data FROM my_other_table;')
AS temp(some_data TEXT);

Note that postgres_fdw provides a more generic and modern way to do a similar thing, particularly if you want to connect to external databases, but dblink may well suit your needs for a quick, local job.

The Tip of the Week is sponsored by Datadog, a cloud monitoring platform for PostgreSQL infrastructure, distributed tracing and logging. Start a free trial.