Hacker News Reader

>> 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.

If you really need to collaborate with other people, then you likely want something that implements GRANT and REVOKE.

Everything it’s just a URL you already have Grant and revoke

> ... 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.

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

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.

If you had to make a few users and passwords and specify which tables were allowed for different users it is only because you chose to design your app this way. You must have felt there was some good reason to divide it up like that.

If that was the case, sqlite would have been unsuitable for your needs.

In other words, the complexity you describe was not caused by postgres. It was caused by your app design. Postgres was able to accommodate your app in a way that sqlite cannot.

Sqlite does have the "it's all contained in this single file" characteristic though. So if and when that's an advantage, there is that. Putting postgres in a container doesn't exactly provide the same characteristic.

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

It works fine, even w/larger data. The data is stored on a bind-mount volume on the host; not in the container.

SQLite can be awesome for huge data sets as I've rarely found anything that can ingest data as rapidly but, as with any database, it requires some specific tweaking to get the most out of it.

The biggest headache is the single write limitation, but that's no different than any other database which is merely hidden behind various abstractions. The solution to 90% of complaints against SQLite is to have a dedicated worker thread dealing with all writes by itself.

I usually code a pool of workers (i.e. scrapers, analysis threads) to prepare data for inserts and then hand it off for rapid bulk inserts to a single write process. SQLite can be set up for concurrent reads so it's only the writes that require this isolation.

This is the way. It would be nice to have a very simple APi that handles the multiprocessing of data for ETL-like pipelines and serializes to one write thread out there. There are a few that get close to that area but I haven't seen any that really nail it.

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.

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.

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -p5432:5432 -d postgres

There is an occasional downside with the Postgres container though... no automatic upgrade path between major versions:

https://github.com/docker-library/postgres/issues/37

So, if you want to keep on a supported version of PostgreSQL then every few years you'll need to figure something out.

Hopefully that gets fixed at some point though.

My in-development "automatically upgraded PostgreSQL" container is about ~400MB though, which is ~200MB more than the standard one. For my use-cases the extra space isn't relevant, but for other people it probably would be.

Just in case anyone here stumbles over this and is interested, there's now a PostgreSQL docker image that automatically upgrades the PostgreSQL data files before launching the server:

https://hub.docker.com/repository/docker/pgautoupgrade/pgaut...

> 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

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

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.

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.