Gravatars and Postgres 12 using Generated Columns

Generated columns are an exciting new feature of Postgres. They allow you to replace some of your existing trigger logic in a faster and easier to read way.

create table users (
  id serial primary key,
  name text not null,
  email text not null,
  avatar_url text generated always as ('http://gravatar.com/avatar/' || md5(email)) stored,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now());

The interesting part of this block of code is where the avatar_url column is being created. It is creating a new column that is generated and stored on disk using the value of another column (email). That also means if a user's email is updated, the avatar_url will also be updated with the correct Gravatar url. This functionality was added in Postgres 12.

Let's say we decide to show an avatar that is more optimized for our app. Gravatar allows you to pass a size parameter in pixels for the returned image. This can be accomplished with Gravatar by adding s=200 as a query parameter. All this takes is altering the column.

alter table users drop avatar_url;
alter table users add column avatar_url text generated always as ('http://gravatar.com/avatar/' || md5(email) || '?s=200') stored;

You might be wondering why you would store this value in the database instead of generating it in your app. Computing MD5 hashes isn't a very fast process (by design), especially when it needs to be done anytime an avatar needs to be displayed. Storing the calculated value will certainly speed things up.

For more information about the possible configuration of Gravatar URLs, see this article from the Gravatar documentation.

Rob Conery shared another use of generated columns using json columns that is worth checking out.

This is just one reason we love Postgres, but there are so many other things to love about it! If you liked this blog post, and want to learn more about what Magistrate has to offer, sign up for our mailing list!