vector-search-demo/README.md

Vector Image Search — PostgreSQL/pgvector vs Oracle 26ai

A comparative demo that vectorizes JPEG photos using the CLIP neural network model and stores the embeddings in two different databases: PostgreSQL with pgvector and Oracle AI Database 26ai. Users search the photo collection by typing plain-text keywords such as "trees" or "water" and receive results ranked by semantic similarity.

Three backends are implemented, demonstrating two fundamental approaches to vector embedding:

Backend	Port	Embedding location	Model
PostgreSQL + pgvector	8000	Python (external)	sentence-transformers CLIP
Oracle 26ai (Python embedding)	8001	Python (external)	sentence-transformers CLIP
Oracle 26ai (in-database embedding)	8002	Inside Oracle SQL	Oracle native CLIP_TXT

The key architectural difference: in the third backend, the text query is embedded inside a SQL statement using Oracle's VECTOR_EMBEDDING() function — no Python ML library is loaded or called at search time.

---

Architecture overview

                        116 JPEG photos
                              │
                              ▼
              ┌───────────────────────────────┐
              │   CLIP model (clip-ViT-B-32)  │
              │   sentence-transformers lib   │
              │   → 512-dimensional float vec │
              └──────────────┬────────────────┘
                             │
              ┌──────────────┴──────────────┐
              │                             │
              ▼                             ▼
  ┌──────────────────────┐    ┌──────────────────────┐    ┌───────────────────────┐
  │  PostgreSQL 18       │    │  Oracle 26ai         │    │  Oracle 26ai          │
  │  + pgvector 0.8.2    │    │  (version 23.26.1)   │    │  (version 23.26.1)    │
  │  database:           │    │  PDB: FREEPDB1       │    │  PDB: FREEPDB1        │
  │  vectors_demo        │    │  schema: VECTORS_USER│    │  schema: VECTOR       │
  │  HNSW index          │    │  HNSW index          │    │  HNSW index           │
  └────────┬─────────────┘    └──────────┬───────────┘    └──────────┬────────────┘
           │                             │                            │
           ▼                             ▼                            │
  Python CLIP encode          Python CLIP encode          Text stays in Oracle SQL
  (search query)              (search query)              VECTOR_EMBEDDING(CLIP_TXT
                                                          USING :q AS data)
           │                             │                            │
           ▼                             ▼                            ▼
  ┌──────────────┐             ┌──────────────┐             ┌──────────────────┐
  │  FastAPI     │             │  FastAPI     │             │  FastAPI         │
  │  main.py     │             │  main_oracle │             │  main_oracle_    │
  │  port 8000   │             │  port 8001   │             │  indb.py         │
  └──────┬───────┘             └──────┬───────┘             │  port 8002       │
         │                            │                     └────────┬─────────┘
         ▼                            ▼                              ▼
  /ui/ (pgvector)            /ui/ (Oracle 26ai)          /ui/ (Oracle In-DB)

---

Project structure

vector-search-demo/
├── start.sh                         # Start all three backends
├── stop.sh                          # Stop all three backends
├── photos/                          # 116 JPEG photos (gitignored)
├── pgvector-demo/
│   ├── sql/
│   │   └── setup.sql                # Create table and HNSW index
│   ├── backend/
│   │   ├── .env                     # PostgreSQL credentials, photo path
│   │   ├── db.py                    # PostgreSQL connection factory
│   │   ├── embedder.py              # CLIP model wrapper
│   │   ├── index_images.py          # One-time indexing script
│   │   └── main.py                  # FastAPI app (port 8000)
│   └── frontend/
│       └── index.html               # Search UI (served at /ui/)
└── oravector-demo/
    ├── sql/
    │   ├── setup_vectors_user.sql   # Create vectors_user, table and HNSW index
    │   └── setup_vector_schema.sql  # Create VECTOR user, load ONNX models, FOTO_VEKTOR table
    ├── backend/
    │   ├── .env                     # Oracle credentials, photo path
    │   ├── db_oracle.py             # Oracle connection factory
    │   ├── embedder.py              # CLIP model wrapper (identical to pgvector)
    │   ├── index_images_oracle.py   # One-time indexing script (Python embedding, VECTORS_USER)
    │   ├── index_images_indb.py     # One-time indexing script (in-DB embedding, VECTOR schema)
    │   ├── main_oracle.py           # FastAPI app — Python embedding (port 8001)
    │   └── main_oracle_indb.py      # FastAPI app — in-database embedding (port 8002)
    └── frontend/
        ├── index.html               # Search UI (Oracle 26ai, served at port 8001 /ui/)
        └── indb/
            └── index.html           # Search UI (Oracle In-DB, served at port 8002 /ui/)

