Bulk Load and Build a Vector Index

이 예제는 SQL COPY로 tab-separated row를 적재한 뒤 HNSW 인덱스를 만듭니다. 인덱스 작업이 끝날 때까지 기다린 다음 SQL view로 상태를 확인합니다.

COPY ... FROM STDIN은 psql session에서 실행하는 문법입니다. Supabase SQL editor처럼 독립적인 SQL statement만 실행하는 환경에는 적합하지 않습니다.

DROP TABLE IF EXISTS akasicdb_example_product_vectors;

CREATE TABLE akasicdb_example_product_vectors (
  id integer PRIMARY KEY,
  sku text NOT NULL,
  title text NOT NULL,
  embedding vector(4) NOT NULL
);

COPY akasicdb_example_product_vectors (id, sku, title, embedding)
FROM STDIN WITH (FORMAT text, DELIMITER E'\t');
1   sku-001 compact keyboard    [0.01,0.19,0.10,0.00]
2   sku-002 ergonomic keyboard  [0.02,0.18,0.12,0.00]
3   sku-003 mechanical keyboard [0.03,0.17,0.10,0.01]
4   sku-004 wireless mouse  [0.04,0.16,0.20,0.00]
5   sku-005 trackball mouse [0.05,0.15,0.22,0.00]
6   sku-006 desk mat    [0.06,0.14,0.05,0.02]
7   sku-007 monitor arm [0.07,0.13,0.30,0.01]
8   sku-008 usb hub [0.08,0.12,0.08,0.03]
9   sku-009 standing desk   [0.09,0.11,0.35,0.02]
10  sku-010 laptop stand    [0.10,0.10,0.25,0.01]
11  sku-011 webcam  [0.11,0.09,0.40,0.02]
12  sku-012 microphone  [0.12,0.08,0.42,0.03]
13  sku-013 studio light    [0.13,0.07,0.38,0.04]
14  sku-014 cable sleeve    [0.14,0.06,0.06,0.02]
15  sku-015 usb charger [0.15,0.05,0.07,0.03]
16  sku-016 tablet stand    [0.16,0.04,0.28,0.02]
17  sku-017 privacy screen  [0.17,0.03,0.33,0.01]
18  sku-018 notebook    [0.18,0.02,0.02,0.04]
19  sku-019 pen set [0.19,0.01,0.01,0.05]
20  sku-020 document tray   [0.20,0.00,0.04,0.04]
\.

CREATE INDEX akasicdb_product_embedding_hnsw_idx
ON akasicdb_example_product_vectors
USING vectoron (embedding vector_l2_ops)
WITH (options = $$
[indexing.hnsw]
$$);

SELECT fence_vector_index('akasicdb_product_embedding_hnsw_idx'::regclass);

SELECT idx_status, idx_indexing, idx_tuples
FROM vectoron_index_stat
WHERE indexname = 'akasicdb_product_embedding_hnsw_idx';

SELECT id, sku, title
FROM akasicdb_example_product_vectors
ORDER BY embedding <-> ARRAY[0.10, 0.10, 0.25, 0.01]::real[]::vector
LIMIT 5;

검증 지점

단계	SQL 인터페이스	목적
Bulk load	COPY ... FROM STDIN	psql에서 vector row를 한 번에 적재
Build index	CREATE INDEX ... USING vectoron	적재 후 AkasicDB vector 인덱스 생성
Wait	fence_vector_index	현재 index 작업이 끝날 때까지 대기
Inspect	vectoron_index_stat	현재 AkasicDB 버전이 제공하는 상태 field 확인

fence_vector_index 이후 idx_indexing = false이면 현재 index 작업이 끝난 상태입니다. 이 예제는 view가 제공하는 field 중 일부만 사용합니다.