Understanding embeddings, similarity search, HNSW indexes, and real-world database applications

Artificial intelligence is changing how applications search, retrieve, and understand information. Traditional relational databases excel at storing structured data and retrieving records using SQL conditions, indexes, and exact matches. However, AI applications often need to find information based on meaning, not just matching words.

For example, consider a user searching for:

“How do I recover an Oracle database after a disk failure?”

A traditional keyword search might look for documents containing the words Oracle, database, recover, or disk failure. But useful information could also be stored in documents titled “RMAN Restore and Recovery Procedures,” “ASM Disk Group Failure Troubleshooting,” or “Data Guard Disaster Recovery.”

These documents may not contain the exact search phrase, but their content is semantically related.

This is where vector embeddings, vector similarity search, and vector databases become important.

In this article, we will explore the fundamentals of vector databases, understand how embeddings and similarity metrics work, examine Oracle AI Vector Search and PostgreSQL with pgvector, and discuss the operational considerations DBAs should understand before implementing vector search in production.

1. What Is a Vector Database?

A vector database stores and searches numerical representations of information called vectors.

A vector is an ordered collection of numerical values. In AI applications, an embedding model converts content such as text, documents, images, or other supported inputs into vectors that represent useful features of that content.

For example, a simplified embedding might look like this:

"Oracle database backup"
        |
        v
[0.12, 0.45, -0.18, 0.73, ...]

Real embedding models often generate hundreds or thousands of dimensions. The example above is illustrative and does not represent an actual model output.

Texts with related meanings may produce vectors that are close to one another under an appropriate similarity metric. A vector search engine can use this property to retrieve semantically related information.

A vector database is not necessarily a completely separate database product. Vector-search capabilities can be built into an existing database, allowing applications to store embeddings alongside business data and retrieve results using familiar database mechanisms.

Examples include:

Oracle Database AI Vector Search: Native vector capabilities in supported Oracle Database releases.
PostgreSQL with pgvector: An extension that adds vector data types, similarity operators, and vector indexes to PostgreSQL.
Dedicated vector databases: Systems designed specifically around vector storage and retrieval.

The appropriate choice depends on the existing technology stack, application requirements, scale, operational skills, and performance objectives.

2. What Are Embeddings?

An embedding is a numerical representation generated by an embedding model.

Suppose an organization has three database troubleshooting documents:

Oracle RMAN backup and recovery.
PostgreSQL streaming replication troubleshooting.
Oracle ASM disk group recovery.

An embedding model converts each document, or suitable chunks of each document, into a vector.

Conceptually:

Document 1 -> [0.15, 0.72, -0.11, ...]
Document 2 -> [0.84, -0.23, 0.56, ...]
Document 3 -> [0.19, 0.69, -0.08, ...]

These values are illustrative. The embedding model determines the actual vector values and dimensions.

When a user submits a question, the application sends that question to a compatible embedding model. The resulting query vector is compared with stored document vectors. The database then retrieves the closest matches according to the chosen distance or similarity calculation.

Important distinction: A database does not create meaning by itself

A vector database stores vectors and searches them efficiently. The embedding model generates the numerical representations.

The application or ingestion pipeline is usually responsible for:

Extracting text from documents.
Cleaning and preparing content.
Splitting long documents into chunks.
Calling the embedding model.
Storing vectors and associated metadata.
Converting incoming questions into query vectors.
Retrieving relevant results and passing them to the application.

This separation matters when designing a production AI system.

3. How Does Vector Search Work?

A typical semantic-search workflow looks like this:

                DOCUMENT INGESTION
                        |
                        v
              Extract and clean text
                        |
                        v
                 Split into chunks
                        |
                        v
                Generate embeddings
                        |
                        v
             Store text, metadata, vectors
                        |
                        v
                 Vector index
                        |
                        |
                        v
USER QUESTION -> Generate query embedding
                        |
                        v
              Search nearest vectors
                        |
                        v
             Retrieve relevant documents
                        |
                        v
             AI application / LLM response

The retrieval component can supply relevant documents to a large language model (LLM). This architecture is commonly used in Retrieval-Augmented Generation (RAG).

