Skip to content
TheChatArchiveThe Chat Archive
Qwenqwen3-30b-a3b· Alibaba (Qwen)Hugging Face LMArena Human Preference 140K

If I use a image analysis model to generate a vector of feature embeddings for image…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-sonnet-4-20250514’s answer.
  1. User

    If I use a image analysis model to generate a vector of feature embeddings for image patches, how do I use a postrgres feature store database to find similar images based on cosine similarity or other metrics? Any example code should either be in postgresql or python.

  2. Qwen Assistant

    To use a PostgreSQL feature store with the pgvector extension to find similar images based on cosine similarity, follow these steps. This guide includes both PostgreSQL and Python examples.


    1. Install and Enable pgvector Extension

    First, install the pgvector extension in PostgreSQL:

    -- Connect to your database
    CREATE EXTENSION vector;
    

    Note: Ensure you have the pgvector extension installed. You can install it via:

    git clone https://github.com/pgvector/pgvector.git
    cd pgvector
    make
    sudo make install
    

    2. Create a Table to Store Image Patches

    Create a table to store image patches with their feature vectors (e.g., 128-dimensional):

    CREATE TABLE image_patches (
        id SERIAL PRIMARY KEY,
        features VECTOR(128)  -- Adjust dimension based on your model
    );
    

    3. Insert Feature Vectors into the Database

    Insert feature vectors (e.g., from a model like ResNet or CLIP):

    INSERT INTO image_patches (features) VALUES 
    ('[1.2, 3.4, 5.6, ...]'),  -- Example vector
    ('[0.1, 0.2, 0.3, ...]');
    

    4. Query for Similar Images Using Cosine Similarity

    Use the <#> operator to compute cosine distance (1 - cosine similarity) and sort by similarity:

    -- Query for top 10 most similar images
    SELECT id, 1 - (features <#> %s) AS similarity
    FROM image_patches
    ORDER BY similarity DESC
    LIMIT 10;
    

    5. Python Example with psycopg2

    Here’s how to generate and query vectors using Python:

    import psycopg2
    import numpy as np
    
    # Connect to PostgreSQL
    conn = psycopg2.connect("dbname=test user=postgres password=secret")
    cur = conn.cursor()
    
    # Create table (if not exists)
    cur.execute("""
    CREATE TABLE IF NOT EXISTS image_patches (
        id SERIAL PRIMARY KEY,
        features VECTOR(128)
    )
    """)
    conn.commit()
    
    # Generate a dummy query vector (e.g., from a model)
    query_vector = np.random.rand(128).tolist()
    
    # Query for similar images
    cur.execute("""
    SELECT id, 1 - (features <#> %s) AS similarity
    FROM image_patches
    ORDER BY similarity DESC
    LIMIT 10
    """, (query_vector,))
    results = cur.fetchall()
    
    for row in results:
        print(f"Image ID: {row[0]}, Similarity: {row[1]:.4f}")
    
    # Close connection
    cur.close()
    conn.close()
    

    6. Optional: Indexing for Performance

    For large datasets, create an index to speed up similarity searches (e.g., using IVFFlat):

    -- Create an IVFFlat index for approximate nearest neighbor search
    CREATE INDEX idx_image_patches_features ON image_patches USING ivfflat (features vector_cosine_ops);
    

    Note: Replace vector_cosine_ops with the appropriate operator class for your use case.


    7. Other Metrics (Euclidean Distance)

    To find similar images using Euclidean distance, use the <-> operator:

    -- Find top 10 closest vectors by Euclidean distance
    SELECT id, features <-> %s AS distance
    FROM image_patches
    ORDER BY distance ASC
    LIMIT 10;
    

    Summary

    • pgvector enables efficient vector storage and similarity queries in PostgreSQL.
    • Use 1 - (features <#> %s) for cosine similarity.
    • Use features <-> %s for Euclidean distance.
    • Indexing (e.g., IVFFlat) improves performance for large datasets.

    This setup allows you to treat PostgreSQL as a feature store for image patches and efficiently retrieve similar images.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.