Cloud Backed SQLite
sqlite.org
sqlite.org
I found a really interesting library called sql.js-httpvfs[0] that does pretty much all the work. I chunked up my 350Mb sqlite db into 43 x 8Mb pieces with the included script and uploaded them with my static files to GitHub, which gets deployed via GitHub Pages.[1]
It's in the very rough early stages but you can check it out here.
I recommend going into the console and network tab to see it in action. It's impressively quick and I haven't even fine-tuned it at all yet. SQLite rules.
The middle tier isn't even fully described yet, but does say 20GB limit (and from $29/month) - I suppose that means that's a base from which you'll be able to pay for more storage as needed.
Wouldn't it be better to make a proper client-server API similar to traditional SQL databases, but on top of SQLite?
We have one: we run thousands of VMs at a time, all accessing the same "database". Since we already have a good amount of horizontally-scaled compute, having to maintain a separate database cluster, or large vertically-scaled database instance, to match our peak load requirements is problematic in terms of one or more of cost, complexity, and performance. In particular, horizontally-scaled distributed databases tend not to scale up and down efficiently, because of the complexity and time involved in joining the cluster, so the cost benefits of horizontal scaling of compute are lost.
An approach like this can fit well in cases like these.
In our case, the data is organized such that there’s usually only one client at a time that would need to write to a given db file. Think for example of a folder per user, each containing a db file and various other files for that user. What we’re doing is actually even more granular than that - we have a folder for each object that a user could be working on at a given time.
Client/Server databases are just remote data structures. (E.g. Redis is short for "Remote Dictionary Server")
Sometimes you want your data structures and algorithms to run locally. Could be performance, privacy, cost, or any number of reasons.
Local, in-memory data structures hit a few bottlenecks. First, they may not fit in memory. A mechanism for keeping the dataset in larger storage (e.g. disk) and paging in the necessary bits as needed extends the range of datasets one can comfortably work with locally by quite a bit. That's standard SQLite.
A second potential bottleneck to local data structures is distribution. We carry computers in our pockets, on our watches, in our cars. Delivering large datasets to each of those locations may be impractical. Cloud based VFS allows the benefits of local data structures on the subset they need without requiring them to fetch the entire dataset. That can be a huge win if there's a specific subset they need.
It always depends on the use case, but when the case fits there are a lot of big wins here.
With client-server architecture, the server code owns the data format, while in this storage-level remote access case, you have to ensure that all of your clients are updated simultaneously. Depending on your architecture it might or might not be feasible.
Querying isn't a problem, you can query as much as you want. But where you'll hit the Sqlite limitation is in scaling for multi user write scenarios. Yes, even Sqlite can handle a few concurrent write requests but once your users start scaling in millions, you'll eventually need a proper RDBMS like mysql or postgres.
However if I want people to be able to submit corrections to the transcriptions, I need a way to do that. I was thinking of setting up some sort of queue system to make writes non-realtime since, for this app, it's not a big deal.
It will be in interesting challenge, for sure.
Were I in your position I'd just create a copy of the DB with the full transcript in each row and run the search against that. If you only have 4 million words, creating an extra copy of the database shouldn't be prohibitively large.
The traditional FTS5 technique as documented [0] should work, same as if this were a local SQLite database. You'll have to duplicate the transcriptions into TEXT form (one row per document) and then use FTS5 to create a virtual table for full text searching. The TEXT form won't actually be accessed for searching, it's just used to build the inverted index.
https://github.com/noman-land/transcript.fish/issues
Thank you!
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.
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.
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.
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.
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.
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.
https://hub.docker.com/repository/docker/pgautoupgrade/pgaut...
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 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.
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.
Is this just a toy? What are the upsides in practice of deploying an embedded database to a cloud service?
* Using indexes and only touching the pages you need. This is specifically much better than S3 Select, which we considered as an alternative.
I built a SQLite VFS module just like the one linked here, and this is what I use it for in production. My use case obviously does not preclude other people’s use cases. It’s one of many.
GP asked whether this is a toy and what the upsides might be. I answered both questions with an example of my production usage and what we get out of it.
It's entirely predicated on developer experience, otherwise there's no reason to specifically reach for an embedded database (in-process doesn't mean anything when the only thing the process is doing is running your DB)
Here’s an in-the-weeds tip for anyone attempting the same: your tables should all be WITHOUT ROWID tables. Otherwise SQLite sprays rows all over the place based on its internal rowids, ruining locality when you attempt to read rows that you thought would be consecutive based on the primary key.
If it ever turns out, at some point in the future, that you do need features from a standard RDBMS after all, you are going to regret not using Postgres in the first place, because re-engineering all of that is going to be vastly more expensive than what it would have cost to just "do it right" from the start.
So it seems that Cloud SQLite is basically a hyper-optimization that only makes sense if you are completely, totally, 100% certain beyond any reasonable doubt that you will never need anything more than that.
Don’t know how else to say “we were not born yesterday; we thought of that” politely here. This definitely isn’t something to have your junior devs work on, nor is it appropriate for most DB usage, but that’s different than it not having any use. It’s a relatively straightforward solution to a niche problem.
For my project I ended up just exporting the graph edges as JSON, but I'm curious if it would still be possible to make work.
Aside from that, I use an entity-attribute-value model. This ensures that all the tables are narrow. Set your primary key (again, with WITHOUT ROWID) to put all the values for the same attribute next to each other. That way, when you query for a particular attribute, you'll get pages packed full with nothing but that attribute's values in the order of the IDs for the corresponding entities (which you manually clustered).
It's worth repeating one more time: you must use WITHOUT ROWID. SQLite tables otherwise don't work the way you'd expect from experience with other DBs; the "primary" key is really a secondary key if you don't use WITHOUT ROWID.
I'm trying to find all the ancestors of a node in a DAG, so the optimal clustering would vary depending on the node I'm querying for
If your app is running on premises and the DB is on cloud, you can also move the DB on premises so the costs are lower.
If your app runs on cloud, too, then you already are paying for the cloud compute so you can just fire up an VM and install Postgres on that.
> If your app runs on cloud, too, then you already are paying for the cloud compute so you can just fire up an VM and install Postgres on that.
This part doesn't make sense. If your app is on the cloud, you're paying for the cloud compute for the app servers. "Firing up a VM" for PostgreSQL isn't suddenly free.
Add in that your RDS instance needs to be high availability like S3 is (and like our RDBMS is). That means a multi-AZ deployment. Multiply your RDS cost by two, including the cost of storage. That still isn't as good as S3 (double price gets you a passive failover partner in RDS PostgreSQL; S3 is three active-active AZs), but it's the best you can do with RDS. We're north of $500/month now for your example.
Add in the cost of backups, because your RDS database's EBS volume doesn't have S3's durability. For durability you need to store a copy of your data in S3 anyway.
Add in that you can access S3 without cross-AZ data transfer fees, but your RDS instance has to live in an AZ. $0.02/GB both ways.
Add in the personnel cost when your RDS volume runs out of disk space because you weren't monitoring it. S3 never runs out of space and never requires maintenance.
$500/month for 1TB of cold data? We were never going to pay that. I won't disclose the size of the data in reality but it's a bunch bigger than 1TB. We host an on-prem database cluster for the majority of things that need a RDBMS, specifically because of how expensive RDS is. Things probably look different for a startup with no data yet, blowing free AWS credits to bootstrap quickly, but we are a mature data-heavy company paying our own AWS bills.
As a final summary to this rant, AWS bills are death by a thousand papercuts, and cost optimization is often a matter of removing the papercuts one by one. I'm the guy that looks at Cost Explorer at our company. One $500/month charge doesn't necessarily break the bank but if you take that approach with everything, your AWS bill could crush you.
I use a presto containers to mock Athena for local and CI tests + a psql container for metastore.
You can partition using several columns. But I get your point, it's not optmized for row level operations in general.
SQLite running in-process is so convenient to build with that even when people use other DBs they write wrappers to sub it in
I guess this is an attempt to let that convenience scale?
pg_dump -U postgres db | ssh user@rsync.net "dd of=db_dump"
mysqldump -u mysql db | ssh user@rsync.net "dd of=db_dump"
I see that there is a '.dump' command for use within the SQLite console but that wouldn't be suitable for pipelining ... is there not a standalone 'sqlitedump' binary ?
sqlite3 /path/to/db.sqlite .dump echo .dump | sqlite3 db | ssh user@rsync.net "dd of=db_dump"
sqlite3 db .dump | ...
I have added that one-liner to the remote commands page:
https://www.rsync.net/resources/howto/remote_commands.html
... although based on some of your siblings, I changed .dump to .backup ...
As noted '.dump' will work in your pipeline, but with the caveats I and others noted elsewhere.
sqlite3 my_database .dump | gzip -c | ssh foo "dd of=db_dump"
From the SQLite docs:
When a SAVEPOINT is the outer-most savepoint and it is not within a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED TRANSACTION
[0]: https://github.com/sqlite/sqlite/blob/3748b7329f5cdbab0dc486...
“To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.”
“It is not possible to change the page size after entering WAL mode.”
“In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.”
https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf
SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.
I mention this as I once wasted a bunch of time trying to get a backup created from a '.dump | sqlite3' pipe to work before taking a proper look at the application code, and finally saw that it silently ignored databases without the correct 'application_id' or 'user_version'.
I once had a problem where my application couldn't read from a SQLite DB whose file was read-only. Turned out that even a read-only access to the DB required a write access to the directory, for a WAL DB. This was partly fixed years later, but I'd learned the hard way that a SQLite DB may be more than a single file.
> The system currently supports Azure Blob Storage and Google Cloud Storage.
I'd interpret that as either a hard "fuck you" to AWS, or a sign that the S3 API is somehow more difficult to use for this purpose.
I wrote a distributed lock on Google Cloud Storage. https://www.joyfulbikeshedding.com/blog/2021-05-19-robust-di... During my research it was quickly evident that GCS has more concurrency control primitives than S3. Heck S3 didn't even guarantee strong read-after-write until recently.
Almost 3 years https://aws.amazon.com/blogs/aws/amazon-s3-update-strong-rea...
Putting the storage in the cloud is completely orthogonal to this ideology. Both latency & simplicity will suffer dramatically. I don't think I'd ever use this feature over something like Postgres, SQL Server or MySQL.
I'm confused by your usage of the term "orthogonal". In my experience, it is used to express that something is independent of something else, similarly to how in mathematics orthogonal vectors are linearly independent and thus form a basis of their linear span. Here, however, it seems you mean to say that "putting storage in the cloud runs counter to this ideology"?
> Ironically, the meaning of "orthogonal" has become orthogonal to the real meaning because now it can mean both "perpendicular" and "parallel", both "at odds" and "unrelated"
Edit: found it https://news.ycombinator.com/item?id=1997093 (my memory was pretty close)
Would this mean I might eventually be able to do something insane like run Datasette (from within Pyodide) against an external cloud storage?
However, it might become possible to run datasette etc much more easily in an edge function.
https://docs.aws.amazon.com/AmazonS3/latest/userguide/enabli...
Sounds like trouble.
In this setup you use something like blobstore for public shared db access. Looks like single writer at a time.
In traditional databases you connect to a database server.
This is like having a cloud sync for your local app state - shared between devices on a cloud device.
Not quite. The very point is not needing a server. Subtle difference.
Think more data lake and less relational db.
What if I would implement an S3-backed block storage. Like every 16MB chunk is stored in a separate object. And filesystem driver would download/upload chunks just like it does it with HDD.
And then format this block device with Ext4 or use it in any other way (LVM, encrypted FS, RAID and so on).
Is it usable concept?
> ...I implemented a virtual file system that fetches chunks of the database with HTTP Range requests when SQLite tries to read from the filesystem...
[1]: https://phiresky.github.io/blog/2021/hosting-sqlite-database...
Seems like a usable concept yes.
I'm not clued-in to AWS's equivalent, but a quick google suggests it's AWS EBS (Elastic Block Storage), though it doesn't seem to coexist side-by-side with normal S3 storage, and their API for reading/writing blocks looks considerably more complicated than Azure's: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-acce...
This thread is in the context of filesystems like ext4, which are completely unsafe for concurrent mounts - if you connect from multiple machines then it will just corrupt horrifically. If your filesystem was glusterfs, moose/lizard, or lustre, then they have ways to handle this (with on-disk locks/semaphores in some other sector).
Turns out, there's no transformation layers or anything. You upload the image using a special tool, it saturates your uplink for a few minutes, and before you can brew some more coffee the VM will be booting up.
Why does the forum say some messages are from 19 years ago?
Except it's 1.19 years ago.
And the oldest one is from 2020. https://sqlite.org/cloudsqlite/forumpost/da9d84ff6e
It might be announced recently, but it has been under development for a while.
Should take only a few lines of PHP. Maybe just one:
echo DB::select($_GET['query']);
And you can add a 100 GB volume for just $10/month.
Anyone care to prove me wrong?