4 ms·

Also I think sqlite would not be a good fit if any sort of slow workers / background jobs are required.

by emmanueloga_ 3y ago

simonw 3y ago
Why not? (I'm planning a background job system based around SQLite at the moment)
- emmanueloga_ 3y ago
  Because typically all writes need to happen from a single process, so if you want to run multiple processes writing to the same DB, you need to synchronize them somehow. If you are running a workers loop together with your http serving loop, running on the same process is awkward: you would need to stop serving your webapp each time you want to deploy new workers. Also you would need to wait until all workers are done before you could redeploy the app. If one of the workers does something unexpected, it could take down your webapp together with any other workers running, etc. If you used multiple processes you would need to perform sync through some IPC or something like redis to perform writes sequentially, but using a DB that already ships as a daemon would fit the problem better.
  - simonw 3y ago
    Thanks, that's useful. My design should be OK - I'm planning on having the workers retrieve jobs and send back their results via an HTTP API to a single process that wraps the SQLite database (Datasette with a custom plugin).
    - emmanueloga_ 3y ago
      I'm not super familiar with Datasette but to a certain extent it does seem like you already implemented some sort of sqlite daemon over http? Is it fair to say Datasette is "like postgrest but for sqlite"? When I was thinking of using sqlite for bg jobs that's the kind of thing I wanted to avoid, but maybe you are already there :-) In case it helps, I've been investigating bg jobs too and saw a bunch of resources that can be helpful. One is a hn post about a job queue on top of pg [0] that has some cool pointers. Someone mentioned the "transactional outbox" pattern [1]. Separately, I found this video about implementing a work queue with Nats JS [2]. I suspect you could implement the outbox pattern in Datasette and provide a way to offload the jobs to any external queue, but Nats/JS seems nice since it provides all the building blocks to implement "exactly once" delivery, dead letter queue, hearbeats to ensure the workers completes the work, etc, and it is very easy to run. I think it could save you a lot of the tricky work of implementing all these features with SQL(ite). The overall design would be something like: def trigger(): """Enqueue a job transactionally: either fully succeeds or fully fails. job_id = transaction { job_data = ... enque job_data into outbox } # This can fail but no biggie, you will still need to poll in case of failure, # so no jobs will be created without their backing data, and no jobs will be dropped. transaction { remove job_id and send to nats } def background_poll(): """Poll the outbox in case we succeeded in inserting into the outbox but somehow failed to deliver to the queue.""" try periodically { transaction { remove from outbox and send to nats } } ... then in another process or processes, the workers would talk to nats/js to perform the work. Elsewhere someone described a job system that only relied on a database without support of events (that is, not pg), and required creating a sessions table to ensure workers complete the jobs, etc [3]. This is the kind of thing that I think could be simplified by using nats/js or another external job queue. -- 0: https://news.ycombinator.com/item?id=38349716 https://news.ycombinator.com/item?id=38349716 1: https://microservices.io/patterns/data/transactional-outbox.html https://microservices.io/patterns/data/transactional-outbox.... 2: https://www.youtube.com/watch?v=7Jp3tyCGMZs https://www.youtube.com/watch?v=7Jp3tyCGMZs 3: https://forum.cockroachlabs.com/t/how-to-implement-a-work-queue-without-skip-locked/3109/9 https://forum.cockroachlabs.com/t/how-to-implement-a-work-qu...

Also I think sqlite would not be a good fit if any sort of slow workers / background jobs are required.

simonw 3y ago

Why not? (I'm planning a background job system based around SQLite at the moment)

emmanueloga_ 3y ago
Because typically all writes need to happen from a single process, so if you want to run multiple processes writing to the same DB, you need to synchronize them somehow. If you are running a workers loop together with your http serving loop, running on the same process is awkward: you would need to stop serving your webapp each time you want to deploy new workers. Also you would need to wait until all workers are done before you could redeploy the app. If one of the workers does something unexpected, it could take down your webapp together with any other workers running, etc. If you used multiple processes you would need to perform sync through some IPC or something like redis to perform writes sequentially, but using a DB that already ships as a daemon would fit the problem better.
- simonw 3y ago
  Thanks, that's useful. My design should be OK - I'm planning on having the workers retrieve jobs and send back their results via an HTTP API to a single process that wraps the SQLite database (Datasette with a custom plugin).
  - emmanueloga_ 3y ago
    I'm not super familiar with Datasette but to a certain extent it does seem like you already implemented some sort of sqlite daemon over http? Is it fair to say Datasette is "like postgrest but for sqlite"? When I was thinking of using sqlite for bg jobs that's the kind of thing I wanted to avoid, but maybe you are already there :-) In case it helps, I've been investigating bg jobs too and saw a bunch of resources that can be helpful. One is a hn post about a job queue on top of pg [0] that has some cool pointers. Someone mentioned the "transactional outbox" pattern [1]. Separately, I found this video about implementing a work queue with Nats JS [2]. I suspect you could implement the outbox pattern in Datasette and provide a way to offload the jobs to any external queue, but Nats/JS seems nice since it provides all the building blocks to implement "exactly once" delivery, dead letter queue, hearbeats to ensure the workers completes the work, etc, and it is very easy to run. I think it could save you a lot of the tricky work of implementing all these features with SQL(ite). The overall design would be something like: def trigger(): """Enqueue a job transactionally: either fully succeeds or fully fails. job_id = transaction { job_data = ... enque job_data into outbox } # This can fail but no biggie, you will still need to poll in case of failure, # so no jobs will be created without their backing data, and no jobs will be dropped. transaction { remove job_id and send to nats } def background_poll(): """Poll the outbox in case we succeeded in inserting into the outbox but somehow failed to deliver to the queue.""" try periodically { transaction { remove from outbox and send to nats } } ... then in another process or processes, the workers would talk to nats/js to perform the work. Elsewhere someone described a job system that only relied on a database without support of events (that is, not pg), and required creating a sessions table to ensure workers complete the jobs, etc [3]. This is the kind of thing that I think could be simplified by using nats/js or another external job queue. -- 0: https://news.ycombinator.com/item?id=38349716 https://news.ycombinator.com/item?id=38349716 1: https://microservices.io/patterns/data/transactional-outbox.html https://microservices.io/patterns/data/transactional-outbox.... 2: https://www.youtube.com/watch?v=7Jp3tyCGMZs https://www.youtube.com/watch?v=7Jp3tyCGMZs 3: https://forum.cockroachlabs.com/t/how-to-implement-a-work-queue-without-skip-locked/3109/9 https://forum.cockroachlabs.com/t/how-to-implement-a-work-qu...