Bulk Load and Build a Vector Index

This example loads tab-separated rows with SQL COPY, creates an HNSW index after the load, waits for index processing, and reads the index state from SQL.

COPY ... FROM STDIN is intended for a psql session. It is not suitable for SQL editors that only execute standalone SQL statements, such as the Supabase SQL editor.

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;

Checkpoints

Step	SQL interface	Purpose
Bulk load	COPY ... FROM STDIN	Load vector rows in one psql-friendly operation
Build index	CREATE INDEX ... USING vectoron	Create the AkasicDB vector index after data load
Wait	fence_vector_index	Block until current index work completes
Inspect	vectoron_index_stat	Read status fields exposed by the current AkasicDB version

idx_indexing = false after the fence means the current index work has completed. The view can expose more fields than this example uses.