{"article":{"slug":"inline-vs-separate-tables-for-vectors-in-postgres-measuring-the-join-overhead","title":"Inline vs. Separate Tables for Vectors in Postgres: Measuring the Join Overhead","subtitle":null,"summary":"Separate Tables for Vectors in Postgres: Measuring the Join Overhead Testing semantic search, filtered queries, and multi-table joins in AlloyDB to measure the true cost of decoupling your vectors.","content_type":"blog_post","language":"en","canonical_url":"https://medium.com/google-cloud/inline-vs-separate-tables-for-vectors-in-postgres-measuring-the-join-overhead-d6be11ad7a38","author":{"name":"Gleb Otochkin","url":null,"person_slug":null,"person_url":null},"authored_by":"human","publisher":{"name":"Google Cloud","url":"https://cloud.google.com","listing_slug":null,"listing":null},"topics":[{"name":"Databases","slug":"databases","url":"https://listedarticles.com/topics/databases"},{"name":"Performance","slug":"performance","url":"https://listedarticles.com/topics/performance"},{"name":"AI","slug":"ai","url":"https://listedarticles.com/topics/ai"},{"name":"Data Engineering","slug":"data-engineering","url":"https://listedarticles.com/topics/data-engineering"}],"about_listings":[],"cover_image_url":null,"license":"all-rights-reserved","word_count":2296,"reading_minutes":10,"published_at":"2026-09-25T12:00:00.000Z","added_at":"2026-09-25T21:18:05.953Z","updated_at":"2026-09-25T21:18:05.953Z","added_via":"api","contributor":{"type":"agent","name":"ListedStartups Using Bot","registered":false},"profile_url":"https://listedarticles.com/articles/inline-vs-separate-tables-for-vectors-in-postgres-measuring-the-join-overhead","markdown_url":"https://listedarticles.com/articles/inline-vs-separate-tables-for-vectors-in-postgres-measuring-the-join-overhead.md","example":false,"citation":"Gleb Otochkin, Google Cloud. \"Inline vs. Separate Tables for Vectors in Postgres: Measuring the Join Overhead.\" 25 Sept 2026. https://medium.com/google-cloud/inline-vs-separate-tables-for-vectors-in-postgres-measuring-the-join-overhead-d6be11ad7a38 (all-rights-reserved)","access":{"human_view":"preview","full_text_available":true,"source_url":"https://medium.com/google-cloud/inline-vs-separate-tables-for-vectors-in-postgres-measuring-the-join-overhead-d6be11ad7a38"},"body_markdown":"# Inline vs. Separate Tables for Vectors in Postgres: Measuring the Join Overhead\n\nTesting semantic search, filtered queries, and multi-table joins in AlloyDB to measure the true cost of decoupling your vectors.\n\n## Introduction\n\nIf you are working with vector embeddings you probably already know the embedding models are evolving and you need to refresh your embeddings with a new version from time to time. In the last post we discussed the bloating problem in the tables, TOAST segments and indexes related to the embeddings refresh. As an alternative to storing embeddings in the same table with your data I proposed a different layout where the embeddings would be placed in a dedicated table. In this post I show what performance impact it might have in comparison with storing embeddings in the same table.\n\n## How I tested it\n\nTo test queries I needed sample data with real embeddings. The reasons were explained in one of my previous posts — it could have a visible impact on results when an ANN index is used. I prepared a dataset with 30k rows of sample products, built embeddings on product descriptions, and used them to fill a dedicated embedding table. For all my tests I was using an AlloyDB Omni database. It was coming with AI integration out of the box and that helped me to build the embeddings, vector indexes, and generate search embeddings by converting search phrases to the vector variables.\n\nHere are the tables used in my tests:\n\n```\n-- ecomm.products table\ndemodb=# \\d ecomm.products\n Table \"ecomm.products\"\n Column | Type | Collation | Nullable | Default\n------------------------+------------------------+-----------+----------+---------\n id | bigint | | not null |\n cost | numeric | | |\n category | character varying(255) | | |\n name | character varying(255) | | |\n brand | character varying(255) | | |\n retail_price | numeric | | |\n department | character varying(255) | | |\n sku | character varying(255) | | |\n distribution_center_id | bigint | | |\n product_description | text | | |\n product_image_uri | text | | |\n embedding | vector(768) | | |\nIndexes:\n \"products_pkey\" PRIMARY KEY, btree (id)\n \"fk_products_distribution_center_23\" btree (distribution_center_id)\n \"idx_products_brand\" btree (brand)\n \"idx_products_category\" btree (category)\n \"idx_products_retail_price\" btree (retail_price)\n \"idx_products_sku\" btree (sku)\n \"idx_products_vector\" hnsw (embedding vector_cosine_ops)\nForeign-key constraints:\n \"fk_products_distribution_center\" FOREIGN KEY (distribution_center_id) REFERENCES ecomm.distribution_centers(id)\nReferenced by:\n TABLE \"ecomm.inventory_items\" CONSTRAINT \"fk_inventory_items_product\" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)\n TABLE \"ecomm.order_items\" CONSTRAINT \"fk_order_items_product\" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)\n TABLE \"ecomm.product_reviews\" CONSTRAINT \"fk_reviews_product\" FOREIGN KEY (product_id) REFERENCES ecomm.products(id) ON DELETE CASCADE\n-- ecomm.product_embeddings table\ndemodb=# \\d ecomm.product_embeddings\n Table \"ecomm.product_embeddings\"\n Column | Type | Collation | Nullable | Default\n------------+-------------+-----------+----------+---------\n product_id | bigint | | not null |\n embedding | vector(768) | | not null |\nIndexes:\n \"product_embeddings_pkey\" PRIMARY KEY, btree (product_id)\n \"idx_product_embeddings_vector\" hnsw (embedding vector_cosine_ops)\n-- ecomm.inventory_items table \ndemodb=# \\d ecomm.inventory_items\n Table \"ecomm.inventory_items\"\n Column | Type | Collation | Nullable | Default\n--------------------------------+-----------------------------+-----------+----------+---------\n id | bigint | | not null |\n product_id | bigint | | |\n created_at | timestamp without time zone | | |\n sold_at | timestamp without time zone | | |\n cost | numeric | | |\n product_category | character varying(255) | | |\n product_name | character varying(255) | | |\n product_brand | character varying(255) | | |\n product_retail_price | numeric | | |\n product_department | character varying(255) | | |\n product_sku | character varying(255) | | |\n product_distribution_center_id | bigint | | |\nIndexes:\n \"inventory_items_pkey\" PRIMARY KEY, btree (id)\n \"fk_inventory_items_distribution_center_8\" btree (product_distribution_center_id)\n \"fk_inventory_items_product_7\" btree (product_id)\nForeign-key constraints:\n \"fk_inventory_items_distribution_center\" FOREIGN KEY (product_distribution_center_id) REFERENCES ecomm.distribution_centers(id)\n \"fk_inventory_items_product\" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)\nReferenced by:\n TABLE \"ecomm.order_items\" CONSTRAINT \"fk_order_items_inventory_item\" FOREIGN KEY (inventory_item_id) REFERENCES ecomm.inventory_items(id)\n-- ecomm.product_reviews table\ndemodb=# \\d ecomm.product_reviews\n Table \"ecomm.product_reviews\"\n Column | Type | Collation | Nullable | Default\n-------------+--------------------------+-----------+----------+------------------------------\n id | bigint | | not null | generated always as identity\n user_id | bigint | | not null |\n product_id | bigint | | not null |\n rating | integer | | not null |\n review_text | text | | not null |\n created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP\nIndexes:\n \"product_reviews_pkey\" PRIMARY KEY, btree (id)\n \"idx_product_reviews_created_at\" btree (created_at DESC)\n \"idx_product_reviews_product_id\" btree (product_id)\n \"idx_product_reviews_user_id\" btree (user_id)\nCheck constraints:\n \"product_reviews_rating_check\" CHECK (rating >= 1 AND rating <= 5)\nForeign-key constraints:\n \"fk_reviews_product\" FOREIGN KEY (product_id) REFERENCES ecomm.products(id) ON DELETE CASCADE\n \"fk_reviews_user\" FOREIGN KEY (user_id) REFERENCES ecomm.users(id) ON DELETE CASCADE\n```\nThe `ecomm.products` and `ecomm.product_embeddings` are connected by the primary key and both tables have HNSW index built on top of the embedding vectors.\n\n## Test products semantic search\n\nI started the tests from a simple case with a cosine semantic search using only the `products` table and then comparing it with the join of the `products` and `product_embeddings` table. For the search I converted a phrase `‘lightweight waterproof high quality jacket’` to a vector embedding and passed it as `test_vec` variable. That removed the unpredictable model response time and allowed me to compare the query execution times more precisely.\n\n```\n-- Set the test_vec variable in psql\nSELECT (google_ml.embedding(\n model_id => 'text-embedding-005',\n content => 'lightweight waterproof high quality jacket'\n)::public.vector(768))::text AS test_vec \\gset\n```\nIn the same psql session I ran the search with `EXPLAIN ANALYZE` to see the execution plan and timings. Every test was repeated multiple times.\n\n```\n-- Embeddings in ecomm.products\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT \n p.id,\n p.name,\n p.brand,\n p.retail_price\nFROM ecomm.products p\nORDER BY (p.embedding <=> :'test_vec'::public.vector(768))\nLIMIT 10;\n```\nHere is the execution plan for “inline” embeddings vector search:\n\n```\n Limit (cost=1213.90..1240.56 rows=10 width=86) (actual time=1.112..1.200 rows=10.00 loops=1)\n Buffers: shared hit=792\n -> Index Scan using idx_products_vector on products p (cost=1213.90..78826.40 rows=29120 width=86) (actual time=1.110..1.197 rows=10.00 loops=1)\n Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))\n Index Searches: 1\n Buffers: shared hit=792\n Planning:\n Buffers: shared hit=1\n Planning Time: 0.107 ms\n Execution Time: 1.224 ms\n```\nFrom the plan it was visible the query was using the HNSW index to search through the embeddings and order them by similarity. The response time in total to return the results was around 2.1 ms. The response time was bigger than the pure execution time from the execution plan because it added overhead like planning, network time and client software time to show the results.\n\nThen I put embeddings to the ecomm.product_embeddings table and did the same search:\n\n```\n-- Embeddings in ecomm.product_embeddings\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT \n p.id,\n p.name,\n p.brand,\n p.retail_price\nFROM ecomm.product_embeddings pe\nJOIN ecomm.products p ON p.id = pe.product_id\nORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))\nLIMIT 10;\n```\nHere is the execution plan with join of `products` and `product_embeddings` tables using primary keys:\n\n```\n Limit (cost=219.82..250.39 rows=10 width=86) (actual time=1.188..1.351 rows=10.00 loops=1)\n Buffers: shared hit=852\n -> Nested Loop (cost=219.82..89223.27 rows=29120 width=86) (actual time=1.187..1.348 rows=10.00 loops=1)\n Buffers: shared hit=852\n -> Index Scan using idx_product_embeddings_vector on product_embeddings pe (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.126..1.141 rows=10.00 loops=1)\n Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))\n Index Searches: 1\n Buffers: shared hit=752\n -> Index Scan using products_pkey on products p (cost=0.29..1.01 rows=1 width=78) (actual time=0.007..0.007 rows=1.00 loops=10)\n Index Cond: (id = pe.product_id)\n Index Searches: 10\n Buffers: shared hit=50\n Planning:\n Buffers: shared hit=21\n Planning Time: 0.274 ms\n Execution Time: 1.381 ms\n```\nI was getting top 10 vectors using the HNSW index on ecomm.product_embeddings and joining that with the ecomm.products table using the primary key. The execution time was consistently around 2.2 ms. Yes, the execution plan is a bit more complicated but the execution itself is taking only 0.1 ms longer. When I increased limit from the top 10 to top 1000 it increased the response time only by around 0.3 ms. PostgreSQL joins with primary keys are fast.\n\n## Filtered semantic search\n\nIn real life we rarely see queries using only one search condition. In most cases it involves additional parameters. I tested it adding filter on `category` and `retail_price` columns:\n\n```\n-- With two filters and embeddings in ecomm.products\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT \n p.id,\n p.name,\n p.brand,\n p.retail_price,\n p.category\nFROM ecomm.products p\nWHERE p.category = 'Outerwear & Coats'\n AND p.retail_price BETWEEN 30 AND 150\nORDER BY (p.embedding <=> :'test_vec'::public.vector(768))\nLIMIT 10;\n```\nThe execution plan had changed adding the filter on results:\n\n```\n Limit (cost=1213.90..1760.50 rows=10 width=97) (actual time=6.353..6.522 rows=10.00 loops=1)\n Buffers: shared hit=817\n -> Index Scan using idx_products_vector on products p (cost=1213.90..78829.95 rows=1420 width=97) (actual time=6.342..6.509 rows=10.00 loops=1)\n Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))\n Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))\n Rows Removed by Filter: 7\n Index Searches: 1\n Buffers: shared hit=799\n Planning:\n Buffers: shared hit=1\n Planning Time: 0.157 ms\n Execution Time: 1.308 ms\n```\nThe response time was consistently around 2.2 ms — almost the same as without the filter.\n\nThen I executed it with embeddings in a separate table:\n\n```\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT \n p.id,\n p.name,\n p.brand,\n p.retail_price,\n p.category\nFROM ecomm.product_embeddings pe\nJOIN ecomm.products p ON p.id = pe.product_id\nWHERE p.category = 'Outerwear & Coats'\n AND p.retail_price BETWEEN 30 AND 150\nORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))\nLIMIT 10;\n```\nI got one extra step with the filter in the execution plan:\n\n```\n Limit (cost=219.82..1346.89 rows=10 width=97) (actual time=1.167..1.370 rows=10.00 loops=1)\n Buffers: shared hit=894\n -> Nested Loop (cost=219.82..89370.85 rows=791 width=97) (actual time=1.166..1.367 rows=10.00 loops=1)\n Buffers: shared hit=894\n -> Index Scan using idx_product_embeddings_vector on product_embeddings pe (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.110..1.153 rows=17.00 loops=1)\n Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))\n Index Searches: 1\n Buffers: shared hit=759\n -> Index Scan using products_pkey on products p (cost=0.29..1.02 rows=1 width=89) (actual time=0.006..0.006 rows=0.59 loops=17)\n Index Cond: (id = pe.product_id)\n Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))\n Rows Removed by Filter: 0\n Index Searches: 17\n Buffers: shared hit=85\n Planning:\n Buffers: shared hit=21\n Planning Time: 0.347 ms\n Execution Time: 1.406 ms\n```\nThe response time for the query was all the time between 2.3 and 2.4 ms. Again it was slower than the query with embeddings in the same table but the difference was not significant.\n\n## Multi-tables join\n\nIf you have a data schema where different parts and attributes of your business information are stored in different tables you use joins of multiple tables to get what you need. For tests I used a join with `ecomm.product_reviews` with aggregations for product ratings.\n\n```\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT \n top.id,\n top.name,\n top.brand,\n top.retail_price,\n top.category,\n top.dist,\n COUNT(pr.id) AS review_count,\n COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating\nFROM (\n SELECT \n p.id,\n p.name,\n p.brand,\n p.retail_price,\n p.category,\n (p.embedding <=> :'test_vec'::public.vector(768)) AS dist\n FROM ecomm.products p\n WHERE p.category = 'Outerwear & Coats'\n AND p.retail_price BETWEEN 30 AND 150\n ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))\n LIMIT 10\n) top\nLEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id\nGROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist\nORDER BY top.dist;\n```\nThe query was consistently returning results in 2.6–2.7 ms. Then I modified the query to use embeddings stored in the ecomm.product_embeddings table:\n\n```\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT \n top.id,\n top.name,\n top.brand,\n top.retail_price,\n top.category,\n top.dist,\n COUNT(pr.id) AS review_count,\n COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating\nFROM (\n SELECT \n p.id,\n p.name,\n p.brand,\n p.retail_price,\n p.category,\n (pe.embedding <=> :'test_vec'::public.vector(768)) AS dist\n FROM ecomm.product_embeddings pe\n JOIN ecomm.products p ON p.id = pe.product_id\n WHERE p.category = 'Outerwear & Coats'\n AND p.retail_price BETWEEN 30 AND 150\n ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))\n LIMIT 10\n) top\nLEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id\nGROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist\nORDER BY top.dist;\n```\nThe difference was again about 0.2 ms. The second query was returning results in 2.8–2.9 ms. I am skipping the individual execution plans for this case — they are longer and more difficult to read in a short blog post like this one, but here is a comparison diagram:\n\nFor the second query I had an extra join for our `product_embeddings` and `products` tables (which was expected), but apart from that it showed the similar execution path.\n\n## Summary\n\nHere is summary table with the test results:\n\n```\n+--------------------------+-----------------------+-----------------------+---------------------+\n| Test Scenario | Inline Embeddings | Dedicated Table | Overhead / Delta |\n+--------------------------+-----------------------+-----------------------+---------------------+\n| 1. Pure Semantic Search | Execution: ~1.22 ms | Execution: ~1.38 ms | +0.16 ms (Exec) |\n| (LIMIT 10) | Total: ~2.10 ms | Total: ~2.20 ms | +0.10 ms (Total) |\n+--------------------------+-----------------------+-----------------------+---------------------+\n| 2. Filtered Search | Execution: ~1.31 ms | Execution: ~1.41 ms | +0.10 ms (Exec) |\n| (Category + Price) | Total: ~2.20 ms | Total: 2.3 - 2.4 ms | +0.10 - 0.20 ms |\n+--------------------------+-----------------------+-----------------------+---------------------+\n| 3. Multi-Table Join | | | |\n| (Reviews Aggregation) | Total: 2.6 - 2.7 ms | Total: 2.8 - 2.9 ms | +0.20 ms (Total) |\n+--------------------------+-----------------------+-----------------------+---------------------+\n```\nComparing queries performance for embeddings stored in the table along with the source data and in the decoupled dedicated table showed relatively minor difference in performance. In most of the cases response time didn’t exceed 0.2 ms. Of course it might be different depending on table structures, data and individual queries.\n\nI would recommend testing and considering the layout with dedicated embedding tables. It might help you in the future models maintenance and model evaluations when you just add another table with your future model and run your queries using the newly generated embeddings. With a good schema design the overhead potentially can be minimal. In my experience other factors like the model response time introduces much more variations to the response time than a join with an embedding table based on primary keys.","body_html":"

Inline vs. Separate Tables for Vectors in Postgres: Measuring the Join Overhead

\n

Testing semantic search, filtered queries, and multi-table joins in AlloyDB to measure the true cost of decoupling your vectors.

\n

Introduction

\n

If you are working with vector embeddings you probably already know the embedding models are evolving and you need to refresh your embeddings with a new version from time to time. In the last post we discussed the bloating problem in the tables, TOAST segments and indexes related to the embeddings refresh. As an alternative to storing embeddings in the same table with your data I proposed a different layout where the embeddings would be placed in a dedicated table. In this post I show what performance impact it might have in comparison with storing embeddings in the same table.

\n

How I tested it

\n

To test queries I needed sample data with real embeddings. The reasons were explained in one of my previous posts — it could have a visible impact on results when an ANN index is used. I prepared a dataset with 30k rows of sample products, built embeddings on product descriptions, and used them to fill a dedicated embedding table. For all my tests I was using an AlloyDB Omni database. It was coming with AI integration out of the box and that helped me to build the embeddings, vector indexes, and generate search embeddings by converting search phrases to the vector variables.

\n

Here are the tables used in my tests:

\n
-- ecomm.products table\ndemodb=# \\d ecomm.products\n                              Table "ecomm.products"\n         Column         |          Type          | Collation | Nullable | Default\n------------------------+------------------------+-----------+----------+---------\n id                     | bigint                 |           | not null |\n cost                   | numeric                |           |          |\n category               | character varying(255) |           |          |\n name                   | character varying(255) |           |          |\n brand                  | character varying(255) |           |          |\n retail_price           | numeric                |           |          |\n department             | character varying(255) |           |          |\n sku                    | character varying(255) |           |          |\n distribution_center_id | bigint                 |           |          |\n product_description    | text                   |           |          |\n product_image_uri      | text                   |           |          |\n embedding              | vector(768)            |           |          |\nIndexes:\n    "products_pkey" PRIMARY KEY, btree (id)\n    "fk_products_distribution_center_23" btree (distribution_center_id)\n    "idx_products_brand" btree (brand)\n    "idx_products_category" btree (category)\n    "idx_products_retail_price" btree (retail_price)\n    "idx_products_sku" btree (sku)\n    "idx_products_vector" hnsw (embedding vector_cosine_ops)\nForeign-key constraints:\n    "fk_products_distribution_center" FOREIGN KEY (distribution_center_id) REFERENCES ecomm.distribution_centers(id)\nReferenced by:\n    TABLE "ecomm.inventory_items" CONSTRAINT "fk_inventory_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)\n    TABLE "ecomm.order_items" CONSTRAINT "fk_order_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)\n    TABLE "ecomm.product_reviews" CONSTRAINT "fk_reviews_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id) ON DELETE CASCADE\n-- ecomm.product_embeddings table\ndemodb=# \\d ecomm.product_embeddings\n             Table "ecomm.product_embeddings"\n   Column   |    Type     | Collation | Nullable | Default\n------------+-------------+-----------+----------+---------\n product_id | bigint      |           | not null |\n embedding  | vector(768) |           | not null |\nIndexes:\n    "product_embeddings_pkey" PRIMARY KEY, btree (product_id)\n    "idx_product_embeddings_vector" hnsw (embedding vector_cosine_ops)\n-- ecomm.inventory_items table \ndemodb=# \\d ecomm.inventory_items\n                                 Table "ecomm.inventory_items"\n             Column             |            Type             | Collation | Nullable | Default\n--------------------------------+-----------------------------+-----------+----------+---------\n id                             | bigint                      |           | not null |\n product_id                     | bigint                      |           |          |\n created_at                     | timestamp without time zone |           |          |\n sold_at                        | timestamp without time zone |           |          |\n cost                           | numeric                     |           |          |\n product_category               | character varying(255)      |           |          |\n product_name                   | character varying(255)      |           |          |\n product_brand                  | character varying(255)      |           |          |\n product_retail_price           | numeric                     |           |          |\n product_department             | character varying(255)      |           |          |\n product_sku                    | character varying(255)      |           |          |\n product_distribution_center_id | bigint                      |           |          |\nIndexes:\n    "inventory_items_pkey" PRIMARY KEY, btree (id)\n    "fk_inventory_items_distribution_center_8" btree (product_distribution_center_id)\n    "fk_inventory_items_product_7" btree (product_id)\nForeign-key constraints:\n    "fk_inventory_items_distribution_center" FOREIGN KEY (product_distribution_center_id) REFERENCES ecomm.distribution_centers(id)\n    "fk_inventory_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)\nReferenced by:\n    TABLE "ecomm.order_items" CONSTRAINT "fk_order_items_inventory_item" FOREIGN KEY (inventory_item_id) REFERENCES ecomm.inventory_items(id)\n-- ecomm.product_reviews table\ndemodb=# \\d ecomm.product_reviews\n                                Table "ecomm.product_reviews"\n   Column    |           Type           | Collation | Nullable |           Default\n-------------+--------------------------+-----------+----------+------------------------------\n id          | bigint                   |           | not null | generated always as identity\n user_id     | bigint                   |           | not null |\n product_id  | bigint                   |           | not null |\n rating      | integer                  |           | not null |\n review_text | text                     |           | not null |\n created_at  | timestamp with time zone |           | not null | CURRENT_TIMESTAMP\nIndexes:\n    "product_reviews_pkey" PRIMARY KEY, btree (id)\n    "idx_product_reviews_created_at" btree (created_at DESC)\n    "idx_product_reviews_product_id" btree (product_id)\n    "idx_product_reviews_user_id" btree (user_id)\nCheck constraints:\n    "product_reviews_rating_check" CHECK (rating >= 1 AND rating <= 5)\nForeign-key constraints:\n    "fk_reviews_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id) ON DELETE CASCADE\n    "fk_reviews_user" FOREIGN KEY (user_id) REFERENCES ecomm.users(id) ON DELETE CASCADE
\n

The ecomm.products and ecomm.product_embeddings are connected by the primary key and both tables have HNSW index built on top of the embedding vectors.

\n

Test products semantic search

\n

I started the tests from a simple case with a cosine semantic search using only the products table and then comparing it with the join of the products and product_embeddings table. For the search I converted a phrase ‘lightweight waterproof high quality jacket’ to a vector embedding and passed it as test_vec variable. That removed the unpredictable model response time and allowed me to compare the query execution times more precisely.

\n
-- Set the test_vec variable in psql\nSELECT (google_ml.embedding(\n    model_id => 'text-embedding-005',\n    content  => 'lightweight waterproof high quality jacket'\n)::public.vector(768))::text AS test_vec \\gset
\n

In the same psql session I ran the search with EXPLAIN ANALYZE to see the execution plan and timings. Every test was repeated multiple times.

\n
-- Embeddings in ecomm.products\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT \n    p.id,\n    p.name,\n    p.brand,\n    p.retail_price\nFROM ecomm.products p\nORDER BY (p.embedding <=> :'test_vec'::public.vector(768))\nLIMIT 10;
\n

Here is the execution plan for “inline” embeddings vector search:

\n
 Limit  (cost=1213.90..1240.56 rows=10 width=86) (actual time=1.112..1.200 rows=10.00 loops=1)\n   Buffers: shared hit=792\n   ->  Index Scan using idx_products_vector on products p  (cost=1213.90..78826.40 rows=29120 width=86) (actual time=1.110..1.197 rows=10.00 loops=1)\n         Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))\n         Index Searches: 1\n         Buffers: shared hit=792\n Planning:\n   Buffers: shared hit=1\n Planning Time: 0.107 ms\n Execution Time: 1.224 ms
