[HN Gopher] Show HN: Query SQLite files stored in S3
___________________________________________________________________
Show HN: Query SQLite files stored in S3
Author : polyrand
Score : 61 points
Date : 2022-09-13 19:03 UTC (3 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| aleph- wrote:
| Nifty, reminds me of https://github.com/backtrace-labs/verneuil
| or possibly https://litestream.io/
| simonw wrote:
| This is really neat. It looks like it's using a similar trick to
| 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.
| remram wrote:
| Is there a benefit over the existing S3 VFS sqlite3-s3vfs,which
| also uses APSW?
| polyrand wrote:
| 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=v...
|
| [1]: https://s3fs.readthedocs.io/en/latest/index.html
|
| [2]:
| https://s3fs.readthedocs.io/en/latest/index.html#s3-compatib...
| 8organicbits wrote:
| See: https://github.com/litements/s3sqlite#alternatives
| gkaemmer wrote:
| 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
| svnpenn wrote:
| 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.
| freeqaz wrote:
| 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!
| samatman wrote:
| 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.
| ks2048 wrote:
| 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 wrote:
| 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/ad2c9b8826c75939608f5561...
|
| [1]:
| https://github.com/fsspec/filesystem_spec/blob/2633445fc5479...
|
| [2]:
| https://github.com/fsspec/filesystem_spec/blob/2633445fc5479...
| endisneigh wrote:
| There have been many of these custom vfs implementations. Is
| there a benchmark on performance between them?
| psanford wrote:
| 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
|
| [2]: https://github.com/psanford/donutdb
| polyrand wrote:
| 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
|
| [1]: https://news.ycombinator.com/item?id=32269287
| endisneigh wrote:
| 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 wrote:
| > 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 wrote:
| I'd read this:
|
| https://apple.github.io/foundationdb/cap-theorem.html
|
| And
|
| 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.
| jgraettinger1 wrote:
| Here's [1] a particularly crazy one which offers synchronous,
| low-latency (millis) replication of a SQLite database to a
| distributed recovery log. It does this by instrumenting the
| file operations SQLite is making and recording their effects
| into the log.
|
| [1]
| https://pkg.go.dev/go.gazette.dev/core@v0.89.0/consumer/stor...
| Datagenerator wrote:
| This could be done by mounting S3 as filesystem and plain sqlite.
| Any differences?
| simonw wrote:
| 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 wrote:
| That really depends on the S3 filesystem implementation. It
| could make the same sort of optimizations as this does using
| range reads.
| michalc wrote:
| 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 wrote:
| Yep! This kind of thing works around these problems.
|
| https://www.sqlite.org/draft/useovernet.html
___________________________________________________________________
(page generated 2022-09-13 23:00 UTC)