ncruces helped me with some code I made for VFS. It uses [zstd seekable](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.
2. These repos' first commits happened around July 12th, 2024, and July 9th, 2024.
3. One reference "Farm Manager," and another reference "Farm to Market."
Questions:
- Are these artifacts from a school project?
- Are these AI-driven code repositories bent on world domination?
- Are these questions to remain unanswered?
I did see that. I'm going to let the fork simmer for awhile. I didn't require module support for my runtime. Since its meant to be very basic ETL runtime.
Not doing it all, Go allows my API endpoint to accept sandboxed javascript code to enable users to run more complex queries against my data. It is also faster because they don't have to make individual API calls for each data set. Therefore, more data is required to be sent over the wire to them.
Processing the data with code and SQLite takes about 4 hours for the United States and Canada. If I can figure out how to parallelize some of the work, it could be faster.
The benefit of using SQLite is its low memory footprint. I can have this 40GB file (the uncompressed version) and use a 256MB container instance. It shows how valuable SQLite is in terms of resources. It takes up disk space, but I followed up with the zstd compression (from the post).
Was this more work and should've I have used Postgres and osm2pql, yes. Was it fun, well yeah, because I have another feature, which I have not written about yet, which I highly value.
That's great. I was impressed with DuckDB's speed with the OSM data. I'm doing it with all the data, but I intended to have:
- a single file that can be queried
- a single file that can be easily compressed
When I index the entire United States, admittedly with a subset of tags, I can search in milliseconds. I'll take it.
I do some sharding, mainly by grid-based bounding boxes. This allows for searching with an area or overlapping areas FTS index. It means there is some data duplication, but it is minimal compared to the speed boost.
The FTS table also has the extra benefit of different tokenizers -- porter, unicode, trigram, etc. It allows less precise searches to happen but still returns some results. I liked this because I wanted the best effort, not exact results for some user queries.
I also found [flatgeobuf](https://github.com/flatgeobuf/flatgeobuf), which promises something similar-ish. Perhaps not the same interface for querying, but organizing data in an r-tree format for streaming from a blob store.
I understand this is how they would work for a JSON/JSONB payload in sqlite. In Postgres, there are [indexes](https://www.postgresql.org/docs/current/datatype-json.html#J...), but sqlite doesn't support the same. You'd have to create individual indexes for the keys from the JSON payload you'd need.
If I miss a feature, I'd love to experiment more. Please let me know.
Warning: Was vibe coded with my instructions of architecture, testing preferences (LuaJIT for FFI), and how I wanted to be able to accept stories.