postgres-job-queue

What This Skill Does

The postgres-job-queue skill provides a robust and dependency-light job queue system implemented entirely within PostgreSQL. It's designed for scenarios where you need background task processing, reliable job execution, and sophisticated queue management without introducing external services like Redis or RabbitMQ. Key features include priority scheduling, allowing critical jobs to be processed first; batch claiming, enabling workers to efficiently pick up multiple jobs at once using SKIP LOCKED to prevent contention; and progress tracking, offering visibility into the status and advancement of long-running tasks. Jobs are designed to survive service restarts due to their persistent storage in the database. The skill includes a well-defined SQL schema for the jobs table and a PostgreSQL function (claim_job_batch) for workers to claim jobs, along with considerations for a Go implementation.

Installation

To install this skill, use the following command:

clawhub install openclaw/skills/skills/wpank/postgres-job-queue

This command will download and configure the necessary components for the postgres-job-queue skill within your OpenClaw environment.

Use Cases

This skill is ideal for a variety of background processing needs:

• Microservice Task Offloading: Offload computationally intensive or time-consuming tasks from your main application services to background workers.
• Scheduled Reporting & Data Processing: Generate reports, perform data aggregations, or run batch updates on a schedule.
• Asynchronous Operations: Handle tasks like sending emails, processing images, or making external API calls without blocking the main application thread.
• Resilient Background Jobs: Ensure that jobs are not lost even if the application or worker instances crash and restart, thanks to PostgreSQL's transactional guarantees.
• Prioritized Workflows: Implement systems where certain jobs (e.g., critical user requests) must be processed before others (e.g., routine cleanup tasks).

Example Prompts

1. "Set up a PostgreSQL job queue for processing image uploads, ensuring high priority for user-submitted images."
2. "Create a worker that claims up to 5 jobs of type 'report_generation' from the PostgreSQL queue and updates their progress."
3. "Monitor the PostgreSQL job queue for jobs that have been 'claimed' for more than 10 minutes and have not changed status."

Tips & Limitations

• PostgreSQL Resource Management: Ensure your PostgreSQL instance is adequately resourced, as the job queue operations will consume CPU, memory, and I/O. Monitor query performance, especially on the jobs table.
• Worker Implementation: You will need to implement worker processes (e.g., in Go, Python, or another language) that connect to your PostgreSQL database, call the claim_job_batch function, process the claimed jobs, and update their status (e.g., 'completed', 'failed').
• Error Handling & Retries: The schema includes attempts, max_attempts, and last_error fields. Implement logic in your workers to handle job failures, increment attempts, and potentially backoff or requeue failed jobs.
• Progress Tracking Granularity: The progress and current_stage fields are simple indicators. For complex workflows, you might need more sophisticated event logging or a separate table to track detailed job progress.
• Scalability: While SKIP LOCKED helps with concurrent workers, the ultimate scalability is limited by your PostgreSQL instance's capacity and your worker implementation's efficiency.
• Schema Evolution: Plan for potential schema migrations if your job requirements evolve over time.