News for hackers. Stuff that matters.
Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by Scaevolus on Saturday July 06, 2024 @04:15PM (#40892919)
> CREATE INDEX entries_name ON entries(tags->>'name'); > However, this requires an index per tag, which won’t scale, especially for a dynamic list of tags.

That's not how indexes work at all. This will be fine.

Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by bspammer on Sunday July 07, 2024 @09:23AM (#40897531)
Wouldn’t creating an index for each tag cost a lot of disk space? There are hundreds (thousands?) of OSM tags. I like their approach of combining a FTS table with a normal JSON query for ad-hoc queries.
Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by jtarchie on Monday July 08, 2024 @11:06AM (#40906085)
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.
Re:Optimizing Large-Scale OpenStreetMap Data with SQLite by jtarchie on Monday July 08, 2024 @11:02AM (#40906049)
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.