2 ms·
SQLite directly against S3 is workable if you mean querying a read-only database. For example, from Go, you could use my driver, and point it to a database fil
by ncruces 1y ago
SQLite directly against S3 is workable if you mean querying a read-only database.
For example, from Go, you could use my driver, and point it to a database file stored in S3 using this: https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readervfs#example-package-Http https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/readerv...
For read-write it's a terrible idea. Object storage assumes objects are immutable. There may be some support for appends, but modifying the middle of an object in place involves copying the entire thing.
What is on the verge of becoming viable is to use Litestream to do asynchronous replication to S3, and have read replicas that stream the data directly from S3. But what's stored in S3 isn't a database file, but a format created for the purpose called LTX.
- jtarchie 1y agoncruces helped me with some code I made for VFS. It uses [zstd seekable](https://github.com/jtarchie/sqlitezstd https://github.com/jtarchie/sqlitezstd) for reading a file. I thought it would be really well-suited for S3. - Support for HTTP range queries - "Fast" read times - No disk required I was wrong. It turns out that for specific SQL queries, it might be fine, but not fast. For queries that do aggregations, like `COUNT`, sqlite loads the whole database anyway.