「Beatles abbey rd」と入力したときに「Abbey Road」を見つけるには、PostgreSQL でファジー／セマンティック検索を使用してください。

問題点：汚い入力ときれいなデータ

検索機能を構築する際、データベースには「Abbey Road」「The Dark Side of the Moon」「OK Computer」のように完全に整備されたアルバム名が入っているかもしれません。
しかしユーザーは次のように入力します。

beatles abbey rd
dark side moon pink floyd
ok computer radiohead 1997

単純な

WHERE name = ?

二つのアプローチ、二つの PostgreSQL 拡張

• pg_trgm

  はテキストを 3文字ずつに分割し、重複率を測ります。
  例：

  "Abbey Road"

  →

  {" ab","abb","bbe","bey","ey "," ro","roa","oad","ad "}

  。

• pgvector

  は機械学習モデルで生成した意味表現（ベクトル）を格納します。

データベースのセットアップ

-- 拡張機能を有効化
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS vector;

-- カタログテーブル
CREATE TABLE album_catalog (
    id SERIAL PRIMARY KEY,
    track_id VARCHAR(50),
    track_name VARCHAR(500) NOT NULL,
    artists VARCHAR(500),
    album_name VARCHAR(500),
    popularity INTEGER,
    album_normalized VARCHAR(500),       -- ファジーマッチ用に整形した文字列
    album_embedding vector(768)          -- 意味検索用埋め込みベクトル
);

Spotify データセットをロード

# load_spotify_data.py
from datasets import load_dataset
import psycopg2

dataset = load_dataset("maharshipandya/spotify-tracks-dataset", split="train")

conn = psycopg2.connect(host='localhost', database='music_catalog',
                        user='your_user', password='your_password')
cursor = conn.cursor()

seen_albums = set()
inserted = 0
for row in dataset:
    album_key = (row['album_name'], row['artists'])
    if album_key in seen_albums: continue
    seen_albums.add(album_key)

    cursor.execute("""
        INSERT INTO album_catalog (track_id, track_name, artists, album_name, popularity)
        VALUES (%s,%s,%s,%s,%s)
    """, (row['track_id'], row['track_name'],
          row['artists'], row['album_name'], row['popularity']))
    inserted += 1
    if inserted % 5000 == 0:
        print(f"Inserted {inserted} albums...")

conn.commit()
conn.close()

実行方法：

pip install datasets psycopg2-binary
python load_spotify_data.py

約5万件のユニークアルバムが投入されます。

インデックス

-- pg_trgm 用 GIN インデックス
CREATE INDEX idx_album_name_trgm 
ON album_catalog USING gin (album_normalized gin_trgm_ops);

-- pgvector 用 IVFFlat インデックス
CREATE INDEX idx_album_embedding 
ON album_catalog USING ivfflat (album_embedding vector_cosine_ops)
WITH (lists = 100);

lists=100

1️⃣ pg_trgm を使ったファジーマッチング

基本的な類似度クエリ

SELECT album_name, artists,
       similarity('abbey rd beatles', album_name) AS score
FROM album_catalog
WHERE similarity('abbey rd beatles', album_name) > 0.3
ORDER BY score DESC
LIMIT 5;

例結果：

GIN インデックスを使う

SET pg_trgm.similarity_threshold = 0.3;

SELECT album_name, artists,
       similarity('abbey rd', album_normalized) AS score
FROM album_catalog
WHERE album_normalized % 'abbey rd'
ORDER BY score DESC;

強み

• タイプミス（

  Abey Road

  →

  Abbey Road

  ）に対処
• 略語（

  abbey rd

  ）を拡張
• 単語の欠落や順序変更にも柔軟

弱点

• 同義語・概念的なクエリには弱い
• 完全に別表現の場合は失敗する

2️⃣ pgvector を使った意味検索

埋め込みベクトルの生成（1度だけ）

# generate_embeddings.py
from sentence_transformers import SentenceTransformer
import psycopg2

model = SentenceTransformer('all-mpnet-base-v2')

conn = psycopg2.connect(host='localhost', database='music_catalog',
                        user='your_user', password='your_password')
cursor = conn.cursor()

cursor.execute("""
    SELECT id, album_name, artists 
    FROM album_catalog
    WHERE album_embedding IS NULL
""")
albums = cursor.fetchall()

batch_size = 100
for i in range(0, len(albums), batch_size):
    batch = albums[i:i+batch_size]
    texts = [f"{album} by {artist}" if artist else album 
             for _, album, artist in batch]

    embeddings = model.encode(texts)

    for j, (album_id, _, _) in enumerate(batch):
        cursor.execute("""
            UPDATE album_catalog
            SET album_embedding = %s
            WHERE id = %s
        """, (embeddings[j].tolist(), album_id))
    conn.commit()

conn.close()

埋め込み生成は CPU が良ければ 1 分あたり約500件です。
一度だけ実行してください。

埋め込みで検索

def search_by_embedding(query, cursor, model, threshold=0.6):
    query_emb = model.encode(query).tolist()
    cursor.execute("""
        SELECT album_name, artists,
               1 - (album_embedding <=> %s::vector) AS similarity
        FROM album_catalog
        WHERE album_embedding IS NOT NULL
          AND 1 - (album_embedding <=> %s::vector) > %s
        ORDER BY similarity DESC
        LIMIT 5;
    """, (query_emb, query_emb, threshold))
    return cursor.fetchall()

強み

• 同義語・言い換えに強い
• 自然言語クエリを扱える
• 概念的に似たアルバムを探せる