---

System components

PostgreSQL (Docker)

Property	Value
Image	pgvector/pgvector:pg18
Version	PostgreSQL 18
pgvector version	0.8.2
Host port	5433 (mapped to 5432 inside container)
Database	vectors_demo
User	dl
Compose file	~/docker/postgresql/docker-compose.yml

Start PostgreSQL:

cd ~/docker/postgresql && docker compose up -d

The pgvector/pgvector:pg18 image includes pgvector pre-installed. See the Setup from scratch section for first-time database setup.

Oracle 26ai (Podman container)

Property	Value
Product	Oracle AI Database 26ai Free
Version	23.26.1.0.0
Container name	oracle.free
Host port	37611 (mapped to 1521 inside container)
Pluggable Database	FREEPDB1
Schema users	VECTORS_USER, VECTOR

Oracle vector memory — the HNSW index is held entirely in the SGA's Vector Memory Area. This is already configured:

ALTER SYSTEM SET vector_memory_size = 512M SCOPE=SPFILE;

After restart, the SGA confirms: Vector Memory Area: 536870912 bytes (512 MB).

Python packages

Package	Version	Used by	Purpose
sentence-transformers	5.3.0	both	CLIP model loading and inference
torch	2.11.0	both	Neural network runtime for CLIP
Pillow	10.2.0	both	JPEG loading and colour conversion
fastapi	0.135.2	both	REST API framework
uvicorn	0.42.0	both	ASGI server
python-dotenv	1.0.1	both	.env file support
psycopg2-binary	2.9.11	pgvector only	PostgreSQL driver
oracledb	3.4.2	Oracle only	Oracle driver (thin mode, no client libs needed)

Install packages:

pip3 install -r pgvector-demo/backend/requirements.txt --break-system-packages
pip3 install -r oravector-demo/backend/requirements.txt --break-system-packages

---

Vectorization

Model: CLIP (clip-ViT-B-32)

CLIP (Contrastive Language–Image Pretraining) is a neural network model developed by OpenAI. It was trained on hundreds of millions of image–text pairs and maps both images and text into the same 512-dimensional vector space. This enables searching images by plain-text query without any manual labelling or tagging.

Property	Value
Architecture	Vision Transformer ViT-B/32
Output dimension	512 floats
Similarity metric	Cosine similarity
Weights source	Hugging Face Hub: sentence-transformers/clip-ViT-B-32
Downloaded to	~/.cache/huggingface/hub/ on first run

Why cosine similarity? CLIP vectors have varying magnitudes. Cosine similarity normalises for magnitude and measures only the direction — the angle between two vectors — which reliably captures semantic relatedness regardless of vector scale.

The embedder.py module is identical in both projects. It lazily loads the model on first call and exposes two functions:

Function	Input	Output
embed_image(path)	Filesystem path to a JPEG	list[float] — 512 values
embed_text(text)	Plain-text query string	list[float] — 512 values

At search time, the text query is embedded into the same vector space as the photos. The database then finds the photos whose vectors point in the most similar direction.

---

Database schemas

PostgreSQL + pgvector

-- database: vectors_demo  (PostgreSQL 18)
CREATE EXTENSION vector;        -- pgvector 0.8.2

CREATE TABLE images (
    id        SERIAL PRIMARY KEY,
    filename  TEXT NOT NULL UNIQUE,
    filepath  TEXT NOT NULL,
    embedding vector(512)        -- pgvector type, 512 dimensions
);

CREATE INDEX images_embedding_idx
    ON images USING hnsw (embedding vector_cosine_ops);

