Hacker News

Hamming Distance for Okunoonya okw'omugatte mu SQLite

Hamming Distance for Okunoonya okw'omugatte mu SQLite Okunoonyereza kuno kugenda mu maaso n’okunoonyereza ku hamming, okwekenneenya amakulu gaayo n’engeri gye gayinza okukwatamu. Endowooza Enkulu Ezibikkiddwa Ebirimu bino binoonyereza ku: Emisingi n’endowooza enkulu Okugezesa...

8 min read Via notnotp.com

Mewayz Team

Editorial Team

Hacker News

Hamming distance ye metric y’okufaanagana ey’omusingi ebala bits ez’enjawulo wakati wa binary strings bbiri, ekigifuula emu ku nkola ezisinga amangu era ezisinga okukola obulungi mu kunoonya okumpi n’omuliraanwa asinga okumpi mu databases. Bwe kikozesebwa ku SQLite okuyita mu nsengeka z’okunoonya ez’omugatte, Hamming distance esumulula obusobozi bw’okunoonya amakulu obw’omutindo gw’ekitongole awatali kusaasaanya ssente za data za vekita ezeetongodde.

Hamming Distance Kiki era Lwaki Kikulu mu Kunoonya Database?

Ebanga lya hamming ligera omuwendo gw’ebifo emiguwa ebiri egy’obuwanvu obwenkanankana we gyawukana. Okugeza, ennyiriri za binary 10101100 ne 10001101 zirina ebanga lya Hamming erya 2, kubanga zaawukana mu bifo bya bit bibiri ddala. Mu mbeera z’okunoonya mu database, okubala kuno okulabika ng’okwanguyira kufuuka okw’amaanyi ag’ekitalo.

Okunoonya kwa SQL okw’ennono kwesigamye ku kukwatagana okutuufu oba okuwandiika ebiwandiiko ebijjuvu, ekilwanagana n’okufaanagana kw’amakulu — okuzuula ebivuddemu ebitegeeza ekintu kye kimu okusinga okugabana ebigambo ebikulu ebifaanagana. Hamming distance eziba ekituli kino nga ekola ku binary hash codes eziggibwa mu content embeddings, okusobozesa databases nga SQLite okugeraageranya obukadde n’obukadde bwa records mu milliseconds nga ekozesa bitwise XOR operations.

Metrika yatongozebwa Richard Hamming mu 1950 mu mbeera ya koodi ezitereeza ensobi. Nga wayise emyaka mingi, kyafuuka ekikulu mu kuggya amawulire naddala mu nkola nga sipiidi y’esinga obukulu okusinga okutuufu okutuukiridde. Okubalirira kwayo O(1) buli kugeraageranya (nga tukozesa ebiragiro bya CPU popcount) kugifuula esaanira mu ngeri ey’enjawulo ku yingini za database eziteekeddwamu n’obuzito obutono.

Okunoonya kwa Hybrid Kugatta Kutya Ebanga lya Hamming n'okubuuza kwa SQLite okw'ennono?

Okunoonya okw’omugatte mu SQLite kugatta enkola bbiri ez’okuggya ezijjuliza: okunoonya ebigambo ebikulu ebitali bimu (nga tukozesa SQLite’s built-in FTS5 full-text search extension) n’okunoonya okufaanagana okunene (nga tukozesa Hamming distance ku binary quantized embeddings). Enkola zombi yokka temala ku byetaago by’okunoonya eby’omulembe.

Payipu y'okunoonya ey'omugatte eya bulijjo ekola bweti:

  1. Okuzaala okuyingiza: Buli kiwandiiko oba likodi ekyusibwa okufuuka vekita y’ensonga etengejja ey’ebipimo ebya waggulu nga ekozesa ekifaananyi ky’olulimi oba omulimu gw’okuwandiika enkodi.
  2. Binary quantization: Float vector enyigirizibwa mu compact binary hash (e.g., 64 oba 128 bits) nga tukozesa obukodyo nga SimHash oba random projection, okukendeeza ennyo ku byetaago by’okutereka.
  3. Hamming index storage: Hash ya binary eterekebwa nga INTEGER oba BLOB column mu SQLite, okusobozesa emirimu gya bitwise egy'amangu mu kiseera ky'okubuuza.
  4. Okugaba obubonero mu kiseera ky’okubuuza: Omukozesa bw’awaayo okubuuza, SQLite ebala ebanga lya Hamming ng’eyita mu nkola ya scalar eya bulijjo ng’ekozesa XOR ne popcount, n’ezzaayo abesimbyewo abasunsuddwa okusinziira ku kufaanagana kwa bit.
  5. Okugatta obubonero: Ebivudde mu kunoonya amakulu okwesigamiziddwa ku Hamming n’okunoonya ebigambo ebikulu ebya FTS5 bigattibwa nga tukozesa Reciprocal Rank Fusion (RRF) oba obubonero obuzitowa okufulumya olukalala olusembayo olw’ensengeka.

