返回提示词目录第 1170 页

构建数据库后台任务队列

评估现成队列库是否适用,并为定制实现提供表结构、任务领取、重试回收及并发测试代码。

完整提示词

<context>
A Postgres-backed queue is a good choice when the app already has Postgres and jobs number in the thousands per minute rather than the hundreds of thousands: enqueue can be part of the same transaction as the business write, which removes a whole class of lost or phantom jobs. The core is a claim query: `SELECT ... WHERE status = 'queued' AND run_at <= now() ORDER BY priority, run_at FOR UPDATE SKIP LOCKED LIMIT n`, then marking those rows as running with a lease expiry, in one short transaction. Holding the row lock for the whole job instead ties a connection up and breaks when jobs take minutes. Workers crash, so leases must expire and be reclaimed; jobs then run at least once and must be idempotent. A queue table is a high-churn table: without a partial index on ready jobs, regular deletion of finished rows and autovacuum keeping up, it bloats and the claim query slows. `LISTEN/NOTIFY` can wake workers but is not a delivery guarantee. Mature libraries exist for most languages; using one is often better than writing the queue.
</context>

<task>
Requirements: {{requirements}}
{{#existing}}
<existing>
{{existing}}
</existing>
{{/existing}}

1. Read the project: database access layer, migrations, how workers or processes run, and any existing queue. If a maintained Postgres-backed queue library fits the stack, recommend it and explain what it already handles; continue only if the user wants a custom one or the library does not fit.
2. Fit check: compare the stated volume and latency needs with what a Postgres queue handles comfortably; say when they should move to a broker.
3. Schema: jobs table with id, type, payload (JSONB), status, priority, run_at, attempts, max_attempts, locked_until, locked_by, last_error, timestamps; a partial index on ready jobs matching the claim query.
4. Enqueue inside the caller's transaction, with an optional unique key to prevent duplicate jobs.
5. Claim: one short transaction using `FOR UPDATE SKIP LOCKED` that sets status running and `locked_until`; run the job outside that transaction; mark done or failed afterwards. Reclaim jobs whose lease expired.
6. Retries with exponential backoff and jitter via `run_at`, a dead status after max attempts with the error kept, and a way to retry dead jobs by hand.
7. Maintenance: delete or archive finished jobs on a schedule, autovacuum settings for the table if needed, and metrics (queue depth, oldest ready job age, dead count).
8. Tests: concurrent workers never run the same job at once, a crashed worker's job is reclaimed, retries back off, enqueue rolls back with its transaction. Run them against a local or disposable Postgres.
</task>

<constraints>
- Run migrations and tests only against local or disposable databases unless the user confirms otherwise.
- Never hold the claim transaction open for the duration of a job.
- State plainly that delivery is at least once and job handlers must be idempotent.
- Do not add a new dependency without saying so and why.
{{> guardrails/scope-discipline}}
{{> guardrails/verify-before-done}}
</constraints>

<output_format>
## Fit check
Three to five lines, including library recommendation.

## Schema
Migration SQL with file path.

## Enqueue and claim
Code with file path.

## Worker loop
Code with file path.

## Retries and dead jobs
Code and rules.

## Maintenance
Cleanup job, vacuum notes, metrics.

## Tests
Code with file path.

## Check
Commands run and their real results.
</output_format>

可替换内容

  • {{requirements}}
  • {{#existing}}
  • {{existing}}
  • {{/existing}}
  • {{> guardrails/scope-discipline}}
  • {{> guardrails/verify-before-done}}

分类与标签

编程开发、后台任务、数据库队列、失败重试、并发测试

前往 AI 图片创作