Published at Dec 5, 2024
AI development for all developers, not just AI experts. Build your AI app with Tiger Cloud today.
Written by Haziqa Sajid and Ana Tavares
Search functionality is the backbone of most modern applications, enabling users to retrieve relevant information efficiently. Whether looking up a product in an e-commerce store like Amazon or finding a document in a legal archive, the ability to search through vast amounts of data effectively and efficiently is important. Over the years, search methods have expanded from traditional keyword-based (full-text) searches to advanced approaches like vector search.
Choosing between full-text and vector search depends on your use case and the data type you’re working with. Both methods are essential for retrieving information efficiently, but they solve different problems.
In this article, we’ll explain each search method and how it works and provide guidance on when to use one method over the other—or a combination of both—to achieve optimal results.
Full-text search is a technique that involves matching user-provided keywords or phrases against the actual text content of documents. Beyond finding exact matches, it can account for variations in words or phrases to deliver more relevant results.
At its core, full-text search relies on indexing the contents of documents. It breaks down text into individual words or tokens, creating an index that maps these tokens to their locations within the documents. This index allows the search engine to quickly locate and retrieve documents that contain the keywords specified in a user's query.
For example, PostgreSQL’s built-in full-text search is a robust and efficient solution for handling text-heavy queries. It is well-suited for applications like e-commerce platforms (for product searches) and help desk systems (for knowledge base queries).
Prefix and infix searching: This allows you to search for parts of words, like finding "apple" by searching "app" or finding "highlight" by searching "light."
Morphology processing: This includes stemming and lemmatization. Stemming finds different forms of a word, like "running" "and ran," all stemming from "run." Lemmatization finds the base form of a word, so "running" becomes "run."
Fuzzy searching: This helps find results even when the query contains typos.
Exact result count: Full-text search provides the total number of documents that match the search criteria.
Vector, or semantic search, is a more advanced method that uses machine learning to understand the meaning behind the words in a search query and the documents being searched.
Unlike traditional keyword-based search, vector search retrieves results by analyzing the similarity between vectors or embeddings, which are numerical representations of data. It excels at unstructured or high-dimensional datasets like text, images, and audio files.
Open-source extensions like pgai and pgvectorscale simplify and improve vector search for PostgreSQL users. They enable artificial intelligence (AI)-driven use cases such as large-scale semantic searches and recommendations.
Here is the vector search's core mechanism and how it works.
Vectorization: Machine learning (ML) models, such as sentence transformers or OpenAI embeddings, convert the search query text and the documents into numerical representations. These representations are called vectors or embeddings.
Embedding space: These vectors are plotted in a multi-dimensional space, where the distance between vectors reflects the semantic similarity between the original pieces of text. Documents with similar meanings have vectors that are closer together in this space.
Nearest neighbors: The search engine uses algorithms like k-nearest neighbors (KNN) to find the vectors in the embedding space that are closest to the query vector. These closest vectors represent the documents that are most semantically similar to the search query.
Context awareness: It understands a search query's overall intent and meaning.
Flexibility: It's excellent for handling open-ended questions or searches expressed in natural language.
Multi-modal search: It can be used to search across different types of content, like text and images, by representing them in the same embedding space.
Handling synonyms: It can retrieve documents that use different words but have the same meaning, like finding documents about "cars" when the search is for "automobiles."
Read why we think RAG is more than vector search.
Feature | Full-Text Search | Vector Search |
Data Type | Structured or semi-structured text | Unstructured or high-dimensional data |
Query Type | Keyword or phrase matching | Similarity matching |
Primary Use Case | Exact matches, metadata filtering | Semantic understanding, recommendations |
Technology Examples | PostgreSQL full-text search, Elasticsearch | pgvectorscale, FAISS |
Full-text search excels in scenarios where precise, structured queries are essential. It provides high efficiency and straightforward implementation. Below are some of the key use cases where full-text search proves to be the optimal choice:
When users seek information based on specific keywords or phrases, full-text search quickly identifies documents with exact terms from a query. This capability shines in two important areas.
Legal documents: The precise wording of documents is important in the legal field. Lawyers and legal researchers frequently need to locate specific clauses, precedents, or legal definitions. Full-text search allows them to search for exact terms or phrases within legal databases or document repositories. This ensures they retrieve the precise information required for their case or research.
E-commerce searches: E-commerce platforms rely heavily on full-text search to help customers find specific products quickly and efficiently. Users often search for products using specific keywords like brand names, product models, or key features. For instance, a customer searching for a "Sony WH-1000XM4" headphones on Amazon expects the search results to display precisely that product. Full-text search allows e-commerce platforms to index product catalogs by relevant attributes.
Full-text search engines often incorporate fuzzy logic to accommodate minor typos or misspellings in user queries. This feature improves the user experience by allowing for slight variations in spelling without derailing the search process.
For example, a user searching for "restaurant" might accidentally type "restaraunt." Fuzzy logic would recognize this as a potential misspelling and still return relevant results, preventing a failed search due to a minor error.
Due to its relatively simple algorithms and indexing structure, full-text search is well-suited for resource-constrained environments where computational power or storage capacity is limited. For instance, a small business with a limited budget might opt for a basic full-text search engine for its website instead of a more resource-intensive vector search solution.
Vector search is an invaluable tool for applications requiring a deep understanding of meaning and context. Go with vector search in customer support, content recommendations, and unstructured data handling scenarios.
Vector search is designed to interpret the intent behind queries. It is ideal for applications where understanding meaning is more important than matching specific words.
Customer support: When a customer contacts support with a complex issue, a vector search system can analyze their query to understand its meaning and search a knowledge base for relevant solutions. This allows the support team to offer accurate assistance, even if customers have trouble expressing their problems.
Content recommendations: Streaming platforms and online retailers use vector search in their recommendation engines to suggest content aligned with user preferences. For instance, Spotify analyzes listening history to recommend artists or playlists with similar styles. This ability of vector search improves personalized recommendations, boosting user satisfaction and engagement.
Full-text search can struggle with unstructured or messy data, such as text filled with typos, slang, or colloquialisms. Vector search excels in these scenarios because it can learn and adapt to linguistic variations.
Handling typos, synonyms, and informal language: Social media platforms and chat applications often deal with unstructured or informal data. Vector search’s ability to handle synonyms, typos, and slang makes it indispensable in these contexts.
Search across different content types: Applications like multimedia search engines or digital archives often include cross-modal queries, such as when users search for “pictures of dogs playing in the park.” A vector search engine could convert both the query and the images in its database into vectors, allowing it to identify images that semantically match the query.
In many cases, combining full-text search and vector search can deliver the best results. For example, you can use full-text search to narrow down a dataset and vector search to rank the results by semantic similarity.
Initial filtering with full-text search: The process begins by using a full-text search to quickly narrow down the dataset based on specific keywords or phrases present in the user's query. This step acts as an initial filter, eliminating irrelevant documents and creating a smaller subset of potentially relevant results.
Semantic ranking with vector search: Once the initial filtering is complete, vector search re-ranks the results based on their semantic relevance to the user's query. For example, a hybrid search for "affordable Italian pizza with good review" might use a full-text search to filter for documents containing "pizza" and "Italian." Then, vector search would take those results and re-rank them based on semantic similarity to the concepts of "affordable" and "good review."
PostgreSQL’s flexible ecosystem, powered by extensions like pgai and pgvectorscale, enables seamless integration of these two approaches.
Consider your application’s data and infrastructure when implementing these search methods. PostgreSQL’s native full-text search is ideal for keyword-based queries. For vector search, pgai and pgvectorscale enable efficient AI-powered retrieval and ranking directly within your PostgreSQL environment.
While pgai brings AI workflows to PostgreSQL, allowing PostgreSQL users to perform AI-powered queries within their database, pgvectorscale builds on pgvector, enhancing it for greater scalability and performance. Plus, with pgai Vectorizer, part of the pgai extension, you can automate embedding creation and update in your PostgreSQL database.
To see this hybrid approach in action, check out this tutorial using PostgreSQL, Cohere, and pgai.
Docker setup with the pgvector extension:
Create a docker-compose.yml file to configure a PostgreSQL database with the pgvector extension.
The provided configuration runs a PostgreSQL container with the database vectordb, user testuser, and password testpwd.
# Compose a postgres database together with the extension pgvector
Services:
db:
hostname: db
image: ankane/pgvector
ports:
- 5432:5432
restart: always
environment:
- POSTGRES_DB=vectordb
- POSTGRES_USER=testuser
- POSTGRES_PASSWORD=testpwd
- POSTGRES_HOST_AUTH_METHOD=trust
Run the Docker container: Open a terminal, navigate to the directory with the docker-compose.yml file, and run:
docker-compose up --build
Run the following commands to install the required libraries:
pip install sentence_transformers psycopg2 ipython-sql
sentence_transformers: for generating embeddings from text
psycopg2: for interacting with PostgreSQL
ipython-sql: for executing SQL queries in Jupyter/IPython
Download the dataset from Kaggle
Use this Kaggle dataset link to download the job posting dataset. After downloading it, save the dataset (data job posts.csv) in the same directory where you are working on this project.
Define classes for reading data
The Reader class loads and preprocesses the CSV file. The class loads the file in chunks, fills missing values with empty strings, and returns a DataFrame.
class Reader(object):
def __init__(self, file_name):
self.file_name = file_name
def run(self):
df = pd.read_csv(self.file_name, chunksize=5000)
df = next(df)
df = df.fillna("")
return df
Load the dataset:
helper = Reader(file_name="data job posts.csv")
df = helper.run()
Use the SentenceTransformer model to generate embeddings for text data.
Define an EmbeddingGenerator class that encodes the text into embeddings.
from sentence_transformers import SentenceTransformer
import numpy as np
from tqdm import tqdm
class EmbeddingGenerator:
def __init__(self):
self.model = SentenceTransformer('all-MiniLM-L6-v2')
def get_embeddings(self, document):
sentences = [document]
sentence_embeddings = self.model.encode(sentences)
embedding_array = np.array(sentence_embeddings.flatten())
return embedding_array.tolist()
Generate embeddings for the dataset:
The progress_apply method applies the embedding generator to the jobpost column.
# Generate embeddings
tqdm.pandas()
helper_embedding = EmbeddingGenerator()
df["vectors"] = df["jobpost"].progress_apply(helper_embedding.get_embeddings)
🔖 Note: While creating embeddings is a pretty straightforward process, updating and keeping them in sync is much more challenging. To ease this process, you can use pgai Vectorizer to automatically create embeddings and keep them in sync with your source data. Since pgai Vectorizer is part of the pgai extension, no extra steps are required to install it. Learn more about it here.
Use the DatabaseConnection class to manage database interactions:
It uses connection pooling to optimize database operations.
import psycopg2
from psycopg2 import pool
from psycopg2 import sql
# Class to handle PostgreSQL database connections
class DatabaseConnection:
def __init__(self):
self.dbname = 'vectordb'
self.user = 'testuser'
self.password = 'testpwd'
self.host = '127.0.0.1'
self.port = '5432'
# Initializing a connection pool with a minimum of 1 and maximum of 20 connections
self.pool = psycopg2.pool.SimpleConnectionPool(1, 20,
dbname=self.dbname,
user=self.user,
password=self.password,
host=self.host,
port=self.port)
# Method to fetch a connection from the pool
def get_connection(self):
return self.pool.getconn()
# Method to release a connection back to the pool
def release_connection(self, conn):
self.pool.putconn(conn)
# Method to close all connections in the pool when no longer needed
def close_all_connections(self):
self.pool.closeall()
Define a method to create the jobs table in the PostgreSQL database.
db_connection = DatabaseConnection()
def create_jobs_table():
conn = db_connection.get_connection()
cursor = conn.cursor()
Try:
#The CREATE EXTENSION ensures the PGVector extension is available.
cursor.execute("CREATE EXTENSION IF NOT EXISTS vector;")
cursor.execute("""
CREATE TABLE IF NOT EXISTS jobs (
jobpost TEXT,
date TEXT,
Title TEXT,
Company TEXT,
AnnouncementCode TEXT,
Term TEXT,
Eligibility TEXT,
Audience TEXT,
StartDate TEXT,
Duration TEXT,
Location TEXT,
JobDescription TEXT,
JobRequirment TEXT,
RequiredQual TEXT,
Salary TEXT,
ApplicationP TEXT,
OpeningDate TEXT,
Deadline TEXT,
Notes TEXT,
AboutC TEXT,
Attach TEXT,
Year TEXT,
Month TEXT,
IT TEXT,
vectors VECTOR
);
""")
conn.commit()
finally:
cursor.close()
db_connection.release_connection(conn)
create_jobs_table()
Insert data function: Define a function to insert each row of the DataFrame into the jobs table.
def insert_data_into_table(row):
conn = db_connection.get_connection()
cursor = conn.cursor()
Try:
# Define the SQL INSERT statement to add data into the 'jobs' table
insert_statement = sql.SQL("""
INSERT INTO jobs (
jobpost, date, Title, Company, AnnouncementCode, Term,
Eligibility, Audience, StartDate, Duration, Location,
JobDescription, JobRequirment, RequiredQual, Salary,
ApplicationP, OpeningDate, Deadline, Notes, AboutC,
Attach, Year, Month, IT, vectors
) VALUES (
%s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s
)
""")
values = [val if val != '' else None for val in row]
cursor.execute(insert_statement, values)# Execute the SQL statement
conn.commit() # Commit the transaction
finally:
cursor.close()
db_connection.release_connection(conn)
# Insert data into PostgreSQL
for index, row in tqdm(df.iterrows(), total=df.shape[0], desc="Inserting data into PostgreSQL"):
insert_data_into_table(row)
Define a similarity search function: Define a function to find similar documents using the cosine similarity formula.
def find_similar_documents(embedding):
conn = db_connection.get_connection()
cursor = conn.cursor()
try:
query = f"""
SELECT Title, 1 - (vectors <=> '{embedding}') AS cosine_similarity
FROM jobs
ORDER BY cosine_similarity DESC
LIMIT 3
"""
cursor.execute(query)
return cursor.fetchall()
finally:
cursor.close()
db_connection.release_connection(conn)
Search workflow: Take user input, generate embedding, and query for similar results. This function calculates cosine similarity between the input embedding and database embeddings, returning the top three matches.
# Query similar documents
input_query = input("Enter the Input Query: ")
input_embedding = helper_embedding.get_embeddings(input_query)
similar_docs = find_similar_documents(input_embedding)
if similar_docs:
print("Similar Documents:")
for doc in similar_docs:
print(doc)
else:
print("No similar documents found.")
Given query:
I have experience in Python and SQL. Can you suggest me jobs?
Output:
Similar Documents:
('Web Systems Group Engineer', 0.5207197641468366)
('Software Developer/ Programmer', 0.44037696363788703)
('Python Developers', 0.43885444865876133)
Ambiguity: struggles to differentiate between multiple meanings of the same word
Rigid query structures: requires users to formulate queries carefully to achieve optimal results
Computational demands: requires GPUs or high-performance servers for embedding and similarity calculations
Storage requirements: managing large embeddings demands efficient storage solutions
Approximation errors: using ANN methods for scalability can introduce slight inaccuracies
Full-text and vector search are effective tools, each used in unique situations. Full-text search is well-suited for precise, structured queries and resource-constrained environments. In contrast, vector search is ideal for unstructured data, semantic understanding, and advanced use cases like multimedia and cross-modal search. A hybrid approach combines their strengths, offering improved accuracy and flexibility.
Want to power AI-driven search or optimize full-text search within PostgreSQL? Check out Timescale’s open-source PostgreSQL stack for AI applications to integrate semantic search into your applications seamlessly. To learn more about vector search, read the following resources: