Session 1: Installation, vector similarity search, HNSW indexing, execution-plan analysis, and performance benchmarking

Introduction

Artificial intelligence is changing how applications search and retrieve information. Traditional relational databases are excellent at storing structured data and finding records using SQL conditions, indexes, and exact matches. AI-powered applications introduce another requirement: finding information based on semantic similarity.

For example, an engineer searching for “Oracle database recovery after storage failure” might benefit from documentation about RMAN recovery, ASM disk group failures, or disaster recovery procedures—even if those documents do not contain the exact search phrase.

Vector embeddings make this type of search possible by representing information as numerical vectors. PostgreSQL can support vector storage and similarity search through the open-source pgvector extension.

In this first hands-on session, I explored how to install and verify pgvector, store vectors, perform similarity searches, create an HNSW index, inspect execution plans, and compare exact search with approximate nearest-neighbor search.

The objective was not simply to make a query work. It was to understand what PostgreSQL was doing and measure the results from a database administrator's perspective.

1. Lab environment

The practical exercises were performed in my PostgreSQL lab.

Component	Configuration
Operating system	RHEL 8.10
Database	PostgreSQL 16.15
Vector extension	pgvector 0.8.7
Database used	`salesdb`
Extension	`vector`
Benchmark dataset	10,000 synthetic vectors
Vector dimensions	3
ANN index	HNSW
Distance metric	Cosine distance

Important: The three-dimensional vectors used in this session are synthetic test data. They demonstrate database behavior and query performance; they are not embeddings generated by an AI model.

2. Installing and verifying pgvector

The first step was to verify the PostgreSQL installation and check whether the vector extension was available.

For a compatible PostgreSQL 16 installation using the PGDG RPM repository on RHEL 8, the package can be installed with:

sudo dnf install pgvector_16

I verified the installed package:

rpm -q pgvector_16

The installed version was:

pgvector_16-0.8.7-1PGDG.rhel8.10.x86_64

Check extension availability

After installing the package, I connected to the salesdb database and ran:

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

This confirms that PostgreSQL can find the extension files. An empty installed_version indicates that the extension is available but has not yet been enabled in that database.

Enable and verify the extension

I enabled pgvector in salesdb:

CREATE EXTENSION vector;

Then I verified the installed extension:

SELECT
    current_database(),
    extname,
    extversion
FROM pg_extension
WHERE extname = 'vector';

The output confirmed:

 current_database | extname | extversion
------------------+---------+-----------
 salesdb          | vector  | 0.8.7

This is an important distinction for PostgreSQL administrators: installing an operating-system package does not automatically enable the extension in every database. Extension availability and database-level activation are separate steps.

3. Understanding vector data and similarity

A vector is an ordered collection of numerical values.

For example:

[0.12, 0.45, 0.78]

This is a three-dimensional vector. Real embedding models may generate vectors with hundreds or thousands of dimensions, depending on the model.

A vector database stores these representations and calculates distances or similarity scores to retrieve related vectors.

Testing a vector operation

I started with a simple query:

SELECT
    '[1,2,3]'::vector AS test_vector,
    '[1,2,3]'::vector <=> '[1,2,3]'::vector
        AS cosine_distance;

The result was:

 test_vector | cosine_distance
-------------+----------------
 [1,2,3]     | 0

The cosine distance is zero because the vectors are identical.

Important pgvector operators

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

For cosine distance, smaller values indicate vectors pointing in more similar directions. For Euclidean distance, smaller values indicate that vectors are closer in straight-line distance.

The appropriate metric depends on the embedding model and application requirements. A database administrator should not select a metric solely because it is commonly used.

4. Creating a vector-enabled table

Next, I created a table for storing documents and their vectors.

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 table contains:

document_id: unique identifier for each document.
document_title: document title.
document_content: document text.
embedding: vector representation of the document.
created_at: timestamp for when the row was created.

The VECTOR(3) definition specifies three dimensions for this learning exercise.

I inserted three sample documents covering Oracle RMAN recovery, PostgreSQL replication, and Oracle ASM troubleshooting. Each document was assigned a synthetic vector.

These manually assigned vectors helped me understand vector storage and SQL operations before introducing a real embedding model.

