đŸ€­ Believe it or not, next week is the last full week before Christmas.. so next week we'll be doing a roundup of the most interesting and newsworthy items of 2020 before taking our Christmas break. See you then! :-)
Peter Cooper, your editor

#385 — December 9, 2020

Web Version

Postgres Weekly

A Seamless MongoDB to PostgreSQL Migration — Coinbase is a large digital currency exchange so having a solid foundation for its data is a must. Here they share some lessons learned from a large cross-database data migration to AWS RDS PostgreSQL.

Alex Ghise (Coinbase)

Replacing Lines of Code with 2 Little Regexes in Postgres — I love them but the term ‘regular expression’ admittedly sends many developers running for the hills. Is there a good use for them with Postgres? Yes, and Steve shows one off here.

Steve Pousty

▶ Future of Database Architecture is Postgres Compatible — The language and the tools that you use with Postgres are important for your efficiency. Join us on Dec 16 for a live tech talk to learn how and why database architecture is evolving without leaving Postgres behind. Save your spot.

CockroachDB sponsor

When to Use Hyperscale (Citus) to Scale Out Postgres — Citus by Citus Data is now a part of Microsoft (though remains open source) and is a powerful way to horizontally scale Postgres. In this article, Claire explains when and why you’d need this sort of power.

Claire Giordano (Microsoft)

Cleaning Up the BLOBs — BLOB is the SQL standard’s binary string type which differs slightly to Postgres’s bytea approach, but either way, you need to take care if using the BLOB interface (such as via lo_import) for bringing binary data into your database because imported data can be left sitting around in your database even when you thought you deleted it.

Hans-JĂŒrgen Schönig

Using Postgres Aggregate Functions in YugabyteDB to Analyze COVID-19 Data — Yugabyte is a Postgres-compatible ‘NewSQL’ database and here’s a practical look at using SQL functions for linear regression analysis of coronavirus statistics.

Bryn Llewellyn

A V4 UUID Generation Benchmark — uuid_generate_v4 vs gen_random_uuid.. fight! And the result is not even close.

Shane Husson

Wondering How You Should Optimize a Particular Query? — pganalyze uses auto_explain to automatically collect EXPLAIN plans. Identify slow sequential scans, sorts on disk & more.

pganalyze sponsor

Restoring An Individual Postgres Table — What if you want to restore an individual table from a dump?

Thomas Vilhena

Upgrading and Updating Postgres — Updating (such as from 12.0 to 12.1, say) is a different ball game to upgrading (such as from 12 to 13) and there are a few tools to consider.

Hans-JĂŒrgen Schönig

Comparing Options for Bulk Loading into Postgres — A comparison of COPY, \copy, file_fdw, and pg_bulkload for loading in 624MB of CSV data under different scenarios.

Muhammad Usama

🔧 Tools and Code

A Look at pgtop, A top Clone for Postgres — Another way to look at currently running queries.

Cosimo Streppone

pgagroal 1.0: High-Performance Connection Pool for Postgres — Has a lot of features and makes a lot of promises, including being extremely fast. — GitHub repo.


pg-shortkey: YouTube-Like Short IDs as Postgres Primary Keys — A trigger for your database that lets you use YouTube-like URL-safe short IDs (e.g. oHg5SJYRHA0) as primary keys, if you wish. We first linked this a few months ago but Hacker News found it and a fun discussion ensued.


pg-listen: Postgres LISTEN and NOTIFY for Node.js — If you’re using Node.js and wish to use Postgres as a message broker with its notification channels, this will help. (If you’re unfamiliar with this Postgres feature, this article will help.)

Andy Wermke