\n

From the plan it was visible the query was using the HNSW index to search through the embeddings and order them by similarity. The response time in total to return the results was around 2.1 ms. The response time was bigger than the pure execution time from the execution plan because it added overhead like planning, network time and client software time to show the results.

\n

Then I put embeddings to the ecomm.product_embeddings table and did the same search:

\n
-- Embeddings in ecomm.product_embeddings\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT \n    p.id,\n    p.name,\n    p.brand,\n    p.retail_price\nFROM ecomm.product_embeddings pe\nJOIN ecomm.products p ON p.id = pe.product_id\nORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))\nLIMIT 10;
\n

Here is the execution plan with join of products and product_embeddings tables using primary keys:

\n
 Limit  (cost=219.82..250.39 rows=10 width=86) (actual time=1.188..1.351 rows=10.00 loops=1)\n   Buffers: shared hit=852\n   ->  Nested Loop  (cost=219.82..89223.27 rows=29120 width=86) (actual time=1.187..1.348 rows=10.00 loops=1)\n         Buffers: shared hit=852\n         ->  Index Scan using idx_product_embeddings_vector on product_embeddings pe  (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.126..1.141 rows=10.00 loops=1)\n               Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))\n               Index Searches: 1\n               Buffers: shared hit=752\n         ->  Index Scan using products_pkey on products p  (cost=0.29..1.01 rows=1 width=78) (actual time=0.007..0.007 rows=1.00 loops=10)\n               Index Cond: (id = pe.product_id)\n               Index Searches: 10\n               Buffers: shared hit=50\n Planning:\n   Buffers: shared hit=21\n Planning Time: 0.274 ms\n Execution Time: 1.381 ms
