#339 — January 22, 2020

Read on the Web

Postgres Weekly

Chasing a Postgres Performance Issue (Twitter Thread) — A developer found that index-only scans on a database got 3x slower after running a newly optimized ETL process and set out to establish why.. an interesting case of chasing a performance issue.

Felix Geisendörfer on Twitter

pgwatch2 v1.7.0 Released — pgwatch2 is a popular Postgres metrics dashboard and monitoring system and 1.7.0 brings in some neat features like log parsing and extra dashboards.

Kaarel Moppel

Your Data Is Your Business — PGX is a full-service database consultancy focused around PostgreSQL systems. Let us help you architect, build, troubleshoot and scale your PostgreSQL data systems on any platform or hosting environment.

PostgreSQL Experts, Inc. sponsor

VACUUM in PostgreSQL 13 to Process Indexes in Parallel — In future, Postgres’s vacuum will be able to use multiple CPUs and cores to process indexes in parallel which could yield significant speedups. (That aside, the way Hubert sets up his test data in this example is pretty neat!)

Hubert depesz Lubaczewski

Effectively Using Materialized Views in Ruby on Rails — Postgres’s views and materialized views provide easy alternative ways of looking at a database’s underlying data through the lens of a persistent query, and working with them in Rails is not too tricky.

Leigh Halliday

Tips on How to Get Your (Postgres) Conference Talk Selected — 16 tips from someone who was instrumental in choosing talks for this week’s PGDay SF conference. These tips will be useful for any future event IMHO.

Claire Giordano

Different Ways of 'Sleeping' in Postgres — A quick look at pg_sleep, pg_sleep_for and pg_sleep_until which do what you’d guess they would.

Andreas 'ads' Scherbaum

Introducing Automated Postgres EXPLAIN Plan Insights on pganalyze — pganalyze can now automatically collect Postgres EXPLAIN plans and provide a visual representation of your costliest plan nodes.

pganalyze sponsor

Deploying High-Availability PostgreSQL Clusters on Kubernetes by Example — This leans on Crunchy Data’s (open source) PostgreSQL Operator for Kubernetes.

Jonathan S. Katz

Supabase: Adds Realtime and RESTful APIs to Existing Postgres Databases — An Elixir-powered server that allows you to listen to inserts, updates, and deletes using WebSockets.

Supabase

pg_probackup: Backup and Recovery Manager for Postgres

Postgres Professional

supported by Digital Ocean

💡 Tip of the Week

NOTNULL and ISNULL

Kaarel Moppel has put together a Tips and Tricks to Kick Start Postgres in 2020 article which has a variety of interesting tips and is worth checking out. But the shortest and sweetest tip jumped out at me and I wanted to include it here :-)

When dealing with NULL values in SQL, you have to use things like IS NULL or IS NOT NULL in your predicates:

SELECT * FROM people WHERE age IS NOT NULL;

But did you know that Postgres supports a shorter (though non-standard) approach if you want to save some characters?

SELECT * FROM people WHERE age NOTNULL;

There's also ISNULL for the IS NULL case, although the numbers of characters saved is somewhat reduced ;-)

And if you've ever wondered why you can't just go age = NULL or age != NULL, this is because NULL is defined to be an 'unknown' value and the SQL standard dictates that you can't compare an 'unknown' value with another.

This tip of the week is sponsored by Heroku, the cloud platform by developers, for developers.

🗓 Upcoming Events

  • PgConf.Russia (February 3-5 in Moscow, Russia) — One day of tutorials and two days of talks in three parallel sessions.
  • PgConf India (Febuary 26-28 in Bengaluru, India) — A dedicated training day and a multi-track two-day conference.
  • 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.

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.

🏙 A good first PgDay SF

We sponsored PgDay SF this year and it seems to have gone well! :-) If you went and blogged about it, let us know so we can link in future issues.