4 ms·

This adds more cache consistency issues, concurrency issues, network blocking issues, authn+z issues, daemon process supervision issues, ... for what? To store

This adds more cache consistency issues, concurrency issues, network blocking issues, authn+z issues, daemon process supervision issues, ... for what? To store your SQL data in a remote data store? I would rather my app shell out to gsutil (or curl!) than deal with all of this. Simple hack: mount a tmpfs filesystem and write your sqlite database there. Every 30 seconds, stop writing, make a copy of the old database to a new file, start writing to the new file, fork a process to copy the database to the object store and delete the old database file when it's done. Add a routine during every 30 second check to look for stale files/forked processes. Why use that "crazy hack", versus the impeccably programmed Cloud Backed SQLite solution? - Easier to troubleshoot. The components involved are all loosely-coupled, well tested, highly stable, simple operations. Every step has a well known set of operations and failure modes that can be easily established by a relatively unskilled technician. - File contents are kept in memory, where copies are cheap and fast. - No daemon outside of the program to maintain - Simple global locking semantics for the file copy, independent of the application - Thread-safe - No network blocking of the application - Authentication is... well, whatever you want, but your application doesn't have to handle it, an external application can. - It's (mostly) independent of your application, requiring less custom coding, allowing you to focus more on your app and less on the bizarre semantics of directly dealing with writes to a networked block object store.

vasco 3y ago
> ... for what? Backups, replication to different regions, access to the data etc become standardized when it's the same as any other cloud bucket. This makes compliance easier and there's no need to roll your own solution. I also never had to troubleshoot sqlite, so I'd trust this will be more robust than what I'll come up with, so I don't get your troubleshooting argument. Not everyone will care about this, but those can just not use it I guess.
beebmam 3y ago
Or just use a database like Postgres. It's a one liner to download and start up a postgres container.
- chaxor 3y ago
  Is it really now? I have heavily preferred SQLite to postgres after having nightmares with it many years ago. I preferred SQLite because I often needed a single 1TB file to hand someone to get started on a project with the DB, which seemed far more complex with postgres. There were a ton of steps required to get things set up, just with the installation and authentication alone. I recall needing to make a few users and passwords and specify which tables were allowed for different users, etc. It was far, far too complex for just storing data. Multiple users are extremely uncommon for data analysis, and hiding certain tables isn't really needed most of the time. I know it does have it's use cases, but if you don't need access control and more complexities, postgres (at least then) seems like so much hassle. If it's better now perhaps I may try it, but I can't say I have high hopes.
  - fauigerzigerk 3y ago
    Is cloud storage authnz is any less convoluted? I realise you're responding only to the "one liner" argument, but as soon as you do anything with SQLite that involves the cloud or authnz or secondary processes, you're right back to where you were with client/server database systems in terms of complexity.
    - chaxor 3y ago
      I never touch cloud dev, so it's a bit out of the realm I was discussing, but yes my understanding is that postgres is more geared towards companies trying to serve millions of simultaneous users or something. I haven't seen it as very useful simply because I don't develop in that space.
  - sauercrowd 3y ago
    Using the docker container is a breeze - add a POSTGRES_PASSWORD env variable and you're all set. I'd be curios how it performs for a TB of data but I would be surprised if it straight out breaks. https://hub.docker.com/_/postgres https://hub.docker.com/_/postgres
    - 3y ago
CraigJPerry 3y ago
>> Every 30 seconds, stop writing, make a copy of the old database That sounds so simple that i almost glossed over it. But then the alarm bells started ringing. If you don’t need to collaborate with other people, this kind of hacking is mostly fine (and even where its not fine, you can always upgrade to using filesystem snapshots to reduce copy burden when that becomes an issue and use the sqlite clone features to reduce global lock contention when you grow into that hurdle etc etc) but if you need to collaborate with others, the hacky approaches always lead to tears. And the tears arrive when you’re busy with something else.
- chasil 3y ago
  If you really need to collaborate with other people, then you likely want something that implements GRANT and REVOKE.
  - sitkack 3y ago
    Everything it’s just a URL you already have Grant and revoke
