If you tweak the default it uses to write to disk(2 seconds or 64kb), then you can make it safer, but at cost of increasing disk IO.
Exadra37
Sebb
Yes, but that also depends on the memory you are writing to.
Its about risk management. What is the risk of losing data, how likely is a power failure.
If the data must not be lost, even SQLite may not be enough (I think).
Data is eg very safe on a microcontroller that can prevent writes/erases while power failure by brown-out-detection / Vdd monitoring.
0
Exadra37
Sebb
Sure, I mean the type of disk. You can’t say, I only write one byte at a time, that’s so fast, surely nothing will happen - because you always have to write a whole page to the flash, that just takes its time. If you write smaller chunks more often at some point this will likely make things worse.
1
lud
I have an app that monitors multiple Gitlab/Github projects and need to store some computed data about them. I tried to use SQLite but it was cumbersome. Now that there is an Ecto driver I may want to give it another go.
The problem is that, if I remember correctly, Ecto connection is configured project-wide. Our app is designed to have custom data tables for each monitored project. That means a different SQLite DB for each monitored project. On the other hand, performance requirements are very low and a single connection is enough for each DB, no pools needed (the current DB implementation is held by a GenServer anyway).
Would dynamic databases be possible with Ecto?
1
gregvaughn
Ecto connections are managed by a connection pool per Repo. You can have multiple Repos per app. Ecto has recently added the idea of dynamic repos, but I haven’t explored that.
3
webuhu
Haven’t tried, but it seem to be possible:
› Underjord | Ecto & Multi-tenancy - Dynamic Repos - Part 1 - Getting started
4
dimitarvp
My upcoming library will hide that from you because it will do pooling on the Rust side – the pool size will be configurable either as a project-wide config or when opening the DB.
I’ve done extensive tests (although I am not sure they are very scientific just yet). Opening 50 separate sqlite3 databases each with 20 “connections” (as we know, they are not really connections but file descriptors) and the test program barely consumed a single digit MBs of RAM.
The main challenge for such scenarios would be environments with severely reduced file descriptor limit (part of all Linux installs and a good chunk of the container cloud hosting platforms) so one has to be very judicious in picking a pool size with sqlite3. I suggest you put a pool size equaling the average expected amount of parallel tasks that will read from your DB. But honestly, for most projects a size of 2-3 should be quite fine – sqlite3 is extremely fast so taking a “connection” from the pool even if all “connections” are busy should be a matter of just a few milliseconds.
(Sorry that I haven’t pinged you to help me with the library yet; I’ll be at a lot of doctors in the next 1-2 months so I am keeping quiet since I barely have any free time.)
It’s quite interesting and I’ve used it once – it works just fine (which is something I find myself automatically taking for granted in the Elixir libraries! 
).
6
kevinlang
For this talk of data resiliency, SQLite3 itself provides pretty great guarantees as mentioned. Of course, there is always the possibility of losing the entire host machine in some disaster, and thus the single-node aspect of SQLite3 can work against you. Of course, this applies equally to simple Postgres setups as well.
One library I am eagerly following GitHub - benbjohnson/litestream: Streaming replication for SQLite. · GitHub, which can more or less tail the WAL into S3 or any other object storage, allowing one to recover nearly all data in the case of a total node failure, only losing a couple of seconds worth of data at most. This is much better than hourly or daily backups.
I haven’t played with it yet, but it seems like it is as simple as setting up a systemd service with the S3 storage config and the location of the local database file and it “just works”.
5
Exadra37
This may work for SQLite, but I think it will not work for other object storages, like Postgres WAL logs. If I am not in mistake in Postgres you also need Point in time recovery to be able to restore from a WAL backup.
This repo supports Postgres and also as now beta support for others, but don’t see their SQLite 
WAL-G is an archival restoration tool for Postgres(beta for MySQL, MariaDB, and MongoDB)
From SQLite official docs it seems that that they have a native API to allow for continuous backup:
The Online Backup API was created to address these concerns. The online backup API allows the contents of one database to be copied into another database, overwriting the original contents of the target database. The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from. This allows other database users to continue uninterrupted while a backup of an online database is made.
0