In RAG, the language model receives relevant retrieved content as context before generating an answer. Retrieval can improve grounding, but it does not guarantee that the answer will be correct. Source quality, retrieval quality, permissions, and the model's behavior still matter.

4. Understanding Vector Similarity Metrics

Before creating indexes, DBAs should understand how vectors are compared.

Three common approaches are cosine similarity, Euclidean distance, and inner product.

4.1 Cosine similarity

Cosine similarity measures the angle between two vectors.

For vectors (A) and (B):

\frac{A \cdot B}{|A||B|}
]

The result is commonly interpreted as follows for nonzero vectors:

Close to 1: similar direction.
Close to 0: approximately orthogonal.
Close to -1: opposite direction.

These interpretations are mathematical. Whether a score represents meaningful semantic similarity depends on the embedding model and the application.

4.2 Cosine distance

Cosine distance is often defined as:

1-\operatorname{cosine\ similarity}(A,B)
]

A smaller cosine distance indicates that the vectors point in more similar directions.

For example, using the vectors [2,3] and [3,4]:

Cosine similarity ≈ 0.99846035
Cosine distance    ≈ 0.00153965

These two-dimensional vectors are used only to demonstrate the calculation. They are not realistic semantic embeddings.

4.3 Euclidean distance

Euclidean distance measures the straight-line distance between two vectors:

[
d(A,B)=\sqrt{\sum_{i=1}^{n}(A_i-B_i)^2}
]

For [2,3] and [3,4], the Euclidean distance is approximately 1.41421356.

4.4 Inner product

The inner product is calculated as:

[
A \cdot B=\sum_{i=1}^{n}A_iB_i
]

Depending on the embedding model and how the vectors are normalized, inner product can be an appropriate retrieval metric.

DBA takeaway: Do not select a metric simply because it is popular. Use the metric recommended for the embedding model, and evaluate retrieval quality using representative queries and real application data.

5. Oracle AI Vector Search

Oracle Database provides native AI Vector Search capabilities in supported releases, including Oracle Database 23ai and subsequent releases with the relevant functionality.

These capabilities allow organizations to store vector embeddings and perform similarity searches within Oracle Database, alongside relational data and existing SQL-based access patterns.

This can be useful when an organization already relies on Oracle Database for transactional systems, enterprise applications, or operational data.

Key capabilities

Depending on the database release and configuration, Oracle AI Vector Search supports capabilities such as:

A native VECTOR data type.
Vector distance calculations and similarity search.
Approximate nearest-neighbor indexing, including HNSW and IVF in supported configurations.
SQL queries that combine vector search with relational filters.
Integration of vector retrieval with enterprise data stored in Oracle Database.

Always verify the exact capabilities and syntax for the Oracle Database release you operate. Vector-search functionality and supported options can differ between releases and deployment environments.

Example Oracle table

The following example illustrates a simple table containing document metadata, text, and embeddings. The vector dimension must match the embedding model used by the application.

CREATE TABLE ai_documents (
    document_id NUMBER GENERATED BY DEFAULT AS IDENTITY
        PRIMARY KEY,
    document_title VARCHAR2(200),
    document_content CLOB,
    embedding VECTOR(3, FLOAT32)
);

The three-dimensional vector is deliberately small for demonstration. A real application should use the dimension and numeric representation appropriate for its chosen embedding model.

Example Oracle similarity query

SELECT
    document_id,
    document_title,
    VECTOR_DISTANCE(
        embedding,
        :query_vector,
        COSINE
    ) AS cosine_distance
FROM ai_documents
WHERE embedding IS NOT NULL
ORDER BY cosine_distance
FETCH FIRST 5 ROWS ONLY;

Here, :query_vector is a bind variable supplied by the application. It must contain a vector with the appropriate dimensions.

The query orders results by increasing cosine distance, returning the five nearest results among the rows that satisfy the filter.

For production use, test the query plan, index compatibility, filtering behavior, and performance against the exact Oracle version and data distribution. An index does not automatically guarantee that every query will use it.

Why Oracle DBAs should care

Oracle DBAs can apply existing skills in:

SQL tuning and execution-plan analysis.
Index management.
Memory and storage capacity planning.
Availability, backup, and recovery.
Security, access control, and auditing.
Monitoring and performance troubleshooting.

