jtarchie

sqlite extension that uses database tables for indexing HNSW values. This is something I wanted to build after trying to build my own in pure Go.

Warning: Was vibe coded with my instructions of architecture, testing preferences (LuaJIT for FFI), and how I wanted to be able to accept stories.

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.

There are two sections that describe the same thing.

- Try: turning off synchronous mode

- Try: synchronous “OFF”

Was this AI generated?

I'm doing a little detective work here.

1. A posting like this appeared on [Reddit]( https://www.reddit.com/r/programming/comments/1e79xoz/creati...) two months ago. This repo started two months ago. The user [ApexProgrammer](https://github.com/ApexProgrammer) has one repo under their account.

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?

Note: We've crossed paths on another thread (Reddit?).

I have an experiment going (as part of a talk) of how compressed of a sqlite database I can get all of English Wikipedia with full-text search.

So far, the smallest with full articles and titles is 28 GiB.

I'm glad it was helpful. It was a list of things I had known before I used it. So it was meant to be a quick getting started like doc.

Someone on Reddit mentioned in Norwegian, it means "nothing."

I've used Pocketbase for some other projects. A quick small web app, but it was so easy.

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.

I just wanted to share the details of my experience with Goja. It was not relevant to compare Otto.

Currently 55GB, for read-only work at the moment. Using full text search, rtree, JSON, and indexes.

This is almost the same. We both do compression. The compression layer it transparent to the application via VFS.

I have tried this trick with Wikipedia too. It don’t have the numbers. I’ll push up the code when I can.

I can help answer this a bit.

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.

Oh, you!

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.

That's an interesting take.

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.

Grammarly, but point taken. Will fix.

I have not tried that.

Will PGO work with CGO external libraries? I'm using https://github.com/mattn/go-sqlite3

At first, I didn't do it, but I had that shower thought of "What If?"

It worked! It was super helpful in finding more accurate results.

I did try DuckDB at first. Not for tags, but the relationships between the nodes, ways, and relations. It was slow. Really fast to important, however!

Would you say your knowledge of Cloud Foundry and Diego is directly proportional to how fancy you dress on Fridays? Because today is Friday and I don't see fancy dress.