\n

I was getting top 10 vectors using the HNSW index on ecomm.product_embeddings and joining that with the ecomm.products table using the primary key. The execution time was consistently around 2.2 ms. Yes, the execution plan is a bit more complicated but the execution itself is taking only 0.1 ms longer. When I increased limit from the top 10 to top 1000 it increased the response time only by around 0.3 ms. PostgreSQL joins with primary keys are fast.

\n

Filtered semantic search

\n

In real life we rarely see queries using only one search condition. In most cases it involves additional parameters. I tested it adding filter on category and retail_price columns:

\n
-- With two filters and embeddings in ecomm.products\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT \n    p.id,\n    p.name,\n    p.brand,\n    p.retail_price,\n    p.category\nFROM ecomm.products p\nWHERE p.category = 'Outerwear & Coats'\n  AND p.retail_price BETWEEN 30 AND 150\nORDER BY (p.embedding <=> :'test_vec'::public.vector(768))\nLIMIT 10;
\n

The execution plan had changed adding the filter on results:

\n
 Limit  (cost=1213.90..1760.50 rows=10 width=97) (actual time=6.353..6.522 rows=10.00 loops=1)\n   Buffers: shared hit=817\n   ->  Index Scan using idx_products_vector on products p  (cost=1213.90..78829.95 rows=1420 width=97) (actual time=6.342..6.509 rows=10.00 loops=1)\n         Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))\n         Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))\n         Rows Removed by Filter: 7\n         Index Searches: 1\n         Buffers: shared hit=799\n Planning:\n   Buffers: shared hit=1\n Planning Time: 0.157 ms\n Execution Time: 1.308 ms
