How to Store Embeddings in a Vector Database - Learn Java Online, Learn Python Online, Learn Laravel Online, Learn PHP Online

If you want to store embeddings in a vector database, you need more than a generated vector. Your application also needs persistent storage, efficient nearest-neighbor search, metadata filtering, indexing, and a reliable update strategy. This tutorial explains how to store embeddings in a vector database with PostgreSQL and pgvector, then extend the storage layer for semantic search and RAG.

Creating embeddings is useful only when the vectors can be stored, indexed, and retrieved efficiently. A vector-enabled relational database lets application data, metadata, and embeddings live together, which can simplify search and content management.

Why Store Embeddings in a Vector Database?

Traditional SQL is excellent for exact filters such as category=’PHP’ or status=’published’. Vector retrieval answers a different question: which records have embeddings closest to this query vector? OpenAI’s current embeddings FAQ recommends a vector database for fast nearest-neighbor retrieval over many vectors. pgvector adds vector operations directly to PostgreSQL, making it convenient when your application already depends on relational data.

Create the Database

Create a PostgreSQL database and enable the vector extension. The extension is enabled with CREATE EXTENSION vector. Verify that your PostgreSQL installation supports pgvector before continuing. In a managed environment, use the provider’s extension installation process.

SQL Setup

CREATE DATABASE semantic_search;

-- connect to semantic_search
CREATE EXTENSION IF NOT EXISTS vector;

CREATE DATABASE semantic_search;

— connect to semantic_search

CREATE EXTENSION IF NOT EXISTS vector;

Design a Table to Store Embeddings in a Vector Database

A practical table should contain the original content, source information, metadata, and embedding. The vector dimension must match the embedding model configuration. OpenAI’s v3 embedding models support a dimensions parameter, so a developer can intentionally request a smaller vector, but the database schema must then match the selected dimension.

Code: Table

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    url TEXT,
    category TEXT,
    difficulty TEXT,
    embedding VECTOR(1536),
    status TEXT DEFAULT 'published',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE documents (

id BIGSERIAL PRIMARY KEY,

title TEXT NOT NULL,

content TEXT NOT NULL,

url TEXT,

category TEXT,

difficulty TEXT,

embedding VECTOR(1536),

status TEXT DEFAULT ‘published’,

created_at TIMESTAMPTZ DEFAULT NOW()

);

Connect Python to PostgreSQL

Use psycopg2 or another PostgreSQL driver. Store credentials in environment variables. The connection should be opened with a controlled lifecycle and closed after work is complete. In web applications, use a connection pool rather than creating a brand-new database connection for every request.

Generate and Insert Vectors

The indexing process should create an embedding for each clean document or chunk and insert it with its metadata. Use parameterized SQL rather than string concatenation. Parameterization protects the database from SQL injection and avoids quoting problems in document content.

Code: Insert

cursor.execute(
    """
    INSERT INTO documents
    (title, content, url, category, difficulty, embedding)
    VALUES (%s, %s, %s, %s, %s, %s)
    """,
    (title, content, url, category, difficulty, vector)
)

connection.commit()

cursor.execute(

“””

INSERT INTO documents

(title, content, url, category, difficulty, embedding)

VALUES (%s, %s, %s, %s, %s, %s)

“””,

(title, content, url, category, difficulty, vector)

)

connection.commit()

Search Stored Embeddings With Cosine Distance

pgvector provides distance operators. The <=> operator represents cosine distance. Ordering by this expression returns the nearest vectors. A similarity-style value can be calculated as 1 minus the distance. Use the distance for ranking and treat the resulting score as a relative retrieval signal rather than a universal probability.

Code: Search

SELECT id, title, url, category,
       1 - (embedding <=> %s::vector) AS similarity
FROM documents
WHERE status = 'published'
ORDER BY embedding <=> %s::vector
LIMIT 10;

SELECT id, title, url, category,

1 – (embedding <=> %s::vector) AS similarity

FROM documents

WHERE status = ‘published’

ORDER BY embedding <=> %s::vector

LIMIT 10;

Add an HNSW Index for Stored Embeddings

For small datasets, exact search can be sufficient. As the number of vectors grows, approximate nearest-neighbor indexes can reduce query latency. pgvector supports HNSW and IVFFlat. HNSW is a common starting point for cosine search. Benchmark before and after adding the index because indexes also consume storage and require maintenance.

Code: HNSW

CREATE INDEX documents_embedding_hnsw
ON documents
USING hnsw (embedding vector_cosine_ops);

CREATE INDEX documents_embedding_hnsw

ON documents

USING hnsw (embedding vector_cosine_ops);

Chunk Long Documents

Long tutorials should usually be divided into chunks. Store chunks in a separate table with a foreign key to the source document. Include chunk_index and section so search results can point to the relevant part of an article. Chunking also makes RAG context more focused because the language model receives the passage that answers the question instead of an entire page.

Code: Chunks

CREATE TABLE document_chunks (
    id BIGSERIAL PRIMARY KEY,
    document_id BIGINT REFERENCES documents(id) ON DELETE CASCADE,
    chunk_index INTEGER NOT NULL,
    section TEXT,
    chunk_text TEXT NOT NULL,
    embedding VECTOR(1536)
);

CREATE TABLE document_chunks (

id BIGSERIAL PRIMARY KEY,

document_id BIGINT REFERENCES documents(id) ON DELETE CASCADE,

chunk_index INTEGER NOT NULL,

section TEXT,

chunk_text TEXT NOT NULL,

embedding VECTOR(1536)

);

Metadata Filtering

One major advantage of PostgreSQL is combining vector retrieval with relational filters. A CodexJunction search could retrieve only published tutorials in the PHP category at Beginner difficulty. A private knowledge base could filter by tenant ID and permissions. Authorization filters must be part of the retrieval query so unauthorized text never reaches the LLM.

Code: Filtered Search

SELECT title, url, category,
       1 - (embedding <=> %s::vector) AS similarity
FROM document_chunks
WHERE category = %s
  AND status = 'published'
ORDER BY embedding <=> %s::vector
LIMIT 10;

SELECT title, url, category,

1 – (embedding <=> %s::vector) AS similarity

FROM document_chunks

WHERE category = %s

AND status = ‘published’

ORDER BY embedding <=> %s::vector

LIMIT 10;

Update and Delete Vectors

When a document changes, its vectors can become stale. Reindex changed chunks and remove old chunks that no longer exist. When content is unpublished, use a status filter or remove it depending on your retention requirements. Store a content version or source hash so the indexing pipeline can quickly determine whether a document actually changed.

Build an Indexing Pipeline

A production pipeline can run whenever a CMS page is created or updated. It should fetch the source, clean it, split it into chunks, generate embeddings in batches, upsert the new chunks, delete stale chunks, and mark the source as successfully indexed. Failed jobs should be recorded and retried. Keep indexing separate from user search so a slow ingestion job cannot block visitors.

Security

Vector search does not enforce authorization by itself. If private data is stored, every retrieval query must include access-control constraints. For multi-tenant applications, filter by tenant ID. For role-based documents, filter by permitted roles or access groups. Do not retrieve first and then ask an LLM whether the user is allowed to see the result.

Performance

Measure embedding latency and database latency independently. Batch ingestion requests, cache embeddings for unchanged text, and keep database connections pooled. Use HNSW or IVFFlat only when measurements show a need. Review query plans and monitor index size. For very large systems, evaluate whether PostgreSQL remains the best operational fit or whether a dedicated vector platform is justified.

Evaluation

Build a test set of queries and expected documents. Measure whether the relevant document appears in the top 1, top 3, or top 5. Test paraphrases and negative queries. Evaluate metadata filters separately from semantic relevance. A retrieval system can have excellent vector similarity and still be wrong if it returns stale or unauthorized documents.

Common Problems

