Introduction
If you are using vector data in PostgreSQL on Cloud SQL or AlloyDB, you probably use some benchmark queries or functions to check your vector search performance. From time to time, I see people performing speed benchmarks or quality testing using generated, fully synthetic data. The main goal, after all, is to test, for example, performance with a particular data type and get some comparable numbers. Does it really matter what data I have in my vector column?
It really depends, and things can indeed go very wrong when you try to evaluate the index’s performance. I use synthetic data all the time for my tests. But it depends on what I am testing. In some cases, real vs. synthetic data might have a significant impact on the results. Let me show you how it can change the outcome of some tests for the vector data type in PostgreSQL.
Two datasets
Some tests can be done using fully synthetic data. For example, if you test inserts, updates, or deletes for vectors, then synthetic data works for you. In such cases, I usually create a fully synthetic dataset of totally random, normalized number arrays. Here is an example of a simple function and how to generate the dataset.
-- Create a function
CREATE OR REPLACE FUNCTION generate_random_vector(dimensions integer)
RETURNS vector
LANGUAGE sql
AS $$
SELECT array_agg(random()*2-1)::vector
FROM generate_series(1, dimensions);
$$;
-- Creating the test table with vector data type 768 dimensions
CREATE TABLE tvectr768(
id BIGINT PRIMARY KEY,
d VECTOR(768)
);
-- Populate the table with 1M or fows
INSERT INTO tvectr768 (id, d)
SELECT
i AS id,
-- This function is now called for each row
generate_random_vector(768) AS d
FROM
generate_series(1, 1000000) AS i;
In the end, we have a table with 1 million vectors. Each vector dimension value is between -1 and 1. So, the vector is normalized and shows more or less the same behaviour for the DML operations as normally generated embedding for a piece of content.
But when I test recall quality or performance for vector search, I use one of the publicly available datasets with vectors created based on real data. You can find multiple datasets for embeddings on Hugging Face or other sources. For example, you can check the Cohere Labs datasets here: https://huggingface.co/CohereLabs/datasets.
Here are my 1M rows with the same dimensions and structure, but all the vectors in those rows are based on real data.
pgdata=# \d tvreal768
Table "public.tvreal768"
Column | Type | Collation | Nullable | Default
--------+-------------+-----------+----------+---------
id | bigint | | |
d | vector(768) | | |
pgdata=#
Both tables have the same size and structure.
pgdata=# SELECT
pgdata-# relname AS table_name,
pgdata-# pg_size_pretty(pg_table_size(relid)) AS table_size
pgdata-# FROM
pgdata-# pg_catalog.pg_statio_user_tables
pgdata-# WHERE
pgdata-# relname IN ('tvectr768', 'tvreal768');
table_name | table_size
------------+------------
tvectr768 | 4008 MB
tvreal768 | 4008 MB
If you go a bit deeper, you can see that both tables are about 57 MB in heap size, plus 3950 MB in TOAST where the actual vector data is stored. It makes perfect sense, since the d column with vector(768) is larger than the default 2k threshold and is stored entirely in the TOAST segment.
pgdata-# relname AS table_name,
pgdata-# pg_size_pretty(pg_relation_size(oid)) AS heap_size,
pgdata-# pg_size_pretty(pg_table_size(oid) - pg_relation_size(oid)) AS toast_size
pgdata-# FROM pg_class
pgdata-# WHERE relname = ANY ('{"tvectr768" ,"tvreal768"}')
pgdata-# AND relkind = 'r';
table_name | heap_size | toast_size
------------+-----------+------------
tvectr768 | 57 MB | 3950 MB
tvreal768 | 57 MB | 3950 MB
Let us do some performance tests and compare the results.
Building Vector Index
For my tests, I was using Postgres 18 on a Debian VM in Google Cloud, and AlloyDB Omni for ScaNN index tests on a VM of the same size. We are mostly interested in the difference between “real” and synthetic vector values, but this varies depending on the index. The first test is to build an IVFFLAT index on our vectors.
Here is an example for the IVFFLAT index on synthetic vector data.
drop index if exists tvectr768_d_idx;
CREATE INDEX ON tvectr768 USING ivfflat (d vector_cosine_ops) WITH (lists=1000);
You can see I was using default parameters, specifying only the number of lists. According to my tests, it takes about 271,068 ms to build the index on the synthetic tvectr768 table. And when it is built, the size of the index is roughly equal to the size of all the vectors (3912 MB), which makes total sense.
When we run the same for the tvreal768 table with real vectors, we get similar results: 271,788 ms. So far, there is no difference. And it is probably expected if we think about how the IVFFLAT index is being built with splitting to buckets according to the number of lists and then simply moving each vector to one or another bucket.
What about the HNSW index? Here is an example of a query used to build the HNSW index.
drop index if exists tvectr768_d_idx;
CREATE INDEX ON tvectr768 USING hnsw (d vector_cosine_ops);
And here we start to see the difference in performance. The HNSW is a graph-based index and it is much harder to find node connections for a totally random array of numbers. You can play with different build parameters for the index and see how it can impact the results.
For the building with default parameters it took on average 6601789 ms for synthetic data and 4174196 ms for the “real” vectors. That’s 1.5 faster for the real data and it is significant.
So, why is it happening? How does the randomness in the numbers in the vector impact the performance? It probably deserves a separate article but I will try to give a short explanation. The HNSW builds its graph incrementally. When it adds another vector to the index it has to search for the right place in the graph. It means to find the closest neighbors to connect the new vector to. HNSW relies on the assumption that your data contains some underlying structure and it means clusters, patterns, and dense neighborhoods. The embedding vectors have but the random vector most likely not. Because of that the build process has to evaluate more candidates to find neighbors. That process is mathematically expensive for distances like cosine distance and takes time. That’s a short explanation but I hope it helps to understand to some extent the reasons behind the difference in built time.
With the ScaNN index we have another case of striking difference between building time for index with fully synthetic vs “real” dataset. I was using the basic recommended parameters for the ScaNN index according to the guide for AlloyDB Omni.
BEGIN;
SET LOCAL scann.num_leaves_to_search = 1;
SET LOCAL scann.pre_reordering_num_neighbors=50;
drop index if exists tvreal768_d_idx;
CREATE INDEX tvreal768_d_idx ON tvreal768 USING scann (d cosine) WITH (num_leaves = 1000);
END;
It took 237094 ms to build the index on the synthetic data vs only 62673 ms on the “real” dataset. And the size of indexes was quite different because AlloyDB ScaNN was using internal index optimization for the real dataset. We have 1562 MB for the synthetic data index vs 275 MB for the “real” one.
So, when you start to evaluate performance in building indexes then you probably should use proper data or you can get misleading results. I would recommend using vectors built on your actual data.
Vector Search Performance
Now that we have our ANN indexes built, we can evaluate how quickly you can find the top 5 similar vectors using the HNSW index. I am using a procedure that takes 100 samples from the table and uses some of those samples as predicates to find the 5 most similar vectors. I repeated this 11 times, discarding the first execution. This gives me the throughput in Queries Per Second (QPS) and the average execution time per query, along with the max and min times for all executions. You can check the procedure benchmark_vector_search in the Appendix chapter and the end of the article or download it from GitHub.
Here is a sample output from the procedure.
NOTICE: ------------------------------------------------------------------------
NOTICE: Benchmark finished successfully.
NOTICE: Total queries executed: 1000 (excluding warmup)
NOTICE: Total execution time: 2.145 seconds (excluding warmup)
NOTICE: Throughput (overall): 466.1 QPS (excluding warmup)
NOTICE: ------------------------------------------------------------------------
NOTICE: Latency Stats (excluding warmup):
NOTICE: Average: 2.125 ms
NOTICE: Minimum: 1.389 ms
NOTICE: Maximum: 3.888 ms
NOTICE: ------------------------------------------------------------------------
When we run our tests, it shows 4.173 ms on average for synthetic data vs. 2.102 ms for our “real” vectors using HNSW index. Interestingly enough, for AlloyDB ScaNN, the difference is not so big — only 5.958 ms vs. 5.161 ms if we use 1,000 lists. But if we use an index with only 100 lists, we can see a significant difference in performance, where the response time for synthetic data is 7.239 ms vs. 4.249 ms for the “real” ones.
The reasons behind the difference in performance have the same nature but slightly different consequences. Since the random vector lacks clusters and dense neighborhoods it goes through more steps to find the closest neighbours. Instead, for example going through 32 nodes to find the closest neighbour it has to traverse 256 or more and even in such a case the quality might be still low.
So, if you are testing HNSW, which is widely used in the community, the 2x difference is big enough and fully justifies the effort of getting a real dataset.
Vector Search Quality
We already established that synthetic data can mislead you about build time or vector search speed for your vector indexes. What about quality?
Usually, when we talk about vector search quality, we use the term “recall,” which represents the percentage of exact matches returned by an ANN index compared to the list of hits returned by a KNN exact search. For example, we do a KNN search returning the top 10 similar vectors. Then, we run exactly the same query with an ANN index and compare how many of the 10 vectors returned by the ANN search match the KNN results. If it is 9 out of 10, then it is 90% recall.
In my procedure evaluate_vector_recall, I use 100 random samples from the dataset and then iterate on those samples to get the recall for each of them, averaging the results at the end. I tested it with the top 10 similar vectors (k_param=>10). The procedure is provided in the Appendix and on GitHub.
Here is a sample output of the procedure.
pgdata=# call evaluate_vector_recall('tvreal768',768,k_param=>10);
NOTICE: Starting vector similarity search recall evaluation...
NOTICE: Target table: tvreal768, Expected dimensions: 768
NOTICE: Parameters - Vector Column: d, ID Column: id, K: 10, Sample Size: 100
NOTICE: Selecting 100 random vectors for sampling from tvreal768...
NOTICE: ------------------------------------------------------------------------
NOTICE: Recall Evaluation Finished.
NOTICE: Total queries evaluated: 100
NOTICE: Average Recall@10: %91.80
NOTICE: Minimum Recall@10: %0.00
NOTICE: Maximum Recall@10: %100.00
NOTICE: ------------------------------------------------------------------------
CALL
According to my test results, the recall for synthetic data is quite low. On average for the 100 samples, I am getting only 3.8% recall using HNSW, while for “real” data, the recall is 91.8%. This means our search on synthetic data is not only slow but also hugely inaccurate. When we try it using the AlloyDB ScaNN index, it shows 16.4% recall for synthetic data vs. 91.4% for the “real” data. Only IVFFLAT didn’t show itself good with the real dataset giving only about 51.9% recall on average. But the quality with synthetic data was still much worse than that — only 10.6%
Of course, your test results can and most likely will be different, but the trend is clear. Even if ScaNN shows slightly better results for a synthetic vector search, it is still quite misleading and inaccurate.
You also can opt-in to use the evaluate_query_recall procedure coming with the AlloyDB Omni and it gives you the option to measure recall. Read more in the official guide on how to use the procedure.
Conclusion
Here is my short summary for all the tests and results
- Synthetic vectors can be used for pure performance DML tests where the vectors are not used as predicates and are only used to fill up space. For example, for inserts, updates, or deletes of rows filtered by non-vector column data.
- Synthetic vector data shows incorrect results for index build times and can mislead testers by giving inaccurate results. So far, in most of my tests, synthetic data required more time to build the index compared to realistic vector data.
- An ANN vector search executed against a synthetic dataset can be 1.5 times slower than for a similar dataset with realistic vectors.
- Recall quality for an ANN vector search cannot be measured against synthetic vectors. In my tests, the recall quality ranged from 3.8% to 16.4%, depending on the index type and index parameters. The real dataset showed more than 90% recall for most of the tests.
- Get a real dataset and use it for all your performance and quality testing for ANN indexes.
Appendix
Here are some procedures I used for testing. I don’t pretend they are perfect in any way but should be clear enough for everyone who tries to use it. I used some hardcoded values to make it simpler to read. Both procedures are available on GitHub.
Here is the procedure to check response time for vector search. It is using cosine distance for measurement and takes the first 100 vectors from the table as a sample to be used later.
CREATE OR REPLACE PROCEDURE benchmark_vector_search(
n_repetitions integer,
table_name_param text,
vector_dimensions integer
)
LANGUAGE plpgsql
AS $$
DECLARE
-- Array to hold the 100 vectors sampled for the benchmark run
sample_vectors vector[];
-- Variable to hold the current vector being searched for
current_vector vector;
-- Variables for timing
overall_start_time timestamptz;
overall_end_time timestamptz;
repetition_start_time timestamptz;
q_start timestamptz;
q_end timestamptz;
q_diff double precision; -- in milliseconds
-- Metrics tracked across the entire benchmark
global_min_latency double precision := 1e9;
global_max_latency double precision := 0;
global_sum_latency double precision := 0;
overall_total_queries integer := 0;
-- Variable for dimension validation
actual_dimensions integer;
actual_sample_size integer;
-- Loop counter
i int;
BEGIN
RAISE NOTICE 'Starting vector similarity search benchmark...';
RAISE NOTICE 'Target table: %, Repetitions: %, Expected dimensions: %', table_name_param, n_repetitions, vector_dimensions;
-- Validate that the vector column "d" exists and dimensions match
BEGIN
EXECUTE format('SELECT vector_dims(d) FROM %I WHERE d IS NOT NULL LIMIT 1', table_name_param)
INTO actual_dimensions;
IF actual_dimensions IS NULL THEN
RAISE EXCEPTION 'Could not find any non-NULL vectors in table %. The table might be empty or column "d" contains only NULLs.', table_name_param;
END IF;
IF actual_dimensions != vector_dimensions THEN
RAISE EXCEPTION 'Vector dimension mismatch. Expected dimension %, but found dimension % in table %.', vector_dimensions, actual_dimensions, table_name_param;
END IF;
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exist.', table_name_param;
WHEN undefined_column THEN
RAISE EXCEPTION 'Table "%" does not have a column named "d".', table_name_param;
END;
RAISE NOTICE 'Dimension validation successful.';
-- Fetch 100 random vectors to use as search queries
RAISE NOTICE 'Selecting 100 random vectors for sampling from %...', table_name_param;
EXECUTE format('SELECT array_agg(d) FROM (SELECT d FROM %I WHERE d IS NOT NULL ORDER BY random() LIMIT 100) AS random_sample', table_name_param)
INTO sample_vectors;
IF sample_vectors IS NULL OR COALESCE(array_length(sample_vectors, 1), 0) = 0 THEN
RAISE EXCEPTION 'Could not retrieve any sample vectors. The table % might be empty or all vectors are NULL.', table_name_param;
END IF;
actual_sample_size := array_length(sample_vectors, 1);
IF actual_sample_size < 100 THEN
RAISE WARNING 'Could not retrieve 100 sample vectors. Using % available sample vectors instead.', actual_sample_size;
END IF;
RAISE NOTICE 'Starting main benchmark loop...';
overall_start_time := clock_timestamp();
-- The main outer loop that repeats the entire test (warmup + N repetitions)
FOR i IN 0..n_repetitions LOOP
repetition_start_time := clock_timestamp();
-- Reset overall_start_time to start of repetition 1 to exclude warmup duration from QPS/Totals
IF i = 1 THEN
overall_start_time := clock_timestamp();
END IF;
DECLARE
rep_min_latency double precision := 1e9;
rep_max_latency double precision := 0;
rep_sum_latency double precision := 0;
rep_queries integer := 0;
rep_duration double precision;
BEGIN
-- The inner loop that iterates through each of the sampled vectors
FOREACH current_vector IN ARRAY sample_vectors
LOOP
q_start := clock_timestamp();
-- Perform the core operation (limiting to top 5)
EXECUTE format('SELECT 1 FROM %I ORDER BY d <=> $1 LIMIT 5', table_name_param)
USING current_vector;
q_end := clock_timestamp();
q_diff := extract(epoch from (q_end - q_start)) * 1000.0; -- milliseconds
-- Accumulate local statistics
IF q_diff < rep_min_latency THEN rep_min_latency := q_diff; END IF;
IF q_diff > rep_max_latency THEN rep_max_latency := q_diff; END IF;
rep_sum_latency := rep_sum_latency + q_diff;
rep_queries := rep_queries + 1;
END LOOP;
-- Calculate total time for this repetition
rep_duration := extract(epoch from (clock_timestamp() - repetition_start_time));
IF i = 0 THEN
-- Warmup iteration: report it but do not add to global statistics
RAISE NOTICE 'Warmup Repetition: Avg Latency = % ms (Min: % ms, Max: % ms) | QPS: %',
round((rep_sum_latency / rep_queries)::numeric, 3),
round(rep_min_latency::numeric, 3),
round(rep_max_latency::numeric, 3),
round((rep_queries / rep_duration)::numeric, 1);
ELSE
-- Accumulate global statistics
IF rep_min_latency < global_min_latency THEN global_min_latency := rep_min_latency; END IF;
IF rep_max_latency > global_max_latency THEN global_max_latency := rep_max_latency; END IF;
global_sum_latency := global_sum_latency + rep_sum_latency;
overall_total_queries := overall_total_queries + rep_queries;
RAISE NOTICE 'Repetition %/%: Avg Latency = % ms (Min: % ms, Max: % ms) | QPS: %',
i, n_repetitions,
round((rep_sum_latency / rep_queries)::numeric, 3),
round(rep_min_latency::numeric, 3),
round(rep_max_latency::numeric, 3),
round((rep_queries / rep_duration)::numeric, 1);
END IF;
END;
END LOOP;
overall_end_time := clock_timestamp();
-- Log overall summary metrics
DECLARE
total_benchmark_time double precision;
overall_qps double precision;
BEGIN
total_benchmark_time := extract(epoch from (overall_end_time - overall_start_time));
overall_qps := overall_total_queries / total_benchmark_time;
RAISE NOTICE '------------------------------------------------------------------------';
RAISE NOTICE 'Benchmark finished successfully.';
RAISE NOTICE 'Total queries executed: % (excluding warmup)', overall_total_queries;
RAISE NOTICE 'Total execution time: % seconds (excluding warmup)', round(total_benchmark_time::numeric, 3);
RAISE NOTICE 'Throughput (overall): % QPS (excluding warmup)', round(overall_qps::numeric, 1);
RAISE NOTICE '------------------------------------------------------------------------';
RAISE NOTICE 'Latency Stats (excluding warmup):';
RAISE NOTICE ' Average: % ms', round((global_sum_latency / overall_total_queries)::numeric, 3);
RAISE NOTICE ' Minimum: % ms', round(global_min_latency::numeric, 3);
RAISE NOTICE ' Maximum: % ms', round(global_max_latency::numeric, 3);
RAISE NOTICE '------------------------------------------------------------------------';
END;
END;
$$;
And here is the procedure where I measure recall quality. It takes recall from each sample and then calculates an average recall quality for the entire execution.
CREATE OR REPLACE PROCEDURE evaluate_vector_recall(
table_name_param text,
vector_dimensions integer,
vector_col_param text DEFAULT 'd',
id_col_param text DEFAULT 'id',
k_param integer DEFAULT 5,
sample_size_param integer DEFAULT 100
)
LANGUAGE plpgsql
AS $$
DECLARE
-- Array to hold the vectors sampled for the recall evaluation
sample_vectors vector[];
-- Variable to hold the current vector being searched for
current_vector vector;
-- Arrays to hold the result IDs from exact and approximate searches
exact_ids text[];
approx_ids text[];
-- Metrics variables
overlap_count integer;
vector_recall double precision;
sum_recall double precision := 0.0;
min_recall double precision := 1.0;
max_recall double precision := 0.0;
actual_dimensions integer;
actual_sample_size integer;
total_queries integer := 0;
BEGIN
RAISE NOTICE 'Starting vector similarity search recall evaluation...';
RAISE NOTICE 'Target table: %, Expected dimensions: %', table_name_param, vector_dimensions;
RAISE NOTICE 'Parameters - Vector Column: %, ID Column: %, K: %, Sample Size: %',
vector_col_param, id_col_param, k_param, sample_size_param;
-- Validate vector column exists and dimension matches
BEGIN
EXECUTE format('SELECT vector_dims(%I) FROM %I WHERE %I IS NOT NULL LIMIT 1',
vector_col_param, table_name_param, vector_col_param)
INTO actual_dimensions;
IF actual_dimensions IS NULL THEN
RAISE EXCEPTION 'Could not find any non-NULL vectors in column % of table %. The table might be empty or contains only NULLs.',
vector_col_param, table_name_param;
END IF;
IF actual_dimensions != vector_dimensions THEN
RAISE EXCEPTION 'Vector dimension mismatch. Expected dimension %, but found dimension % in table %.',
vector_dimensions, actual_dimensions, table_name_param;
END IF;
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exist.', table_name_param;
WHEN undefined_column THEN
RAISE EXCEPTION 'Column "%" does not exist in table "%".', vector_col_param, table_name_param;
END;
-- Validate ID column exists
BEGIN
EXECUTE format('SELECT %I FROM %I LIMIT 1', id_col_param, table_name_param);
EXCEPTION
WHEN undefined_column THEN
RAISE EXCEPTION 'ID column "%" does not exist in table "%". Cannot measure recall.',
id_col_param, table_name_param;
END;
-- Select random sample vectors (ignoring nulls)
RAISE NOTICE 'Selecting % random vectors for sampling from %...', sample_size_param, table_name_param;
EXECUTE format('SELECT array_agg(%I) FROM (SELECT %I FROM %I WHERE %I IS NOT NULL ORDER BY random() LIMIT $1) AS random_sample',
vector_col_param, vector_col_param, table_name_param, vector_col_param)
INTO sample_vectors
USING sample_size_param;
IF sample_vectors IS NULL OR COALESCE(array_length(sample_vectors, 1), 0) = 0 THEN
RAISE EXCEPTION 'Could not retrieve any sample vectors. The table % might be empty or all vectors are NULL.', table_name_param;
END IF;
actual_sample_size := array_length(sample_vectors, 1);
IF actual_sample_size < sample_size_param THEN
RAISE WARNING 'Could not retrieve % sample vectors. Using % available sample vectors instead.',
sample_size_param, actual_sample_size;
END IF;
-- Iterate through sampled vectors to calculate recall
FOREACH current_vector IN ARRAY sample_vectors
LOOP
-- 1. Find exact nearest neighbors (ground truth) by forcing sequential scan
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
EXECUTE format('SELECT array_agg(%I::text) FROM (SELECT %I FROM %I ORDER BY %I <=> $1 LIMIT $2) AS exact_search',
id_col_param, id_col_param, table_name_param, vector_col_param)
INTO exact_ids
USING current_vector, k_param;
-- 2. Find approximate nearest neighbors (allowing index scan)
SET LOCAL enable_indexscan = on;
SET LOCAL enable_bitmapscan = on;
EXECUTE format('SELECT array_agg(%I::text) FROM (SELECT %I FROM %I ORDER BY %I <=> $1 LIMIT $2) AS approx_search',
id_col_param, id_col_param, table_name_param, vector_col_param)
INTO approx_ids
USING current_vector, k_param;
-- 3. Calculate overlap
IF exact_ids IS NOT NULL AND approx_ids IS NOT NULL THEN
SELECT COUNT(*) INTO overlap_count
FROM unnest(approx_ids) a
JOIN unnest(exact_ids) e ON a = e;
vector_recall := overlap_count::double precision / k_param;
sum_recall := sum_recall + vector_recall;
IF vector_recall < min_recall THEN min_recall := vector_recall; END IF;
IF vector_recall > max_recall THEN max_recall := vector_recall; END IF;
total_queries := total_queries + 1;
END IF;
END LOOP;
-- Print recall report
IF total_queries > 0 THEN
RAISE NOTICE '------------------------------------------------------------------------';
RAISE NOTICE 'Recall Evaluation Finished.';
RAISE NOTICE 'Total queries evaluated: %', total_queries;
RAISE NOTICE 'Average Recall@%: %%%', k_param, round(((sum_recall / total_queries) * 100.0)::numeric, 2);
RAISE NOTICE 'Minimum Recall@%: %%%', k_param, round((min_recall * 100.0)::numeric, 2);
RAISE NOTICE 'Maximum Recall@%: %%%', k_param, round((max_recall * 100.0)::numeric, 2);
RAISE NOTICE '------------------------------------------------------------------------';
ELSE
RAISE WARNING 'No recall queries were successfully evaluated.';
END IF;
END;
$$;
Top comments (0)