Vector search introduces additional considerations, especially embedding dimensions, index build costs, recall, query latency, and the lifecycle of stored embeddings.

6. PostgreSQL with pgvector

PostgreSQL is a powerful open-source relational database. The pgvector extension adds vector storage, similarity operations, and indexes for nearest-neighbor search.

It is particularly attractive for teams that want to build AI-powered applications while continuing to use PostgreSQL's SQL, transactions, access controls, and relational data model.

Important pgvector features

The extension supports:

A vector data type.
Exact nearest-neighbor search.
Approximate nearest-neighbor search using HNSW and IVFFlat indexes.
Cosine distance, Euclidean distance, and inner-product operations.
Vector queries combined with ordinary SQL filters and joins.

The available operators and index options depend on the installed pgvector version.

Step 1: Install pgvector

For a PostgreSQL 16 installation using a compatible PostgreSQL Global Development Group (PGDG) RPM repository on RHEL 8, the package is commonly named:

sudo dnf install pgvector_16

The package name depends on the PostgreSQL major version, operating system, and repository configuration.

Check the installed package:

rpm -q pgvector_16

Check which extensions PostgreSQL can find:

SELECT
    name,
    default_version,
    installed_version
FROM pg_available_extensions
WHERE name = 'vector';

An empty installed_version means the extension is available but has not yet been enabled in the current database.

Step 2: Enable the extension

Connect to the target database using an account with the required privileges:

CREATE EXTENSION vector;

Verify the extension:

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'vector';

Extensions are enabled per database. Installing the operating-system package does not automatically enable pgvector in every database.

For a production deployment, verify package compatibility, backups, change approval, and availability of the extension files on every PostgreSQL node where the extension must be loaded.

Step 3: Create a document table

