http://blog.guiltry.com/postgresql-materialized-view

schema.sql

---
CREATE TABLE users(
  id integer PRIMARY KEY,
  name varchar(50),
  ...
);

CREATE TABLE user_followers(
  user_id integer,
  follower_id integer,
  ...
  CONSTRAINT user_id_follower_id PRIMARY KEY(user_id, follower_id)
);

CREATE MATERIALIZED VIEW popular_user AS
  SELECT user_id, count(1) AS follower
  FROM user_followers
  GROUP BY user_id
;

CREATE INDEX ON popular_user (user_id);