docs/migrations/V_optimize_sync.sql

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uq_jobs_job_id_idx ON jobs (job_id);
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'uq_jobs_job_id' AND conrelid = 'jobs'::regclass) THEN ALTER TABLE jobs ADD CONSTRAINT uq_jobs_job_id UNIQUE USING INDEX uq_jobs_job_id_idx; END IF; END $$;
DROP INDEX CONCURRENTLY IF EXISTS idx_jobs_job_id;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_active_run_id ON jobs (run_id) WHERE is_active = true;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_jobs_cat_lower_sorted ON jobs (is_active, lower(job_category), sorted_at DESC, id DESC);
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_jobs_search_vector' AND tgrelid = 'jobs'::regclass) THEN RAISE EXCEPTION 'FTS trigger trg_jobs_search_vector not found on jobs. Run docs/fts_setup.sql first.'; END IF; END $$;
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE tablename = 'jobs' AND indexname = 'idx_jobs_fts_active') THEN RAISE EXCEPTION 'GIN index idx_jobs_fts_active not found. Run docs/fts_setup.sql first.'; END IF; END $$;
SELECT 'Migration complete' AS status, (SELECT COUNT(*) FROM pg_constraint WHERE conname = 'uq_jobs_job_id' AND conrelid = 'jobs'::regclass) AS uq_constraint_exists, (SELECT COUNT(*) FROM pg_indexes WHERE tablename = 'jobs' AND indexname = 'idx_jobs_job_id') AS old_job_id_index_exists, (SELECT COUNT(*) FROM pg_indexes WHERE tablename = 'jobs' AND indexname = 'idx_jobs_active_run_id') AS partial_run_id_index_exists, (SELECT COUNT(*) FROM pg_indexes WHERE tablename = 'jobs' AND indexname = 'idx_jobs_cat_lower_sorted') AS cat_functional_index_exists, (SELECT COUNT(*) FROM pg_indexes WHERE tablename = 'jobs') AS total_jobs_indexes;