Postgresql Vector Search example

01_README.md

This gist has SQL scripts to pull data into PostgreSQL and perform a demo "similarity" search.

Create database

CREATE TABLE iab_assets (
    id SERIAL PRIMARY KEY,
    iab_code TEXT,
    title TEXT,
    origin TEXT,
    date_info TEXT,
    material TEXT,
    dimension TEXT,
    description TEXT,
    footnote_reference TEXT,
    manuscript_description TEXT,
    object_info TEXT,
    inscriptions TEXT,
    artist_bio TEXT,
    curated_essay TEXT,
    curated_footnote TEXT,
    contributor_url TEXT,
    opening_folio TEXT,
    credit_line TEXT,
    curators TEXT,
    writers TEXT,
    section TEXT,
    thematic_sub_section TEXT,
);

Import Data

COPY iab_assets(
    iab_code, title, origin, date_info, material, dimension, 
    description, footnote_reference, manuscript_description, 
    object_info, inscriptions, artist_bio, curated_essay, 
    curated_footnote, contributor_url, opening_folio, 
    credit_line, curators, writers, section, thematic_sub_section
)
FROM '/tmp/data.csv' 
DELIMITER ',' 
CSV HEADER 
QUOTE '"' 
ESCAPE '"' 
ENCODING 'UTF8';

Add fulltext (English) field

This sets an English fulltext search for demo purposes. Because this is an item view, the iab_code will be used as a search parameter, so an arabic index is not required.

ALTER TABLE iab_assets ADD COLUMN search_index tsvector;

UPDATE iab_assets 
SET search_index = to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''));

-- Create a GIN index to make searches near-instant
CREATE INDEX idx_search ON iab_assets USING GIN(search_index);

Example Search

WITH target AS (
    SELECT 
        title, 
        description,
        split_part(trim(iab_code), '-', 2) as gallery,
        split_part(trim(iab_code), '-', 3) as section
    FROM iab_assets 
    WHERE trim(iab_code) = '25-G5-06-0424'
),
scored_assets AS (
    SELECT 
        a.iab_code, 
        a.title,
        -- Text Similarity Component
        (word_similarity(t.title, a.title) * 0.7) + 
        (word_similarity(t.description, a.description) * 0.3) AS text_score,
        -- Location Component (0.0 to 1.0 scale)
        CASE 
            WHEN split_part(trim(a.iab_code), '-', 2) = t.gallery 
                AND split_part(trim(a.iab_code), '-', 3) = t.section THEN 1.0  -- Perfect match
            WHEN split_part(trim(a.iab_code), '-', 2) = t.gallery THEN 0.4   -- Same gallery
            ELSE 0 
        END AS location_match_factor
    FROM iab_assets a, target t
    WHERE trim(a.iab_code) <> '25-G5-06-0424'
        AND a.description IS NOT NULL
)
SELECT 
    iab_code, 
    title,
    text_score,
    location_match_factor,
    -- Adjust the '0.2' below to increase/decrease the influence of location
    (text_score + (location_match_factor * 0.2)) AS final_score
FROM scored_assets
ORDER BY final_score DESC
LIMIT 5;

Results

iab_code	title	text_score	location_match_factor	final_score
25-G5-02-0412	Qur’an folio	0.801327	0.4	0.881327
25-G5-06-0432	Qur’an bifolium	0.676923	1.0	0.876923
25-G5-02-0443	Qur’an folio	0.772671	0.4	0.852671
25-G5-68-0735	Qur’an folio\nChapter Al-Muddaththir (74:1–12)	0.770482	0.4	0.850482
25-G5-02-0431	Qur’an folio\n	0.770412	0.4	0.850412

Gold Plating

This a generic dynamic postgresql view that accepts an IAB Code as a parameter:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE OR REPLACE FUNCTION closest_related_iab_items(target_iab_code text)
RETURNS TABLE (
    gallery text,
    section text,
    target_code text,
    target_title text,
    match_code text,
    match_title text,
    sim_score double precision,
    proximity text
)
LANGUAGE sql
STABLE
AS $$
WITH normalized AS (
    SELECT
        trim(iab_code) AS iab_code,
        title,
        description,
        string_to_array(trim(iab_code), '-') AS code_parts
    FROM iab_assets
    WHERE description IS NOT NULL
      AND trim(iab_code) <> ''
      AND position(',' IN iab_code) = 0
),
parsed AS (
    SELECT
        iab_code,
        title,
        description,
        CASE
            WHEN array_length(code_parts, 1) = 4 THEN code_parts[2]
            WHEN array_length(code_parts, 1) = 3 THEN code_parts[1]
        END AS gallery,
        CASE
            WHEN array_length(code_parts, 1) = 4 THEN code_parts[3]
            WHEN array_length(code_parts, 1) = 3 THEN code_parts[2]
        END AS section,
        CASE
            WHEN array_length(code_parts, 1) = 4 THEN code_parts[4]
            WHEN array_length(code_parts, 1) = 3 THEN code_parts[3]
        END AS item_id
    FROM normalized
    WHERE array_length(code_parts, 1) IN (3, 4)
),
scored AS (
    SELECT
        p1.gallery,
        p1.section,
        p1.iab_code AS target_code,
        p1.title AS target_title,
        p2.iab_code AS match_code,
        p2.title AS match_title,
        (
            word_similarity(coalesce(p1.title, ''), coalesce(p2.title, '')) * 0.7
          + word_similarity(p1.description, p2.description) * 0.3
        ) AS sim_score,
        CASE
            WHEN p1.section = p2.section THEN 'Section Match'
            ELSE 'Gallery Match'
        END AS proximity
    FROM parsed p1
    JOIN parsed p2
      ON p1.gallery = p2.gallery
     AND p1.iab_code <> p2.iab_code
    WHERE p1.iab_code = trim(target_iab_code)
)
SELECT
    scored.gallery,
    scored.section,
    scored.target_code,
    scored.target_title,
    scored.match_code,
    scored.match_title,
    scored.sim_score,
    scored.proximity
FROM scored
WHERE scored.sim_score > 0.25
ORDER BY
    scored.sim_score + CASE WHEN scored.proximity = 'Section Match' THEN 0.05 ELSE 0 END DESC,
    scored.sim_score DESC,
    scored.match_code
LIMIT 5;
$$;

Usage

SELECT * FROM closest_related_iab_items('25-G5-06-0424');