15 ms·

Show HN: Query SQLite files stored in S3

160 points by polyrand 4y ago

aleph- 4y ago
Nifty, reminds me of https://github.com/backtrace-labs/verneuil https://github.com/backtrace-labs/verneuil or possibly https://litestream.io/ https://litestream.io/
simonw 4y ago
This is really neat. It looks like it's using a similar trick to https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database... from last year - querying SQLite databases hosted as static files using the HTTP range header to fetch back just the pages from the database file that are needed by a specific query.
- chrsig 4y ago
  i wish s3 supported multiple ranges in the range header. I've done this sort of access with a custom columnar format that stored a set of columns in a single file it was a non-trivial overhead to have to send an http request per column, when i could have sent one with multiple ranges. i can imagine something similar with this, fetching multiple pages in one request
- mbo 4y ago
  [Shameless plug] I built a very minimal wrapper to get this working for the Observable web data visualisation environment https://observablehq.com/@mjbo/sqljs-httpvfs https://observablehq.com/@mjbo/sqljs-httpvfs
remram 4y ago
Is there a benefit over the existing S3 VFS sqlite3-s3vfs,which also uses APSW?
- 8organicbits 4y ago
  See: https://github.com/litements/s3sqlite#alternatives https://github.com/litements/s3sqlite#alternatives
- polyrand 4y ago
  Author here! I would say the main advantage is that this just needs uploading a single file to S3. `sqlite-s3vfs` will split the database in pages and upload the pages separately to a bucket prefix. Having just a single file has some advantages, like making use of object versioning in the bucket[0]. I also think that relying on s3fs[1] makes the VFS more flexible[2] than calling `boto3` as `sqlite3-s3vfs` does. [0]: https://s3fs.readthedocs.io/en/latest/index.html?highlight=version#bucket-version-awareness https://s3fs.readthedocs.io/en/latest/index.html?highlight=v... [1]: https://s3fs.readthedocs.io/en/latest/index.html https://s3fs.readthedocs.io/en/latest/index.html [2]: https://s3fs.readthedocs.io/en/latest/index.html#s3-compatible-storage https://s3fs.readthedocs.io/en/latest/index.html#s3-compatib...
  - remram 4y ago
    I see. The drawback of course is that you don't support writing. So there are for completely different use cases, thanks!
psanford 4y ago
I made a similar sqlite vfs in Go[1]. I really love the VFS api. HTTP range queries is about the most mild thing you can do with it. The VFS implementations that use non-traditional backing stores are more fun. Like say, using DynamoDB as a read/write backing store for your sqlite database[2]. [1]: https://github.com/psanford/sqlite3vfshttp https://github.com/psanford/sqlite3vfshttp [2]: https://github.com/psanford/donutdb https://github.com/psanford/donutdb
- polyrand 4y ago
  That DynamoDB VFS looks cool! I agree that the VFS api makes one think about plenty of crazy ideas. Someone is working on a VFS based on Foundation DB[0] that looks very promising. It was recently discussed here[1] [0]: https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite [1]: https://news.ycombinator.com/item?id=32269287 https://news.ycombinator.com/item?id=32269287
  - endisneigh 4y ago
    I wish I knew rust so I could contribute, as I think that's the best one. FDB has the best durability/transactional guarantees of any key/value store that's distributed as far as I've seen. Syclla for example is also pretty great but doesn't have the same guarantees for transactions.
    - victor106 4y ago
      > FDB has the best durability/transactional guarantees of any key/value store that's distributed as far as I've seen. Can you expand more on this? I keep hearing that on HN but what guarantees does FDB provide over other key/value DB’s?
      - endisneigh 4y ago
        I’d read this: https://apple.github.io/foundationdb/cap-theorem.html https://apple.github.io/foundationdb/cap-theorem.html And https://apple.github.io/foundationdb/consistency.html https://apple.github.io/foundationdb/consistency.html For a summary. Tldr is that FDB provides transactional guarantees similar to an rdbms but can be distributed and has optimistic concurrency, meaning locks are not necessary. In the context of SQLite that’s the main issue it has and FDB is positioned to solve it.
