Advanced Phase - AI Book Recommendation System with pgvector
Опис
Во оваа фаза е имплементиран систем за персонализирани препораки на книги со користење на AI embeddings и pgvector екстензијата за PostgreSQL.
Целта е за даден член да се препорачаат книги што сè уште ги нема позајмено, но кои се семантички слични со книгите што претходно ги има позајмено.
Секоја книга добива векторска репрезентација, односно embedding, генерирана од:
book title + description + genres + categories + authors
Како функционира?
Основната идеја е:
Book metadata -> AI model -> Vector embedding -> Cosine similarity -> Recommended books
За секој член системот ги зема книгите од loan_history, го пресметува просечниот embedding профил на членот и потоа ги споредува сите останати книги со тој профил преку cosine similarity.
Чекор 1 - PostgreSQL со pgvector во Docker
Бидејќи локалниот PostgreSQL немаше достапна vector екстензија, беше креиран Docker PostgreSQL container со веќе инсталиран pgvector.
docker run --name booknest-postgres ^ -e POSTGRES_PASSWORD=postgres ^ -e POSTGRES_DB=booknest ^ -p 5433:5432 ^ -d pgvector/pgvector:pg16
Потоа во DataGrip беше креирана нова конекција:
Host: localhost Port: 5433 Database: booknest User: postgres Password: postgres
Со ова беше овозможено користење на pgvector екстензијата во проектот.
Чекор 2 - Префрлање на постоечката база
Постоечката BookNest база беше извезена со pg_dump:
"C:\Program Files\PostgreSQL\18\bin\pg_dump.exe" -h localhost -p 5432 -U postgres -d postgres -f C:\Users\Ivana\Desktop\booknest_backup.sql
Потоа backup фајлот беше внесен во Docker PostgreSQL базата:
"C:\Program Files\PostgreSQL\18\bin\psql.exe" -h localhost -p 5433 -U postgres -d booknest -f C:\Users\Ivana\Desktop\booknest_backup.sql
На овој начин сите постоечки податоци беа успешно префрлени во новата pgvector база.
Чекор 3 - Активирање на pgvector
Во PostgreSQL беше активирана vector екстензијата:
CREATE EXTENSION IF NOT EXISTS vector;
Проверка:
SELECT * FROM pg_available_extensions WHERE name = 'vector';
Резултатот потврдува дека pgvector е успешно инсталиран и активен.
Чекор 4 - Додавање embedding колона
Во табелата book беше додадена нова колона од тип vector(384):
ALTER TABLE book ADD COLUMN IF NOT EXISTS embedding vector(384);
Димензијата 384 одговара на AI моделот all-MiniLM-L6-v2, кој се користи за генерирање embeddings.
Секој запис во оваа колона содржи векторска репрезентација на една книга.
Чекор 5 - Генерирање embeddings
Беа инсталирани потребните Python библиотеки:
pip install sentence-transformers psycopg2-binary
Креирана беше Python скрипта generate_embeddings.py.
Скриптата ги чита:
- насловот на книгата
- описот
- жанровите
- категориите
- авторите
Од овие податоци се прави еден текстуален запис за секоја книга. Потоа тој текст се испраќа до моделот all-MiniLM-L6-v2, кој генерира embedding vector. Генерираниот vector се зачувува во колоната book.embedding.
Целата Python скрипта:
import psycopg2
from sentence_transformers import SentenceTransformer
conn = psycopg2.connect(
host="localhost",
port=5433,
database="booknest",
user="postgres",
password="postgres"
)
cursor = conn.cursor()
print("Connected!")
cursor.execute("""
SELECT
b.barcode,
b.title,
COALESCE(b.description, '') AS description,
COALESCE(string_agg(DISTINCT g.name, ', '), '') AS genres,
COALESCE(string_agg(DISTINCT c.name, ', '), '') AS categories,
COALESCE(string_agg(DISTINCT a.first_name || ' ' || a.last_name, ', '), '') AS authors
FROM book b
LEFT JOIN book_genre bg ON bg.barcode = b.barcode
LEFT JOIN genre g ON g.genre_id = bg.genre_id
LEFT JOIN category_book cb ON cb.barcode = b.barcode
LEFT JOIN category c ON c.category_id = cb.category_id
LEFT JOIN book_author ba ON ba.barcode = b.barcode
LEFT JOIN author a ON a.author_id = ba.author_id
GROUP BY b.barcode, b.title, b.description
LIMIT 10000
""")
books = cursor.fetchall()
print(f"Found {len(books)} books")
model = SentenceTransformer("all-MiniLM-L6-v2")
for i, (barcode, title, description, genres, categories, authors) in enumerate(books, start=1):
text = (
title + " " +
description + " " +
genres + " " +
categories + " " +
authors
)
```
embedding = model.encode(text).tolist()
cursor.execute(
"""
UPDATE book
SET embedding = %s
WHERE barcode = %s
""",
(embedding, barcode)
)
if i % 500 == 0:
conn.commit()
print(f"Updated {i}/{len(books)} books")
```
conn.commit()
print("DONE!")
cursor.close()
conn.close()
По извршувањето на скриптата беа генерирани embeddings за 10000 книги.
Проверка:
SELECT COUNT(*) FROM book WHERE embedding IS NOT NULL;
Резултат:
10000
Ова покажува дека embeddings се успешно генерирани и зачувани во табелата book.
Чекор 6 - Индекс за побрзо пребарување
За побрзо пребарување по cosine similarity беше креиран ivfflat индекс:
CREATE INDEX IF NOT EXISTS idx_book_embedding ON book USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100); ANALYZE book;
Овој индекс овозможува побрзо пребарување на најслични вектори без да се споредуваат сите книги во табелата.
Чекор 7 - Функција за препораки
Беше креирана PostgreSQL функција:
recommend_books_for_member_pgvector( p_member_id, p_limit )
Функцијата работи во неколку чекори:
- Ги наоѓа книгите што членот претходно ги има позајмено.
- Го пресметува просечниот embedding профил на членот.
- Ги разгледува сите останати книги.
- Ги исклучува книгите што членот веќе ги има позајмено.
- Ги рангира книгите според cosine similarity.
- Ги враќа најдобрите N препораки.
Кодот на функцијата:
CREATE OR REPLACE FUNCTION recommend_books_for_member_pgvector( p_member_id INT, p_limit INT DEFAULT 10 ) RETURNS TABLE ( barcode VARCHAR, title VARCHAR, similarity FLOAT ) LANGUAGE SQL AS $$ WITH borrowed_books AS ( SELECT DISTINCT bc.barcode FROM loan_history lh JOIN book_copy bc ON bc.copy_id = lh.copy_id WHERE lh.member_user_id = p_member_id ), member_profile AS ( SELECT AVG(b.embedding) AS profile_embedding FROM book b JOIN borrowed_books bb ON bb.barcode = b.barcode WHERE b.embedding IS NOT NULL ) SELECT b.barcode, b.title, 1 - (b.embedding <=> mp.profile_embedding) AS similarity FROM book b CROSS JOIN member_profile mp WHERE b.embedding IS NOT NULL AND b.barcode NOT IN (SELECT barcode FROM borrowed_books) ORDER BY b.embedding <=> mp.profile_embedding LIMIT p_limit; $$;
Клучниот pgvector дел е:
b.embedding <=> mp.profile_embedding
Операторот <=> пресметува cosine distance помеѓу два embeddings.
Similarity се пресметува како:
1 - (b.embedding <=> mp.profile_embedding)
Колку вредноста е поблиску до 1, толку книгата е послична на читачкиот профил на членот.
Чекор 8 - Валидација на препораките
За да се провери дали препораките имаат смисла, прво беа анализирани книгите што членот претходно ги има позајмено, а потоа беа споредени со препораките генерирани од системот.
8.1 Loan History за членот
Користен е следниот SQL query:
SELECT DISTINCT b.title FROM loan_history lh JOIN book_copy bc ON bc.copy_id = lh.copy_id JOIN book b ON b.barcode = bc.barcode WHERE lh.member_user_id = 10835 LIMIT 20;
Резултат:
| Title |
| Frommer's Ottawa |
| The Iraq Study Group Report: The Way Forward |
| Sex and Subterfuge: Women Writers to 1850 |
| Imperfect Birds: A Novel |
| Improving the Quality of Long-Term Care |
| The Unsinkable Charlie Brown |
| Twelve |
| Women of Faith: New Testament With Psalms & Proverbs |
| Professional ASP XML |
| Duct Tape Marketing: The World's Most Practical Small Business Marketing Guide |
| The Dig |
| Under the Big Sky: Love Spans Three Generations |
| The Joy of Signing: Second Edition |
| The Indwelling: The Beast Takes Possession |
| Annuals: How to Select, Grow and Enjoy |
| Steadfast Tin Soldier |
| Best of Friends |
| How to Get Out of Debt, Stay Out of Debt and Live Prosperously |
| Hugs for Friends: Stories, Sayings and Scriptures |
| Picnic, Lightning |
8.2 Препораки генерирани од системот
Користен е следниот SQL query:
SELECT * FROM recommend_books_for_member_pgvector(10835, 10);
Резултат:
| Barcode | Title | Similarity |
| 1508595120619 | Confirmation | 0.751 |
| 7068091724530 | My First Book: My First Steps to Reading | 0.748 |
| 5353409214655 | Write Source 2000: A Guide to Writing, Thinking & Learning | 0.739 |
| 3991697075354 | Publication Manual of the American Psychological Association | 0.735 |
| 2052048066367 | The Everyday Writer | 0.731 |
| 8847817354941 | American Literary Criticism From the Thirties to the Eighties | 0.731 |
| 8861556346531 | The Art and Craft of Handmade Paper | 0.730 |
| 0959535611626 | Ethics & National Defense: The Timeless Issues | 0.730 |
| 3014017184207 | Encyclopedia of Discovery: Science and History | 0.729 |
| 0390749084413 | Achieving Authentic Success | 0.727 |
8.3 Проверка дека не се препорачуваат веќе позајмени книги
Користен е следниот SQL query:
SELECT COUNT(*) FROM loan_history lh JOIN book_copy bc ON bc.copy_id = lh.copy_id WHERE lh.member_user_id = 10835 AND bc.barcode IN ( SELECT barcode FROM recommend_books_for_member_pgvector(10835, 10) );
Резултат:
0
Ова покажува дека ниту една од препорачаните книги не се појавува во претходната историја на позајмувања на членот.
8.4 Конкретни примери
Пример 1
Loan History: Sex and Subterfuge: Women Writers to 1850 Recommended: American Literary Criticism From the Thirties to the Eighties
Објаснување:
И двете книги се поврзани со литература и анализа на книжевни дела. Иако не станува збор за исти автори или ист временски период, системот препознава поврзаност во областа на книжевноста и литературната критика.
Пример 2
Loan History: Professional ASP XML Recommended: Write Source 2000: A Guide to Writing, Thinking & Learning
Објаснување:
Двете книги имаат образовен и професионален карактер. Системот препознава интерес за книги кои служат за учење, развој на вештини и работа со специјализирани знаења.
Пример 3
Loan History: Improving the Quality of Long-Term Care Recommended: Publication Manual of the American Psychological Association
Објаснување:
И двете книги припаѓаат на стручна и академска литература. Едната е насочена кон здравствена грижа, а другата кон академско истражување и научно пишување, што укажува на интерес кон професионална и образовна содржина.
8.5 Анализа на резултатите
Системот генерираше препораки со similarity вредности помеѓу 0.72 и 0.75.
Резултатите покажуваат дека препораките се базираат на семантичка сличност помеѓу книгите, а не само на точно совпаѓање на жанрови, категории или автори.
Дополнително, проверката врати резултат 0, што потврдува дека системот успешно ги исклучува книгите кои членот веќе ги има позајмено и препорачува само нови книги.
