More from Sarah Chen

RSS

Cache-Friendly “Top N” with Materialized View Refresh

13793 views

0 comments

                  class CreateTopSnipsMaterializedView < ActiveRecord::Migration[6.1]
  def change
    execute <<~SQL
      CREATE MATERIALIZED VIEW top_snips AS
      SELECT id, title, score
      FROM snips
      ORDER BY score DESC
      LIMIT 100;

      CREATE UNIQUE INDEX index_top_snips_on_id ON top_snips (id);
    SQL
  end
end

                  class RefreshTopSnipsJob < ApplicationJob
  queue_as :maintenance

  def perform
    ApplicationRecord.connection.execute('REFRESH MATERIALIZED VIEW CONCURRENTLY top_snips')
  end
end

If you have a “top list” that’s expensive to compute, a materialized view is a clean approach. Refresh concurrently on a schedule to keep reads fast without blocking.

Sarah Chen

More from Sarah Chen

Cache-Friendly “Top N” with Materialized View Refresh

0 Comments

More from Sarah Chen