--- title: "Making schema changes in Postgres without downtime" publication: "Postgres Weekly" issue: 291 published_at: "2019-02-06" canonical_url: "https://postgresweekly.com/issues/291" --- The latest Postgres news, articles, tools, and projects. #291 — February 6, 2019 [Read on the Web](https://postgresweekly.com/issues/291) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/d6li9nzua3jpxwctjydp.jpg)](https://www.youtube.com/watch?v=MJ_y5Ej-5vE) ▶[PostgreSQL Goes to Eleven](https://www.youtube.com/watch?v=MJ_y5Ej-5vE) — 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](https://medium.com/braintree-product-technology/postgresql-at-scale-database-schema-changes-without-downtime-20d3749ed680) — 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 [![\](https://copm.s3.amazonaws.com/444751f9.jpg)](https://www.springboard.com/workshops/ai-machine-learning-career-track/?utm_source=postgresweekly&utm_medium=email&utm_content=postgresweekly-1) [Get a Machine Learning Job, Guaranteed](https://www.springboard.com/workshops/ai-machine-learning-career-track/?utm_source=postgresweekly&utm_medium=email&utm_content=postgresweekly-1) — 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](https://news.ycombinator.com/item?id=19086626) — 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](https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres.html) and did a full writeup. Hacker News [Using Postgres with Passphrase-Protected SSL Keys Under `systemd`](https://blog.2ndquadrant.com/postgresql-passphrase-protected-ssl-keys-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](https://www.cybertec-postgresql.com/en/implementing-as-of-queries-in-postgresql/) — 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'](https://www.enterprisedb.com/blog/definitive-guide-database-platforms-service) — Get your copy of the report. EnterpriseDB sponsor [How Long Would a 64 Bit Transaction-ID Last in Postgres?](https://andreas.scherbaum.la/blog/archives/970-How-long-will-a-64-bit-Transaction-ID-last-in-PostgreSQL.html) — 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](https://news.ycombinator.com/item?id=19083745) though._ Andreas 'ads' Scherbaum [The Life of a SQL Query](https://numeracy.co/blog/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](https://www.percona.com/blog/2019/02/04/pg_repack-rebuild-postgresql-database-objects-online/) Avinash Vallarapu supported by [![\](https://res.cloudinary.com/cpress/image/upload/v1549285104/cdttwslojuv9zso9nswk.png)](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyT) 💡 Tip of the Week Local cross-database `SELECT` and `INSERT` _A tip from [Alan Au](https://twitter.com/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](https://www.postgresql.org/docs/9.3/dblink.html) 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](http://www.craigkerstiens.com/2016/09/11/a-tour-of-fdws/) 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](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyT), a cloud monitoring platform for PostgreSQL infrastructure, distributed tracing and logging. [Start a free trial.](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyT) 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