Semantic search extends PostgreSQL beyond the string comparison that traditional full text search relies on. Where keyword search matches tokens, embedding-based search captures context and meaning, which improves relevance. Extensions like pgvector and pgai bring this capability into the relational database itself rather than a separate system.
The practical payoffs are improved search accuracy and user experience, handling of ambiguous queries, generated content recommendations, and personalized content.
Loading the extension and defining storage
A demonstration dataset of 3.6 million Amazon reviews in CSV format provides the corpus. The first step is loading the extension and creating a table to hold the data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
cybertec=# CREATE EXTENSION IF NOT EXISTS vector; CREATE EXTENSION cybertec=# CREATE TABLE t_document ( id serial PRIMARY KEY, polarity float, title text, body text, embedding vector(384) ); CREATE TABLE |
Two details matter here. The extension is loaded under the name vector, not pgvector. And a data type named vector is used in the table definition. The extension ships additional types beyond the fixed-dimension vector:
vector: up to 2,000 dimensionshalfvec: up to 4,000 dimensionssparsevec: up to 1,000 non-zero elements
This use case needs a fixed vector of 384 entries, the output size of the chosen model.
Embedding and importing the rows
Embedding means turning a string into a vector, a list of numbers. It is a crucial point: pgvector alone does not convert text into vectors. That work has to be done outside the database, and Python is a straightforward way to do it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
#!/usr/bin/python3 import os import csv from pgvector.psycopg import register_vector import psycopg from sentence_transformers import SentenceTransformer # ----------------------------------------- # EDIT HERE file = '/path/amazon_reviews/train.csv' model_name = "multi-qa-MiniLM-L6-cos-v1" # Set up database connection parameters db_params = { 'host': 'localhost', 'database': 'cybertec', 'user': 'your_user', 'password': 'your_password', 'port': '5432' } # ----------------------------------------- def main(): # create model for embedding model = SentenceTransformer(model_name) # Create the connection conn = psycopg.connect( host=db_params['host'], dbname=db_params['database'], user=db_params['user'], password=db_params['password'], port=db_params['port'] ) register_vector(conn) cur = conn.cursor() lines = 0 with open(file, 'r', encoding='utf-8') as csvfile: csv_reader = csv.reader(csvfile, delimiter=',') for row in csv_reader: lines += 1 emb = model.encode(row[1] + " " + row[2]) cur.execute("INSERT INTO t_document (polarity, title, body, embedding) VALUES (%s, %s, %s, %s) ", (row[0], row[1], row[2], emb)) if lines % 1000 == 0: print("rows imported: %s" % (lines)) conn.commit() conn.commit() if __name__ == "__main__": main() |
The script imports libraries including psycopg, the Python driver used to talk to PostgreSQL, and then runs:
|
1 |
from pgvector.psycopg import register_vector |
This registration step is required so that psycopg can handle vectors properly. The model itself comes from HuggingFace, whose models work with the imported SentenceTransformer class. After the additional data types are registered with the driver and a cursor is opened, the CSV file can be read line by line, one review per line. The conversion happens here:
|
1 |
emb = model.encode(row[1] + " " + row[2]) |
The title and body are fed to the HuggingFace model, which returns a vector that is inserted into the table. A COMMIT is issued every few thousand lines. It is not strictly necessary, but it allows basic checks while embedding is still running. Most of the elapsed time is spent in the model, not the database, so parallelizing the work helps.
|
1 2 3 4 5 6 7 8 9 10 |
$ time ./handle_comments.py rows imported: 1000 rows imported: 2000 ... rows imported: 3599000 rows imported: 3600000 real 325m35.287s user 311m50.151s sys 2m11.664s |
Execution is fairly slow, and running the import in parallel to use all available CPUs is worth considering. The measurements here came from a single core.
Similarity, distance and distance operators
A stored record then looks something like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
cybertec=# \x Expanded display is on. cybertec=# SELECT * FROM t_document LIMIT 1; -[ RECORD 1 ]----------------------------------------------------------------- id | 1 polarity | 2 title | Stuning even for the non-gamer body | This sound track was beautiful! It paints the senery in your mind so well I would recomend it even to people who hate vid. game music! I have played the game Chrono Cross but out of all of the games I have ever played it has the best music! It backs away from crude keyboarding and takes a fresher step with grate guitars and soulful orchestras. It would impress anyone who cares to listen! embedding | [-0.051104408,-0.06477496,0.053947136,-0.090758994,-0.11248113, 0.021052703,0.040592767,0.033487927,0.008018044,0.030972324, -0.09552986,-0.041627012,-0.032203916,-0.08294443,-0.0076782834, 0.039493423,0.06395606,-0.014506877,0.023361415,-0.022605361, ... 0.0133066205,-0.003375273,-0.003283798,-0.08727879,0.02891936] |
The input data contains typos, and this is a reminder that input cannot be assumed to be clean. The central idea is that each text becomes a set of numbers, and the goal is to find vectors that are close to one another. Distance can be computed in several ways:
Dot product and cosine distance are common choices; the illustration above shows what they mean geometrically. Similarity itself is simple to picture:
The real computation happens in hyperspace, but a simple mental image is useful.
Querying by nearest neighbors
With the data loaded, a search string can be turned into a query. The following returns the best matches for a given string:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
#!/usr/bin/python3 import os, sys import psycopg from pgvector.psycopg import register_vector from sentence_transformers import SentenceTransformer # ----------------------------------------- # EDIT HERE model_name = "multi-qa-MiniLM-L6-cos-v1" # Set up database connection parameters db_params = { 'host': 'localhost', 'database': 'cybertec', 'user': 'your_user', 'password': 'your_password', 'port': '5432' } # ----------------------------------------- def main(): if len(sys.argv) < 2: print("Usage: please provide a query string") sys.exit(1) query_string = sys.argv[1] print("Querying database for: '%s'" % (query_string)) # create model for embedding model = SentenceTransformer(model_name) # Create the connection conn = psycopg.connect( host=db_params['host'], dbname=db_params['database'], user=db_params['user'], password=db_params['password'], port=db_params['port'] ) register_vector(conn) cur = conn.cursor() # transform query string into vector emb = model.encode(query_string) result = conn.execute('SELECT embedding <=> %s AS distance, title, body FROM t_document ORDER BY embedding <=> %s LIMIT 5', (emb, emb)).fetchall() for row in result: print("Result item: ") print(f" Distance: " + str(row[0])) print(f" Title: : " + str(row[1])) print(f" Body : " + str(row[2])) print ("\n") if __name__ == "__main__": main() |
Roughly the first 40 lines repeat the earlier setup: define the model, connect to the database, read a command line argument, and print an error message if no argument was passed. The substantive part is:
|
1 |
emb = model.encode(query_string) |
The same model as before converts the search string into a vector, and the result is a KNN search: sort by distance to the target element and return the top rows, which is what the trailing loop consumes. The <=> in the ORDER BY clause is a distance operator supplied by pgvector. The available operators are:
- inner product:
<#> - cosine distance:
<=> - L1 distance:
<+>
Cosine distance was used in this example.
What a result set looks like
Searching for "mooney airplane navigation literature" illustrates the behavior — the intent being reviews connected to Mooney Aircraft and to books on navigation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
$ ./query_comments.py "mooney airplane navigation literature" Querying database for: 'mooney airplane navigation literature' Result item: Distance: 0.3748793229388776 Title: : Great Book! Body : I gave this book as a gift to a person who has an interest in historic aircraft. The recipient of the gift was very, very appeciative and thought it was an excellent reference. Result item: Distance: 0.3833693082144648 Title: : Decent airplane book but nothing more Body : I would not recommend anyone buying this book. If you are curious, check it out from the library. The character development is shallow and the story is generally not well written. The readers who have enjoyed this must be very young, and therefore not exposed to the better writers of the past. I put a lot of blame on the editor of this book, as it is his job to assist the writer in preparing the manuscript. I will not read this author again. Result item: Distance: 0.38802472913338903 Title: : Super, Great Information Body : If you are an airplane lover, you should order this book. Great pictures and facts. Result item: Distance: 0.3974898514887534 Title: : Wonderfully done book Body : This book is wonderful, I love seeing aircraft put to sleep in the boneyards! Philip D. Chinnery has an awesome book here! I would recommend it to all aviation enthusiasts to buy, it's well worth it! Result item: Distance: 0.39884170286903886 Title: : Good book Body : Very good way to adventure on this Plane but the content is a little bit on the less exciting part but a good book over all |
None of the returned entries concerns Mooney aircraft specifically, which is reasonable since navigation is not tied to a brand. The word "literature" is not matched literally either, but the system has inferred that it relates to books, so many book reviews come back because that is what the dataset contains. The output is relevant and clearly better than what standard FTS would produce.
Adding a vector index
Without an index, PostgreSQL scans all 3.6 million rows to produce those results, which does not scale well. An index addresses this:
|
1 2 3 4 5 |
cybertec=# SET maintenance_work_mem TO '64 GB'; SET cybertec=# CREATE INDEX ON t_document USING ivfflat (embedding vector_cosine_ops); CREATE INDEX |
On the machine used for the demo, the index built quickly with maintenance_work_mem set to 64 GB, since that setting governs index creation. Once the index exists, database-side performance improves significantly. The Python script still needs time for loading libraries and models, but the query itself becomes extremely fast.
