`db/migrations/003_perf.sql`

75 lines
-- 003 · Performance migrations from database-optimizer review (2026-05-05)
--
-- All CREATE INDEX statements use CONCURRENTLY where possible. Note: PG won't
-- run CREATE INDEX CONCURRENTLY inside a transaction block, so this file is
-- intentionally not wrapped in BEGIN/COMMIT. Apply with:
--
--   psql -d national_paper_hangers -f db/migrations/003_perf.sql
--
-- Idempotent via IF NOT EXISTS / ON CONFLICT guards.

-- 1) Slot-calc hot-path index — partial composite covering only the statuses
-- that count toward conflict checks. Replaces the broader idx_bookings_scheduled.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bookings_slot_hot
  ON bookings (installer_id, scheduled_start, scheduled_end)
  WHERE status IN ('pending', 'confirmed');

DROP INDEX CONCURRENTLY IF EXISTS idx_bookings_scheduled;

-- 2) Lead-list filter — unclaimed studios with a scrapeable website.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_installers_unclaimed_with_site
  ON installers (state, city)
  WHERE claim_status = 'unclaimed' AND website IS NOT NULL;

-- 3) Time-off range scans — BRIN is tiny vs BTree for naturally time-ordered data.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_time_off_start_brin
  ON installer_time_off USING BRIN (start_at, end_at)
  WITH (pages_per_range = 32);

-- 4) Denormalize avg_rating + review_count onto installers (avoids GROUP BY
-- join on every directory listing render at scale).
ALTER TABLE installers
  ADD COLUMN IF NOT EXISTS avg_rating NUMERIC(3,2),
  ADD COLUMN IF NOT EXISTS review_count INTEGER DEFAULT 0;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_installers_rating
  ON installers (avg_rating DESC NULLS LAST)
  WHERE status = 'active';

-- One-shot backfill for any existing reviews. Safe to re-run.
UPDATE installers i
   SET avg_rating   = sub.avg,
       review_count = sub.cnt
  FROM (
    SELECT installer_id,
           ROUND(AVG(rating)::numeric, 2) AS avg,
           COUNT(*)::int                  AS cnt
      FROM installer_reviews
     WHERE published = true
     GROUP BY installer_id
  ) sub
 WHERE sub.installer_id = i.id;

-- 5) GIN on ad_signals JSONB — supports `@>` containment + key-exists as the
-- signals schema grows beyond paid_ads_count.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_installers_ad_signals_gin
  ON installers USING GIN (ad_signals)
  WHERE ad_signals IS NOT NULL;

-- 6) Subscription-events orphan cleanup index. installer_id can become NULL
-- via ON DELETE SET NULL — make those rows easy to find for retention sweeps.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sub_events_no_installer
  ON subscription_events (created_at)
  WHERE installer_id IS NULL;

-- 7) Autovacuum tuning for high-churn tables.
ALTER TABLE bookings SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

ALTER TABLE scrape_log SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_insert_scale_factor = 0.01
);