Compare Index Families

This example compares the CREATE INDEX syntax for Flat, HNSW, IVF, and Vamana on the same L2 vector dataset. It does not compare performance.

DROP TABLE IF EXISTS akasicdb_example_index_family;

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

INSERT INTO akasicdb_example_index_family (id, title, embedding) VALUES
  (1, 'vector search overview', ARRAY[0.10,0.80,0.05,0.00]::real[]::vector),
  (2, 'nearest neighbor terms', ARRAY[0.12,0.78,0.06,0.01]::real[]::vector),
  (3, 'index build options', ARRAY[0.09,0.82,0.04,0.00]::real[]::vector),
  (4, 'query search width', ARRAY[0.15,0.74,0.08,0.02]::real[]::vector),
  (5, 'filtered search', ARRAY[0.20,0.65,0.10,0.03]::real[]::vector),
  (6, 'index status checks', ARRAY[0.24,0.60,0.12,0.03]::real[]::vector),
  (7, 'product taxonomy', ARRAY[0.30,0.50,0.20,0.05]::real[]::vector),
  (8, 'document ranking', ARRAY[0.32,0.48,0.22,0.05]::real[]::vector),
  (9, 'graph traversal', ARRAY[0.50,0.30,0.35,0.08]::real[]::vector),
  (10, 'storage checkpoint', ARRAY[0.55,0.25,0.40,0.10]::real[]::vector),
  (11, 'backup planning', ARRAY[0.60,0.20,0.42,0.12]::real[]::vector),
  (12, 'access control', ARRAY[0.65,0.18,0.44,0.14]::real[]::vector),
  (13, 'event ingestion', ARRAY[0.70,0.16,0.46,0.16]::real[]::vector),
  (14, 'analytics refresh', ARRAY[0.74,0.14,0.48,0.18]::real[]::vector),
  (15, 'metadata filters', ARRAY[0.21,0.64,0.11,0.03]::real[]::vector),
  (16, 'distance operators', ARRAY[0.13,0.77,0.05,0.01]::real[]::vector),
  (17, 'index build queue', ARRAY[0.25,0.59,0.14,0.03]::real[]::vector),
  (18, 'service limits', ARRAY[0.68,0.17,0.46,0.15]::real[]::vector),
  (19, 'search api design', ARRAY[0.16,0.76,0.10,0.02]::real[]::vector),
  (20, 'ranking eval', ARRAY[0.14,0.79,0.09,0.01]::real[]::vector),
  (21, 'vector schema', ARRAY[0.11,0.81,0.03,0.00]::real[]::vector),
  (22, 'index memory review', ARRAY[0.22,0.62,0.13,0.03]::real[]::vector),
  (23, 'content moderation', ARRAY[0.40,0.40,0.28,0.07]::real[]::vector),
  (24, 'stream processing', ARRAY[0.78,0.12,0.50,0.20]::real[]::vector),
  (25, 'catalog sync', ARRAY[0.34,0.46,0.24,0.06]::real[]::vector),
  (26, 'observability plan', ARRAY[0.28,0.58,0.18,0.04]::real[]::vector),
  (27, 'sql migration', ARRAY[0.62,0.22,0.43,0.11]::real[]::vector),
  (28, 'read replica notes', ARRAY[0.66,0.19,0.45,0.13]::real[]::vector),
  (29, 'feature flags', ARRAY[0.72,0.15,0.47,0.17]::real[]::vector),
  (30, 'dashboard refresh', ARRAY[0.76,0.13,0.49,0.19]::real[]::vector),
  (31, 'tenant routing', ARRAY[0.36,0.44,0.26,0.06]::real[]::vector),
  (32, 'operations review', ARRAY[0.29,0.57,0.19,0.04]::real[]::vector);

CREATE INDEX akasicdb_index_family_flat_idx
ON akasicdb_example_index_family
USING vectoron (embedding vector_l2_ops)
WITH (options = $$
[indexing.flat]
$$);

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

CREATE INDEX akasicdb_index_family_ivf_idx
ON akasicdb_example_index_family
USING vectoron (embedding vector_l2_ops)
WITH (options = $$
[indexing.ivf]
nlist = 4
$$);

CREATE INDEX akasicdb_index_family_vamana_idx
ON akasicdb_example_index_family
USING vectoron (embedding vector_l2_ops)
WITH (options = $$
[indexing.vamana]
$$);

SELECT fence_vector_index('akasicdb_index_family_flat_idx'::regclass);
SELECT fence_vector_index('akasicdb_index_family_hnsw_idx'::regclass);
SELECT fence_vector_index('akasicdb_index_family_ivf_idx'::regclass);
SELECT fence_vector_index('akasicdb_index_family_vamana_idx'::regclass);

SELECT indexname, idx_indexing, idx_tuples
FROM vectoron_index_stat
WHERE indexname IN (
  'akasicdb_index_family_flat_idx',
  'akasicdb_index_family_hnsw_idx',
  'akasicdb_index_family_ivf_idx',
  'akasicdb_index_family_vamana_idx'
)
ORDER BY indexname;

Index Families

Index	Description	Build option
Flat	Exact-search baseline	[indexing.flat]
HNSW	Graph ANN index	[indexing.hnsw]
IVF	Cluster-based ANN index	nlist = 4
Vamana	Graph ANN index for L2-distance workloads	[indexing.vamana]

The default IVF nlist value is not suitable for small datasets, so this example uses 4.