Background job processing is a fundamental part of modern applications. Sending emails, generating reports, processing payments, importing data, and executing scheduled tasks are typically handled asynchronously to improve responsiveness and scalability.
Many distributed job processing systems rely on Redis for queues and worker coordination. While Redis is an excellent solution, not every organization wants to introduce another infrastructure component. If your application already uses PostgreSQL, you can build a reliable distributed background job system using PostgreSQL's transactional capabilities, row-level locking, and notification features.
In this article, you'll learn how to design a distributed background job system using PostgreSQL and ASP.NET Core, implement safe worker coordination, and apply production-ready best practices.
Why PostgreSQL for Background Jobs?
PostgreSQL offers several features that make it suitable for distributed job processing:
ACID transactions
Row-level locking
FOR UPDATE SKIP LOCKED
Transactional consistency
Reliable persistence
Notification support using LISTEN and NOTIFY
Mature replication and backup capabilities
For organizations already using PostgreSQL, leveraging these features can simplify infrastructure and operational management.
Background Job Architecture
A typical PostgreSQL-based job system consists of:
Application
|
Create Job
|
PostgreSQL Jobs Table
|
Multiple Workers
|
Execute Job
|
Update Status
Workers compete for available jobs, but row-level locking ensures each job is processed only once.
Designing the Jobs Table
A basic table structure might include:
CREATE TABLE background_jobs
(
id UUID PRIMARY KEY,
job_type TEXT NOT NULL,
payload JSONB NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMP NOT NULL,
started_at TIMESTAMP,
completed_at TIMESTAMP,
retry_count INT DEFAULT 0
);
Each job stores:
Job identifier
Job type
Payload
Current status
Execution timestamps
Retry information
Additional fields such as priority or scheduled execution time can be added based on application requirements.
Job Lifecycle
A background job typically progresses through several states.
Created
|
Queued
|
Running
|
Completed
If failures occur:
Running
|
Failed
|
Retry
Tracking job state simplifies monitoring and recovery.
Creating Jobs
A simple job model:
public class BackgroundJob
{
public Guid Id { get; set; }
public string JobType { get; set; } = "";
public string Payload { get; set; } = "";
public string Status { get; set; } = "Queued";
}
Insert jobs using normal application services rather than allowing workers to create jobs directly.
Enqueueing a Job
Example:
await db.BackgroundJobs.AddAsync(
new BackgroundJob
{
Id = Guid.NewGuid(),
JobType = "Email",
Payload = payload
});
await db.SaveChangesAsync();
Once committed, the job becomes available to workers.
Retrieving Jobs Safely
The most important part of a distributed queue is ensuring only one worker processes a job.
PostgreSQL provides:
SELECT *
FROM background_jobs
WHERE status = 'Queued'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1;
SKIP LOCKED allows multiple workers to retrieve different jobs simultaneously without conflicts.
This approach removes the need for distributed locks managed by external systems.
Worker Architecture
A simple design:
Worker 1
\
Worker 2 -----> PostgreSQL
/
Worker 3
Each worker independently polls the database for available work.
Because rows are locked transactionally, workers cannot process the same job concurrently.
Background Worker in ASP.NET Core
A worker service might inherit from BackgroundService.
public class JobWorker : BackgroundService
{
protected override async Task ExecuteAsync(
CancellationToken stoppingToken)
{
while (!stoppingToken.IsCancellationRequested)
{
await ProcessNextJob();
await Task.Delay(1000);
}
}
}
The delay prevents continuous polling while still allowing timely processing.
Polling intervals should be adjusted according to workload requirements.
Updating Job Status
After acquiring a job:
job.Status = "Running";
await db.SaveChangesAsync();
Upon successful completion:
job.Status = "Completed";
await db.SaveChangesAsync();
Explicit state transitions improve visibility and operational monitoring.
Handling Failures
Jobs should not disappear when processing fails.
Example:
try
{
await ExecuteJob(job);
job.Status = "Completed";
}
catch
{
job.Status = "Failed";
job.RetryCount++;
}
Keeping failed jobs enables investigation and controlled retries.
Retry Strategy
A retry workflow might look like this:
Failure
|
Retry 1
|
Retry 2
|
Retry 3
|
Dead Letter Queue
Avoid infinite retry loops.
Applications should define maximum retry counts and escalation procedures.
Scheduling Future Jobs
Some jobs should execute later.
Example schema addition:
scheduled_at TIMESTAMP
Workers then retrieve:
WHERE scheduled_at <= NOW()
This enables delayed execution without requiring separate scheduling infrastructure.
Using LISTEN and NOTIFY
Polling every second may be sufficient for many systems, but PostgreSQL also supports event notifications.
Example:
NOTIFY new_job;
Workers execute:
LISTEN new_job;
When a new job is inserted, PostgreSQL notifies waiting workers, reducing unnecessary polling.
The implementation depends on the PostgreSQL client library being used.
Monitoring Job Processing
Useful operational metrics include:
Jobs created
Jobs completed
Failed jobs
Retry count
Queue length
Average execution time
Processing throughput
Monitoring these metrics helps detect bottlenecks before they affect users.
Scaling Workers
Adding additional workers is straightforward.
Load Balancer
|
Application
|
PostgreSQL
/ | \
Worker Worker Worker
Because PostgreSQL coordinates row locking, workers can scale horizontally without additional synchronization mechanisms.
Production Best Practices
| Practice | Benefit |
|---|
| Use SKIP LOCKED | Prevent duplicate processing |
| Keep transactions short | Improve concurrency |
| Track retry counts | Controlled failure handling |
| Log job execution | Easier troubleshooting |
| Monitor queue length | Capacity planning |
| Archive completed jobs | Keep tables manageable |
| Separate job execution logic | Better maintainability |
Common Mistakes
| Mistake | Better Approach |
|---|
| Long-running database transactions | Keep transactions brief |
| Infinite retries | Set retry limits |
| Missing job status updates | Track lifecycle explicitly |
| Deleting failed jobs | Preserve for analysis |
| No monitoring | Collect operational metrics |
| Large payloads in every row | Store references when appropriate |
Troubleshooting
Jobs remain queued
Check:
Duplicate processing
Verify:
FOR UPDATE SKIP LOCKED
Transaction boundaries
Worker implementation
Commit timing
Slow job execution
Review:
Database indexes
Job complexity
External API latency
Worker concurrency
Queue continues growing
Investigate:
Worker capacity
Processing failures
Long-running jobs
Database performance
PostgreSQL vs Redis for Background Jobs
| Feature | PostgreSQL | Redis |
|---|
| Persistent Storage | Yes | Configurable |
| ACID Transactions | Yes | Limited |
| Row-Level Locking | Yes | No |
| Existing Infrastructure Reuse | Excellent | Moderate |
| High-Speed Queue Operations | Good | Excellent |
| Operational Simplicity (Existing PostgreSQL) | High | Moderate |
Redis remains an excellent choice for high-throughput messaging workloads, but PostgreSQL provides a compelling alternative when transactional consistency and infrastructure simplicity are priorities.
Frequently Asked Questions
Can PostgreSQL replace Redis for every background job system?
No. The best choice depends on workload characteristics. PostgreSQL is well-suited for many enterprise applications, while Redis may offer advantages for extremely high-throughput queue processing.
Why is SKIP LOCKED important?
It allows multiple workers to safely retrieve different jobs without blocking each other or processing the same job twice.
Should completed jobs be deleted immediately?
Not necessarily. Many organizations retain completed jobs for auditing, monitoring, or troubleshooting before archiving or purging them.
Can scheduled jobs be implemented with PostgreSQL?
Yes. Adding a scheduled execution timestamp allows workers to process jobs only after the specified time.
Is polling the only option?
No. PostgreSQL supports LISTEN and NOTIFY, enabling workers to receive notifications when new jobs are available.
Conclusion
PostgreSQL offers a powerful foundation for building reliable distributed background job systems without introducing additional infrastructure. Features such as transactional consistency, row-level locking, FOR UPDATE SKIP LOCKED, and event notifications enable safe coordination across multiple workers while keeping the architecture relatively simple.
By designing a clear job lifecycle, implementing controlled retries, monitoring queue health, and following production best practices, developers can build scalable background processing systems that integrate naturally with existing PostgreSQL-based applications. For organizations seeking to reduce infrastructure complexity while maintaining reliability, PostgreSQL is a practical and capable solution for distributed job processing.