ks2048 4y ago
This is cool. It would be interesting to see some stats like how many separate requests are needed for a single SELECT statement, since S3 charges per GET, even on a small range, I believe. Obviously that would depend on the query and various DB settings like page_size, etc. Also wondering if this does aggressive local caching for the same reason.
- polyrand 4y ago
  Author here! This is something I want to look into. One of the reasons to use s3fs is that the underlying `fsspec` classes already handle caching. S3File (from s3fs) inherits from AbstractBufferedFile[0], which has a cache[1], implemented here[2]. I haven't read through all the code yet, but experimenting with different cache implementations will probably make the VFS faster. It will also depend on the type of queries you're executing. [0]: https://github.com/fsspec/s3fs/blob/ad2c9b8826c75939608f5561a2a0ad33b9dafaf9/s3fs/core.py#L1830 https://github.com/fsspec/s3fs/blob/ad2c9b8826c75939608f5561... [1]: https://github.com/fsspec/filesystem_spec/blob/2633445fc54797c79a7ac96d213bd24dfbdfcdc2/fsspec/spec.py#L1318 https://github.com/fsspec/filesystem_spec/blob/2633445fc5479... [2]: https://github.com/fsspec/filesystem_spec/blob/2633445fc54797c79a7ac96d213bd24dfbdfcdc2/fsspec/caching.py#L118 https://github.com/fsspec/filesystem_spec/blob/2633445fc5479...
  - vlovich123 4y ago
    Do you have any insight into whether the cache will alter consistency in the face of concurrent writes from multiple locations in a way that SQLite can't handle? I imagine this would be a very real concern for a project like this.
    - polyrand 4y ago
      This VFS is only for reading databases stored in S3, not writing. The README gives some alternatives for other implementations which allow writing.
Datagenerator 4y ago
This could be done by mounting S3 as filesystem and plain sqlite. Any differences?
- simonw 4y ago
  If you did that, wouldn't you have to fetch the entire SQLite database file before you could perform any queries? With this you don't need to do that - the software magically fetches just the pages needed to answer your specific query.
  - spullara 4y ago
    That really depends on the S3 filesystem implementation. It could make the same sort of optimizations as this does using range reads.
    - michalc 4y ago
      Ah even if this were the case, in some situations you can’t mount filesystems (e.g. Docker without certain capabilities enabled?), and so something more in the application layer would be the only way to access SQLite on S3?
- samatman 4y ago
  Yep! This kind of thing works around these problems. https://www.sqlite.org/draft/useovernet.html https://www.sqlite.org/draft/useovernet.html
endisneigh 4y ago
There have been many of these custom vfs implementations. Is there a benchmark on performance between them?
gkaemmer 4y ago
This is cool, I'll probably end up using this (we store sqlite snapshots in S3). It'd be very handy if this was packaged as a CLI that worked like `sqlite3`, so you could get a SQL repl by running: s3sqlite mybucket/mydb.sqlite3
- psanford 4y ago
  The sqlite cli supports loading modules at runtime, so you can do things like load and use a db via a custom vfs. My Go implementation of this vfs supports running in loadable module mode[1]. [1]: https://github.com/psanford/sqlite3vfshttp#demo https://github.com/psanford/sqlite3vfshttp#demo
svnpenn 4y ago
I feel like people are pushing SQLite beyond its limits with this stuff. If you're going to do something like this, wouldn't just a normal client/server option like PostgreSQL be way more performant? I think SQLite is fantastic, but this just smacks of scope creep.
- samatman 4y ago
  I would call this hacking, actually. Is it useful in a financially productive sense? Maybe, don't care. It's pushing a program we like past its limits. That's fun.
- freeqaz 4y ago
  There are some real benefits to using SQLite as a "serialization format" over something like JSON or Protobuf. To elaborate on a specific case, think about a 'package-lock.json' file in a Node project. These files are frequently many megabytes in size, even for simple projects, and if you wanted to do something like inventory _every package_ used on GitHub, it would be a ton of data. You might argue that you could compress these files because they're text, and that's true, but what about when you only need to read a subset of the data? For example, what if you needed to say, "Does this project depend on React?" If you have it as JSON or Protobuf, you have to deserialize the entire data structure before you can query it (or write some nasty regex). With SQLite you have a format you can efficiently search through. There are some other formats in this space like ORC and Parquet but they're just optimized for column-reads (read only _this_ column). They don't provide efficient querying over large datasets like SQLite can. This is at least my understanding. If anybody has a perspective that's opposite, I'd appreciate hearing it!
rgovostes 4y ago
I was thinking it would be nice if there were a generic VFS library for C that offered stdio-like APIs (fread, fprintf, etc.) in order to do tricks like this beyond SQLite, without having to make major changes porting existing codebases over to different APIs. It occurs to me that because a FILE * is an opaque pointer, it ought to be possible for a libc to provide this functionality: the FILE struct could contain function pointers to the the underlying implementations for writing, flushing, etc. This ought to be backwards compatible with existing FILE * returning APIs (fopen, fdopen, popen), while allowing new backing stores without having to rewrite (or even recompile) existing code.
- teraflop 4y ago
  You're in luck: https://www.gnu.org/software/libc/manual/html_node/Streams-and-Cookies.html https://www.gnu.org/software/libc/manual/html_node/Streams-a...
  - rgovostes 4y ago
    Thanks! Looks like it is a glibc (and musl) feature, though this led me to discover funopen() in BSD and macOS: https://man.openbsd.org/funopen.3 https://man.openbsd.org/funopen.3
- vlovich123 4y ago
  Local file semantics like FILE* don't map well to how large scale object storage systems operate. For example, FILE* supports seeking (specifically for writes) and mutating files after construction whereas object storage systems generate immutable copies of files once made visible after uploading. There's also no fsync. FWIW S3 kind of is that "de facto" interface. There were attempts at standardizing it via OpenStack's SWIFT project but to my eye S3 remains the industry de facto for better or worse.
redleader55 4y ago
Sorry for the stupid question: does this work with MinIO?
- polyrand 4y ago
  Yes, I actually used MinIO to test it (see the `test.py` script)
banana_giraffe 4y ago
I implement a similar concept for Python using APSW and boto3. https://gist.github.com/Q726kbXuN/56a095d0828c69625fa44c23118e5221 https://gist.github.com/Q726kbXuN/56a095d0828c69625fa44c2311... It's not really useful for deep dives into data, though it has gotten me out of a few jams that would have otherwise required a real database just to pull out a value or two from a large dataset.
civilized 4y ago
This is nice, but before you go to the trouble of using it, consider just putting your CSV, JSON, or parquet data file in S3 and query it with S3 Select https://docs.aws.amazon.com/AmazonS3/latest/userguide/selecting-content-from-objects.html https://docs.aws.amazon.com/AmazonS3/latest/userguide/select... I haven't tried this myself but parquet + S3 select should be especially good for analytical queries.
- grangerg 4y ago
  Seems like it should be. That's pretty much the status quo with how Azure Synapse works. You put your Parquet file out on storage, and query it (via Spark or Pandas).
lootsauce 4y ago
Has anyone created a vfs for redis?
sntran 4y ago
This would be quite convenient when dealing with MBtiles files hosted on S3 or similar. There are efforts such as PMTiles[1] that let us use HTTP Range query to retrieve tiles directly from S3, but we still have to convert MBTiles to PMTiles first. More importantly that OSM is considering providing MBTiles directly (besides PBF)[2]. [1]: https://protomaps.com/docs/pmtiles/ https://protomaps.com/docs/pmtiles/ [2]: https://github.com/openstreetmap/operations/issues/565 https://github.com/openstreetmap/operations/issues/565
- michalc 4y ago
  Aha! This is done in https://github.com/uktrade/mbtiles-s3-server https://github.com/uktrade/mbtiles-s3-server, but using an alternative library to query SQLite on S3, https://github.com/michalc/sqlite-s3-query https://github.com/michalc/sqlite-s3-query (Full disclosure: I wrote most of both of these)
JaggerFoo 4y ago
I read of someone using AWS EFS for sqlite, which has nfs functionality, if I recall correctly. However I like the idea of using a local store with litestream to S3 and updating to the latest db data from S3, if using my application on another device or computer. Sort of like github. Tailscale was looking into this option, instead of Postgres and other DBs. https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/ Cheers
Rapzid 4y ago
Something really neat about SQLite is how self contained the database file is.. You can take say, a large book or log output, then copy it into a table with full text search. Optimize the DB to get rid of empty space, then ship that out to S3 or something. Now you can fetch that down and have indexed and fts access to the contents. Cool way to store data with indexes into it in a way that makes it as easy to use as copying around and opening a file; no need for dumps or restores or server processes.
- encryptluks2 4y ago
  You can also do the same with a file and you can even open it in plain text and read it too.
  - Rapzid 4y ago
    I mean, the database IS a file. The key here is that it colocates indexes and other metadata that can be used to efficiently access large data sets without having to hand-roll indexes and/or process them on-the-fly everytime you load the data up...
  - VHRanger 4y ago
    SQLite is made to replace "dread" by being generally better than storing the data as a json blob or csv There's still an argument for columnar formats like arrow, but compatibility is iffy compared to SQLite which "just works"
- bob1029 4y ago
  Most problems can be neatly packed into 1 sqlite database. Even a big AAA video game could benefit from this type of representation on disk. I am also now starting to look at SQLite as a sort of application framework / operating system. The SQLite VM is running on billions of devices right now without any issues. Seems like a safe bet if I wanted to use the relational/SQL model to represent most of my application state & logic. Anything else I want to bring to the party I can inject by way of application-defined functions.
  - 4y ago
wanghq 4y ago
If I need to query the db repeatedly (N times per second), isn't it more efficient (low latency and aws cost) to just download the db and run queries locally?
staticassertion 4y ago
A comparison to S3 Select would make sense since S3 Select lets you leverage RANGE, leverage compact formats like compressed Parquet, and would presumably have a similar fundamental cost ie: get + data transfer.
traceroute66 4y ago
I admire people for their inventiveness, however surely attempting to square-peg-round-hole something like this into AWS S3 is only going to end up biting them in the backside ? S3 is delivered over a stateless protocol (http) and AWS makes no promises about any given request being fulfilled (the user is merely invited to retry). The safety of your data is then further dependent on what storage tier you chose. There just seem to be so many footgun opportunities here. Why not just use the right tool for the job ? (i.e. hosted DB or compute).
- rad_gruchalski 4y ago
  > Why not just use the right tool for the job ? (i.e. hosted DB or compute). Because sometimes you want a database for 10000 rows, or you have 5 logins a month and you don't want a $30/month database running in the cloud. There's a market out there for real "server-less" database that charges you per rows stored, is priced per read/write operations, and is reasonably priced for a 10000 rows per month, without having to calculate how many 4KB blocks you are going to read or write.
  - quickthrower2 4y ago
    Firebase Firestore?
  - endisneigh 4y ago
    Like… dynamodb?
    - Brystephor 4y ago
      Dynamodb doesn't do well with complex queries. Imagine you've got 4 integer fields, any combination of which can be filtered by a range. Using dynamoDb, you're going to have a hard time querying if field A, B, and D are included. If you add a fifth field, it's going to be a pain to add to historical data.
      - endisneigh 4y ago

160 points by polyrand 4y ago

Nifty, reminds me of https://github.com/backtrace-labs/verneuil https://github.com/backtrace-labs/verneuil or possibly https://litestream.io/ https://litestream.io/

simonw 4y ago

This is really neat. It looks like it's using a similar trick to https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database... from last year - querying SQLite databases hosted as static files using the HTTP range header to fetch back just the pages from the database file that are needed by a specific query.

chrsig 4y ago
i wish s3 supported multiple ranges in the range header. I've done this sort of access with a custom columnar format that stored a set of columns in a single file it was a non-trivial overhead to have to send an http request per column, when i could have sent one with multiple ranges. i can imagine something similar with this, fetching multiple pages in one request
mbo 4y ago
[Shameless plug] I built a very minimal wrapper to get this working for the Observable web data visualisation environment https://observablehq.com/@mjbo/sqljs-httpvfs https://observablehq.com/@mjbo/sqljs-httpvfs

remram 4y ago

Is there a benefit over the existing S3 VFS sqlite3-s3vfs,which also uses APSW?

8organicbits 4y ago
See: https://github.com/litements/s3sqlite#alternatives https://github.com/litements/s3sqlite#alternatives
polyrand 4y ago
Author here! I would say the main advantage is that this just needs uploading a single file to S3. `sqlite-s3vfs` will split the database in pages and upload the pages separately to a bucket prefix. Having just a single file has some advantages, like making use of object versioning in the bucket[0]. I also think that relying on s3fs[1] makes the VFS more flexible[2] than calling `boto3` as `sqlite3-s3vfs` does. [0]: https://s3fs.readthedocs.io/en/latest/index.html?highlight=version#bucket-version-awareness https://s3fs.readthedocs.io/en/latest/index.html?highlight=v... [1]: https://s3fs.readthedocs.io/en/latest/index.html https://s3fs.readthedocs.io/en/latest/index.html [2]: https://s3fs.readthedocs.io/en/latest/index.html#s3-compatible-storage https://s3fs.readthedocs.io/en/latest/index.html#s3-compatib...
- remram 4y ago
  I see. The drawback of course is that you don't support writing. So there are for completely different use cases, thanks!

psanford 4y ago

I made a similar sqlite vfs in Go[1]. I really love the VFS api. HTTP range queries is about the most mild thing you can do with it. The VFS implementations that use non-traditional backing stores are more fun. Like say, using DynamoDB as a read/write backing store for your sqlite database[2]. [1]: https://github.com/psanford/sqlite3vfshttp https://github.com/psanford/sqlite3vfshttp [2]: https://github.com/psanford/donutdb https://github.com/psanford/donutdb

polyrand 4y ago
That DynamoDB VFS looks cool! I agree that the VFS api makes one think about plenty of crazy ideas. Someone is working on a VFS based on Foundation DB[0] that looks very promising. It was recently discussed here[1] [0]: https://github.com/losfair/mvsqlite https://github.com/losfair/mvsqlite [1]: https://news.ycombinator.com/item?id=32269287 https://news.ycombinator.com/item?id=32269287
- endisneigh 4y ago
  I wish I knew rust so I could contribute, as I think that's the best one. FDB has the best durability/transactional guarantees of any key/value store that's distributed as far as I've seen. Syclla for example is also pretty great but doesn't have the same guarantees for transactions.
  - victor106 4y ago
    > FDB has the best durability/transactional guarantees of any key/value store that's distributed as far as I've seen. Can you expand more on this? I keep hearing that on HN but what guarantees does FDB provide over other key/value DB’s?
    - endisneigh 4y ago
      I’d read this: https://apple.github.io/foundationdb/cap-theorem.html https://apple.github.io/foundationdb/cap-theorem.html And https://apple.github.io/foundationdb/consistency.html https://apple.github.io/foundationdb/consistency.html For a summary. Tldr is that FDB provides transactional guarantees similar to an rdbms but can be distributed and has optimistic concurrency, meaning locks are not necessary. In the context of SQLite that’s the main issue it has and FDB is positioned to solve it.

ks2048 4y ago

This is cool. It would be interesting to see some stats like how many separate requests are needed for a single SELECT statement, since S3 charges per GET, even on a small range, I believe. Obviously that would depend on the query and various DB settings like page_size, etc. Also wondering if this does aggressive local caching for the same reason.

polyrand 4y ago
Author here! This is something I want to look into. One of the reasons to use s3fs is that the underlying `fsspec` classes already handle caching. S3File (from s3fs) inherits from AbstractBufferedFile[0], which has a cache[1], implemented here[2]. I haven't read through all the code yet, but experimenting with different cache implementations will probably make the VFS faster. It will also depend on the type of queries you're executing. [0]: https://github.com/fsspec/s3fs/blob/ad2c9b8826c75939608f5561a2a0ad33b9dafaf9/s3fs/core.py#L1830 https://github.com/fsspec/s3fs/blob/ad2c9b8826c75939608f5561... [1]: https://github.com/fsspec/filesystem_spec/blob/2633445fc54797c79a7ac96d213bd24dfbdfcdc2/fsspec/spec.py#L1318 https://github.com/fsspec/filesystem_spec/blob/2633445fc5479... [2]: https://github.com/fsspec/filesystem_spec/blob/2633445fc54797c79a7ac96d213bd24dfbdfcdc2/fsspec/caching.py#L118 https://github.com/fsspec/filesystem_spec/blob/2633445fc5479...
- vlovich123 4y ago
  Do you have any insight into whether the cache will alter consistency in the face of concurrent writes from multiple locations in a way that SQLite can't handle? I imagine this would be a very real concern for a project like this.
  - polyrand 4y ago
    This VFS is only for reading databases stored in S3, not writing. The README gives some alternatives for other implementations which allow writing.

Datagenerator 4y ago

This could be done by mounting S3 as filesystem and plain sqlite. Any differences?

simonw 4y ago
If you did that, wouldn't you have to fetch the entire SQLite database file before you could perform any queries? With this you don't need to do that - the software magically fetches just the pages needed to answer your specific query.
- spullara 4y ago
  That really depends on the S3 filesystem implementation. It could make the same sort of optimizations as this does using range reads.
  - michalc 4y ago
    Ah even if this were the case, in some situations you can’t mount filesystems (e.g. Docker without certain capabilities enabled?), and so something more in the application layer would be the only way to access SQLite on S3?
samatman 4y ago
Yep! This kind of thing works around these problems. https://www.sqlite.org/draft/useovernet.html https://www.sqlite.org/draft/useovernet.html

endisneigh 4y ago

There have been many of these custom vfs implementations. Is there a benchmark on performance between them?

gkaemmer 4y ago

This is cool, I'll probably end up using this (we store sqlite snapshots in S3). It'd be very handy if this was packaged as a CLI that worked like `sqlite3`, so you could get a SQL repl by running: s3sqlite mybucket/mydb.sqlite3

psanford 4y ago
The sqlite cli supports loading modules at runtime, so you can do things like load and use a db via a custom vfs. My Go implementation of this vfs supports running in loadable module mode[1]. [1]: https://github.com/psanford/sqlite3vfshttp#demo https://github.com/psanford/sqlite3vfshttp#demo

svnpenn 4y ago

I feel like people are pushing SQLite beyond its limits with this stuff. If you're going to do something like this, wouldn't just a normal client/server option like PostgreSQL be way more performant? I think SQLite is fantastic, but this just smacks of scope creep.

samatman 4y ago
I would call this hacking, actually. Is it useful in a financially productive sense? Maybe, don't care. It's pushing a program we like past its limits. That's fun.
freeqaz 4y ago
There are some real benefits to using SQLite as a "serialization format" over something like JSON or Protobuf. To elaborate on a specific case, think about a 'package-lock.json' file in a Node project. These files are frequently many megabytes in size, even for simple projects, and if you wanted to do something like inventory _every package_ used on GitHub, it would be a ton of data. You might argue that you could compress these files because they're text, and that's true, but what about when you only need to read a subset of the data? For example, what if you needed to say, "Does this project depend on React?" If you have it as JSON or Protobuf, you have to deserialize the entire data structure before you can query it (or write some nasty regex). With SQLite you have a format you can efficiently search through. There are some other formats in this space like ORC and Parquet but they're just optimized for column-reads (read only _this_ column). They don't provide efficient querying over large datasets like SQLite can. This is at least my understanding. If anybody has a perspective that's opposite, I'd appreciate hearing it!

rgovostes 4y ago

I was thinking it would be nice if there were a generic VFS library for C that offered stdio-like APIs (fread, fprintf, etc.) in order to do tricks like this beyond SQLite, without having to make major changes porting existing codebases over to different APIs. It occurs to me that because a FILE * is an opaque pointer, it ought to be possible for a libc to provide this functionality: the FILE struct could contain function pointers to the the underlying implementations for writing, flushing, etc. This ought to be backwards compatible with existing FILE * returning APIs (fopen, fdopen, popen), while allowing new backing stores without having to rewrite (or even recompile) existing code.

teraflop 4y ago
You're in luck: https://www.gnu.org/software/libc/manual/html_node/Streams-and-Cookies.html https://www.gnu.org/software/libc/manual/html_node/Streams-a...
- rgovostes 4y ago
  Thanks! Looks like it is a glibc (and musl) feature, though this led me to discover funopen() in BSD and macOS: https://man.openbsd.org/funopen.3 https://man.openbsd.org/funopen.3
vlovich123 4y ago
Local file semantics like FILE* don't map well to how large scale object storage systems operate. For example, FILE* supports seeking (specifically for writes) and mutating files after construction whereas object storage systems generate immutable copies of files once made visible after uploading. There's also no fsync. FWIW S3 kind of is that "de facto" interface. There were attempts at standardizing it via OpenStack's SWIFT project but to my eye S3 remains the industry de facto for better or worse.

redleader55 4y ago

Sorry for the stupid question: does this work with MinIO?

polyrand 4y ago
Yes, I actually used MinIO to test it (see the `test.py` script)

banana_giraffe 4y ago

I implement a similar concept for Python using APSW and boto3. https://gist.github.com/Q726kbXuN/56a095d0828c69625fa44c23118e5221 https://gist.github.com/Q726kbXuN/56a095d0828c69625fa44c2311... It's not really useful for deep dives into data, though it has gotten me out of a few jams that would have otherwise required a real database just to pull out a value or two from a large dataset.

civilized 4y ago

This is nice, but before you go to the trouble of using it, consider just putting your CSV, JSON, or parquet data file in S3 and query it with S3 Select https://docs.aws.amazon.com/AmazonS3/latest/userguide/selecting-content-from-objects.html https://docs.aws.amazon.com/AmazonS3/latest/userguide/select... I haven't tried this myself but parquet + S3 select should be especially good for analytical queries.

grangerg 4y ago
Seems like it should be. That's pretty much the status quo with how Azure Synapse works. You put your Parquet file out on storage, and query it (via Spark or Pandas).

lootsauce 4y ago

Has anyone created a vfs for redis?

sntran 4y ago

This would be quite convenient when dealing with MBtiles files hosted on S3 or similar. There are efforts such as PMTiles[1] that let us use HTTP Range query to retrieve tiles directly from S3, but we still have to convert MBTiles to PMTiles first. More importantly that OSM is considering providing MBTiles directly (besides PBF)[2]. [1]: https://protomaps.com/docs/pmtiles/ https://protomaps.com/docs/pmtiles/ [2]: https://github.com/openstreetmap/operations/issues/565 https://github.com/openstreetmap/operations/issues/565