5. Performing vector similarity searches

Once the table contained vectors, I executed a cosine-distance query.

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 3;

The results were:

Document	Cosine distance
Oracle RMAN Recovery	0.000000
Oracle ASM Troubleshooting	0.005219
PostgreSQL Replication	0.490828

The first document had a distance of zero because its vector exactly matched the query vector.

I also tested Euclidean distance:

SELECT
    document_title,
    embedding <-> '[0.12,0.45,0.78]'::vector
        AS euclidean_distance
FROM ai_documents
ORDER BY embedding <-> '[0.12,0.45,0.78]'::vector
LIMIT 3;

Both metrics produced the same ranking for these sample vectors. That is not guaranteed for all datasets because cosine distance and Euclidean distance measure different properties.

At this stage, I had verified that PostgreSQL could store vectors, calculate distances, and rank results using SQL.

6. Understanding HNSW indexing

For a small table, exact similarity search is often sufficient. As the number of vectors increases, calculating the distance between a query vector and every stored vector can become expensive.

Approximate nearest-neighbor (ANN) indexing reduces the search work by exploring a subset of candidates.

One widely used ANN technique is Hierarchical Navigable Small World (HNSW).

HNSW organizes vectors into a graph with multiple layers. The search process navigates through this graph to identify likely nearest neighbors without necessarily examining every vector.

In pgvector, HNSW supports approximate nearest-neighbor searches using compatible distance operators and index operator classes.

Creating the HNSW index

I created an HNSW index on the document table:

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

The index uses vector_cosine_ops, making it suitable for cosine-distance searches.

However, creating the index did not make PostgreSQL use it for the three-row table. PostgreSQL continued to choose a sequential scan because scanning such a small table was inexpensive.

This demonstrated an important DBA principle:

An index being available does not mean the query planner should use it.

The correct execution plan depends on table size, data distribution, query structure, and the estimated cost of available access paths.

7. Creating a larger benchmark dataset

To examine HNSW behavior more meaningfully, I created a separate benchmark table containing 10,000 synthetic vectors.

CREATE TABLE ai_vectors_bench (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    embedding VECTOR(3) NOT NULL
);

I generated the vectors with PostgreSQL:

INSERT INTO ai_vectors_bench (embedding)
SELECT
    format(
        '[%s,%s,%s]',
        round((random() * 2 - 1)::numeric, 6),
        round((random() * 2 - 1)::numeric, 6),
        round((random() * 2 - 1)::numeric, 6)
    )::vector
FROM generate_series(1, 10000);

The generate_series() function produced 10,000 rows, and random() generated values for each vector component.

I verified the inserted data:

SELECT
    COUNT(*) AS total_vectors,
    COUNT(embedding) AS nonnull_vectors
FROM ai_vectors_bench;

Both counts returned 10,000.

The benchmark table was separate from the original document table, allowing the two experiments to remain independent.

8. Establishing the exact-search baseline

Before creating an HNSW index on the benchmark table, I measured exact nearest-neighbor search using:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    id,
    embedding <=> '[0.12,0.45,0.78]'::vector
        AS cosine_distance
FROM ai_vectors_bench
ORDER BY embedding <=> '[0.12,0.45,0.78]'::vector
LIMIT 10;

The execution plan showed:

Seq Scan on ai_vectors_bench
Sort Method: top-N heapsort
Execution Time: 29.751 ms

The sequential scan examined all 10,000 vectors. PostgreSQL then used a top-N heapsort to identify the ten smallest distances.

The reported execution time for this run was 29.751 milliseconds.

The plan also reported Buffers: shared hit=74, indicating 74 shared-buffer hits. These are buffer-cache observations, not a direct measurement of all physical disk activity across the system.

This baseline gave me a reference point for the HNSW experiment.

9. Building the HNSW index on the benchmark table

I created the index:

CREATE INDEX ai_vectors_bench_hnsw_idx
ON ai_vectors_bench
USING hnsw (embedding vector_cosine_ops);

I verified the index status using the PostgreSQL catalog:

SELECT
    indexrelid::regclass AS index_name,
    indisvalid,
    indisready
