And the safety of your data depends on the quality of your network filesystem's locking implementation. It's not too difficult to design a locking method that works most of the time, but it's a lot harder to build something that guarantees mutual exclusion over an imperfect network.
On a single machine, file locking is generally reliable (mediated by the kernel). Multiple clients can access the same database simultaneously, with readers and writers taking turns, and all that happens is you pay the performance penalty of serialized access.
But if your client thinks it has a lock on a remotely-stored database file, but the server-side lock has actually expired and another client is writing to the database, your data is likely to get hosed.
https://www.sqlite.org/howtocorrupt.html
SQLite has an alternate lock mode with dotfiles that seems to prevent database corruption over NFS. It is important that all SQLite accesses from all connected processes use the same lock mode.
"2.3. Two processes using different locking protocols
"The default locking mechanism used by SQLite on unix platforms is POSIX advisory locking, but there are other options. By selecting an alternative sqlite3_vfs using the sqlite3_open_v2() interface, an application can make use of other locking protocols that might be more appropriate to certain filesystems. For example, dot-file locking might be select for use in an application that has to run on an NFS filesystem that does not support POSIX advisory locking.
"It is important that all connections to the same database file use the same locking protocol. If one application is using POSIX advisory locks and another application is using dot-file locking, then the two applications will not see each other's locks and will not be able to coordinate database access, possibly leading to database corruption."
1. A crash risks leaving the lock file in place; it must be manually removed or else you hang forever.
2. The same home directory may be local and remote on different machines, meaning different locking protocols.
I am considering a mode where the database is loaded fully into memory, and writes are atomically saved via the classic adjacent-file-renamed mechanism. You risk losing data due to races, but it can't corrupt the database.
Sure, I could just sum them myself, but not only would that still leave me SOL with ad-hoc, interactive queries, but actually be more work than just firing up postgres.
I can't parse [0] right now about the overhead of TCP and round trips with the database server, but it's basically a question of whether one SQLite write is 10x, 100x, 1000x, or more faster than a database server write. That should make a lot of difference.
In terms of latency it'll still be difficult to beat a database that lives in the same process as your application, but it won't be as bad as going over the network might be.
Sounds like a nice topic for a study; I'd expect the latency using local sockets to connect to a DB server to be about 3x as much, minimum, as the latency to simply write to a file.
Writing to a file: a single context-switch from process to kernel (CS). Writing to a socket: CS on write to socket, CS when receiving process writes the data to file, CS when receiving process sends ACK to sender via a socket.
The worst thing you can do in your program is to cause a context switch by passing data to kernel or recieving data from a kernel.
That being said, a trial of the two approaches is not a bad idea for an easily-publishable paper :-)
A program error might still make you issue the wrong command or write corrupt data, but you shouldn't corrupt the internals with process isolation.
I didn't mean the TCP ACK packet, I meant the application layer return saying "Thanks, I got your query".
It is never a good idea to blast some data off into a transmission channel without knowing if the receiver ever got it, hence the receiver always sends something back to say that they received it.
> Neither the sender nor the receiver actually care whether network traffic occurs as long as the semantics of the operation stay the same.
Whether you are sending over the network or over a local UNIX socket is irrelevant, the context switch is going take place for each `write()` and for each `read()`.
That's like saying immortality is trivial modulo death and whatnot. If you don't integrate with sqlite's locking system, you can easily "backup" a file mid-write, corrupted and unusable.
That's why sqlite has a built-in backup command.
There are ways around this (filesystem snapshots and potentially file locking) but it's not trivial and failing to handle this correctly is a very good way to build a system which passes tests under low load (when the file-copying operation will win all the relevant races) but fail catastrophically under heavy load.
.backup ?DB? FILE Backup DB (default "main") to FILE
https://sqlite.org/cli.htmlWhat you are proposing may be trivial but in comparison it might just as well be rocket science. Doing more work to use a simpler database that is harder to setup and has less features is not exactly compelling.
RECOVER DATABASE UNTIL TIME '2021-10-01 02:00:00' USING BACKUP CONTROLFILE;
SQLite does not implement such a feature.EDIT: or maybe the Session Extension [2]
[0]: https://www.sqlite.org/sqldiff.html
See the -timestamp option here: https://litestream.io/reference/restore/
I think you can do concurrent writing now with Write-Ahead Logging (WAL): https://www.sqlite.org/wal.html
I've never tried it though, so I don't know how suitable it is for web apps that might potentially have multiple processes trying to write to the DB at the same time.
But transactions in SQLite are often fast enough that this could still be acceptable for a lot of purposes, especially on an SSD.
The one time I actually wanted to do that, I wrote the server that `accept`ed incoming connections and used the single `fopen`ed SQLite DB.
It can be very flexible that way, TBH, but if you really need that type of thing a more traditional DB is better.
Potential plus in cloud/container deployments.
> It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good.
There are other possibilities given a bit of additional wrappers (over the db process) and federating nodes with some (consensus) protocol. It may actually be a good fit as the 'node-unit' for very large scale distributed metadata store that would benefit from relational DB semantics instead of basic K/V.
With appropriate pragmas and database handles stored persistently between requests in server processes, the rate (in bytes of data affected by INSERT/UPDATE/DELETE) at which users could make DB changes got pretty darn close to the rate at which a file could be sequentially written on the filesystem, which is more than enough aggregate throughput for 1000s of concurrent non-bulk changes in most web applications, I'd imagine.
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 30000;
If the app is not specifically designed to do this, then SQLite would not be an option.
https://serverfault.com/questions/564456/what-are-the-practi...
It is not client/server; a process must be able to fopen() the database file. NFS and SMB are options that can convey access to remote systems, but performance will not likely be good.
Only a single process can write to the database at any time; it does not support concurrent writers.
The backup tools do not support point-in-time recovery to a specific past time.
If your application can live with these limitations, then it does have some wonderful features.