Dimension mismatch usually means the database column and embedding configuration disagree. Empty results may mean vectors were never inserted or a status filter excludes them. Poor relevance can result from bad chunking or noisy source text. Slow search may require indexing or better database configuration. Duplicate results often mean the ingestion pipeline indexed the same page multiple times.

Next Step: Store Embeddings in a Vector Database for RAG

Once the vector store works, you can build RAG on top of it. RAG adds a generation step: retrieve the best chunks, put them into a controlled context, and ask a language model to answer from that context. The database therefore becomes the retrieval memory of the application.

Extended Implementation Guidance

A vector table should be designed as part of the content system, not as an isolated AI table. Keep a stable source document ID and a separate chunk ID. The source table can store title, canonical URL, category, publication status, and content version. The chunk table can store section, position, cleaned text, embedding model, vector, and content hash. This structure makes updates and deletions much safer than storing every piece of information in one unstructured row.

Use upserts for indexing. An indexer may be interrupted after writing some chunks, so running it again should produce the same final state rather than duplicates. A unique constraint on source ID plus chunk index or content hash can help. When a document is re-chunked, remove the old chunks that are no longer present. Otherwise obsolete sections can continue appearing in search results even after the page has changed.

Database authorization is especially important when vectors contain private information. If the application has tenants, teams, or roles, store the relevant access metadata with each chunk. Put the authorization predicate directly into the SQL retrieval query. This ensures unauthorized content is excluded before it can be returned to application code or sent to a language model. Never retrieve all matches and filter them after generation.

For performance, start with exact search and establish a baseline. Then test an approximate index such as HNSW. Compare recall and latency on the same evaluation queries. A fast search that misses the correct tutorial can be worse than a slightly slower search with better recall. Monitor index size, database memory, connection counts, and query latency. If your content grows rapidly, schedule index maintenance and document how to rebuild the index.

Consider backup and disaster recovery as part of vector storage. Rebuilding embeddings from source documents is possible, but it takes time and consumes API resources. A database backup can shorten recovery. Test restoration periodically and document the embedding model, dimensions, preprocessing rules, and indexing code used to create the vectors. This information is essential if you must rebuild the search system months later.

Finally, keep the storage layer independent from the search interface. The web application should call a retrieval function rather than constructing SQL throughout the UI code. This makes it possible to switch from PostgreSQL to another vector store later without redesigning the entire application. It also gives you one place to add filters, reranking, logging, and evaluation.

Further Practical Considerations

When you move from a tutorial prototype to a production feature, keep the architecture modular. Separate content ingestion, preprocessing, embedding generation, storage, retrieval, and the user interface. Each stage should have a clear input and output. This makes debugging easier because a failure can be isolated to one stage instead of being hidden inside a single large function. It also allows you to replace one component without rewriting the entire application.

Create a small evaluation dataset before making major changes. For each representative user question, record the expected result or source. Include both successful queries and queries that should return no useful result. After changing the embedding model, chunk size, database index, prompt, or ranking logic, run the same dataset again. Keep a baseline so you can tell whether a change actually improved the system. This is more reliable than judging quality from one or two examples.

Keep user experience in mind. A technically correct retrieval system can still be frustrating if results are difficult to scan. Return a clear title, a short excerpt, a useful category, and a direct source link. For tutorials, show difficulty and related learning steps. For a Q&A system, show the sources used to construct the answer. Good retrieval should reduce the number of searches a visitor needs to perform, not simply demonstrate that vectors are working.

Plan for failure. The embedding provider can be temporarily unavailable, a database can be slow, a page can fail during ingestion, or a user can submit an unsupported question. Use timeouts, bounded retries, logging, and graceful fallback responses. If the AI layer fails, a normal website search or tutorial index should remain available. If one document fails indexing, do not stop the entire indexing job. Put the failed item into a retry queue and continue processing other content.

Finally, document the system for the next developer. Record the embedding model, vector dimensions, chunking rules, database schema, index type, retrieval filters, evaluation queries, environment variables, and reindexing procedure. AI systems evolve quickly, so this documentation is valuable when a model or dependency changes. A well-documented retrieval pipeline is easier to maintain, cheaper to operate, and much safer to improve over time.