Oracle 26ai — schema VECTORS_USER (Python embedding backend)

-- PDB: FREEPDB1, schema: VECTORS_USER
-- Photos stored as file paths on the app server filesystem

CREATE TABLE images (
    id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    filename  VARCHAR2(255) NOT NULL UNIQUE,
    filepath  VARCHAR2(1000) NOT NULL,
    embedding VECTOR(512, FLOAT32)   -- native Oracle type, typed at definition
);

CREATE VECTOR INDEX images_embedding_idx
    ON images(embedding)
    ORGANIZATION INMEMORY NEIGHBOR GRAPH   -- HNSW (in-memory)
    WITH DISTANCE COSINE
    WITH TARGET ACCURACY 95
    PARAMETERS (type HNSW, neighbors 32, efconstruction 200);

Oracle 26ai — schema VECTOR (in-database embedding backend)

-- PDB: FREEPDB1, schema: VECTOR
-- Photos stored as BLOBs inside Oracle — no filesystem access at query time

CREATE TABLE foto_vektor (
    id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    filename  VARCHAR2(100),
    foto      BLOB,                  -- full JPEG stored in Oracle
    foto_vek  VECTOR                 -- embedding computed by CLIP_IMG ONNX model
);

CREATE VECTOR INDEX foto_vektor_idx
    ON foto_vektor(foto_vek)
    ORGANIZATION INMEMORY NEIGHBOR GRAPH
    WITH DISTANCE COSINE
    WITH TARGET ACCURACY 95
    PARAMETERS (type HNSW, neighbors 32, efconstruction 200);

Key difference between the two Oracle schemas:

Aspect	VECTORS_USER	VECTOR
Photo storage	File path (filesystem)	BLOB (inside Oracle)
Embedding at index time	Python CLIP	Oracle VECTOR_EMBEDDING(CLIP_IMG)
Embedding at query time	Python CLIP	Oracle VECTOR_EMBEDDING(CLIP_TXT)
Indexed by	index_images_oracle.py	index_images_indb.py

Key schema differences:

Aspect	PostgreSQL/pgvector	Oracle 26ai
Extension needed	CREATE EXTENSION vector	Built-in, no extension
Vector column	vector(512) — dimension only	VECTOR(512, FLOAT32) — dimension + element type
Primary key	SERIAL (auto-increment)	NUMBER GENERATED ALWAYS AS IDENTITY
Text columns	TEXT (unlimited)	VARCHAR2(n) (length required)
HNSW syntax	USING hnsw (col vector_cosine_ops)	ORGANIZATION INMEMORY NEIGHBOR GRAPH
IVF syntax	USING ivfflat (col vector_cosine_ops)	ORGANIZATION NEIGHBOR PARTITIONS
Accuracy target	Implicit (set via index params)	WITH TARGET ACCURACY 95 (explicit %)
Memory prereq	None	vector_memory_size > 0 in SGA

---

Backend modules

Connection factories

db.py (PostgreSQL): Reads DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD from .env and returns a psycopg2 connection.

db_oracle.py (Oracle): Reads ORA_HOST, ORA_PORT, ORA_SERVICE, ORA_USER, ORA_PASSWORD (and ORA_USER_INDB, ORA_PASSWORD_INDB for the in-DB backend) from .env and returns an oracledb connection. The DSN is assembled as host:port/service. Runs in thin mode — no Oracle Instant Client installation is required on the host.

---

Indexing scripts

All three scripts are idempotent: they check for existing rows and skip already-indexed photos. Each photo is committed individually so a crash does not lose prior work.

	index_images.py	index_images_oracle.py	index_images_indb.py
Schema	PostgreSQL vectors_demo	Oracle VECTORS_USER	Oracle VECTOR
Run command	python3 index_images.py	python3 index_images_oracle.py	python3 index_images_indb.py
Photo data sent	File path	File path	Full JPEG as BLOB
Embedding	Python CLIP	Python CLIP	Oracle VECTOR_EMBEDDING(CLIP_IMG)
Vector bind	Python list	array.array("f", ...)	Computed inside Oracle
Avg runtime (3 runs, CPU)	12.1 s	12.1 s	13.6 s

