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.
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.

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.
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;
$$;

We use:

<=>

This is the cosine distance operator from pgvector.
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);