Zero-idle-RAM PostgreSQL queueing for CIEDE2000 perceptual locks: inside Shadow's real-time synthesis pipeline

Описание и применение

Перенос очереди и блокировок из Redis в PostgreSQL избавил от 14 ГБ памяти и устранил проблемы с потерей ключей при нагрузке, обеспечив надёжную синхронизацию цветовых блоков в реальном времени.

Задача

A 14 GB Redis instance holding perceptual lock state for 200 k in‑flight colour matches caused pipeline failures under traffic spikes.

Результат

The migration to PostgreSQL removed the 14 GB Redis dependency, eliminated idle RAM usage, and provided robust, transaction‑scoped locks that scale with traffic.

Рабочий процесс

• 1. Create perceptual_buckets and frame_jobs tables with appropriate indexes.
• 2. Worker pulls next job with a single UPDATE query using FOR UPDATE SKIP LOCKED.
• 3. Within same transaction acquire advisory lock on bucket via pg_advisory_xact_lock.
• 4. Update bucket mode and fetch canonical_delta_e.
• 5. Process job; on completion update state to completed and release lock automatically.
• 6. Workers LISTEN on per‑job notify_channel for wake‑up signals instead of polling.

Доказательства

The first time our synthesis pipeline melted down, the cause was not the model. It was a 14 GB Redis instance holding perceptual lock state for 200,000 in-flight colour matches.

Источник

Postgres has three primitives we needed: FOR UPDATE SKIP LOCKED, pg_advisory_xact_lock, LISTEN and NOTIFY.

Источник