pg_cron VS bull

[Postgres Extensions] pg_cron 1.5.2 (new projects only) now supports sub-minute schedules. PR

(Your PostgreSQL installation might already have pg_cron available, but if not, you would need to install it)

I’d set up citusdata/pg_cron in 14, so I’ll need to set that up in 15 as well.

Functions can be executed via a database query or on a regular schedule. Trigger functions are a special type of function that are covered in a previous article. They provide a way to execute an action based on a database event.

* extraction and loading: the psql-http PostgreSQL extension which is available in Supabase (but not all services like AWS RDS). Not sure how it works with OAuth but something to look into. * automation: use pg_cron to automate the ingest. * transformation: leverage Postgresql JSON functions.

A low-level alternative is using a PostgreSQL-side facility like https://github.com/citusdata/pg_cron - this, however, requires installing a PostgreSQL extension.

If you just want scheduling queries, you can use this https://github.com/citusdata/pg_cron

To implement the solution with a queue, I used a package called 'Bull' (https://github.com/OptimalBits/bull). It's a library that helps with distributed job control, providing some very useful solutions for this type of work, such as background job processing, queues with priorities (FIFO, LIFO, and others), among other features. 'Bull' uses Redis for queue storage, so if your application crashes for any reason, once it's back online, it will continue executing the processes that are in the queue. In our case, we'll use the FIFO (First in, first out) queue solution, meaning priority based on arrival order.

We use bull-js for our distributed queue and event-driven library.

I'm using the excellent Redis based Bull.js as a job queue on Kubernetes.

For Job Queue, NestJs provides a package named @nestjs/bull as an abstraction/wrapper on top of Bull, a popular, well-supported, high-performance Node. js-based Queue system implementation.

I'm working on a large ETL project that involves handling queues for file integration. Currently, we are using Redis with Bull (https://www.npmjs.com/package/bull) for this purpose. However, to streamline our architecture and address security concerns, we are considering migrating the queue to PostgreSQL.

BullMQ is a pretty solid choice: https://github.com/taskforcesh/bullmq It's the successor of Bull: https://github.com/OptimalBits/bull

For Node specifically, things like Streams, understanding the asynchronous model completely, and the event loop. Also, queues tend to get used a lot in Node, so understanding the basic concepts behind that and how to use something like bull would be useful.

To do this properly, you need to put the task into queue, so if there are 1000 simultaneous uploads it won't kill your server but will be processed one by one. For the queue, see bull. Image hostings like AWS have some functionality for processing on their side, as an option.

This is a memory intensive process though and Heroku is OOM'ing with R14 errors. For this they recommend migrating intensive work like this to a Background Job via Redis, implemented in Bull and Throng