#343 — February 19, 2020

Read on the Web

Postgres Weekly

pg_flame: A Flamegraph Generator for EXPLAIN ANALYZE's Output — Reads a JSON query plan from stdin and writes the HTML for the flamegraph HTML to stdout. Available to use via Docker, if you want to keep things simple.

Marcus Gartner

Postgres 12.2, 11.7, 10.12, 9.6.17, 9.5.21, and 9.4.26 Released — A true smorgasbord of versions here, with basically every major production of Postgres getting an update to fix a critical security issue, as well as variety of minor bug fixes. This is also Postgres 9.4’s last release with 9.4 now at its “end of life”.

PostgreSQL Global Development Group

Monitor Custom Postgres Metrics in Real-Time with Datadog — Monitor and visualize Postgres performance in context end-to-end alongside the rest of your stack. Create custom, drag-and-drop dashboards to quickly view analytics on any Postgres metric. Try Datadog free.

Datadog sponsor

Isolation Repeatable Reads in Postgres vs MySQL — By default, Postgres and MySQL use different levels of isolation within transactions. Within a running transaction, Postgres will see changes made by other transactions once committed, whereas MySQL uses “repeatable read” where the results of a query remain consistent throughout a transaction. There’s a bit more to it than that, though, and this post deftly shows us how it all works.

Daniel Vérité

An Experience of Migrating to CockroachDB from Postgres — We love Postgres, but we love a story even more, so it’s interesting to hear about the experiences this engineer had when shifting from Postgres to CockroachDB (for the reason of better “out of the box support for high availability”).

Karl Seguin

Useless Vacuuming — What can you do when autovacuum seems to be running fine, but there are still vacuum related problems? Robert explains such a situation and how to resolve it.

Robert Haas

Take Advantage of pg_settings When Dealing with Your Config — Just looking at your config files or assuming you know what the default settings are is risky. The pg_settings view gives you an authoritative view of what’s going on.

Luca Ferrari

The Why and How of Compressing WAL Archives — Why the compression of WAL archives is becoming more important, and how to do it.

Jobin Augustine

Postgres Vision 2020 - Get Your Early Bird Tickets — The leading Postgres conference will be back in Boston this year from June 22-24. Get a 2-day conference pass for $199.

EnterpriseDB sponsor

cloudfs_fdw: A Foreign Data Wrapper for Accessing Files on Cloud Filesystems — Specifically, it handles CSV, JSON, Excel and ODF files on S3 or over HTTP(s).

Ernst-Georg Schmid

Schemalint: A Linter for Postgres Schemas — A linter that lets you raise errors on issues like casing or column types (so think more like ESLint than a basic formatter).

Kristian Dupont

wal2json: A JSON Output Plugin for Changeset Extraction — A JSON object is produced per transaction (or tuple) containing both the new and old tuples for updates.

Euler Taveira de Oliveira

supported by PostgresConf

💡 Tip of the Week

Quickly seeing the size of your database(s)

A very quick tip this week (after the gargantuan scrollfest of a tip in issue 341)! Want to quickly see how big a database is? You can do it in a single query:

select pg_size_pretty(pg_database_size('n2'));

 446 MB
(1 row)

Replace n2 with the name of your database of choice. Or, if you prefer, get the results for all of the databases you can access on your server like so:

select name, pg_size_pretty(pg_database_size(datname)) as size
postgres-# from pg_database;
      name | size
  postgres | 7079 kB
 template1 | 7079 kB
 template0 | 6969 kB
        n2 | 446 MB

If you want to go deeper by looking at specific table or index sizes, head back to our more in-depth tip back in issue 294.

This week's tip is sponsored by PostgresConf. Postgres Conference 2020: The largest gathering about People, Postgres, and Data. Join 800+ attendees and 100+ speakers to build opportunities for the global Postgres ecosystem.

🗓 Upcoming Events

  • PgConf India (Febuary 26-28 in Bengaluru, India) — A dedicated training day and a multi-track two-day conference.
  • Postgres Conference 2020 (March 23-27 in New York, United States) — A broad and large scale (indeed, the largest, they say) Postgres event.
  • Nordic PgDay 2020 (March 24 in Helsinki, Finland)
  • pgDay Paris 2020 (March 26 in Paris, France) — The schedule for this event is now available.
  • 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 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!

If you are running a Postgres related event that's beyond the scale of a small local meetup or user group (so, say, 50+ people with people likely to travel to your event from afar), let us know and we can include it here in future issues.