44 lines
2.2 KiB
SQL
44 lines
2.2 KiB
SQL
|
-- +goose Up
|
||
|
|
-- Hot-path indexes for feed sync / processing jobs at large-tenant scale.
|
||
|
|
-- Product list composites already covered in 018-023; these tables still had
|
||
|
|
-- only single-column company_id / feed_id / job_id / status indexes.
|
||
|
|
-- Pattern mirrors 034_support_auto_jobs (list composite + pending claim partial).
|
||
|
|
|
||
|
|
-- ListSyncJobs: WHERE company_id=? AND feed_id=? ORDER BY created_at DESC
|
||
|
|
-- Also covers enqueue dedupe filter on (company_id, feed_id) + status.
|
||
|
|
CREATE INDEX IF NOT EXISTS feed_sync_jobs_company_feed_created_idx
|
||
|
|
ON feed_sync_jobs (company_id, feed_id, created_at DESC);
|
||
|
|
|
||
|
|
-- ClaimNext sync worker: WHERE status='pending' ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED
|
||
|
|
CREATE INDEX IF NOT EXISTS feed_sync_jobs_pending_claim_idx
|
||
|
|
ON feed_sync_jobs (created_at ASC)
|
||
|
|
WHERE status = 'pending';
|
||
|
|
|
||
|
|
-- Last completed content_hash per feed (skip unchanged sync):
|
||
|
|
-- WHERE feed_id=? AND status='completed' AND content_hash IS NOT NULL ORDER BY completed_at DESC
|
||
|
|
CREATE INDEX IF NOT EXISTS feed_sync_jobs_feed_completed_hash_idx
|
||
|
|
ON feed_sync_jobs (feed_id, completed_at DESC)
|
||
|
|
WHERE status = 'completed' AND content_hash IS NOT NULL AND content_hash <> '';
|
||
|
|
|
||
|
|
-- ListJobs / ListProcessingJobs: WHERE company_id=? ORDER BY created_at DESC
|
||
|
|
CREATE INDEX IF NOT EXISTS processing_jobs_company_created_idx
|
||
|
|
ON processing_jobs (company_id, created_at DESC);
|
||
|
|
|
||
|
|
-- ClaimNextPendingJob: WHERE status='pending' ORDER BY priority DESC, created_at LIMIT 1 FOR UPDATE SKIP LOCKED
|
||
|
|
CREATE INDEX IF NOT EXISTS processing_jobs_pending_claim_idx
|
||
|
|
ON processing_jobs (priority DESC, created_at ASC)
|
||
|
|
WHERE status = 'pending';
|
||
|
|
|
||
|
|
-- ListPendingJobProducts / claim batch (million-item jobs):
|
||
|
|
-- WHERE job_id=? AND status='pending' [OR stuck processing] ORDER BY created_at
|
||
|
|
CREATE INDEX IF NOT EXISTS processing_job_products_job_status_created_idx
|
||
|
|
ON processing_job_products (job_id, status, created_at ASC);
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
DROP INDEX IF EXISTS processing_job_products_job_status_created_idx;
|
||
|
|
DROP INDEX IF EXISTS processing_jobs_pending_claim_idx;
|
||
|
|
DROP INDEX IF EXISTS processing_jobs_company_created_idx;
|
||
|
|
DROP INDEX IF EXISTS feed_sync_jobs_feed_completed_hash_idx;
|
||
|
|
DROP INDEX IF EXISTS feed_sync_jobs_pending_claim_idx;
|
||
|
|
DROP INDEX IF EXISTS feed_sync_jobs_company_feed_created_idx;
|