4/29/2024 0 Comments Sqlite database in android![]() It does bother me that this can only get longer, but we've never actually had a host crash in production. We run weekly tests to determine how long AWS takes to spin everything back up upon catastrophic failure, and it's between 6 and 10 minutes, depending on how much data the service has. I think we used Huey for the Django app, and just some CLI applications fired via systemd timers for the Go services.Įvery service is running on a single (somewhat meaty) host, so we get true snapshot isolation on reads and serialized isolation on writes without doing anything extra. Our services currently contain "own" all of cron jobs, so the jobs run as background tasks on the host containing the db. It also incidentally enforces the good hygiene of "services can only access their own data." Happy to answer any questions you have. * Any transaction that may eventually write must start with BEGIN IMMEDIATE, otherwise SQLite may throw an error to keep it's promise of serialized isolation on writes.Īll said, I'd do it again for any other services-based backend. If possible, "organize" all the data for your writes outside the transaction and then hold the lock for as short as possible. Individual statements in SQLite run very quickly (I've seen ~250 microseconds per insert on a EBS-backed EC2 instance), but if you have a Django app with everywhere, you're going to run into lock contention quite quickly because your Python functions take much longer to run. * You need to think about how long your code holds a write transaction open. SQLite minimizes the barrier to doing this with real snapshots of production data - but that still doesn't mean it's going to be easy. I've been surprised at how performant features can be if a single mind is designing the schema, constraints, triggers, and queries, examining the query plan to choose indexes, and writing the application code that accesses the database. * You need to be working with a team of developers that are pretty comfortable with databases to get a lot of value out of SQLite. Alternatively, be aggressive in writing CHECK constraints and triggers to ensure data validity. * It's best if your code defines meaningful types to scan SQLite values into, and you ideally avoid ever writing to the DB via the CLI. ![]() * You need to have your code repopulate index statistics with ANALYZE or PRAGMA OPTIMIZE now and again, or you may get a confused query planner. ![]() I'm quite happy with it because it's trivial to debug and test against a local copy of the production data if necessary, but there are a few things to think about. Yes, we use it to back several services in production at an 8 figure ARR business, with Litestream as our streaming backup option. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |