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.
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.
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/ad2c9b8826c75939608f5561...
[1]: https://github.com/fsspec/filesystem_spec/blob/2633445fc5479...
[2]: https://github.com/fsspec/filesystem_spec/blob/2633445fc5479...