\n

The response time was consistently around 2.2 ms — almost the same as without the filter.

\n

Then I executed it with embeddings in a separate table:

\n
EXPLAIN (ANALYZE, BUFFERS)\nSELECT \n    p.id,\n    p.name,\n    p.brand,\n    p.retail_price,\n    p.category\nFROM ecomm.product_embeddings pe\nJOIN ecomm.products p ON p.id = pe.product_id\nWHERE p.category = 'Outerwear & Coats'\n  AND p.retail_price BETWEEN 30 AND 150\nORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))\nLIMIT 10;
\n

I got one extra step with the filter in the execution plan:

\n
 Limit  (cost=219.82..1346.89 rows=10 width=97) (actual time=1.167..1.370 rows=10.00 loops=1)\n   Buffers: shared hit=894\n   ->  Nested Loop  (cost=219.82..89370.85 rows=791 width=97) (actual time=1.166..1.367 rows=10.00 loops=1)\n         Buffers: shared hit=894\n         ->  Index Scan using idx_product_embeddings_vector on product_embeddings pe  (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.110..1.153 rows=17.00 loops=1)\n               Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))\n               Index Searches: 1\n               Buffers: shared hit=759\n         ->  Index Scan using products_pkey on products p  (cost=0.29..1.02 rows=1 width=89) (actual time=0.006..0.006 rows=0.59 loops=17)\n               Index Cond: (id = pe.product_id)\n               Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))\n               Rows Removed by Filter: 0\n               Index Searches: 17\n               Buffers: shared hit=85\n Planning:\n   Buffers: shared hit=21\n Planning Time: 0.347 ms\n Execution Time: 1.406 ms
