ai-tools/vector-architecture

75 notes in this subject, read out of this brain and free to use. This is page 1 of 2.

Small workload vector architecture: single database with views

For small workloads, store all vector data in a single Supabase database. Create multiple vector collections using Vecs and expose them to your application through PostgreSQL views. Each collection (such as docs, posts, and images) is stored in the same database and accessed via its corresponding view.

Create view to expose vector collection

Example SQL to expose a vector collection through a view: CREATE VIEW public.docs AS SELECT id, embedding, metadata, (metadata->>'url')::text as url FROM vector; This view selects the id, embedding, and metadata fields from the vector table, and extracts the url from the metadata JSON object as a text field.

Query vector collection via view in application

Example JavaScript code to query a vector collection exposed through a view: const { data, error } = await supabase .from('docs') .select('id, embedding, metadata') .eq('url', '/hello-world'); This uses the Supabase client library to query the docs view with a filter condition on the url field.

Enterprise vector architecture: multiple secondary databases

For production, split vector collections across separate secondary database projects (called pods) alongside a primary database. This allows each vector collection to scale independently of production data, removes single-point-of-failure risk, and accommodates the fact that vectors typically grow faster than operational data and have different resource requirements.

Two methods to access collections in enterprise architecture

Option 1: Query collections directly using Vecs Python client library. Option 2: Access collections from the primary database through a Foreign Data Wrapper. Both methods can be used in tandem. Option 1 is recommended wherever possible as it offers the most scalability.

Query vector collections with Vecs: cosine similarity

Example Python code using Vecs for cosine similarity query: docs.query(query_vector=[0.4,0.5,0.6], limit=5) This queries the docs collection with a query vector and returns the 5 most similar results based on cosine similarity.

Query vector collections with Vecs: metadata filtering

Example Python code using Vecs for metadata filtering: docs.query( query_vector=[0.4,0.5,0.6], limit=5, filters={"year": {"$eq": 2012}} ) This queries the docs collection with a query vector, applies metadata filters to match records where the year field equals 2012, and returns the 5 most similar results.

Foreign Data Wrapper setup for vector collections

To connect a secondary vector database from the primary database using Foreign Data Wrappers, you must first enable the postgres_fdw extension, create a foreign server connection, and set up user mapping credentials. Example SQL: CREATE EXTENSION postgres_fdw; CREATE SERVER docs_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db.xxx.supabase.co', port '5432', dbname 'postgres'); CREATE USER MAPPING FOR docs_user SERVER docs_server OPTIONS (user 'postgres', password 'password'); The server definition specifies the remote database host, port, and database name. The user mapping defines credentials for accessing the remote database.

Create foreign table for vector collection

After setting up the Foreign Data Wrapper server and user mapping, create a foreign table definition in the primary database: CREATE FOREIGN TABLE docs ( id text not null, embedding extensions.vector(384), metadata jsonb, url text ) SERVER docs_server OPTIONS (schema_name 'public', table_name 'docs'); This creates a foreign table named docs that maps to the docs table in the public schema of the secondary database. The foreign table can then be queried using standard client libraries.

Query vector collection via foreign table

Example JavaScript code to query a vector collection through a foreign table in the primary database: const { data, error } = await supabase .from('docs') .select('id, embedding, metadata') .eq('url', '/hello-world'); This uses the same Supabase client library syntax as querying a view, transparently accessing data from the secondary database through the foreign table definition.

Google Colab setup for Supabase vector store

Google Colab is a hosted Jupyter Notebook service that provides free access to computing resources including GPUs and TPUs. It is well-suited to machine learning, data science, and education, and can be used to manage Supabase vector collections using Supabase Vecs. Start by visiting colab.research.google.com to create a new notebook.

Install Vecs Python client in Google Colab

To install the Supabase Vector client (Vecs) in a Google Colab notebook, create a new cell and run: pip install vecs

Connect to Supabase database from Google Colab

To connect to a Supabase database from Google Colab, use the Postgres connection string from your project dashboard. Create a code block with the following code, replacing the connection string with your actual Postgres URI: import vecs DB_CONNECTION = "postgres://postgres.xxxx:password@xxxx.pooler.supabase.com:6543/postgres" # create vector store client vx = vecs.create_client(DB_CONNECTION) Execute the code block to establish the connection.

Create and upsert vectors in Vecs collection