michalc 4y ago
Aha! This is done in https://github.com/uktrade/mbtiles-s3-server https://github.com/uktrade/mbtiles-s3-server, but using an alternative library to query SQLite on S3, https://github.com/michalc/sqlite-s3-query https://github.com/michalc/sqlite-s3-query (Full disclosure: I wrote most of both of these)

JaggerFoo 4y ago

I read of someone using AWS EFS for sqlite, which has nfs functionality, if I recall correctly. However I like the idea of using a local store with litestream to S3 and updating to the latest db data from S3, if using my application on another device or computer. Sort of like github. Tailscale was looking into this option, instead of Postgres and other DBs. https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/ Cheers

Rapzid 4y ago

Something really neat about SQLite is how self contained the database file is.. You can take say, a large book or log output, then copy it into a table with full text search. Optimize the DB to get rid of empty space, then ship that out to S3 or something. Now you can fetch that down and have indexed and fts access to the contents. Cool way to store data with indexes into it in a way that makes it as easy to use as copying around and opening a file; no need for dumps or restores or server processes.

encryptluks2 4y ago
You can also do the same with a file and you can even open it in plain text and read it too.
- Rapzid 4y ago
  I mean, the database IS a file. The key here is that it colocates indexes and other metadata that can be used to efficiently access large data sets without having to hand-roll indexes and/or process them on-the-fly everytime you load the data up...
- VHRanger 4y ago
  SQLite is made to replace "dread" by being generally better than storing the data as a json blob or csv There's still an argument for columnar formats like arrow, but compatibility is iffy compared to SQLite which "just works"
bob1029 4y ago
Most problems can be neatly packed into 1 sqlite database. Even a big AAA video game could benefit from this type of representation on disk. I am also now starting to look at SQLite as a sort of application framework / operating system. The SQLite VM is running on billions of devices right now without any issues. Seems like a safe bet if I wanted to use the relational/SQL model to represent most of my application state & logic. Anything else I want to bring to the party I can inject by way of application-defined functions.
- 4y ago

wanghq 4y ago

If I need to query the db repeatedly (N times per second), isn't it more efficient (low latency and aws cost) to just download the db and run queries locally?

staticassertion 4y ago

A comparison to S3 Select would make sense since S3 Select lets you leverage RANGE, leverage compact formats like compressed Parquet, and would presumably have a similar fundamental cost ie: get + data transfer.

traceroute66 4y ago

I admire people for their inventiveness, however surely attempting to square-peg-round-hole something like this into AWS S3 is only going to end up biting them in the backside ? S3 is delivered over a stateless protocol (http) and AWS makes no promises about any given request being fulfilled (the user is merely invited to retry). The safety of your data is then further dependent on what storage tier you chose. There just seem to be so many footgun opportunities here. Why not just use the right tool for the job ? (i.e. hosted DB or compute).

rad_gruchalski 4y ago
> Why not just use the right tool for the job ? (i.e. hosted DB or compute). Because sometimes you want a database for 10000 rows, or you have 5 logins a month and you don't want a $30/month database running in the cloud. There's a market out there for real "server-less" database that charges you per rows stored, is priced per read/write operations, and is reasonably priced for a 10000 rows per month, without having to calculate how many 4KB blocks you are going to read or write.
- quickthrower2 4y ago
  Firebase Firestore?
- endisneigh 4y ago
  Like… dynamodb?
  - Brystephor 4y ago
    Dynamodb doesn't do well with complex queries. Imagine you've got 4 integer fields, any combination of which can be filtered by a range. Using dynamoDb, you're going to have a hard time querying if field A, B, and D are included. If you add a fifth field, it's going to be a pain to add to historical data.
    - endisneigh 4y ago