How We Replaced Goroutines With a Database Queue for Video Transcription

Source: Dev.to

Transcription Queue Refactor: From In‑Memory Goroutine to DB‑Backed Queue

In our last post about transcription, we showed how we added automatic video transcription with whisper.cpp. The approach was simple: after each upload, fire a goroutine that acquires a semaphore, runs whisper, and updates the database. It worked — until it didn’t.

Why the original approach failed

All three issues stem from the same root cause: the queue lived only in memory. The database knew nothing about pending work.

Choosing a DB‑backed queue

The usual answer to “I need a job queue” is to add a dedicated system (Redis/Sidekiq, RabbitMQ, SQS, …). Each introduces a new service to deploy, monitor, and keep running.

• SendRec runs on a single server.
• We already have PostgreSQL.
• Transcription throughput is low – a handful of jobs per hour, each taking 30‑60 seconds.
• We don’t need sub‑millisecond latency or millions of messages per second.

PostgreSQL’s FOR UPDATE SKIP LOCKED is purpose‑built for exactly this pattern: a worker atomically claims a row, processes it, and updates the status. Other workers (or later runs of the same worker) skip locked rows and grab the next one. Three SQL keywords give us a job queue.

Decision: No new dependencies, no new failure modes, no extra monitoring – just SQL.

---

Schema changes

-- Remove the old enum check (if any)
ALTER TABLE videos DROP CONSTRAINT IF EXISTS videos_transcript_status_check;

-- Add the new enum with the extra “pending” state
ALTER TABLE videos ADD CONSTRAINT videos_transcript_status_check
    CHECK (transcript_status IN ('none', 'pending', 'processing', 'ready', 'failed'));

-- Timestamp for stuck‑job detection
ALTER TABLE videos ADD COLUMN transcript_started_at TIMESTAMPTZ;

-- Index to speed up look‑ups of pending/processing rows
CREATE INDEX idx_videos_transcript_pending
    ON videos (transcript_status)
    WHERE transcript_status IN ('pending', 'processing');

State flow: none → pending → processing → ready | failed

---

Enqueueing a transcription

All previous “fire‑and‑forget” goroutine calls are replaced by a single DB update:

func EnqueueTranscription(ctx context.Context, db database.DBTX, videoID string) error {
    _, err := db.Exec(ctx,
        `UPDATE videos
         SET transcript_status = 'pending',
             updated_at = now()
         WHERE id = $1
           AND status != 'deleted'`,
        videoID,
    )
    return err
}

One UPDATE, no goroutine, no semaphore.
The UI now shows “pending” immediately.

---

Worker implementation

A background goroutine polls the DB every interval (e.g., 5 seconds). Each tick it:

1. Resets stuck jobs (see later).
2. Claims the next pending job using FOR UPDATE SKIP LOCKED.
3. Performs the transcription.
4. Updates the row to ready or failed.

Starting the worker

func StartTranscriptionWorker(ctx context.Context, db database.DBTX,
    storage ObjectStorage, interval time.Duration) {
    go func() {
        ticker := time.NewTicker(interval)
        defer ticker.Stop()
        for {
            select {
            case  In practice we run a single worker, but the pattern scales to many workers without code changes – just start more instances.

---

## Comparison with the old in‑memory semaphore  

```go
// Before: in‑memory semaphore
var transcriptionSemaphore = make(chan struct{}, 1)

func TranscribeVideo(ctx context.Context, ...) {
    select {
    case transcriptionSemaphore  10 minutes (or with a `NULL` start time) is returned to `pending` so it will be retried.*

The `OR transcript_started_at IS NULL` clause exists for a subtle reason: when we deployed the migration, existing videos that were stuck in `processing` from before the migration had a `NULL` `transcript_started_at`. This clause ensures they are also reset.

## Problem  

- The original implementation silently **skipped transcription** when the `whisper` binary wasn’t installed.  
- With a **persistent queue** this caused an **infinite loop**:  

  1. The worker claimed a job.  
  2. It discovered `whisper` was missing → set status back to **pending**.  
  3. Five seconds later the same job was claimed again, and the cycle repeated forever.  

- In SQL, `NULL   
- Worker code: `transcribe_worker.go`  
- Migration file: `000016_add_transcript_pending_status.up.sql`