Postgres Weekly Issue 346: March 11, 2020

#346 — March 11, 2020

Postgres Subtransactions and Performance — This article shows how the overuse of sub-transactions can harm performance and how to diagnose if you are suffering from such problems.

Laurenz Albe

PostgreSQL Anonymizer 0.6: Pseudonymization and Improved Anonymous Exports — PostgreSQL Anonymizer is an extension that hides or replaces personally identifiable information (PII) or commercially sensitive data. The new addition in 0.6 is pseudonymization, basically deterministic data faking.

Dalibo Labs

Visualize Postgres Metrics with Customizable Dashboards in Datadog — Collect and monitor in real-time slow-running queries, bottlenecks, and other errors with granular analytics in Datadog. Visualize Postgres performance alongside the rest of your infrastructure. Try Datadog free.

Datadog sponsor

Handling NULL Values in Postgres — A few weeks ago we linked to a useful look at what NULL means in SQL. This post goes a little more technical comparing things with C and Java and some things you can use in queries to work with NULL values.

Ibrar Ahmed

Fear-Free Postgres Migrations for Ruby on Rails — The ‘fear-free’ element here is knowing how to deal with schema migration problems that can break future queries.

James Turley (GoCardless)

🥇 Some Golden Oldies

As well as tracking news and new projects, we also like to keep hold of older resources that remain useful and interesting. Here are some of the highlights:

Actually Understanding Timezones in Postgres by Chris Clark.
Here's a little look at SQL/JSON paths.
Things not to do when using Postgres.
explain.depesz.com is a tool for pasting in your EXPLAIN ANALYZE results for better visualization.
The periodic table of psql commands.
Looking Back at Postgres ^[PDF] is a paper recollecting thoughts about the earliest days of the Postgres project and the ideas that brought it together.
And it's not that old, but I still really like Bruce Momijan's splendid PDF that's packed with info on Postgres 12's major new features.

Thinking About psycopg3 — psycopg is a popular Postgres adapter used in the Python world and its long-time maintainer is beginning to think about what they’d do in an all new version incompatible version 3. One for curious Pythonistas only.

Daniele Varrazzo

My PostgreSQL Database is Out of Disk Space — How to detect a PostgreSQL out of disk issue, how to prevent it and fix it if needed.

Sebastian Insausti

eBook: Best Practices for Optimizing Postgres Query Performance — Learn how to get a 3x performance improvement on your Postgres database and 500x reduced data loaded from disk in this free pganalyze eBook.

pganalyze sponsor

An Interview with Christophe Pettus — Christophe, CEO of PostgreSQL Experts, is the latest “PostgreSQL Person of the Week” to get asked about his favorite extension, tools, and Postgres resources.

Andreas Scherbaum

▶ Make Your Postgres App Multi-Region — If you’re around on March 13 (this Friday) at 1pm Eastern, CockroachDB are doing a live talk on Twitch.

Twitch

🗓 Upcoming Events

Nordic PgDay 2020 (March 24 in Helsinki, Finland)
pgDay Paris 2020 (March 26 in Paris, France)
Swiss PGDay 2020 (June 18-19 in Switzerland) — A two track conference (one in English, one in German) aimed at the entire Postgres community.
Postgres Vision 2020 (June 22-24 in Boston, MA)
Postgres Ibiza 2020 (June 25-26 in Ibiza, Spain) — A two day conference on the sunny island of Ibiza in the Mediterranean. I recall this one getting some great trip reports last year!

Note: Due to the travel disruption caused by the ongoing COVID-19 outbreak, please check the status of events you are attending or wish to attend. We are checking each event we list prior to publication, but the situation can change quickly.