Graph+Vector Hybrid Example¶

This example demonstrates a workflow that uses graph and vector features together in AkasicDB.

Load the Extension¶

CREATE EXTENSION IF NOT EXISTS akasicdb;

SELECT akasicdb_admin.initialize();

Registers all data types and functions of AkasicDB in the current database.
Only a superuser can perform this operation.

Create Tables and Prepare Tutorial Data¶

For table creation and tutorial data setup, follow Create Tables and Populate Tutorial Data in Graph Example.

Add Vector Columns¶

ALTER TABLE store
ADD COLUMN embedding vector(3) NOT NULL DEFAULT '[0,0,0]';
ALTER TABLE item
ADD COLUMN embedding vector(3) NOT NULL DEFAULT '[0,0,0]';
ALTER TABLE customer
ADD COLUMN embedding vector(3) NOT NULL DEFAULT '[0,0,0]';
ALTER TABLE orders
ADD COLUMN embedding vector(3) NOT NULL DEFAULT '[0,0,0]';

UPDATE store
SET embedding = ARRAY[random(), random(), random()]::real[];
UPDATE item
SET embedding = ARRAY[random(), random(), random()]::real[];
UPDATE customer
SET embedding = ARRAY[random(), random(), random()]::real[];
UPDATE orders
SET embedding = ARRAY[random(), random(), random()]::real[];

Adds a column to each table for storing embedding vectors.
Generates embeddings as random 3-dimensional vectors.

Define and Create the Graph¶

SELECT akasicdb.define_graph('retail_graph');

SELECT akasicdb.define_vertex(
  'retail_graph', 
  'v_item', 
  ARRAY['i_no integer', 'name varchar(50)', 'price decimal(7,2)', 'embedding vector(3)'],
  'item'
);
SELECT akasicdb.define_vertex(
  'retail_graph', 
  'v_customer', 
  ARRAY['c_id integer', 'first_name varchar(20)', 'last_name varchar(30)', 'embedding vector(3)'],
  'customer'
);
SELECT akasicdb.define_vertex(
  'retail_graph', 
  'v_store', 
  ARRAY['s_id integer', 'name varchar(50)', 'embedding vector(3)'], 
  'store',
  'SELECT s_id, name, embedding FROM store'
);

SELECT akasicdb.define_edge(
  'retail_graph', 
  'buy',
  'v_customer', 'v_item',
  null,
  'SELECT null FROM customer c, orders o, item i '
  'WHERE c.c_id = o.c_id AND i.i_no = o.i_no',
  'customer c', 'item i'
);

SELECT akasicdb.define_edge(
  'retail_graph', 
  'sell',
  'v_store', 'v_item',
  null,
  'SELECT null FROM store s, orders o, item i '
  'WHERE s.s_id = o.s_id AND i.i_no = o.i_no',
  'store s', 'item i'
);

SELECT akasicdb.create_graph('retail_graph');

This graph definition is similar to the one used in Define and Create the Graph in Graph Example.
In this example, the embedding vector column added to each table is included as a vertex property.

Create HNSW Index¶

CREATE INDEX ON retail_graph.v_customer USING vectoron (embedding vector_l2_ops);
CREATE INDEX ON retail_graph.v_item USING vectoron (embedding vector_l2_ops);

This example uses AkasicDB's default vector index family, HNSW. For focused vector-only SQL examples, see Vector Examples.
AkasicDB can create indexes on vector columns exposed through graph vertices and edges. In this example, HNSW indexes are created on the embedding properties of the v_customer and v_item vertices.

Graph + Vector Query¶

-- Set the HNSW search width
SET vectoron.hnsw_ef_search = 64;

Use vectoron.hnsw_ef_search to adjust HNSW search width at query time.

SELECT first_name, last_name, product_name, price
FROM akasicdb.cypher('retail_graph', $$
MATCH (v:v_customer)
WITH v.vertex_id AS customer_top_k
ORDER BY v.embedding <-> '[0.15,0.15,0.35]'
LIMIT 5
MATCH (c:v_customer)-[:buy]->(i:v_item)
WHERE c.vertex_id = customer_top_k
RETURN
    c.first_name as first_name,
    c.last_name as last_name,
    i.name as product_name,
    i.price as price
$$) as (
    first_name varchar,
    last_name varchar,
    product_name varchar,
    price decimal
)
ORDER BY first_name, last_name, product_name;

The first MATCH clause uses a vector operator to find the top-k matching customers and assigns them to customer_top_k.
The WITH clause passes customer_top_k to the next MATCH clause.
The graph query then finds items purchased by the customers in customer_top_k.

This example showed how to use vector search and graph queries together in AkasicDB.