3 ms·

I reach for SQLite if I need persisted state for a local application or custom file format but why use it for things that may need more write concurrency like w

by hnaccy 6y ago

I reach for SQLite if I need persisted state for a local application or custom file format but why use it for things that may need more write concurrency like web server? Postgres is basically just as easy to use and backup.

Uberphallus 6y ago
SQLite loses most of its edge in concurrent write scenarios, but its read performance is difficult to beat. A lot of it comes from what TFA says: there's no network roundtrip, but a function call. Even in a local machine, a unix socket query will carry at least a couple of system calls with potential context switches, and that makes regular RDBMS lag behind when you do tons of sequential and small queries. Of course, when you have large results or complex queries that eat a bigger chunk of the time cake and that technical advantage wanes. After that, which RDBMS has the performance lead is largely workload-dependent.
jraph 6y ago
> Postgres is basically just as easy to use and backup. SQLite is way ahead on this: no daemon to run, no user / database to create, manage and administrate, no authentication to set, no socket connection to manage… backup is as easy as it gets: (copy one or two files). Postgres is still largely manageable of course.
- johannes1234321 6y ago
  Well, backing up by copying doesn't neccissarily result in a consistent state if there are writes to the database. For that you have to use the SQLite `.backup` command (or using the backup API https://sqlite.org/c3ref/backup_finish.html https://sqlite.org/c3ref/backup_finish.html) after which the backup database has to be copied over to backup storage (or backup storage has to be mounted to the production system, which is dangerous)
  - beagle3 6y ago
    Use "rsync" instead of "cp". After it finishes copying, it will check to see if the file has changed since it started copying, and will restart the copy if so (with a limited number of retries). If copying the entire database is faster than your average update right, this will converge very quickly and will deliver a consistent copy. For many small applications, this is perfectly fine. It's not much harder to just "sqlite3 $file ".backup $backupfile"' (that's literally all it takes, and what you should do) and guarantee consistency. But it's nice to know that a simple "rsync" is sufficient for slowly-updating uses - e.g. And as for the other side of backup, you know -- restore -- sqlite shines brighter than everything else. You can just take a good copy and put it back. You can examine the file everywhere, on a read only system, etc - without configuring anything if needed.
    - johannes1234321 6y ago
      Yes, rsync is a great toolinnsome cases it might even find which blocks changed and only retransmit those. However if you have frequent writes I would be careful.

by hnaccy 6y ago

Uberphallus 6y ago

SQLite loses most of its edge in concurrent write scenarios, but its read performance is difficult to beat. A lot of it comes from what TFA says: there's no network roundtrip, but a function call. Even in a local machine, a unix socket query will carry at least a couple of system calls with potential context switches, and that makes regular RDBMS lag behind when you do tons of sequential and small queries. Of course, when you have large results or complex queries that eat a bigger chunk of the time cake and that technical advantage wanes. After that, which RDBMS has the performance lead is largely workload-dependent.

jraph 6y ago

> Postgres is basically just as easy to use and backup. SQLite is way ahead on this: no daemon to run, no user / database to create, manage and administrate, no authentication to set, no socket connection to manage… backup is as easy as it gets: (copy one or two files). Postgres is still largely manageable of course.

johannes1234321 6y ago
Well, backing up by copying doesn't neccissarily result in a consistent state if there are writes to the database. For that you have to use the SQLite `.backup` command (or using the backup API https://sqlite.org/c3ref/backup_finish.html https://sqlite.org/c3ref/backup_finish.html) after which the backup database has to be copied over to backup storage (or backup storage has to be mounted to the production system, which is dangerous)
- beagle3 6y ago
  Use "rsync" instead of "cp". After it finishes copying, it will check to see if the file has changed since it started copying, and will restart the copy if so (with a limited number of retries). If copying the entire database is faster than your average update right, this will converge very quickly and will deliver a consistent copy. For many small applications, this is perfectly fine. It's not much harder to just "sqlite3 $file ".backup $backupfile"' (that's literally all it takes, and what you should do) and guarantee consistency. But it's nice to know that a simple "rsync" is sufficient for slowly-updating uses - e.g. And as for the other side of backup, you know -- restore -- sqlite shines brighter than everything else. You can just take a good copy and put it back. You can examine the file everywhere, on a read only system, etc - without configuring anything if needed.
  - johannes1234321 6y ago
    Yes, rsync is a great toolinnsome cases it might even find which blocks changed and only retransmit those. However if you have frequent writes I would be careful.