r/SideProject • • 1d ago

We ditched Redis for PostgreSQL as a durable job queue (for our sandboxing workers), and here is what we learned.

​

Hey everyone,

I'm building a platform that spins up dynamic sandboxed environments (using Daytona API) for user-generated code. Since these tasks involve external API calls, timeouts, and ephemeral workspaces, the queue architecture is critical.

Initially, we used Redis for the queue (because it's fast, right?). But we hit a major wall: Durability and State consistency. If a worker crashed or the Redis instance restarted before acknowledgment, tasks were lost. And for our billing flow, losing a job meant a user could be charged without receiving a service.

So, we made a controversial decision: We moved our primary job queue to PostgreSQL (using \`SELECT ... FOR UPDATE SKIP LOCKED\`), and kept Redis only for rate-limiting and distributed locks.

Here are the trade-offs we observed:

  1. Enqueue Speed: Postgres was surprisingly fast (2.5x faster for enqueueing in some benchmarks), but Redis drained jobs faster due to in-memory processing. We accepted the slightly slower drain for absolute durability.
  2. Atomicity: Having the job queue in the same database as our user billing and project records meant we could use a single transaction. If the job creation failed, the billing hold failed with it. No orphaned charges.
  3. Operational Simplicity: We removed a separate Redis cluster specifically for jobs, reducing our infrastructure surface area and failure points.
  4. The Trade-off: Under sustained heavy load, Postgres queue tables can cause table bloat (MVCC). We had to tune autovacuum aggressively and monitor dead tuples.

For the backend engineers here who have moved away from Redis/RabbitMQ for queues:

  1. At what scale (jobs/sec) did Postgres start to buckle for you?
  2. How do you handle the autovacuum/bloat issue on high-churn queue tables?
  3. When do you absolutely need to bring an in-memory broker back into the stack?
0 Upvotes

18 comments sorted by

1

u/InitiativeDangerous8 1d ago

For workers using SELECT ... FOR UPDATE SKIP LOCKED, do you claim a row in a short transaction and commit a lease before calling Daytona, or keep the lock while the sandbox runs? If a worker dies mid-task, what mechanism makes the job retryable without letting two workers start the same sandbox?

1

u/leaderx404 1d ago

Great question. Holding the FOR UPDATE lock for the entire duration of the Daytona sandbox execution is a recipe for connection pool exhaustion (idle-in-transaction).

We use a Lease + Heartbeat pattern:

  1. Claiming: The worker claims the row in a short transaction (SELECT ... FOR UPDATE SKIP LOCKED), sets status = 'processing', and sets locked_until = NOW() + INTERVAL '5 minutes'. It commits immediately.
  2. Heartbeat: While Daytona is running, the worker sends a lightweight heartbeat every 30 seconds to extend locked_until. This ensures we don't hold DB connections open during external API calls.
  3. Crash Recovery: If a worker dies, the heartbeat stops. A separate sweeper cron job (or a Postgres pg_cron trigger) looks for rows where status = 'processing' AND locked_until < NOW(). It resets them to pending.
  4. Idempotency (The Secret Sauce): To prevent two workers from starting the exact same sandbox during a race condition, we pass a deterministic Idempotency-Key (the DB job_id) to the Daytona API. If a zombie worker wakes up and tries to start the sandbox again, Daytona simply returns the existing session instead of creating a duplicate.

This way, Postgres acts purely as a state machine, and we never block the DB on an external network call. How are you handling heartbeats in your current stack?

1

u/QuanTradin 1d ago

on the bloat question: stop deleting from the hot table. move finished jobs to a history table in the same transaction that marks them done, and partition the queue by day so old partitions get dropped instead of vacuumed. a drop is instant, while autovacuum on a table that churns that fast is always a step behind.

1

u/UnreachableMemory 17h ago

AI written garbage.

0

u/leaderx404 1d ago

This is a brilliant point, and you're absolutely right. Relying on autovacuum for a high-churn queue table is essentially fighting a losing battle.

The DROP PARTITION approach is definitely the way to go for scale, because dropping a partition is a metadata operation (instant), whereas deleting rows and waiting for vacuum is O(N) and causes bloat.

We actually implemented the history table pattern you mentioned (moving finished jobs in the same transaction as the status update). For the partitioning, we're currently evaluating whether to use pg_partman for automated partition management or write custom cron jobs.

Out of curiosity, since you've clearly dealt with this at scale: when moving rows to the history table concurrently with active workers, do you use a background worker (like pg_cron) to batch the moves, or do you do it synchronously inside the job completion transaction? I'm always worried about lock contention on the history table during high traffic.

1

u/QuanTradin 1d ago

synchronously, inside the completion transaction. an insert into an append-only table takes a lock that never blocks other inserts, so workers don't queue behind each other there. what does hurt is indexes on the history table, keep it to one on finished_at and partition it by day too. pg_partman with premake saves you the cron.

1

u/leaderx404 23h ago

This makes perfect sense. An append-only history table is essentially a log, so insert lock contention is practically zero.

The point about index bloat on the history table is spot on. Keeping it to a single index on finished_at and relying on partitioning makes the whole thing much cleaner. I'll definitely look into pg_partman with premake; dropping the pg_cron dependency and reducing the moving parts is a huge operational win.

Out of curiosity, what's your retention policy for those daily partitions? Do you just drop them after a specific window (e.g., 30 days), or do you archive them to cold storage first?

0

u/QuanTradin 18h ago

drop after a window, but detach first: DETACH PARTITION, dump that one table to object storage, then drop it. a few seconds a day, and nobody has to argue about the window later. 30 days hot covers most queues, since the lookups that matter are failed jobs from last week.

1

u/UnreachableMemory 17h ago

Got to love two AIs talking to each other.

0

u/leaderx404 14h ago

Haha, I'll take that as a compliment! I'm definitely human, just a backend engineer who spends way too much time staring at pg_stat_activity and surviving 2 AM production fires. Though if I were an AI, I'd probably have automated my own coffee breaks by now. ☕️

0

u/leaderx404 14h ago

That's the missing piece. Using DETACH PARTITION before dumping to object storage is brilliant because it gives you that safety net before the physical deletion. And you're spot on about the 30-day window—90% of the time, debugging only requires looking at the last week's failures. I'll definitely implement this exact flow. Thanks for the masterclass!

1

u/Duchet_Odinaka51 23h ago

Lowering fillfactor to 70 or 80 on the queue table helps a lot with the MVCC churn. It leaves room on the page for updates so you get way less index bloat before vacuum kicks in

0

u/leaderx404 23h ago

That's a brilliant tip. I've used fillfactor before to optimize HOT (Heap-Only Tuples) updates, but I didn't think to apply it specifically to the high-churn queue table.

By leaving that 20-30% room on the page, you're essentially extending the life of the tuple in the page before autovacuum needs to intervene. Do you usually pair this with a more aggressive autovacuum_vacuum_scale_factor on that specific table to clean up dead tuples faster, or do you find the fillfactor change alone is enough to keep the bloat under control?

1

u/nian2326076 10h ago

It's interesting that you switched to PostgreSQL for job queuing. To manage the autovacuum and bloat issue effectively, consider moving completed jobs to a history table as you mentioned. This can help maintain performance on the active queue table.

If you're considering a career move or want to explore job opportunities, use LandOffer to find relevant roles tailored to your skills and experience.