Brutal Postgres deadlock issue with concurrent Node workers completely stuck. How do you handle high volume status updates?
Hey everyone, my team and I are completely stuck on a database locking issue and could really use some senior backend advice.
We are running a Node backend with a PostgreSQL database, processing thousands of automated tasks per minute. We have multiple worker threads pulling jobs from a queue and updating a main tasks table (updating statuses like 'pending', 'processing', 'completed').
The problem: Under heavy concurrent load, our workers keep colliding when trying to update rows, resulting in endless ERROR: deadlock detected crashes. The transactions keep rolling back, and our workers are just spinning their wheels.
We tried implementing SELECT ... FOR UPDATE SKIP LOCKED so workers only grab free rows, but now it feels like some rows are getting starved and left behind for way too long.
Are we structuring our transactions wrong? Should we be batching these updates in memory first, or is there a better Postgres indexing/locking strategy we are completely missing here?
Any advice would be a lifesaver. We are hitting a wall.