Okugaziwa kwa SQLite okuyita mu kugaziya okutikkibwa oba emirimu egyakuŋŋaanyiziddwa kifuula enzimba eno okutuukirira awatali kusenguka ku nkola ya database enzito. Ekivaamu ye yingini y’okunoonya eyeetongodde ekola wonna SQLite w’eddukira — omuli ebyuma ebiteekeddwamu, pulogulaamu z’oku ssimu, n’okuteekebwa mu nkola ku bbali.

Okutegeera okukulu: Okunoonya kwa Binary Hamming ku 64-bit hashes kusinga ku sipiidi nga 30–50x okusinga okufaanagana kwa cosine ku full float32 vectors ez’obunene obwenkanankana. Ku nkola ezeetaaga sub-10ms search latency mu bukadde n'obukadde bwa records ezitaliimu hardware specialized, Hamming distance mu SQLite etera okuba optimal engineering trade-off wakati wa precision ne performance.

nga bwe kiri

Nkola ki ezikwata ku nkola ya Hamming Search mu SQLite?

SQLite ye database ya fayiro emu, etaliiko seva, ekola ebiziyiza eby'enjawulo n'emikisa gy'okussa mu nkola okunoonya ewala kwa Hamming. Awatali nsengeka za vector indexing enzaaliranwa nga HNSW oba IVF (esangibwa mu maduuka ga vector agaweereddwayo), SQLite yeesigamye ku linear scan okunoonya Hamming — naye kino tekikoma nnyo okusinga bwe kiwulikika.

Okubalirira ebanga lya Hamming erya 64-bit kyetaagisa XOR yokka nga egobererwa popcount (omuwendo gw’abantu, okubala bits eziteekeddwawo). CPU ez’omulembe kino zikola mu kiragiro kimu. Sikan ya linear enzijuvu eya hashes obukadde 1 eza 64-bit emaliriza mu milliseconds nga 5–20 ku hardware y’ebintu, ekifuula SQLite okuba ey’omugaso ku datasets okutuuka ku records obukadde obuwerako awatali bukodyo bwa indexing obw’enjawulo.

💡 DID YOU KNOW?

Mewayz replaces 8+ business tools in one platform

CRM · Invoicing · HR · Projects · Booking · eCommerce · POS · Analytics. Free forever plan available.

Start Free →

Ku datasets ennene, okulongoosa mu nkola kuva mu candidate pre-filtering: okukozesa SQLite's WHERE clauses okumalawo ennyiriri okusinziira ku metadata (date ranges, categories, user segments) nga tonnaba kukozesa Hamming distance, okukendeeza ku effective scan size okusinziira ku orders of magnitude. Wano we wava enzimba z’okunoonya ez’omugatte mu butuufu — omusengejja gw’ebigambo ebikulu ogutali gwa maanyi gukola ng’omusengejja ogw’amangu nga tegunnabaawo, era ebanga lya Hamming liddamu ensengeka y’abasimbyewo abasigaddewo.

Ossa otya mu nkola omulimu gwa Hamming Distance mu SQLite?

SQLite terimu mulimu gwa Hamming distance enzaaliranwa, naye C extension API yaayo efuula emirimu gya custom scalar egyangu okuwandiisa. Mu Python ng’okozesa modulo ya sqlite3, osobola okuwandiisa omulimu ogubala ebanga lya Hamming wakati wa namba bbiri enzijuvu:

Omulimu gukkiriza ensonga bbiri eza namba enzijuvu ezikiikirira hashes za binary, ne gubala XOR zaabwe, olwo ne gubala ebitundu ebiteekeddwawo nga tukozesa bin().count('1') eya Python oba enkola ey'okukozesa bits ey'amangu. Oluvannyuma lw’okuwandiisibwa, omulimu guno gufuuka ogufunibwa mu kubuuza kwa SQL nga omulimu gwonna oguzimbibwamu, nga gusobozesa okubuuza ng’okulonda ennyiriri ebanga lya Hamming eri hash y’okubuuza we ligwa wansi w’omusingi, nga liragirwa ebanga eririnnya okusooka okuggya ebikwatagana ebisinga okumpi.

Ku kuteeka mu nkola okufulumya, okukung’aanya enzikiriziganya ya popcount ng’okugaziya kwa C nga okozesa sqlite3_create_function API ya SQLite kivaamu omulimu omulungi emirundi 10–100 okusinga Python etaputibwa, okuleeta okunoonya kwa SQLite okwa Hamming mu kutuuka ku database za vector ez’enjawulo ku migugu mingi egy’omugaso.