gregwebs 3y ago
If I am reading this right the main difference is you are copying the entire db whereas the linked version can copy just the changed blocks. Also in your solution it seems 30 seconds of data loss is expected although that could be avoided by using cloud disk instead of tmpfs and then you could take snapshots as well
- throwawaaarrgh 3y ago
  Oh I see, it has weird independent block file semantics (weird because they aren't reused, unless they are?). That's interesting, although potentially problematic, with the whole "I'll just keep adding files, unless I remember to cleanup, which could cause problems" thing If you can't handle 30 seconds of data loss you should probably be using a real database. I could be wrong but I don't remember this guaranteeing every transaction synchronously committed to remote storage? There's a lot of caveats around block store restrictions, multi client access, etc. It looks a lot more complicated than it's probably worth. I'll grant you in some cases it's probably useful, but I also can't see a case where a regular database wouldn't be more ideal.
  - electroly 3y ago
    This module DOES guarantee that transactions are synchronously committed to remote storage before completing the transaction. The transaction can be rolled back if uploading to remote storage fails; this is mentioned in the documentation. Ctrl+F "rolled back" in the doc. Doing it asychronously would be more complicated and this is a pretty simplistic module.
ElectricalUnion 3y ago
> Every 30 seconds, stop writing, make a copy of the old database to a new file I hope that by "copy of the old database" you mean "Using the SQLite Online Backup API"[1]. This procedure [copy of sqlite3 database files] works well in many scenarios and is usually very fast. However, this technique has the following shortcomings: * Any database clients wishing to write to the database file while a backup is being created must wait until the shared lock is relinquished. * It cannot be used to copy data to or from in-memory databases. * If a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery. [1] https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html

vasco 3y ago

> ... for what? Backups, replication to different regions, access to the data etc become standardized when it's the same as any other cloud bucket. This makes compliance easier and there's no need to roll your own solution. I also never had to troubleshoot sqlite, so I'd trust this will be more robust than what I'll come up with, so I don't get your troubleshooting argument. Not everyone will care about this, but those can just not use it I guess.

beebmam 3y ago

Or just use a database like Postgres. It's a one liner to download and start up a postgres container.

chaxor 3y ago
Is it really now? I have heavily preferred SQLite to postgres after having nightmares with it many years ago. I preferred SQLite because I often needed a single 1TB file to hand someone to get started on a project with the DB, which seemed far more complex with postgres. There were a ton of steps required to get things set up, just with the installation and authentication alone. I recall needing to make a few users and passwords and specify which tables were allowed for different users, etc. It was far, far too complex for just storing data. Multiple users are extremely uncommon for data analysis, and hiding certain tables isn't really needed most of the time. I know it does have it's use cases, but if you don't need access control and more complexities, postgres (at least then) seems like so much hassle. If it's better now perhaps I may try it, but I can't say I have high hopes.
- fauigerzigerk 3y ago
  Is cloud storage authnz is any less convoluted? I realise you're responding only to the "one liner" argument, but as soon as you do anything with SQLite that involves the cloud or authnz or secondary processes, you're right back to where you were with client/server database systems in terms of complexity.
  - chaxor 3y ago
    I never touch cloud dev, so it's a bit out of the realm I was discussing, but yes my understanding is that postgres is more geared towards companies trying to serve millions of simultaneous users or something. I haven't seen it as very useful simply because I don't develop in that space.
- sauercrowd 3y ago
  Using the docker container is a breeze - add a POSTGRES_PASSWORD env variable and you're all set. I'd be curios how it performs for a TB of data but I would be surprised if it straight out breaks. https://hub.docker.com/_/postgres https://hub.docker.com/_/postgres
  - 3y ago

CraigJPerry 3y ago

>> Every 30 seconds, stop writing, make a copy of the old database That sounds so simple that i almost glossed over it. But then the alarm bells started ringing. If you don’t need to collaborate with other people, this kind of hacking is mostly fine (and even where its not fine, you can always upgrade to using filesystem snapshots to reduce copy burden when that becomes an issue and use the sqlite clone features to reduce global lock contention when you grow into that hurdle etc etc) but if you need to collaborate with others, the hacky approaches always lead to tears. And the tears arrive when you’re busy with something else.

chasil 3y ago
If you really need to collaborate with other people, then you likely want something that implements GRANT and REVOKE.
- sitkack 3y ago
  Everything it’s just a URL you already have Grant and revoke

gregwebs 3y ago

If I am reading this right the main difference is you are copying the entire db whereas the linked version can copy just the changed blocks. Also in your solution it seems 30 seconds of data loss is expected although that could be avoided by using cloud disk instead of tmpfs and then you could take snapshots as well

throwawaaarrgh 3y ago
Oh I see, it has weird independent block file semantics (weird because they aren't reused, unless they are?). That's interesting, although potentially problematic, with the whole "I'll just keep adding files, unless I remember to cleanup, which could cause problems" thing If you can't handle 30 seconds of data loss you should probably be using a real database. I could be wrong but I don't remember this guaranteeing every transaction synchronously committed to remote storage? There's a lot of caveats around block store restrictions, multi client access, etc. It looks a lot more complicated than it's probably worth. I'll grant you in some cases it's probably useful, but I also can't see a case where a regular database wouldn't be more ideal.
- electroly 3y ago
  This module DOES guarantee that transactions are synchronously committed to remote storage before completing the transaction. The transaction can be rolled back if uploading to remote storage fails; this is mentioned in the documentation. Ctrl+F "rolled back" in the doc. Doing it asychronously would be more complicated and this is a pretty simplistic module.

ElectricalUnion 3y ago

> Every 30 seconds, stop writing, make a copy of the old database to a new file I hope that by "copy of the old database" you mean "Using the SQLite Online Backup API"[1]. This procedure [copy of sqlite3 database files] works well in many scenarios and is usually very fast. However, this technique has the following shortcomings: * Any database clients wishing to write to the database file while a backup is being created must wait until the shared lock is relinquished. * It cannot be used to copy data to or from in-memory databases. * If a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery. [1] https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html