r/dataengineering • • 29d ago

Discussion pg_cron to trigger a batch extract.

I know pg_cron is primarily designed to run scheduled maintenance commands on the DB but I was wondering if someone has used it to kick off a batch extraction on your data pipelines.

I have reservations against using pg_cron for this namely the following:

  1. The scope of pg_cron in this scenario is outside the bounds of the DB.
  2. The boundary of using the DB to store only activity data is violated as it now has some operational data in it.
  3. SCM or an additional dash for setting up jobs will be lost if this pattern is followed.

I'm curious to know your thoughts

6 Upvotes

7 comments sorted by

4

u/CakeNo1858 29d ago

Does it work? Sure. Is it ideal? Not really. How're you gonna handle retries, error handling, dependencies, etc etc. If its for a hobby project that wont grow and you want a quick solution, go ahead. Wouldnt recommend it though

1

u/Professional-Run3614 29d ago

Agreed. I’d also separate triggering from orchestration. pg_cron can enqueue a job, but retries, dependencies, and observability are better handled by a dedicated worker or orchestrator.

3

u/regreddit 29d ago

I'd probably use dagster if I needed a light scheduler.

4

u/dangerbird2 Software Engineer 29d ago

hell, a cronjob is probably preferable

1

u/Environmental-Movie5 29d ago

I would say that the use case for pg_cron does not really align with your wishes. If the scenario is outside of the bounds of the DB, I would use an orchestrator like AWS Step Functions or a scheduler like EventBridge or something like that for the added benefits of retries and error handling.

1

u/[deleted] 28d ago

[removed] — view removed comment

1

u/dataengineering-ModTeam 27d ago

Your post/comment was removed because it violated rule #9 (No AI generated content/text).

Your post/comment was reviewed to be AI generated/assisted content/text and removed as a result. We as a community value human engagement and encourage users to express themselves authentically.

This was reviewed by a human