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 LOCKEDTransactional consistency
Reliable persistence
Notification support using
LISTENandNOTIFYMature 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.
Join the conversation! Your thoughts help the community grow.