Why array.array for index_images_oracle.py? The python-oracledb driver does not accept a plain Python list for a VECTOR column. The data must be a Python array.array with typecode "f" (32-bit float), matching the FLOAT32 declaration in the Oracle column type.

Why two SQL statements in index_images_indb.py? Oracle raises ORA-24816 if a BLOB bind variable appears before another bind in the same VALUES clause. The script works around this by inserting the BLOB first, then updating the vector in a second statement — letting Oracle read the stored BLOB to compute the embedding internally.

---

FastAPI applications

All three apps expose identical endpoints:

Endpoint	Description
GET /search?q=<text>&limit=<n>	Embed query, run nearest-neighbour search, return ranked results
GET /stats	Return count of indexed photos
GET /photos/<filename>	Serve original JPEG from the photos directory
GET /ui/	Serve the search frontend (HTML)

Search query comparison:

PostgreSQL (main.py, port 8000):

SELECT filename, 1 - (embedding <=> $1::vector) AS score
FROM images
ORDER BY embedding <=> $1::vector
LIMIT $2

Oracle 26ai (main_oracle.py, port 8001):

SELECT filename,
       1 - VECTOR_DISTANCE(embedding, :vec, COSINE) AS score
FROM images
ORDER BY VECTOR_DISTANCE(embedding, :vec, COSINE)
FETCH FIRST :lim ROWS ONLY

Key query differences:

Aspect	PostgreSQL/pgvector	Oracle 26ai
Distance operator	<=> (cosine distance operator)	VECTOR_DISTANCE(col, val, COSINE)
Cast required	$1::vector — explicit cast	No cast, column type is enforced
Top-N clause	LIMIT n	FETCH FIRST n ROWS ONLY
Bind style	$1, $2 positional (psycopg2)	:name named binds (dict)
Repeated param	$1 can appear multiple times	Same :name can appear multiple times
Score formula	1 - (embedding <=> val)	1 - VECTOR_DISTANCE(...)

In both cases 1 − distance converts cosine distance (0 = identical) into a similarity score (1.0 = identical), displayed as a percentage in the frontend.

---

Frontend

Three single-file HTML frontends, each served by its own backend at /ui/:

	pgvector	Oracle 26ai	Oracle In-DB
URL	http://localhost:8000/ui/	http://localhost:8001/ui/	http://localhost:8002/ui/
Badge colour	Blue	Red	Purple
File	pgvector-demo/frontend/index.html	oravector-demo/frontend/index.html	oravector-demo/frontend/indb/index.html

Features: search box, Enter-key support, suggestion chips (trees, water, people, buildings, sky, street, night, cars), result grid with thumbnails and similarity scores in percent. Click any photo to view it full size in a lightbox overlay; close with a click anywhere or Escape.

---

Configuration (.env files)

Each backend reads its credentials and paths from a .env file in its backend/ directory. These files are gitignored — copy the .env.example template and fill in the values before running.

pgvector-demo/backend/.env

cp pgvector-demo/backend/.env.example pgvector-demo/backend/.env

Variable	Description	Example
DB_HOST	PostgreSQL host	localhost
DB_PORT	PostgreSQL host port	5433
DB_NAME	Database name	vectors_demo
DB_USER	Database user	dl
DB_PASSWORD	Database password	—
PHOTOS_DIR	Absolute path to the photos folder	/home/user/photos

oravector-demo/backend/.env

cp oravector-demo/backend/.env.example oravector-demo/backend/.env

Variable	Description	Example
ORA_HOST	Oracle host	localhost
ORA_PORT	Oracle host port	37611
ORA_SERVICE	Oracle service name (PDB)	FREEPDB1
ORA_USER	User for Python-embedding backend	vectors_user
ORA_PASSWORD	Password for ORA_USER	—
ORA_USER_INDB	User for in-database embedding backend	vector
ORA_PASSWORD_INDB	Password for ORA_USER_INDB	—
PHOTOS_DIR	Absolute path to the photos folder	/home/user/photos