Migration and Maintenance Example

A useful maintenance pattern is to keep the source document and vector index loosely coupled. Give every source a stable ID and every chunk a deterministic ID derived from the source and chunk position. When a page changes, calculate a new content hash. If the hash is unchanged, do nothing. If it changes, generate new chunks and vectors, insert the new version, and then remove the old chunks. This makes indexing repeatable and avoids duplicate results.

For large collections, process indexing in jobs. A job can contain a source ID, content version, chunk count, and status. Store pending, processing, completed, and failed states. If a worker crashes, another worker can safely resume or retry the job. This operational structure becomes important when a website has thousands of tutorials and content is updated regularly.

Store Embeddings in a Vector Database: What the Storage Layer Contains

When you store embeddings in a vector database, each record should have enough information to make retrieval useful. A typical record contains the original text, a source or document ID, metadata such as category and status, the embedding vector, the embedding model, and a version or content hash. This makes it easier to update records without creating duplicates.

The vector is used for similarity retrieval, while relational fields handle normal application requirements. For example, a query can find semantically similar tutorials while returning only published content for a specific category. This combination is one of the main reasons PostgreSQL with pgvector can be practical for content-heavy applications.

Store Embeddings in a Vector Database With a Repeatable Pipeline

A reliable pipeline should fetch or receive the source document, clean the text, split long content into chunks, generate embeddings, and write the chunks and metadata to PostgreSQL. If the source changes, the pipeline should identify the changed version and update only the affected chunks.

The goal is to make store embeddings in a vector database an idempotent operation: running the same indexing job twice should leave the same final state instead of creating duplicate vectors. A unique source-and-chunk identifier or content hash can help enforce that behavior.

Store Embeddings in a Vector Database Securely

Security must be applied before retrieval results reach the application or language model. If your data contains tenant-specific or private content, include tenant IDs, roles, or access groups in the stored metadata and apply those conditions directly in the SQL query.

Do not retrieve a broad set of vectors and filter permissions later. When you store embeddings in a vector database, authorization metadata should travel with the chunk so the retrieval layer can exclude unauthorized content before it is used for RAG.

Store Embeddings in a Vector Database for Better RAG Retrieval

RAG depends on retrieving useful context. If chunks are too large, the result may contain unnecessary information. If they are too small, important context may be split apart. Store a chunk index and section name so the application can identify where each retrieved passage came from.

After you store embeddings in a vector database, the application can retrieve the highest-ranked chunks, place them into a controlled context, and ask a language model to answer from that context. The database becomes the retrieval memory of the RAG application.

Evaluate the Results After You Store Embeddings

A working vector query does not automatically mean the search quality is good. Build a small evaluation set containing representative questions and expected documents. Measure whether the correct document appears in the top 1, top 3, or top 5 results.

Test paraphrases, negative queries, metadata filters, stale content, and permission boundaries. A system can return highly similar vectors while still producing an incorrect answer if the source is outdated or unauthorized.

Frequently Asked Questions

What does it mean to store embeddings in a vector database?

It means saving numerical vector representations of content in a system that can efficiently compare those vectors and retrieve similar records. When you store embeddings in a vector database, you should also retain source text and metadata so results can be filtered, explained, updated, and connected back to the original content.

Why use PostgreSQL with pgvector?

PostgreSQL can keep normal relational fields and vector data together. pgvector adds vector operations and supports approximate nearest-neighbor indexes such as HNSW and IVFFlat. This can be convenient when an existing application already relies on PostgreSQL.

Should every document be stored as one embedding?

For long documents, the source recommends using chunks with their own embeddings. Chunk-level retrieval gives RAG a more focused passage and makes it easier to point users to a specific section of a document.

What happens when a document changes?

Re-embed the changed chunks, upsert the new records, and remove chunks that no longer exist. A source hash or content version helps determine whether the document actually changed and prevents stale vectors from remaining in search results.