bvandeusen
181f1c6a27
The throughput bottleneck was curator-side, not the network. lease() claimed the lowest-id pending/expired jobs with `... ORDER BY id LIMIT n`, but with only a plain `status` index Postgres walked the primary key from id=1, skipping the entire prefix of already done/error rows before reaching pending ones. As `done` grew (69k+), every lease became an O(done) scan — leasing crawled, the DB saturated, and even /status (the queue GROUP BY count) stalled the agent. - Migration 0070 adds two partial indexes over just the live slice: pending rows indexed by id (hot path), and leased rows by lease_expires_at (crash-recovery + orphan sweep). They stay tiny no matter how large the done/error history. - lease() split into two phases so each uses a partial index: claim pending first (id-ordered, O(batch)); reclaim expired leases only when pending can't fill the batch. Same semantics (SKIP LOCKED, attempts++, expired reclaim). - Model __table_args__ declares the indexes so ORM and schema agree. - Test: a done-prefix at low ids must not stop the lease reaching pending. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01Ttrj5P7upUTueSfoJcxEqa
45 lines
1.7 KiB
Python
45 lines
1.7 KiB
Python
"""partial indexes so GPU-job leasing stays O(batch), not O(completed)
|
|
|
|
The lease claims the lowest-id pending (or expired-leased) jobs. With only a
|
|
plain `status` index, `... ORDER BY id LIMIT n` walked the primary-key index from
|
|
the start, skipping the entire prefix of already-done/error rows before reaching
|
|
pending ones — so leasing slowed to a crawl as `done` piled up (the whole reason
|
|
throughput fell off a cliff mid-run and /status stalled). Two partial indexes fix
|
|
it: the pending one is id-ordered so the hot path reads just the first n entries,
|
|
and the leased-expiry one keeps the crash-recovery reclaim + the orphan sweep
|
|
cheap. They cover only the small live slice of the table, so they stay tiny even
|
|
as the done/error history grows to millions.
|
|
|
|
Revision ID: 0070
|
|
Revises: 0069
|
|
Create Date: 2026-06-30
|
|
"""
|
|
from typing import Sequence, Union
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
|
|
revision: str = "0070"
|
|
down_revision: Union[str, None] = "0069"
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# Hot path: lowest-id pending jobs. Index on id, restricted to pending, so
|
|
# `WHERE status='pending' ORDER BY id LIMIT n` is a short index-order scan.
|
|
op.create_index(
|
|
"ix_gpu_job_pending", "gpu_job", ["id"],
|
|
postgresql_where=sa.text("status = 'pending'"),
|
|
)
|
|
# Crash-recovery: expired leases, for the lease backstop + recover_orphaned.
|
|
op.create_index(
|
|
"ix_gpu_job_leased_expires", "gpu_job", ["lease_expires_at"],
|
|
postgresql_where=sa.text("status = 'leased'"),
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_index("ix_gpu_job_leased_expires", table_name="gpu_job")
|
|
op.drop_index("ix_gpu_job_pending", table_name="gpu_job")
|