---

Setup from scratch

0. Python dependencies

Install all required packages for both backends:

pip3 install -r pgvector-demo/backend/requirements.txt --break-system-packages
pip3 install -r oravector-demo/backend/requirements.txt --break-system-packages

1. PostgreSQL

Start the container:

cd ~/docker/postgresql && docker compose up -d

Create the database:

docker exec postgresql-database-1 psql -U dl -d pgdl -c "CREATE DATABASE vectors_demo;"

Run the setup script (creates the pgvector extension, images table, and HNSW index):

docker exec -i postgresql-database-1 psql -U dl -d vectors_demo -f - \
  < pgvector-demo/sql/setup.sql

Copy photos and index them:

cd pgvector-demo/backend && python3 index_images.py

---

2. Oracle 26ai — Python embedding backend

Configure vector memory (once, requires Oracle restart):

podman exec oracle.free bash -c "sqlplus -s / as sysdba <<'EOF'
ALTER SYSTEM SET vector_memory_size = 512M SCOPE=SPFILE;
SHUTDOWN ABORT;
STARTUP;
EXIT;
EOF"

Run the setup script (creates vectors_user, the images table, and HNSW index):

Copy the script into the container and run it as SYSDBA:

podman cp oravector-demo/sql/setup_vectors_user.sql oracle.free:/tmp/
podman exec oracle.free bash -c "sqlplus -s / as sysdba @/tmp/setup_vectors_user.sql"

Index the photos:

cd oravector-demo/backend && python3 index_images_oracle.py

---

3. Oracle 26ai — in-database embedding backend

This backend requires CLIP ONNX models loaded into the Oracle database. The setup is more involved and is intended to be done once by an administrator.

Prerequisites:

• CLIP ONNX model files (clip_txt.onnx, clip_img.onnx) present in the Oracle VEC_DUMP directory inside the container (typically /opt/oracle/dbs/vec_dump/)
• The clip_txt.onnx model must use CLS-token pooling (position 0), not the standard EOS-token pooling — Oracle's ONNX validator rejects models that use ArgMax on input_ids. See the Oracle in-database embedding section for details.

Run the setup script (creates VECTOR user, loads ONNX models, creates FOTO_VEKTOR table):

podman cp oravector-demo/sql/setup_vector_schema.sql oracle.free:/tmp/
podman exec oracle.free bash -c "sqlplus -s / as sysdba @/tmp/setup_vector_schema.sql"

Add HNSW index (after the table is created):

podman exec oracle.free bash -c "sqlplus -s 'vector/Vektor@localhost:1521/FREEPDB1' <<'EOF'
CREATE VECTOR INDEX foto_vektor_idx
    ON VECTOR.FOTO_VEKTOR(foto_vek)
    ORGANIZATION INMEMORY NEIGHBOR GRAPH
    WITH DISTANCE COSINE WITH TARGET ACCURACY 95
    PARAMETERS (type HNSW, neighbors 32, efconstruction 200);
EXIT;
EOF"

Populate FOTO_VEKTOR using the indexing script (reads JPEGs from PHOTOS_DIR, sends them as BLOBs to Oracle, which computes embeddings via VECTOR_EMBEDDING(CLIP_IMG)):

cd oravector-demo/backend && python3 index_images_indb.py

---

Running the applications

Start all backends

./start.sh

This starts all three backends concurrently. Press Ctrl+C to stop all.

Stop all backends

./stop.sh

Start backends individually

# PostgreSQL backend
cd pgvector-demo/backend && uvicorn main:app --host 0.0.0.0 --port 8000

# Oracle backend — Python embedding
cd oravector-demo/backend && uvicorn main_oracle:app --host 0.0.0.0 --port 8001

# Oracle backend — in-database embedding
cd oravector-demo/backend && uvicorn main_oracle_indb:app --host 0.0.0.0 --port 8002

Re-index after adding photos

# PostgreSQL
cd pgvector-demo/backend && python3 index_images.py

# Oracle VECTORS_USER (Python embedding)
cd oravector-demo/backend && python3 index_images_oracle.py