弱点

• 意味的には近いが誤ったアルバムが上位になる可能性
• 略語（

  rd

  →

  Road

  ）は理解できない場合も
• 学習済みモデルが必要

📌 テキスト正規化 ― 秘訣

両手法ともクリーンな入力で最良の結果を得られます。以下は簡易正規化パイプラインです。

import re

ABBREVIATIONS = {
    r'\bfeat\.?\b': 'featuring',
    r'\bft\.?\b'  : 'featuring',
    r'\bvol\.?\b' : 'volume',
    r'\bpt\.?\b'  : 'part',
    r'\bv\.?\s*(\d)': r'volume \1',
    r'\bst\.?\b'   : 'saint',
    r'\b&\b'      : 'and',
}

NOISE_PATTERNS = [
    r'\(remaster(ed)?\s*\d*\)',            # (Remastered 2023)
    r'\(\d{4}\s*remaster(ed)?\)',          # (2011 Remaster)
    r'\[deluxe(\s+edition)?\]',            # [Deluxe Edition]
    r'\(deluxe(\s+edition)?\)',            # (Deluxe Edition)
    r'\s*-\s*single\b',                    # - Single
    r'\s*\[\d+[-/]\d+\]',                  # [Disc 1/2]
    r'\(anniversary(\s+edition)?\)',       # (Anniversary Edition)
    r'\(expanded(\s+edition)?\)',          # (Expanded Edition)
    r'\(bonus\s+track.*?\)',               # (Bonus Track Version)
    r'\(super\s+deluxe\)',                 # (Super Deluxe)
    r'\(\d{4}\s+re-?issue\)',              # (2021 Reissue)
    r'\s+OKNOTOK\s+\d{4}\s+\d{4}',          # OKNOTOK 1997 2017
    r'\s*\(original\s+motion\s+picture.*?\)',# (Original Motion Picture Soundtrack)
]

LEADING_ARTICLES = ['the', 'a', 'an']

def normalize_album(text):
    if not text: return ''
    s = text.lower().strip()
    for pat in NOISE_PATTERNS:
        s = re.sub(pat, '', s, flags=re.IGNORECASE)
    for pat, repl in ABBREVIATIONS.items():
        s = re.sub(pat, repl, s, flags=re.IGNORECASE)
    for art in LEADING_ARTICLES:
        if s.startswith(art + ' '):
            s = s[len(art)+1:]
            break
    s = re.sub(r'\s+', ' ', s).strip()
    s = re.sub(r'[^\w\s]', '', s)  # punctuation を除去
    return s

album_normalized

# normalize_albums.py
import psycopg2

conn = psycopg2.connect(host='localhost', database='music_catalog',
                        user='your_user', password='your_password')
cursor = conn.cursor()
cursor.execute("SELECT id, album_name FROM album_catalog")
albums = cursor.fetchall()

for album_id, name in albums:
    norm = normalize_album(name)
    cursor.execute("""
        UPDATE album_catalog
        SET album_normalized = %s
        WHERE id = %s;
    """, (norm, album_id))
conn.commit()

これでファジーマッチが正確に機能します。

🤝 両手法を組み合わせる

ハイブリッド検索：まずファジー、スコアが低ければ埋め込みへフォールバック。

def search_catalog(query, cursor, model,
                   fuzzy_thr=0.3, embed_thr=0.6):
    norm_q = normalize_album(query)

    # 1️⃣ ファジーマッチ
    cursor.execute("""
        SELECT id, album_name, artists,
               similarity(%s, album_normalized) AS score,
               'fuzzy' AS method
        FROM album_catalog
        WHERE similarity(%s, album_normalized) > %s
        ORDER BY score DESC
        LIMIT 1;
    """, (norm_q, norm_q, fuzzy_thr))
    fuzzy = cursor.fetchone()

    if fuzzy and fuzzy[3] >= 0.65:
        return fuzzy

    # 2️⃣ 埋め込みマッチ
    emb_q = model.encode(query).tolist()
    cursor.execute("""
        SELECT id, album_name, artists,
               1 - (album_embedding <=> %s::vector) AS score,
               'embedding' AS method
        FROM album_catalog
        WHERE album_embedding IS NOT NULL
          AND 1 - (album_embedding <=> %s::vector) > %s
        ORDER BY score DESC
        LIMIT 1;
    """, (emb_q, emb_q, embed_thr))
    embed = cursor.fetchone()

    return embed or fuzzy

例：

print(search_catalog("abbey rd", cursor, model))
# → ファジー: Abbey Road (Remastered)

print(search_catalog("beatles last studio album", cursor, model))
# → 埋め込み: Abbey Road (Remastered)

📈 パフォーマンスノート

lists

• 新しい行を大量に追加した場合（元の行数の >10%）はベクトルインデックスを再構築してください。

• pg_size_pretty(pg_relation_size('idx_album_name_trgm'))

  でインデックスサイズ（約2–3倍程度）が確認できます。

📚 いつどちらを使うか

🔑 埋め込みモデル選択

all-MiniLM-L6-v2

all-mpnet-base-v2

🎉 結論

• pg_trgm は文字レベルで高速にファジーマッチ。
• pgvector は意味ベースの類似検索を実現。
• 正規化は両手法のパフォーマンスを最大限に引き上げます。
• ハイブリッドアプローチが最良の結果を提供します。

Spotify の約5万件の実データでテストしたとおり、同じパターンは書籍・商品など他のカタログでも適用できます。

すべては標準 PostgreSQL と

pg_trgm

pgvector