Ollama 임베딩을 pgvector로 PostgreSQL에 저장하고 검색하기

4차원 3행. 텍스트는 /api/embed 요청 본문, 벡터는 Ollama /api/embed 응답(으)로 읽었습니다.

psql이나 다른 클라이언트에서 실행하세요. 행은 하나의 트랜잭션으로 삽입됩니다.

BEGIN;

INSERT INTO documents (content, embedding) VALUES
  ('Embeddings turn text into numeric vectors.', '[0.620186,0.410123,0.550165,0.380114]'),
  ('pgvector stores vectors in PostgreSQL, next to the rest of your data.', '[0.578815,0.469039,0.518937,0.419142]'),
  ('Ollama''s /api/embed returns one vector per input, in the same order.', '[0.119982,0.809879,-0.329951,0.46993]');

COMMIT;

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text NOT NULL,
  embedding vector(384) NOT NULL
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

PREPARE는 /api/embed로 얻은 질문의 벡터를 $1로 받습니다. '[0.01,-0.02,…]'처럼 텍스트로 전달하세요.

PREPARE search_documents(vector(384)) AS
  SELECT id, content, 1 - (embedding <=> $1) AS cosine_similarity
  FROM documents
  ORDER BY embedding <=> $1
  LIMIT 5;

-- EXECUTE search_documents('[0.0123, -0.0456, ...]');

출처 3개, 객체의 JSON 배열로 읽었습니다.

이 컴퓨터의 Ollama(localhost:11434)로 요청을 보냅니다.

curl http://localhost:11434/api/chat -d '{
  "model": "qwen2.5:7b",
  "messages": [
    {
      "role": "system",
      "content": "Answer the question using only the numbered sources in the user message. Cite the number of each source you use. If the sources do not contain the answer, say that you do not know and cite nothing. Answer in the same language as the question. For each citation, give the source number and copy a short quote from that source exactly as it is written. Return JSON that matches this schema: {\"type\":\"object\",\"properties\":{\"answer\":{\"type\":\"string\"},\"citations\":{\"type\":\"array\",\"items\":{\"type\":\"object\",\"properties\":{\"source\":{\"type\":\"integer\"},\"quote\":{\"type\":\"string\"}},\"required\":[\"source\",\"quote\"]}}},\"required\":[\"answer\",\"citations\"]}"
    },
    {
      "role": "user",
      "content": "Sources:\n\n[1] (id: 12)\nWith HNSW, hnsw.ef_search is 40 by default, so a query returns at most 40 rows unless you raise it.\n\n[2] (id: 7)\nSET LOCAL inside a transaction changes hnsw.ef_search for a single query.\n\n[3] (id: 3)\npgvector supports PostgreSQL 13 and later.\n\nQuestion: Why does my HNSW search return only 40 rows with LIMIT 100?"
    }
  ],
  "stream": false,
  "format": {
    "type": "object",
    "properties": {
      "answer": {
        "type": "string"
      },
      "citations": {
        "type": "array",
        "items": {
          "type": "object",
          "properties": {
            "source": {
              "type": "integer"
            },
            "quote": {
              "type": "string"
            }
          },
          "required": [
            "source",
            "quote"
          ]
        }
      }
    },
    "required": [
      "answer",
      "citations"
    ]
  },
  "options": {
    "temperature": 0
  },
  "truncate": false
}'

{
  "model": "qwen2.5:7b",
  "messages": [
    {
      "role": "system",
      "content": "Answer the question using only the numbered sources in the user message. Cite the number of each source you use. If the sources do not contain the answer, say that you do not know and cite nothing. Answer in the same language as the question. For each citation, give the source number and copy a short quote from that source exactly as it is written. Return JSON that matches this schema: {\"type\":\"object\",\"properties\":{\"answer\":{\"type\":\"string\"},\"citations\":{\"type\":\"array\",\"items\":{\"type\":\"object\",\"properties\":{\"source\":{\"type\":\"integer\"},\"quote\":{\"type\":\"string\"}},\"required\":[\"source\",\"quote\"]}}},\"required\":[\"answer\",\"citations\"]}"
    },
    {
      "role": "user",
      "content": "Sources:\n\n[1] (id: 12)\nWith HNSW, hnsw.ef_search is 40 by default, so a query returns at most 40 rows unless you raise it.\n\n[2] (id: 7)\nSET LOCAL inside a transaction changes hnsw.ef_search for a single query.\n\n[3] (id: 3)\npgvector supports PostgreSQL 13 and later.\n\nQuestion: Why does my HNSW search return only 40 rows with LIMIT 100?"
    }
  ],
  "stream": false,
  "format": {
    "type": "object",
    "properties": {
      "answer": {
        "type": "string"
      },
      "citations": {
        "type": "array",
        "items": {
          "type": "object",
          "properties": {
            "source": {
              "type": "integer"
            },
            "quote": {
              "type": "string"
            }
          },
          "required": [
            "source",
            "quote"
          ]
        }
      }
    },
    "required": [
      "answer",
      "citations"
    ]
  },
  "options": {
    "temperature": 0
  },
  "truncate": false
}

응답에서 답변은 message.content에 있습니다. prompt_eval_count가 예상보다 훨씬 낮다면 프롬프트가 잘렸을 수 있습니다.

인용