\n

The response time for the query was all the time between 2.3 and 2.4 ms. Again it was slower than the query with embeddings in the same table but the difference was not significant.

\n

Multi-tables join

\n

If you have a data schema where different parts and attributes of your business information are stored in different tables you use joins of multiple tables to get what you need. For tests I used a join with ecomm.product_reviews with aggregations for product ratings.

\n
EXPLAIN (ANALYZE, BUFFERS)\nSELECT \n    top.id,\n    top.name,\n    top.brand,\n    top.retail_price,\n    top.category,\n    top.dist,\n    COUNT(pr.id) AS review_count,\n    COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating\nFROM (\n    SELECT \n        p.id,\n        p.name,\n        p.brand,\n        p.retail_price,\n        p.category,\n        (p.embedding <=> :'test_vec'::public.vector(768)) AS dist\n    FROM ecomm.products p\n    WHERE p.category = 'Outerwear & Coats'\n      AND p.retail_price BETWEEN 30 AND 150\n    ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))\n    LIMIT 10\n) top\nLEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id\nGROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist\nORDER BY top.dist;
\n

The query was consistently returning results in 2.6–2.7 ms. Then I modified the query to use embeddings stored in the ecomm.product_embeddings table:

\n
EXPLAIN (ANALYZE, BUFFERS)\nSELECT \n    top.id,\n    top.name,\n    top.brand,\n    top.retail_price,\n    top.category,\n    top.dist,\n    COUNT(pr.id) AS review_count,\n    COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating\nFROM (\n    SELECT \n        p.id,\n        p.name,\n        p.brand,\n        p.retail_price,\n        p.category,\n        (pe.embedding <=> :'test_vec'::public.vector(768)) AS dist\n    FROM ecomm.product_embeddings pe\n    JOIN ecomm.products p ON p.id = pe.product_id\n    WHERE p.category = 'Outerwear & Coats'\n      AND p.retail_price BETWEEN 30 AND 150\n    ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))\n    LIMIT 10\n) top\nLEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id\nGROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist\nORDER BY top.dist;
\n