FROM pg_index
WHERE indexrelid =
    'ai_vectors_bench_hnsw_idx'::regclass;

The index was valid and ready.

I then repeated the same nearest-neighbor query with EXPLAIN (ANALYZE, BUFFERS).

The execution plan showed:

Index Scan using ai_vectors_bench_hnsw_idx
Order By: (embedding <=> '[0.12,0.45,0.78]'::vector)
Execution Time: 0.716 ms

PostgreSQL now selected the HNSW index for the query.

Comparing the measured results

Metric	Exact search	HNSW search
Execution time	29.751 ms	0.716 ms
Access method	Sequential scan	HNSW index scan
Vectors in table	10,000	10,000
Search results requested	10	10

The observed speedup is approximately:

[
\text{Speedup}=
\frac{29.751}{0.716}
\approx 41.6
]

In this particular comparison, HNSW completed the query approximately 41.6 times faster than the recorded exact-search run.

This is an encouraging result from the lab, but it should not be interpreted as a universal speedup. The measurements came from individual runs, and query timing can vary with caching, system load, hardware, and other factors.

10. Measuring recall@10

Speed alone does not establish the quality of an approximate search.

HNSW can return a set of neighbors that differs from the exact nearest neighbors. Therefore, I compared the IDs returned by exact search and approximate search.

Recall@10 is calculated as:

\frac{\text{Exact top-10 results found in ANN top-10}}
{10}
\times 100
]

In my first test, the results were:

Metric	Result
Exact result count	10
ANN result count	10
Overlapping IDs	10
Recall@10	100%

All ten approximate-search results overlapped with the exact-search results for the tested query.

This is a useful initial validation, but it does not prove that HNSW will achieve 100% recall for other queries. The experiment used one query vector and a small, synthetic, three-dimensional dataset.

A more rigorous evaluation should use many representative query vectors, realistic embedding dimensions, repeated timing measurements, and a defined recall target.

11. What I learned from this session

This first session reinforced several practical lessons.

Lesson 1: Installing an extension is only the beginning

The extension must be available to PostgreSQL and enabled in the target database. Successful package installation alone is insufficient.

Lesson 2: Understand the distance operator

Cosine distance and Euclidean distance measure different properties. The metric should be selected based on the embedding model and application requirements.

Lesson 3: The query planner makes cost-based decisions

PostgreSQL used a sequential scan for the small document table, then selected HNSW for the larger benchmark table. The existence of an index does not guarantee its use.

Lesson 4: Measure before optimizing

EXPLAIN (ANALYZE, BUFFERS) helped me understand the scan method, sort operation, buffer activity, and execution time.

Lesson 5: Measure quality as well as speed

A fast approximate search is not sufficient if it misses important results. Recall and application-level relevance must be evaluated alongside latency.

Lesson 6: Synthetic vectors have limitations

The benchmark demonstrated database mechanics and performance behavior, not the semantic quality of an AI-powered search system. Real embeddings are the next step.

12. What comes next?

In Session 2, I plan to explore HNSW query-time tuning, especially the hnsw.ef_search parameter.

The goal will be to investigate the trade-off between recall and query latency, compare multiple parameter settings, and develop a more reliable benchmarking method.

Further sessions will move toward real embeddings and practical AI retrieval workflows, while considering the database administrator's responsibilities around indexing, monitoring, backup, recovery, replication, and security.

Conclusion

This first hands-on session took me from pgvector installation to a working vector-search benchmark in PostgreSQL 16.

I verified the extension, created vector-enabled tables, explored cosine and Euclidean distance, built HNSW indexes, analyzed query plans, and compared exact search with approximate nearest-neighbor search.

The initial benchmark showed a substantial reduction in measured query time, with 100% recall@10 for the query tested. More representative workloads are needed before drawing production conclusions.

For database administrators, vector search adds new capabilities but does not eliminate the fundamentals of good database engineering. Understanding execution plans, measuring performance, validating results, and planning for reliability remain essential.

The journey continues: learning vector search one query, one execution plan, and one benchmark at a time.

Muthukumar Cloud Oracle DBA & Exadata Knowledge Hub

Search This Blog

PostgreSQL 16 + pgvector: My First Hands-On AI Vector Database Lab