Changes between Version 1 and Version 2 of AdvancedTopics

Timestamp:

06/08/26 22:32:44 (13 days ago)

Author:

231035

Comment:

--

AdvancedTopics

@@ -1 +1 @@
 = Advanced Topics
+== Vector Database and Property Recommendation System
+A vector database is a database that stores data as mathematical vectors.
+A vector is a list of numbers that represents the meaning of some text, image, product or object.
+In our project, we use vectors to represent real estate properties.
+[[br]]
+Example:
+"Modern apartment in Skopje with parking, Wi-Fi, balcony and 4.7 rating"
+becomes:
+
+{{{[0.021, -0.112, 0.334, ..., 0.087]}}}
+We need a vector database to build a recommendation system based on similarity.
+
+We added an embedding column to the properties table:
+
+{{{
+ALTER TABLE properties
+ADD COLUMN embedding vector(384);
+}}}
+
+This column stores the semantic meaning of each property.We created a database view:{{{vw_property_embedding_text}}}
+This view prepares one text description for every property.This text is then converted into an embedding.
+[[br]]
+[[br]]
+The user recommendation profile represents the user’s preferences.
+In our system, we do not ask the user to manually write preferences.
+Instead, we learn preferences from the properties they have already booked.
+[[br]]
+We create a function that recalculates the user preference embedding.
+{{{
+CREATE OR REPLACE FUNCTION refresh_user_recommendation_profile(
+    p_user_id BIGINT
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    INSERT INTO user_recommendation_profiles (
+        user_id,
+        preference_embedding,
+        updated_at
+    )
+    SELECT
+        u.user_id,
+        AVG(p.embedding),
+        CURRENT_TIMESTAMP
+    FROM users u
+    JOIN guests g
+        ON g.user_id = u.user_id
+    JOIN bookings b
+        ON b.guest_id = g.guest_id
+    JOIN rooms r
+        ON r.room_id = b.room_id
+    JOIN properties p
+        ON p.property_id = r.property_id
+    WHERE u.user_id = p_user_id
+      AND p.embedding IS NOT NULL
+      AND b.booking_status IN ('CONFIRMED', 'COMPLETED')
+    GROUP BY u.user_id
+    ON CONFLICT (user_id)
+    DO UPDATE SET
+        preference_embedding = EXCLUDED.preference_embedding,
+        updated_at = CURRENT_TIMESTAMP;
+END;
+$$;
+}}}
+[[br]]
+We use:
+
+<=> 
+
+This is the cosine distance operator from pgvector.
+[[br]]
+The recommendation query returns properties that are most similar to the user profile.
+Use:
+{{{
+SELECT *
+FROM recommend_properties_for_user(1, 10);
+}}}
+This returns the top 10 recommended properties for user 1.
+=== Index
+To make vector search faster, we add an HNSW index:
+{{{
+CREATE INDEX IF NOT EXISTS idx_properties_embedding_hnsw
+ON properties
+USING hnsw (embedding vector_cosine_ops);
+}}}