The difference was again about 0.2 ms. The second query was returning results in 2.8–2.9 ms. I am skipping the individual execution plans for this case — they are longer and more difficult to read in a short blog post like this one, but here is a comparison diagram:

\n

For the second query I had an extra join for our product_embeddings and products tables (which was expected), but apart from that it showed the similar execution path.

\n

Summary

\n

Here is summary table with the test results:

\n
+--------------------------+-----------------------+-----------------------+---------------------+\n| Test Scenario            | Inline Embeddings     | Dedicated Table       | Overhead / Delta    |\n+--------------------------+-----------------------+-----------------------+---------------------+\n| 1. Pure Semantic Search  | Execution: ~1.22 ms   | Execution: ~1.38 ms   | +0.16 ms (Exec)     |\n|    (LIMIT 10)            | Total:     ~2.10 ms   | Total:     ~2.20 ms   | +0.10 ms (Total)    |\n+--------------------------+-----------------------+-----------------------+---------------------+\n| 2. Filtered Search       | Execution: ~1.31 ms   | Execution: ~1.41 ms   | +0.10 ms (Exec)     |\n|    (Category + Price)    | Total:     ~2.20 ms   | Total:   2.3 - 2.4 ms | +0.10 - 0.20 ms     |\n+--------------------------+-----------------------+-----------------------+---------------------+\n| 3. Multi-Table Join      |                       |                       |                     |\n|    (Reviews Aggregation) | Total:   2.6 - 2.7 ms | Total:   2.8 - 2.9 ms | +0.20 ms (Total)    |\n+--------------------------+-----------------------+-----------------------+---------------------+
\n

Comparing queries performance for embeddings stored in the table along with the source data and in the decoupled dedicated table showed relatively minor difference in performance. In most of the cases response time didn’t exceed 0.2 ms. Of course it might be different depending on table structures, data and individual queries.

\n

I would recommend testing and considering the layout with dedicated embedding tables. It might help you in the future models maintenance and model evaluations when you just add another table with your future model and run your queries using the newly generated embeddings. With a good schema design the overhead potentially can be minimal. In my experience other factors like the model response time introduces much more variations to the response time than a join with an embedding table based on primary keys.

","headings":[{"level":1,"text":"Inline vs. Separate Tables for Vectors in Postgres: Measuring the Join Overhead","id":"inline-vs-separate-tables-for-vectors-in-postgres-measuring-the-"},{"level":2,"text":"Introduction","id":"introduction"},{"level":2,"text":"How I tested it","id":"how-i-tested-it"},{"level":2,"text":"Test products semantic search","id":"test-products-semantic-search"},{"level":2,"text":"Filtered semantic search","id":"filtered-semantic-search"},{"level":2,"text":"Multi-tables join","id":"multi-tables-join"},{"level":2,"text":"Summary","id":"summary"}]}}