Ddi Bizinensi Zirina Okulonda Okunoonya kwa SQLite Hamming Ku Databases za Vector Ezetongodde?

Okulonda wakati w'okunoonya kwa Hamming okwesigamiziddwa ku SQLite ne database za vector eziweereddwayo nga Pinecone, Weaviate, oba pgvector kisinziira ku bunene, obuzibu bw'emirimu, n'ebiziyiza okuteekebwa mu nkola. Okunoonya kwa SQLite Hamming kwe kulonda okutuufu ng’obwangu, okutambuza, n’omuwendo bye bisinga obukulu — nga bwe kiri ku nkola za bizinensi ezisinga obungi.

Ebifo eby’okutereka amawulire ebya vekita ebiweereddwayo bireeta omuwendo omunene ogw’emirimu: ebikozesebwa eby’enjawulo, okusirika kw’omukutu, obuzibu bw’okukwataganya, n’omuwendo omunene ku mutendera. Ku nkola eziweereza enkumi n’enkumi okutuuka ku bukadde bwa likodi eza wansi, okunoonya kwa SQLite Hamming kuwa obukwatagana obugeraageranyizibwa obutunuulidde abakozesa nga tebulina bikozesebwa ebirala. Kikwataganya omuwendo gwo ogw'okunoonya ne data yo ey'enkola, okumalawo ekika kyonna eky'engeri z'okulemererwa kw'enkola ezisaasaanyiziddwa.

Ebibuuzo Ebitera Okubuuzibwa

Okunoonya ebanga lya Hamming kutuufu ekimala ku nkola z'okunoonya okufulumya?

Hamming distance ku binary-quantized embeddings esuubula omuwendo omutono ogw'okujjukira precision ku massive speed gains. Mu nkola, binary quantization mu ngeri entuufu ekuuma 90–95% ku mutindo gw’okujjukira ogw’okunoonya okufaanagana kwa float32 cosine okujjuvu. Ku nkola ezisinga ez’okunoonya bizinensi — okuzuula ebintu, okuggya ebiwandiiko, ebifo eby’okumanya okuwagira bakasitoma — okusuubulagana kuno kukkirizibwa ddala, era abakozesa tebasobola kutegeera njawulo mu mutindo gw’ebivuddemu.

SQLite esobola okukwata okusoma n'okuwandiika mu kiseera kye kimu mu kiseera ky'okubuuza kw'okunoonya kwa Hamming?

SQLite ewagira okusoma mu kiseera kye kimu okuyita mu mbeera yaayo eya WAL (Write-Ahead Logging), okusobozesa abasomi abawera okubuuza omulundi gumu awatali kuziyiza. Okuwandiika mu kiseera kye kimu kukoma — SQLite serializes writes — naye kino tekitera kuba kizibu ku mirimu emizito egy’okunoonya ng’okuwandiika tekutera kubaawo okusinziira ku kusoma. Ku nkola z'okunoonya ez'omugatte ezisoma ennyo, enkola ya WAL eya SQLite emala ddala.

Okugerageranya kwa binary quantization kukwata kutya ku byetaago by’okutereka bw’ogeraageranya ne float vectors?

Okutereka mu kutereka kwa maanyi nnyo. Okuyingiza kwa float32 okwa bulijjo okw’ebitundu 768 kwetaaga bytes 3,072 (3 KB) buli likodi. Hash ya binary ya 128-bit eya embedding y’emu yeetaaga bytes 16 zokka — okukendeeza kwa 192x. Ku dataset ya records obukadde 1, kino kitegeeza enjawulo wakati wa 3 GB ne 16 MB ez'okutereka embedding, ekifuula okunoonya okwesigamiziddwa ku Hamming okusoboka mu mbeera ezitali za memory nga okutereka full float tekyandibadde kya mugaso.

Okuzimba ebintu ebigezi, ebinoonyezebwa kye kika kyennyini eky’obusobozi obwawula bizinensi ezikula ku ziyimiridde. Mewayz ye OS ya bizinensi yonna mu emu eyesigika abakozesa abasukka mu 138,000, ng’ewa modulo 207 ezigatta — okuva ku CRM n’okwekenneenya okutuuka ku kuddukanya ebirimu n’okusingawo — okutandika ne doola 19 zokka/omwezi. Lekera awo okutunga awamu ebikozesebwa ebikutuddwa otandike okuzimba ku pulatifomu eyakolebwa ku minzaani.

Tandika olugendo lwo olwa Mewayz leero ku app.mewayz.com era olabe enkola y’emirimu eya bizinensi ekwataganye mu butuufu ky’esobola okukolera ttiimu yo.