CareerByteCode
ByteBlogby CareerByteCode

Vector Search with AlloyDB pgvector and Gemini

What you build: semantic search with AlloyDB and pgvector, Vertex AI embeddings, an HNSW index and Gemini answers that cite their source. Every step tested.

Vector Search with AlloyDB pgvector and Gemini

✍️ Translate this blog into Hindi, Tamil or Telugu

Vector search finds text by meaning, not by matching words: ask "how can I pay less for test machines nobody uses at night?" and it finds a note titled "Stop idle VMs at night", although they share almost no words. In this lab you store 20 short notes in AlloyDB for PostgreSQL with the pgvector extension, turn them into embeddings with Vertex AI, search them by meaning with plain SQL, speed it up with an HNSW index, and let Gemini answer questions from the closest notes (RAG). Then you break the search with the wrong vector size and fix it.

Who it is for: developers who know a little SQL and want to build AI search on a real database. Time: about 1 hour. Cost: the smallest AlloyDB (2 vCPUs, zonal) for about 25 minutes and a few Vertex AI calls, covered by the free trial credit. Delete it right after.

Every command below was run for real by the CareerByteCode Team, and every output is the real result we got. We typed the commands in a Cloud Shell-like Linux VM in the same VPC network as the database (Debian with gcloud, psql and python3); Cloud Shell itself is outside your VPC, so use a small VM like ours.

🎓⭐ Recommended by CareerByteCode · Live trainingAI Engineering and Agentic AI Programโ‚น35,400See the training →

Part A: embeddings, pgvector and RAG in plain English

Vector search with AlloyDB and Gemini
Notes become 768-number embeddings stored next to their text; a question becomes an embedding too; the closest rows go to Gemini as context

Words you will use:

  • Embedding: a list of numbers (here 768) that captures the meaning of a text. Texts with similar meaning get similar lists. We use Vertex AI's text-embedding-005 model.
  • pgvector: a PostgreSQL extension that adds a vector(768) column type and distance operators. <=> is cosine distance: 0 means the same direction (same meaning), larger means less similar.
  • AlloyDB for PostgreSQL: Google's PostgreSQL-compatible managed database, with pgvector built in.
  • HNSW index: a graph index that finds the nearest vectors quickly without comparing every row.
  • RAG (retrieval-augmented generation): find the closest notes, then ask Gemini to answer using only those notes and name its sources.
  • Private service access: AlloyDB gets a private IP inside your VPC through a reserved range and a peering with Google's network.
  • Real-life example: a librarian who understands what you mean. You describe the problem, she brings the three most relevant pages, and then summarises them for you with page numbers.

Before you start

A Google Cloud project with billing linked (the free trial works), and a small VM in the default network with gcloud, psql and Python 3 (we use the same lab VM as in our other database labs).

Part B: the lab

Step 1: private service access

Why: AlloyDB lives in Google's network and reaches your VPC through a peering. You reserve a range for it once per network:

# AlloyDB gets a PRIVATE IP in our VPC: reserve a range for Google's network and peer with it (once per network)
gcloud services enable alloydb.googleapis.com servicenetworking.googleapis.com aiplatform.googleapis.com > /dev/null && echo "AlloyDB, Service Networking and Vertex AI APIs on"
gcloud compute addresses create lab-psa-range --global --purpose=VPC_PEERING --prefix-length=20 --network=default 2>&1 | tail -1
gcloud services vpc-peerings connect --service=servicenetworking.googleapis.com --ranges=lab-psa-range --network=default 2>&1 | tail -1
Operation "operations/acat.p2-PROJECT_NUMBER-09d76532-71fa-4ab6-8c1a-dcdb540f17bf" finished successfully.
AlloyDB, Service Networking and Vertex AI APIs on
Created [https://www.googleapis.com/compute/v1/projects/your-project-id/global/addresses/lab-psa-range].
Operation "operations/pssn.p24-PROJECT_NUMBER-6a3efb95-877a-4ca2-8318-7ffa8655bb5b" finished successfully.

Step 2: the smallest AlloyDB

Why: a cluster plus one 2 vCPU primary instance, zonal (no standby) to keep the cost low. It takes about 5 minutes. The password is made in the lab and never printed:

# The smallest AlloyDB: a cluster and one 2 vCPU zonal primary instance (about 10 minutes)
openssl rand -hex 16 > .dbpass   # a lab-only password, never printed
gcloud alloydb clusters create lab-vec --region=us-east1 --network=default --password="$(cat .dbpass)" 2>&1 | tail -1
gcloud alloydb instances create lab-vec-1 --cluster=lab-vec --region=us-east1 --instance-type=PRIMARY --cpu-count=2 --availability-type=ZONAL 2>&1 | tail -1
gcloud alloydb instances describe lab-vec-1 --cluster=lab-vec --region=us-east1 --format="value(ipAddress)" > .db-ip
gcloud alloydb instances list --format="table(name.basename(),state,machineConfig.cpuCount)"
..........done.
..........done.
NAME       STATUS  CPU_COUNT
lab-vec-1  READY   2

Step 3: a table with a vector column

# pgvector: a column type "vector(768)" that holds an embedding, next to the normal text columns
PGPASSWORD=$(cat .dbpass) psql -h $(cat .db-ip) -U postgres -d postgres -q <<'SQL'
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE docs (id serial PRIMARY KEY, title text NOT NULL, body text NOT NULL, emb vector(768));
SELECT extname || ' ' || extversion AS extension FROM pg_extension WHERE extname = 'vector';
SQL
       extension       
-----------------------
 vector 0.8.7.google-1
(1 row)

AlloyDB ships pgvector (here version 0.8.7). Each row will hold a note's title, its text and its embedding.

Step 4: embed and store 20 notes

Why: the notes are short tips about cloud costs, networking, security and reliability. Save them as docs.json (first lines shown; the full file has 20):

[
 {"title": "Stop idle VMs at night", "body": "Use an instance schedule to stop development VMs every evening and start them in the morning. A stopped VM does not pay for CPU or memory, only for its disk."},
 {"title": "Committed use discounts", "body": "If a workload runs all year, a one or three year commitment lowers the price of vCPUs and memory compared with on-demand use."},

embed.py sends all 20 to the embedding model in one call (signed in with the VM's Google login, no API key) and inserts text plus vector:

# embed.py: turn each document into an embedding (768 numbers that capture its meaning) with Vertex AI,
# and store text + embedding in AlloyDB. Uses the VM's own Google login: no API key anywhere.
import json, os
import psycopg
from google import genai
from google.genai import types

PROJECT = os.environ["PROJECT_ID"]
client = genai.Client(vertexai=True, project=PROJECT, location="us-central1")
docs = json.load(open("docs.json"))
r = client.models.embed_content(model="text-embedding-005", contents=[d["title"] + ". " + d["body"] for d in docs],
                                config=types.EmbedContentConfig(task_type="RETRIEVAL_DOCUMENT", output_dimensionality=768))
with psycopg.connect(host=open(".db-ip").read().strip(), user="postgres", password=open(".dbpass").read().strip(), dbname="postgres") as db:
    for d, e in zip(docs, r.embeddings):
        db.execute("INSERT INTO docs (title, body, emb) VALUES (%s, %s, %s::vector)", (d["title"], d["body"], str(e.values)))
    print("stored", db.execute("SELECT count(*) FROM docs").fetchone()[0], "documents, each with", len(r.embeddings[0].values), "numbers")
# Embed the 20 notes with Vertex AI (text-embedding-005) and store them (Python, signed in as the VM's account)
python3 -m venv .venv && . .venv/bin/activate && pip install -q "google-genai==1.*" "psycopg[binary]==3.2.*" 2>&1 | grep -v "notice" | tail -1
python3 embed.py
stored 20 documents, each with 768 numbers

Step 5: search by meaning

Why: ask.py embeds the question with the same model, asks AlloyDB for the 3 nearest rows with ORDER BY emb <=> question LIMIT 3, and with --search-only stops there:

# ask.py "<question>": find the 3 closest documents in AlloyDB (cosine distance, pgvector), then let Gemini answer
# using ONLY those documents, and show which ones it used. Add --no-context to ask Gemini without the documents.
import os, sys
import psycopg
from google import genai
from google.genai import types

PROJECT = os.environ["PROJECT_ID"]
question = sys.argv[1]
embedder = genai.Client(vertexai=True, project=PROJECT, location="us-central1")
chat = genai.Client(vertexai=True, project=PROJECT, location="global")
q = embedder.models.embed_content(model="text-embedding-005", contents=[question],
                                  config=types.EmbedContentConfig(task_type="RETRIEVAL_QUERY", output_dimensionality=768)).embeddings[0].values
with psycopg.connect(host=open(".db-ip").read().strip(), user="postgres", password=open(".dbpass").read().strip(), dbname="postgres") as db:
    rows = db.execute("SELECT title, body, emb <=> %s::vector AS distance FROM docs ORDER BY distance LIMIT 3", (str(q),)).fetchall()
print("question:", question)
for t, _, dist in rows:
    print(f"  match: {t:<36} distance {dist:.3f}")
if "--search-only" in sys.argv:
    sys.exit(0)
context = "\n".join(f"[{t}] {b}" for t, b, _ in rows)
prompt = (f"Answer in 2 sentences using only these notes. Name the note titles you used in square brackets.\n{context}\n\nQuestion: {question}"
          if "--no-context" not in sys.argv else f"Answer in 2 sentences. Question: {question}")
r = chat.models.generate_content(model="gemini-3.5-flash-lite", contents=prompt, config=types.GenerateContentConfig(temperature=0))
print("answer:", r.text.strip())
# Search by MEANING: none of these questions uses the same words as the note it should find
. .venv/bin/activate
python3 ask.py "how can I pay less for test machines that nobody uses at night?" --search-only
python3 ask.py "my database must not stop while I rename a column" --search-only
question: how can I pay less for test machines that nobody uses at night?
  match: Stop idle VMs at night               distance 0.340
  match: Delete unattached disks              distance 0.422
  match: Committed use discounts              distance 0.426
question: my database must not stop while I rename a column
  match: Database migrations without downtime distance 0.367
  match: Blue-green deployments               distance 0.489
  match: Least privilege service accounts     distance 0.536

"Pay less for test machines nobody uses at night" found Stop idle VMs at night (distance 0.340), and "my database must not stop while I rename a column" found Database migrations without downtime, with almost no shared words.

Search by meaning
AlloyDB ready, pgvector on, 20 notes embedded, and two questions answered by meaning

Step 6: an HNSW index

Why: without an index, AlloyDB compares the question with every row. With 20 rows that is fine; with millions it is not. We create an HNSW index for cosine distance and ask the planner how it would run the search:

# An index for fast search on many rows: HNSW. EXPLAIN shows the planner uses it instead of reading every row
PGPASSWORD=$(cat .dbpass) psql -h $(cat .db-ip) -U postgres -d postgres -qAt <<'SQL'
CREATE INDEX docs_emb_hnsw ON docs USING hnsw (emb vector_cosine_ops);
SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT title FROM docs ORDER BY emb <=> (SELECT emb FROM docs WHERE id = 1) LIMIT 3;
SQL
Limit
  InitPlan 1
    ->  Index Scan using docs_pkey on docs docs_1
          Index Cond: (id = 1)
  ->  Index Scan using docs_emb_hnsw on docs
        Order By: (emb <=> (InitPlan 1).col1)

Index Scan using docs_emb_hnsw: the search uses the index. (We switch sequential scans off only to show this on a tiny table; on a big table the planner picks the index by itself.)

Step 7: RAG with Gemini

Why: search finds the notes; Gemini turns them into an answer and names the notes it used:

# RAG: retrieve the 3 closest notes, then Gemini answers from them and names its sources
. .venv/bin/activate
python3 ask.py "which firewall rule wins if one allows and one denies the same port?"
🎓⭐ Recommended by CareerByteCode · Live trainingLearn AI, Build 12+ AI Agentsโ‚น10,620See the training →
question: which firewall rule wins if one allows and one denies the same port?
  match: Firewall rules and priority          distance 0.242
  match: Cloud NAT for private VMs            distance 0.503
  match: Private Service Access               distance 0.518
answer: Firewall rules are checked by priority, where the lowest number wins. [Firewall rules and priority] Therefore, a deny rule at priority 900 beats an allow rule at 1000. [Firewall rules and priority]

The closest note was "Firewall rules and priority" (distance 0.242), and Gemini's answer cites it twice. The answer comes from our notes, not from the model's general knowledge.

Break it: the wrong vector size

A common bug: someone switches to another embedding model, or asks for fewer dimensions, and searches with a vector of a different size. Here a vector with 3 numbers:

# Break it: search with a vector from a different model or size (here 3 numbers instead of 768)
PGPASSWORD=$(cat .dbpass) psql -h $(cat .db-ip) -U postgres -d postgres -qAt -c "SELECT title FROM docs ORDER BY emb <=> '[0.1, 0.2, 0.3]'::vector LIMIT 1;" 2>&1 | head -1
ERROR:  different vector dimensions 768 and 3

pgvector refuses to compare vectors of different sizes, which is good: a silent wrong answer would be worse.

Fix it: same model, same size

The column type tells you what it expects; embed every question with the same model and size as the documents:

# Fix it: the column type tells you the size; embed questions with the SAME model and size as the documents
PGPASSWORD=$(cat .dbpass) psql -h $(cat .db-ip) -U postgres -d postgres -qAt -c "SELECT 'column emb is ' || format_type(atttypid, atttypmod) FROM pg_attribute WHERE attrelid = 'docs'::regclass AND attname = 'emb';"
. .venv/bin/activate; python3 ask.py "how do I keep pods running during a node upgrade?" --search-only
column emb is vector(768)
question: how do I keep pods running during a node upgrade?
  match: Pod Disruption Budgets               distance 0.343
  match: Blue-green deployments               distance 0.381
  match: Readiness probes                     distance 0.404
Index, RAG, break and fix
The HNSW index in the plan, Gemini answering with its source, the 3-number vector refused, the right size working

POC: prove it works

  • 20 notes embedded and stored with 768-number vectors (Step 4).
  • Questions found the right notes by meaning, with no shared keywords (Step 5).
  • The planner used the HNSW index (Step 6).
  • Gemini answered from the closest note and cited it (Step 7).
  • A wrong-size vector was refused; the right size worked (Break it, Fix it).

Clean up (delete everything)

Why: AlloyDB costs by the hour. We delete the cluster with its instance, then the peering and the reserved range (the peering can only be removed after the database is fully gone, which took a while in our run):

# Clean up right away: the AlloyDB cluster (with its instance) and the peering range
gcloud alloydb clusters delete lab-vec --region=us-east1 --force --quiet 2>&1 | tail -1; rm -f .dbpass .db-ip
for i in $(seq 1 20); do gcloud services vpc-peerings delete --service=servicenetworking.googleapis.com --network=default --quiet > /dev/null 2>&1 && break; sleep 30; done
gcloud compute addresses delete lab-psa-range --global --quiet 2>&1 | tail -1
echo "left: $(gcloud alloydb clusters list --format='value(name)' | wc -l) AlloyDB clusters, $(gcloud compute addresses list --filter='name=lab-psa-range' --format='value(name)' | wc -l) reserved ranges"
..........done.
Deleted [https://www.googleapis.com/compute/v1/projects/your-project-id/global/addresses/lab-psa-range].
WARNING: The following filter keys were not present in any resource : name
left: 0 AlloyDB clusters, 0 reserved ranges

FAQ

Why a database and not a separate vector database? Your text, metadata, permissions and vectors stay in one place, with SQL joins and transactions. For many apps that is simpler. Dedicated vector services make sense at very large scale.

Can AlloyDB create the embeddings itself? Yes, AlloyDB AI can call Vertex AI from SQL (google_ml_integration). We created them in Python here, which works with any PostgreSQL that has pgvector; we did not test the in-database way.

Cosine, L2 or inner product? Use the distance the embedding model was made for; cosine (<=>) is the usual choice for text embeddings. The index must use the matching operator class (vector_cosine_ops).

What does the distance number mean? Lower is closer. There is no universal cut-off; look at your own data and decide what "too far" means, as in the second question, where the third match (0.536) was clearly unrelated.

Can I use Cloud SQL instead? Cloud SQL for PostgreSQL also supports pgvector. AlloyDB adds faster vector index types and in-database AI functions.

Troubleshooting

You seeWhyFix
different vector dimensions 768 and NThe question was embedded with another model or sizeUse the same model and output_dimensionality as the documents
Cannot connect to the databaseYou are outside the VPC (Cloud Shell, your laptop)Connect from a VM in the same network, or use the AlloyDB Auth Proxy
extension "vector" is not availableNot an AlloyDB or pgvector-enabled databaseUse AlloyDB, or Cloud SQL for PostgreSQL with pgvector
Vertex AI PERMISSION_DENIEDThe VM's account cannot call Vertex AIGrant roles/aiplatform.user
Deleting the peering failsThe database is still being deletedWait until the cluster is gone, then retry, as the clean-up does

What next

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →Generative AI Project for Your Resume on Google Cloud⏱ 15 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Expert writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get CareerByteCode Team's next blog by email

One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.

We email you once to confirm. By subscribing you agree to the privacy policy.

💬 Comments (0)

Top · Newest

Sign in to comment

Related blogs

🎓⭐ Recommended by CareerByteCode · Live trainingAI Engineering and Agentic AI Programโ‚น35,400See the training →