To create a collection and insert vectors in Google Colab, use the following code: collection = vx.get_or_create_collection(name="colab_collection", dimension=3) collection.upsert( vectors=[ ( "vec0", # the vector's identifier [0.1, 0.2, 0.3], # the vector. list or np.array {"year": 1973} # associated metadata ), ( "vec1", [0.7, 0.8, 0.9], {"year": 2012} ) ] ) This creates a table in the vecs schema with the specified collection name. Each vector has an identifier, a vector array, and associated metadata.

Query vectors by similarity in Vecs

To search for vectors based on similarity in a Vecs collection, use the query method with the following parameters: collection.query( query_vector=[0.4,0.5,0.6], # required limit=5, # number of records to return filters={}, # metadata filters measure="cosine_distance", # distance measure to use include_value=False, # should distance measure values be returned? include_metadata=False, # should record metadata be returned? ) The query_vector parameter is required. The measure parameter defaults to cosine_distance. Results are returned as an array of vector identifiers.

Vecs query warning about covering index

When querying vectors in Vecs without a covering index for the distance measure being used, Vecs will return a warning: "Query does not have a covering index for cosine_distance." Creating an index can improve query performance. See the Vecs documentation for details on creating indexes.

View Vecs collection data in Supabase dashboard

Vector collections created through Vecs are stored as tables in the vecs schema of your Supabase database. You can view the inserted items in the Table Editor of the Supabase dashboard by selecting the vecs schema from the schema dropdown.

Sequential scans vs indexes tradeoff

Sequential scans guarantee 100% accuracy and are not RAM bound, but result in significantly higher latencies and lower throughput. Indexes provide lower latencies and higher throughput but reduce accuracy since they replace exact KNN search with approximate ANN search and require additional RAM for Postgres caching. Use sequential scans if you have a small dataset, don't expect high vector search queries per second, or need to guarantee 100% accuracy. Extra CPU cores may help improve queries per second with sequential scans but will not reduce latency.

HNSW index parameters: m, ef_construction, ef_search

HNSW index build parameters are: m (number of bi-directional links created for each new element, default 16, range 2-100, 12-48 recommended), and ef_construction (size of dynamic list for nearest neighbors during construction, default 64, must be at least 2*m). Higher m suits datasets with high dimensionality and accuracy requirements. Higher ef_construction improves index quality and accuracy but increases build time. Measure accuracy when ef_search=ef_construction; if lower than 0.9, there is room for improvement. The search parameter ef_search (size of dynamic list during search, default 40) increases accuracy when raised but also increases query execution time.

IVFFlat index parameters: lists and probes

IVFFlat divides a dataset into partitions defined by the lists constant. The probes parameter controls how many lists are searched during a query. Higher lists means slower index building but better QPS and accuracy. Higher probes means slower select queries but better accuracy. lists and probes are not independent: higher lists requires higher probes to achieve the same accuracy.

Vector search distance metric performance

Prefer inner-product distance over L2 or Cosine if vectors are normalized (such as text-embedding-ada-002). If embeddings are not normalized, Cosine distance should give the best results with an index.

Pre-warm database for optimal vector search performance

Use pg_prewarm to load the index into RAM with the command: select pg_prewarm('vecs.docs_vec_idx');. This avoids cold cache issues. Additionally, execute 10,000 to 50,000 warm-up queries before each benchmark or production deployment to use cache and buffers more efficiently.

Postgres configuration optimization for vector indexes

The Supabase managed platform automatically optimizes Postgres configs based on compute add-on. For self-hosted deployments, adjust Postgres config based on available RAM and CPU cores. See example optimizations at https://gist.github.com/egor-romanov/323e2847851bbd758081511785573c08.

RAG with Row Level Security using pgvector

pgvector is built on top of Postgres, allowing you to implement fine-grained access control on your vector database using Row Level Security (RLS). This restricts which documents are returned during a vector similarity search to users that have access to them.

Foreign Data Wrappers for external permission data

Supabase supports Foreign Data Wrappers (FDW) which allows you to use an external database or data source to determine permissions if your user data doesn't exist in Supabase.

Basic RAG document structure with owner tracking

A typical RAG setup uses a documents table to track documents/pages/files with an owner_id field, and a document_sections table to store chunked content and embedding vectors. The document_sections table references the documents table and includes a vector column of type extensions.vector.

RLS policy for document section queries

Create an RLS policy that restricts access to document_sections based on ownership of the linked document. The policy checks if the current user owns the document referenced by document_id using auth.uid() for REST API queries or current_setting() for direct Postgres connections.

Many-to-many document ownership with RLS

For documents owned by multiple people, use a join table called document_owners with columns id (bigint primary key), owner_id (uuid references auth.users), and document_id (bigint references documents). Update RLS policies to query the join table instead of the documents table directly.

FDW setup for external Postgres database

To use external Postgres data for permissions: create a foreign server using postgres_fdw extension, map local 'authenticated' role to external database user, and import foreign tables into a local schema using 'import foreign schema' statement.

RLS latency consideration with FDW

When using Foreign Data Wrappers for RLS, extra caution should be taken because RLS is latency-sensitive. Use the query plan analyzer to measure execution times and ensure they are within expected ranges. For enterprise applications, contact enterprise@supabase.io.

Session variable for direct Postgres connection RLS

When connecting directly to Supabase Postgres DB, use custom session variables to track the current user ID. Access the variable through current_setting() function. Example: set app.current_user_id = '<current-user-id>' at the beginning of each session, then cast it to the appropriate data type in RLS policies.

auth.uid() references JWT subject claim

The auth.uid() function used in RLS policies references current_setting('request.jwt.claim.sub'), which corresponds to the JWT's 'sub' (subject) claim. This setting is automatically set at the beginning of each REST API request.

Custom JWT with REST API for external auth

To use the auto-generated REST API with JWTs from an external auth provider, configure the external auth provider to issue a custom JWT for Supabase. The JWT must include a 'sub' (subject) claim containing the user ID. Example: Clerk provides integration instructions for Supabase.

Vector similarity search respects RLS policies

When performing vector similarity search queries using pgvector operators like <#> (inner product), RLS policies are automatically applied. The query only returns document sections that the current user has access to based on the RLS policy conditions.

Direct Postgres connection without RLS is not recommended

While you could discard RLS and filter by user within the WHERE clause of queries, this is not recommended as a best practice. RLS is preferred because it is always applied automatically, even as new queries and application logic are introduced in the future.

Foreign data wrapper sources beyond Postgres

For data sources other than Postgres, consult the Foreign Data Wrappers documentation for a list of external sources currently supported. If your data lives in an unsupported source, contact Supabase support to discuss your use case.

pgvector index types: HNSW and IVFFlat

pgvector supports two types of indexes: HNSW and IVFFlat. HNSW is recommended for its superior performance and robustness against changing data.

Use same embedding model for vector comparisons

Always use embeddings produced from the same embedding model when calculating distance. Comparing embeddings from two different models will produce no meaningful result.

Vector search with similarity threshold prevents irrelevant results

The `match_threshold` parameter ensures that only documents with a minimum similarity to the query embedding are returned. Without this filter, you may end up returning documents that do not subjectively match. The appropriate threshold varies by application and requires testing to determine.

Example storing vector embedding with Transformers.js

```js import { pipeline } from '@huggingface/transformers' const generateEmbedding = await pipeline('feature-extraction', 'Supabase/gte-small') const title = 'First post!' const body = 'Hello world!' // Generate a vector using Transformers.js const output = await generateEmbedding(body, { pooling: 'mean', normalize: true, }) // Extract the embedding output const embedding = Array.from(output.data) // Store the vector in Postgres const { data, error } = await supabase.from('documents').insert({ title, body, embedding, }) ```

pgvector extension for storing vectors in Postgres

Supabase uses pgvector, a Postgres extension, to store and query vectors in Postgres. It can be used to store embeddings. Enable it via the Dashboard Extensions page or with SQL: `create extension vector with schema extensions;`. Disable with `drop extension if exists vector;`.

Create vector column with specified dimensions

After enabling the vector extension, you can create columns with the `vector` data type. The size in parentheses specifies the number of dimensions. Example: `embedding extensions.vector(384)` creates a vector column with 384 dimensions. Match the dimension count to your embedding model's output.

PostgREST does not support pgvector operators directly

PostgREST does not currently support pgvector similarity operators. To query vectors from client libraries, wrap the query in a Postgres function and call it via the `rpc()` method.

Example vector search function with RPC call

SQL function: `create or replace function match_documents (query_embedding extensions.vector(384), match_threshold float, match_count int) returns table (id bigint, title text, body text, similarity float) language sql stable as $$ select documents.id, documents.title, documents.body, 1 - (documents.embedding <=> query_embedding) as similarity from documents where 1 - (documents.embedding <=> query_embedding) > match_threshold order by (documents.embedding <=> query_embedding) asc limit match_count; $$;` Call from client with: `const { data: documents } = await supabaseClient.rpc('match_documents', { query_embedding: embedding, match_threshold: 0.78, match_count: 10 })`

Match documents vector search function pattern

Create a Postgres function that takes a query_embedding and compares it to embeddings in your table. Use `1 - (documents.embedding <=> query_embedding)` to convert cosine distance to similarity score. Filter by `match_threshold` to return only sufficiently similar documents, and limit results with `match_count`. When indexing, sort by distance directly, not the calculated similarity column, to preserve index use.

HNSW index creation for cosine distance

To create an HNSW index for cosine distance, use: create index on items using hnsw (column_name vector_cosine_ops);

HNSW hierarchical structure combines skip lists and NSW

HNSW combines hierarchical and navigable small world concepts. The bottom layer consists of a NSW with short links between nodes. Each layer above skips elements and creates longer links between nodes further away. Search starts at the top layer and works downward, using multi-dimensional distance measures like Euclidean distance to determine descent.

Iterative index scans for HNSW filtering

From pgvector 0.8.0, the planner supports iterative index scans controlled by the hnsw.iterative_scan GUC (default off). Two enabled modes are: 'strict_order' preserves exact distance ordering across iterations; 'relaxed_order' allows slight reordering for better recall. hnsw.max_scan_tuples (default 20,000) and hnsw.scan_mem_multiplier (default 1) bound how far iterative scan goes.

HNSW filtering with where clause behavior

Adding a where clause to a vector query does not bypass the HNSW index. The Postgres planner chooses between using the index and sequential scan based on filter selectivity and table size. When the index is used, the filter is applied as the index returns candidates. With selective filters, an HNSW scan returns the top k rows by distance, and filtering may result in fewer rows than the LIMIT.

When to use HNSW indexes

HNSW should be the default choice for creating a vector index when 100% accuracy is not required and you are willing to trade a small amount of accuracy for significant throughput. Unlike IVFFlat indexes, HNSW indexes can be built immediately after table creation as they are based on graphs which automatically fill and maintain optimal structure as new data is added.

Check pgvector version

Check the current pgvector version by running: SELECT * FROM pg_extension WHERE extname = 'vector'; or by navigating to the Extensions tab in the Supabase project dashboard.

HNSW index with halfvec for high-dimensional vectors

For vectors with more than 2,000 dimensions, use the halfvec type to create indexes. Example: CREATE TABLE documents (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, content text, embedding vector(3072)); CREATE INDEX ON documents USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

HNSW algorithm components

HNSW combines two concepts: Hierarchical (H) - the algorithm operates over multiple layers; and Navigable Small World (NSW) - each vector is a node within a graph connected to several other nodes.

Skip lists in HNSW hierarchical structure

Skip lists are multi-layer linked lists where the bottom layer is a regular linked list connecting ordered elements, and each layer above removes some elements based on fixed probability, producing a sparser subsequence that skips over elements. This provides O(log n) average complexity for search and insertion/deletion.

Navigable Small World graph structure

A navigable small world (NSW) is a proximity graph that includes long-range connections between nodes, supporting the small world property where almost every node can be reached from any other node within a few hops. The navigable property refers to the ability to logarithmically scale the greedy search algorithm on the graph.

HNSW index definition and purpose

HNSW is an algorithm for approximate nearest neighbor search used to improve performance when querying high-dimensional vectors like embeddings.

HNSW index creation for Euclidean distance

To create an HNSW index for Euclidean L2 distance, use: create index on items using hnsw (column_name vector_l2_ops);

HNSW index creation for inner product

To create an HNSW index for negative inner product, use: create index on items using hnsw (column_name vector_ip_ops);

pgvector maximum vector dimensions by type

For pgvector versions 0.7.0 and above, vectors can have up to 2,000 dimensions, halfvec can have up to 4,000 dimensions, and bit can have up to 64,000 dimensions.

pgvector distance operators

pgvector supports three distance operators: '<->' for Euclidean distance using 'vector_l2_ops' operator class, '<#>' for negative inner product using 'vector_ip_ops' operator class, and '<=>' for cosine distance using 'vector_cosine_ops' operator class.

Give your agent this brain