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