# Oracle VECTOR (in-database embedding)
cd oravector-demo/backend && python3 index_images_indb.py

---

Oracle in-database embedding

The VECTOR schema, its ONNX models, and the FOTO_VEKTOR table were manually set up by the administrator — they are not part of a standard Oracle 26ai installation. The setup involved:

1. Creating a VECTOR database user
2. Exporting CLIP (ViT-B/32) to ONNX format and loading the models via DBMS_VECTOR.LOAD_ONNX_MODEL
3. Creating the FOTO_VEKTOR table and HNSW index
4. Populating FOTO_VEKTOR using index_images_indb.py

The resulting models and table are:

Object	Type	Input	Output	Purpose
VECTOR.CLIP_TXT	ONNX model	VARCHAR2 text	VECTOR(512)	Embed text queries at search time
VECTOR.CLIP_IMG	ONNX model	BLOB image	VECTOR(512)	Embed images at index time
VECTOR.FOTO_VEKTOR	Table	—	—	Stores filenames, image BLOBs, and vectors

These are called with the VECTOR_EMBEDDING() SQL function. The table VECTOR.FOTO_VEKTOR stores images as BLOBs alongside their CLIP_IMG-computed embeddings.

The complete in-database search query:

SELECT filename,
       1 - VECTOR_DISTANCE(
               foto_vek,
               VECTOR_EMBEDDING(CLIP_TXT USING :q AS data),
               COSINE
           ) AS score
FROM VECTOR.FOTO_VEKTOR
ORDER BY VECTOR_DISTANCE(
             foto_vek,
             VECTOR_EMBEDDING(CLIP_TXT USING :q AS data),
             COSINE
         )
FETCH FIRST 12 ROWS ONLY

The Python FastAPI backend (main_oracle_indb.py) passes only the raw text string to Oracle via a bind variable :q. Oracle tokenizes the text, runs the CLIP_TXT ONNX model internally, produces the 512-dim vector, and performs the similarity search — all within one SQL statement. No Python ML library is involved at query time.

Why Oracle can ship CLIP as an in-database ONNX model: Oracle's DBMS_VECTOR.LOAD_ONNX_MODEL requires the model's ONNX graph to use input_ids in a single Gather node (embedding lookup only). CLIP's standard export uses input_ids additionally in ArgMax for EOS-token pooling, which Oracle's validator rejects. The manually loaded CLIP_TXT model in the VECTOR schema uses CLS-token pooling (position 0) instead, which produces a simpler graph that Oracle accepts. The cosine similarity between EOS-pooling and CLS-pooling variants is ~0.70.

---

Performance comparison

Measured on this installation (CPU only, no GPU):

Metric	PostgreSQL + pgvector	Oracle 26ai (Python embed)	Oracle 26ai (in-DB embed)
Photos indexed	116	116	116
Avg indexing time (3 runs, CPU)	12.1 s	12.1 s	13.6 s
Index type	HNSW (on disk)	HNSW (in-memory)	HNSW (in-memory)
Memory required	None	512 MB SGA	512 MB SGA
Photo storage	File path (filesystem)	File path (filesystem)	BLOB (in Oracle)
Python CLIP at query time	Yes	Yes	No
Embedding at index time	Python CLIP	Python CLIP	Oracle VECTOR_EMBEDDING(CLIP_IMG)
Embedding at query time	Python CLIP	Python CLIP	Oracle VECTOR_EMBEDDING(CLIP_TXT)
VECTOR_EMBEDDING() used	No	No	Yes
Oracle schema	—	VECTORS_USER	VECTOR

Note: indexing time is dominated by CLIP inference for backends 1 and 2 (CPU, no GPU). Backend 3 is slightly slower because each photo is transferred as a full JPEG BLOB to Oracle over the network before Oracle computes the embedding internally.

---

Presentation

The presentation Vektoren in der Datenbank.pptx is generated by make_presentation.py:

python3 make_presentation.py

Start the slideshow directly (skips the LibreOffice UI):

libreoffice --impress --show "Vektoren in der Datenbank.pptx"

Or use the local helper script (gitignored):

./present.sh

Press Esc to exit the presentation.