Database Denormalization

Counter cache and pre-computed totals in Rails migration

class AddDenormalisedFields < ActiveRecord::Migration[7.1]
  def change
    # Counter cache: avoid COUNT query on every post display
    # Without: Post.all.map { |p| [p.id, p.comments.count] } = N+1
    # With: Post.select(:id, :comments_count) = single query, no JOIN
    add_column :posts, :comments_count, :integer, default: 0, null: false
    add_column :posts, :likes_count, :integer, default: 0, null: false

    # Backfill existing data
    Post.find_each do |post|
      Post.where(id: post.id).update_all(
        comments_count: post.comments.count,
        likes_count: post.likes.count
      )
    end

    # Pre-computed order total: avoid SUM(line_items.price * qty) on every display
    add_column :orders, :subtotal_cents, :integer, null: false, default: 0
    add_column :orders, :total_cents, :integer, null: false, default: 0

    # Customer order summary: avoid expensive aggregation per customer page load
    add_column :customers, :total_orders_count, :integer, default: 0, null: false
    add_column :customers, :lifetime_value_cents, :integer, default: 0, null: false
  end
end

ActiveRecord counter_cache: true in the belongs_to declaration automatically updates the parent's counter column on association create/destroy. For custom counters, use after_create/after_destroy callbacks or DB triggers for consistency.

Materialised view for complex reporting query in PostgreSQL

// Materialised views: a pre-computed result set stored as a table
// Refreshing is an explicit operation (REFRESH MATERIALIZED VIEW)

const createMaterialisedView = async (pool: import('pg').Pool) => {
  await pool.query(`
    CREATE MATERIALIZED VIEW daily_revenue_summary AS
    SELECT
      DATE_TRUNC('day', o.placed_at) AS day,
      p.category,
      COUNT(o.id) AS order_count,
      SUM(o.total_cents) AS revenue_cents,
      AVG(o.total_cents)::int AS avg_order_cents,
      COUNT(DISTINCT o.customer_id) AS unique_customers
    FROM orders o
    JOIN order_line_items li ON li.order_id = o.id
    JOIN products p ON p.id = li.product_id
    WHERE o.status = 'fulfilled'
    GROUP BY 1, 2
    WITH DATA
  `);

  // Index on the materialised view for fast date-range queries
  await pool.query(
    'CREATE INDEX idx_revenue_summary_day ON daily_revenue_summary (day DESC)'
  );
};

// Refresh daily (in a cron job or pg_cron)
const refreshView = async (pool: import('pg').Pool) => {
  // CONCURRENTLY does not block reads but requires a unique index
  await pool.query('REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue_summary');
};

REFRESH MATERIALIZED VIEW CONCURRENTLY (PostgreSQL 9.4+) creates a new version of the view in the background and swaps atomically, preventing read locks during refresh. Without CONCURRENTLY, reads are blocked for the duration of the refresh.