161–165 of 165 posts
Earlier quoted context omitted.
How is it for writes? Would a CRM type system benefit from liteFS setup?
In Fly's implementation; > LiteFS’ use of FUSE limits the write throughput to about 100 transactions per second so write-heavy applications may not be a good fit. https://fly.io/docs/litefs/faq/#what-are-the-tradeoffs-of-us...
Earlier quoted context omitted.
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 don…
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).
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 } }
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
1: https://microservices.io/patterns/data/transactional-outbox....
2: https://www.youtube.com/watch?v=7Jp3tyCGMZs
3: https://forum.cockroachlabs.com/t/how-to-implement-a-work-qu...
Is there a distributed version of SQLite that keeps its embedded library feature? For example, it could use EBS and S3 for shared storage, allowing for distributed read and write access, and possibly even multiple concurrent reads and writes. Should this be available, numerous lightweight web applications could operate without having to set up a separate PostgreSQL or MySQL database.
Earlier quoted context omitted.
One reason to reorder columns with SQLite is that if a column is usually null or has the default value, SQLite will not store the column at all if it is at the end of the row. It only saves a couple of bytes per column, but it is a reason to get these columns at the end.
AFAIK position has nothing to do with nulls, a null is a 0 byte in the header and has no payload in the row: https://www.sqlite.org/fileformat.html#record_format
"Missing values at the end of the record are filled in using the default value for the corresponding columns defined in the table schema."
If you have a table with 5 columns and you only insert the first 3 columns (based on create table column order) because the last 2 values are null or default, SQLite will only insert 3 type bytes in the header. However, if the first column (in create table order) is the one you omit, SQLite has to include its type byte, even if the value is null.
I think this skips one mega benefit for apps. I’ve been using liteFS in production for a couple months. Your web app is able to resolve db queries instantly. You don’t need loading states if you’re using complex charts and other frontend JS that waits for data. All the data is resolved so fast and you can just return all your data like more traditional apps, and the load times are insane. If you’re multi region you c…
You can run postgres on the same host as the web server too. Isn't that going to get you most of that same benefit in speed?