CREATE TABLE ai_documents (
    document_id BIGINT GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    document_title TEXT NOT NULL,
    document_content TEXT NOT NULL,
    embedding VECTOR(3),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

The example uses three dimensions to keep the demonstration simple. Replace VECTOR(3) with the actual dimension produced by your embedding model.

Step 4: Insert sample vectors

INSERT INTO ai_documents (
    document_title,
    document_content,
    embedding
)
VALUES
(
    'Oracle RMAN Recovery',
    'Steps to restore and recover an Oracle database using RMAN.',
    '[0.12, 0.45, 0.78]'
),
(
    'PostgreSQL Replication',
    'Troubleshooting physical streaming replication in PostgreSQL.',
    '[0.82, 0.16, 0.31]'
),
(
    'Oracle ASM Troubleshooting',
    'Investigating ASM disk group and storage issues.',
    '[0.18, 0.49, 0.72]'
);

These vectors are synthetic examples for testing SQL. They are not embeddings generated by an AI model and should not be used to evaluate semantic-search quality.

Step 5: Run a similarity search

SELECT
    document_id,
    document_title,
    embedding <=> '[0.12, 0.45, 0.78]'::vector
        AS cosine_distance
FROM ai_documents
WHERE embedding IS NOT NULL
ORDER BY embedding <=> '[0.12, 0.45, 0.78]'::vector
LIMIT 5;

The operator <=> calculates cosine distance. The query retrieves the rows whose vectors are closest to the supplied query vector.

Other important pgvector operators include:

Operator	Meaning
`<->`	Euclidean (L2) distance
`<=>`	Cosine distance
`<#>`	Negative inner product

For <#>, the result is the negative inner product rather than the ordinary positive inner product. This allows ascending ordering to work with inner-product retrieval.

Important: A query vector must have the same dimensionality as the vectors stored in the column. In a real application, the query vector should come from the same compatible embedding model and configuration used to create the stored embeddings.

7. Understanding HNSW Indexes

As the number of stored vectors grows, an exact search may require calculating the distance between the query vector and a large number of stored vectors.

For small datasets, this can be perfectly reasonable. For larger datasets, approximate nearest-neighbor (ANN) indexing can reduce search time by exploring a subset of the possible candidates.

One popular ANN approach is Hierarchical Navigable Small World (HNSW).

HNSW organizes vectors into a graph with multiple layers. Higher layers help navigate larger distances through the graph, while lower layers provide more detailed local exploration.

Instead of comparing every stored vector with the query, the algorithm navigates the graph to identify likely nearest neighbors.

Create an HNSW index in PostgreSQL

For cosine-distance searches:

CREATE INDEX ai_documents_embedding_hnsw_idx
ON ai_documents
USING hnsw (embedding vector_cosine_ops);

The operator class vector_cosine_ops makes the index suitable for cosine-distance queries.

For Euclidean distance, use vector_l2_ops. For inner-product searches, use vector_ip_ops.

Understanding HNSW parameters

Two important index-build parameters are m and ef_construction.

m controls the graph connectivity, including the maximum number of connections maintained for each node according to the implementation's rules.
ef_construction controls the breadth of candidate exploration during index construction.

A higher setting can improve index quality but may increase index-build time, memory requirements, and index size.

At query time, hnsw.ef_search controls the size of the dynamic candidate list considered during search. Increasing it can improve recall, usually at the cost of additional query work and latency.

For example:

SET hnsw.ef_search = 100;

This setting applies to the current database session unless configured differently. Start with measured defaults, then tune using representative queries and a workload-specific recall and latency benchmark.

Do not assume that a higher parameter value is always better. Measure both result quality and resource consumption.

8. Exact Search vs Approximate Search

Understanding this distinction is essential before putting vector search into production.

Exact nearest-neighbor search evaluates the distance against every eligible candidate. It can be more expensive for large datasets but provides a useful reference for measuring result quality.

Approximate nearest-neighbor search uses an index to avoid examining every candidate. It can reduce latency substantially, but may not return the exact nearest neighbors.

A useful evaluation process is:

Run exact search on a representative sample of queries.
Run approximate search using the proposed index and settings.
Compare the results.
Measure recall, latency, CPU, memory, and index size.
Repeat with realistic filters and concurrent workloads.

For example, if exact search returns ten reference results and ANN search returns eight of those same results, recall at ten is 0.8, assuming ten results are returned by ANN as well.

High recall does not necessarily mean that retrieved documents are useful to end users. Application-level relevance should also be evaluated.

9. Oracle vs PostgreSQL with pgvector

Area	Oracle AI Vector Search	PostgreSQL with pgvector
Vector storage	Native vector type in supported releases	`vector` type provided by extension
Search interface	SQL with Oracle vector functions and operators	SQL with pgvector operators
ANN indexes	HNSW and IVF in supported configurations	HNSW and IVFFlat
Relational integration	Vector retrieval alongside Oracle data	Vector retrieval alongside PostgreSQL data
Administration	Oracle-specific tools and operational practices	PostgreSQL and extension administration
Licensing and cost	Depends on Oracle edition, services, and deployment	PostgreSQL and pgvector are open source; infrastructure and operations still cost money
Best fit	Organizations invested in Oracle and its ecosystem	Teams using PostgreSQL or seeking an open-source implementation

There is no universally superior option. The decision should be based on actual requirements, current database versions, expected data volume, security, operational expertise, cost, and measured performance.

10. Real-World Use Cases for Database Engineers

AI-powered database troubleshooting

A DBA knowledge base can contain incident reports, alert explanations, runbooks, and recovery procedures.

An engineer searching for “replication lag after network interruption” might retrieve documents about WAL retention, standby replay delays, connection interruptions, or replication-slot issues.

The system should show source documents and allow the engineer to verify the recommended action rather than blindly executing generated commands.

Enterprise document search

Organizations can index technical documentation, policies, internal FAQs, product manuals, and support tickets.

Semantic retrieval helps users discover related information even when they do not know the exact document title or terminology.

Retrieval-Augmented Generation (RAG)

A RAG pipeline retrieves relevant document chunks and supplies them to an LLM as context.

A production RAG implementation should address document freshness, access permissions, prompt injection in retrieved content, source attribution, and evaluation of answer quality.

Recommendation systems

Embeddings can represent products, articles, or other items. A recommendation service can retrieve items whose vectors are close to a user's interests or to an item the user has selected.

The embedding model, business rules, and feedback data determine how useful the recommendations are.

11. Production Considerations for DBAs

Vector search adds operational requirements that should be included in database design and capacity planning.

Storage and memory

Vector storage grows with the number of vectors and their dimensions. Indexes introduce additional storage and memory overhead.

A rough estimate for raw vector payload is:

[
\text{Raw bytes} \approx
N \times D \times B
]

Where:

(N) is the number of vectors.
(D) is the number of dimensions.
(B) is the bytes per stored component.

For example, one million vectors with 1,536 dimensions stored as four-byte floating-point values require approximately 6.144 billion bytes, or 6.144 GB in decimal units, for vector payload alone.

This excludes row headers, text, metadata, indexes, WAL, replicas, backups, and other storage overhead. Actual requirements should be measured.

Index creation and maintenance

HNSW index construction can consume significant memory and CPU. Large index builds should be tested with production-like data volumes and planned around workload requirements.

DBAs should monitor:

Index-build duration and resource use.
Index size and growth.
Query latency and recall.
CPU, memory, I/O, and cache behavior.
Vacuum and table maintenance requirements where applicable.
Backup duration and restore behavior.

Replication and disaster recovery

Vector data stored in ordinary database tables generally participates in the database's relevant backup and replication mechanisms, but the extension's binaries and compatible versions must also be available where needed.

For PostgreSQL physical replication, install compatible pgvector packages on the primary and standby systems before relying on vector functionality after failover. Test extension creation, recovery, upgrades, and promotion procedures.

Do not treat a successful package installation on the primary as proof that the standby is ready.

Security

Embeddings should not automatically be treated as harmless data. They can represent information derived from confidential documents.

Apply database permissions, row-level access controls where appropriate, encryption, auditing, and application-level authorization. Ensure that vector retrieval cannot expose documents the requesting user is not authorized to see.

Monitoring and performance tuning

Measure end-to-end latency rather than database execution time alone. Embedding generation, network transfer, retrieval, reranking, and LLM inference can all contribute to response time.

Use representative data and queries. Track query latency, recall, throughput, resource use, and user-perceived relevance.

12. A Practical Learning Roadmap for DBAs

For a database administrator starting with vector search, the following sequence builds useful skills progressively.

Phase 1 — Fundamentals

Understand embeddings and vector dimensions.
Calculate cosine similarity and distance.
Learn Euclidean distance and inner product.
Understand the distinction between similarity scores and distances.

Phase 2 — PostgreSQL and pgvector

Install a version-compatible pgvector package.
Enable the extension in a test database.
Create a vector column and insert synthetic vectors.
Run exact similarity queries.
Compare results using the available distance operators.

Phase 3 — Indexing

Create an HNSW index.
Compare exact and approximate search.
Tune m, ef_construction, and hnsw.ef_search.
Measure recall, latency, index size, and resource consumption.

Phase 4 — Application integration

Generate actual embeddings using a suitable embedding model.
Store documents, embeddings, and metadata.
Build a query-embedding workflow.
Add appropriate SQL filters and source attribution.

Phase 5 — Production readiness

Test backup and recovery.
Validate standby readiness and failover behavior.
Benchmark concurrent workloads.
Establish monitoring and alerting.
Review access control and data lifecycle requirements.

The goal is not simply to make a vector query return results. It is to build a solution that is measurable, secure, recoverable, and maintainable.

Conclusion

Vector databases bring semantic search into modern data platforms. They allow applications to retrieve related information using embeddings instead of depending exclusively on exact keyword matching.

Oracle AI Vector Search provides native vector-search capabilities for supported Oracle Database releases. PostgreSQL with pgvector offers an open-source approach that combines vector operations with PostgreSQL's relational database capabilities.

For DBAs, the next step is to understand how embeddings, distance metrics, indexes, and retrieval quality interact with familiar responsibilities such as performance tuning, backup and recovery, replication, security, and capacity planning.

As AI applications become more common, database professionals who understand both traditional database administration and vector-search operations will be better equipped to design and maintain reliable AI-enabled data systems.

What would you choose for an enterprise RAG application: Oracle AI Vector Search or PostgreSQL with pgvector? Share your experience and thoughts.

AI Vector Databases: Oracle AI Vector Search vs PostgreSQL pgvector