So, uhm, not to dunk on Photon, but I've just built a proof of concept #SQLite database containing all of the addresses in OpenStreetMap and created a full-text search index over them.
A query like "give me all OSM objects where any of the address fields starts with 'Frankf'" takes 36 ms and uses 11 MB of RAM.
That's a FTS5 trigraph search over 13.5 million unique strings and then joining them with nodes & ways.
The database takes 25 GB, compared to Photon's 230 GB.
I'm impressed.
scy /
npub15p…khvce
2024-04-29 17:17:31
in reply to nevent1q…ncae
Author Public Key
npub15pc5vt5kqgr60g389gl4n5zzuktz8wezz76klym9ew3puy3p8clqckhvcePublished at
2024-04-29 17:17:31Event JSON
{
"id": "46ad43ca16a17a506d35bab20a777e1373fe0aab9a7aacce15108b9a0f3a11bd",
"pubkey": "a071462e960207a7a2272a3f59d042e59623bb2217b56f9365cba21e12213e3e",
"created_at": 1714403851,
"kind": 1,
"tags": [
[
"e",
"f3c9610075542e2851ba62cc13804192e61fe324b53e63ef65cba4c006cdf43e",
"",
"root"
],
[
"t",
"sqlite"
],
[
"p",
"a071462e960207a7a2272a3f59d042e59623bb2217b56f9365cba21e12213e3e"
],
[
"e",
"7770483d3e43c42d4fd2dad516e929c7516d1243e0e4fe6b1811fd6e716eea01",
"",
"reply"
],
[
"proxy",
"https://chaos.social/users/scy/statuses/112355170792579791",
"activitypub"
],
[
"L",
"pink.momostr"
],
[
"l",
"pink.momostr.activitypub:https://chaos.social/users/scy/statuses/112355170792579791",
"pink.momostr"
]
],
"content": "So, uhm, not to dunk on Photon, but I've just built a proof of concept #SQLite database containing all of the addresses in OpenStreetMap and created a full-text search index over them.\n\nA query like \"give me all OSM objects where any of the address fields starts with 'Frankf'\" takes 36 ms and uses 11 MB of RAM.\n\nThat's a FTS5 trigraph search over 13.5 million unique strings and then joining them with nodes \u0026 ways.\n\nThe database takes 25 GB, compared to Photon's 230 GB.\n\nI'm impressed.",
"sig": "cc1c88c728f855522d3504183e9ccba8ff6f0542ab1f63901ee9ceeaa5ddbe6851f31152b3aa12e778c036ac24970d42f38c46708e741f43f03830dedb5528de"
}