Postgres Weekly Issue 382: November 18, 2020

#382 — November 18, 2020

pg_timetable v3 Released — An advanced PostgreSQL cron-compatible scheduler now with some new features including improved scheduling (e.g. ‘every 6 hours’), an option to forbid shell tasks, and interval tasks that can be run repeatedly.

Pavlo Golub

Migrating Large Heroku Postgres Instances to AWS Aurora without Downtime — Heroku can make ops easy but the flexibility of RDS is appealing when you need to scale, reduce costs, or if there are other parts of the AWS ecosystem you want to work with. There’s a lot to process and enjoy here as there were a lot of moving parts.

Adam McQuistan

Postgres Build 2020 - PostgreSQL Online Conference [Free] — Listen to 25+ talks on the latest in PostgreSQL and on how PostgreSQL helps businesses. This conference will also provide wellness sessions to promote health and revitalize. Join us from December 8 - 9. Register now.

EDB sponsor

Postgres 13.1, 12.5, 11.10, 10.15, 9.6.20, and 9.5.24 Released — Updates to all of the currently maintained lines of Postgres to tackle three security vulnerabilities (plus the usual small bug fixes). Upgrading is urged in particular due to one issue allowing a user with permission to create non-temporary objects to then execute arbitrary SQL functions under the identity of a superuser.

PostgreSQL News

What’s New in the Citus 9.5 Extension to Postgres — A walk through everything new in the Citus 9.5 open source extension that scales out Postgres horizontally. From Postgres 13 support to undistribute_table to adaptive connection management for COPY, and more.

Claire Giordano (Microsoft)

Google Cloud SQL Adds Postgres 13 Support — Google’s fully managed Postgres database service can now run on the latest version.

Google Cloud

Do This 10 Times And Stop... In Postgres — This is a really neat psql trick involving using \gexec to run the result of a query as a new query itself.

Robert Treat

Full-Text Search Since PostgreSQL 8.3 — Postgres has had full text search functionality for some time and it continues to get better. This post looks at its history and runs benchmarks over each key version from 8.3 to 13.

Tomas Vondra

⚡️ 13 Tips to Improve PostgreSQL INSERTS — Get our top ways to speed up INSERTs, from optimizing disk performance to using parallel writes + batch inserts 🚀

Timescale sponsor

macOS Big Sur Allegedly Breaking Postgres Installations — The latest version of macOS may be causing issues with existing Postgres installs.. or it may not. If it is for you, maybe this tip will help.

Dave Page

Postgres Streaming Replication on Windows: A Quick Guide
Yorvi Arias

Some Performance Improvements Coming to PostGIS 3.1
Paul Ramsey

pgtt: Extension to Manage and Use Oracle-Style Global Temporary Tables
Gilles Darold

supported by

💡 Tip of the Week

Speed up batch inserts when using JDBC

For Java developers using the PostgreSQL JDBC driver, you can significantly speed up batch operations with the reWriteBatchedInserts connection parameter.

If reWriteBatchedInserts=true, the JDBC driver will rewrite batch inserts as multi-line inserts in a way that limits the number of calls to the database.

For example, the driver changes batch inserts from:

    insert into foo (col1, col2, col3) values (1,2,3);

insert into foo (col1, col2, col3) values (4,5,6);

Into:

    insert into foo (col1, col2, col3) values (1,2,3), (4,5,6);
    

When used correctly, reWriteBatchedInserts can improve batch insert performance by as much as 2-3x. Vlad Mihalcea also wrote more about reWriteBatchedInserts last year if you want a more extended example.

This week’s tip is sponsored by Cockroach Labs, Learn about CockroachDB in this demo: “Distributed SQL: a modern, cloud-native PostgreSQL.”