PgVector is a PostgreSQL extension that adds support for vector data types, enabling efficient storage, indexing, and similarity search on high-dimensional vectors. Let us delve into understanding the introduction to PgVector and explore why it has become a key component in modern AI-powered applications.
1. Why PgVector?
With the rise of Large Language Models (LLMs) and embeddings, traditional databases fall short when it comes to performing efficient similarity searches on high-dimensional vector data. PgVector bridges this gap by enabling seamless integration of vector search capabilities within PostgreSQL.
- Store embedding vectors in PostgreSQL alongside structured data, simplifying architecture and reducing system complexity
- Run similarity searches using Euclidean, cosine, or inner product distance to match user queries with the most relevant documents or items
- Index vectors using
ivfflatfor fast and scalable approximate nearest neighbor (ANN) search - Works natively with your existing PostgreSQL setup, avoiding the need to maintain a separate vector database
- Supports integration with popular embedding generation tools and LLM pipelines for end-to-end AI workflows
By understanding the introduction to pgvector and its core capabilities, teams can unlock powerful retrieval-augmented generation (RAG) systems, semantic search, recommendation engines, and more — all within the familiarity of a relational database environment.
2. Installing PgVector
To install and use the pgVector extension in your PostgreSQL database, we’ll use a Docker-based setup. This approach provides a local, isolated environment ideal for development and testing without relying on cloud providers.
Start by running the following Docker command to launch a PostgreSQL container with pgVector pre-installed:
docker run -d \ --name pgvector-db \ -e POSTGRES_USER=postgres \ -e POSTGRES_PASSWORD=postgres \ -e POSTGRES_DB=vector_db \ -p 5432:5432 \ pgvector/pgvector
Once the container is running, connect to the PostgreSQL database using a client like `psql`, DBeaver, or PgAdmin. After connecting, enable the vector extension by executing:
CREATE EXTENSION IF NOT EXISTS vector;
This will activate pgVector support for your local PostgreSQL instance running inside Docker.
In the upcoming steps, we will create the required tables, define the schema to hold vector data, and add indexes for efficient similarity search operations. These configurations will be applied programmatically through code.
3. Code Example
To begin working with PgVector and OpenAI embeddings in your application, start by installing the required Python packages. These packages will allow you to interact with PostgreSQL, manage vector data, and generate embeddings using OpenAI.
pip install psycopg2-binary openai pgvector
3.1 Writing code
This script demonstrates how to generate text embeddings using Azure OpenAI and store them in a PostgreSQL database configured with pgVector for vector similarity search. Before running the script, make sure you have completed the following:
- Created and deployed an OpenAI resource via the Azure portal.
- Obtained your API key and endpoint URL from the Azure OpenAI resource.
- Set up a PostgreSQL database using Neon (or any provider of your choice).
- Installed the
pgvectorextension in the database as described earlier.
You are free to use any cloud provider or the official OpenAI API to generate embeddings. Just ensure the corresponding API key and endpoint are correctly configured.
import logging
import sys
import psycopg2
from openai import AzureOpenAI
from pgvector.psycopg2 import register_vector
# Azure OpenAI and PostgreSQL connection constants
AZURE_API_VERSION = 'api_version'
AZURE_ENDPOINT = 'azure_endpoint'
AZURE_API_KEY = 'azure_api_key'
PG_HOST = 'postgres_host'
PG_DATABASE = 'your_database'
PG_USER = 'your_user'
PG_PASSWORD = 'your_password'
LOG_FORMAT = "%(asctime)s - %(levelname)s - %(name)s - %(filename)s:%(lineno)d - %(funcName)s() - %(message)s"
logging.basicConfig(stream=sys.stdout, level=logging.INFO, format=LOG_FORMAT)
logger = logging.getLogger(__name__)
def get_embedding(client, text: str) -> list:
# Function to get text embedding using Azure OpenAI
response = client.embeddings.create(input=, model="text-embedding-3-small")
return response.data[0].embedding
def setup_database(cur):
# Function to set up the PostgreSQL database
logger.info("Dropping and creating documents table")
cur.execute("DROP TABLE IF EXISTS documents")
cur.execute("""
CREATE TABLE IF NOT EXISTS documents (
id SERIAL PRIMARY KEY,
content TEXT UNIQUE NOT NULL,
embedding VECTOR(1536)
)
""")
logger.info("Creating vector index")
cur.execute("""
CREATE INDEX IF NOT EXISTS idx_embedding_vector ON documents USING ivfflat (embedding vector_l2_ops)
""")
def insert_documents(cur, client, texts):
# Function to insert documents into the PostgreSQL database
for text in texts:
logger.info(f"Getting embedding for: {text}")
embedding = get_embedding(client, text)
try:
cur.execute(
'INSERT INTO documents (content, embedding) VALUES (%s, %s) ON CONFLICT (content) DO NOTHING',
(text, embedding)
)
logger.info(f"Inserted: {text}")
except Exception as e:
logger.error(f"Failed to insert '{text}': {e}")
cur.connection.rollback()
logger.info("Transaction rolled back due to error")
def query_similar_documents(cur, client, query_text, limit=5):
# Function to query similar documents based on the embedding of the query text
logger.info(f"Getting embedding for query: {query_text}")
query_embedding = get_embedding(client, query_text)
logger.info("Querying similar documents")
cur.execute(
'SELECT content, embedding FROM documents ORDER BY embedding %s::vector LIMIT %s',
(query_embedding, limit)
)
results = cur.fetchall()
logger.info(f"Top {limit} similar documents for query: '{query_text}'")
for content, embedding in results:
logger.info(f"Content: {content}")
def main():
# Main function to initialize the Azure OpenAI client and PostgreSQL connection
logger.info("Initializing AzureOpenAI client")
client = AzureOpenAI(
api_version=AZURE_API_VERSION,
azure_endpoint=AZURE_ENDPOINT,
api_key=AZURE_API_KEY
)
logger.info("Connecting to PostgreSQL")
with psycopg2.connect(
host=PG_HOST,
database=PG_DATABASE,
user=PG_USER,
password=PG_PASSWORD
) as conn:
register_vector(conn)
with conn.cursor() as cur:
setup_database(cur)
texts = [
'This is an example text for embedding.',
'Another document for testing vector search.',
'Sample content to demonstrate multiple inserts.',
'Yet another example for the database.',
'Final test document for embeddings.'
]
insert_documents(cur, client, texts)
query_text = 'vector search demonstration.'
query_similar_documents(cur, client, query_text)
conn.commit()
logger.info("Done")
if __name__ == "__main__":
main()
3.1.1 Code Explanation
This script demonstrates how to generate text embeddings using Azure OpenAI and store them in a PostgreSQL database equipped with the PgVector extension to enable vector similarity search. It defines configuration constants for both Azure OpenAI and PostgreSQL, sets up logging for detailed execution tracking, and provides utility functions for key operations. The get_embedding() function retrieves a 1536-dimensional embedding for any input text using Azure’s text-embedding-3-small model. The setup_database() function prepares the PostgreSQL table by creating a documents table with a vector column and adds an ivfflat index for efficient vector similarity search. The insert_documents() function processes a list of sample texts, generates embeddings for each, and inserts them into the database while handling duplicates and errors gracefully. The query_similar_documents() function embeds the query text and retrieves the top N most similar records from the table using L2 distance. The main() function orchestrates all these steps—initializing the Azure OpenAI client and PostgreSQL connection, setting up the database, inserting sample data, and performing a similarity query. The overall flow showcases how to build a semantic search pipeline using Azure OpenAI and PostgreSQL with PgVector.
3.1.2 Code Output
Once the Python script is executed successfully, you should see logs similar to the following in your terminal or console:
/Users/mycave/code/personal/python/python-and-pgvector/.ppgv/bin/python /Users/mycave/code/personal/python/python-and-pgvector/main.py 2025-07-23 16:06:46,363 - INFO - __main__ - main.py:81 - main() - Initializing AzureOpenAI client 2025-07-23 16:06:46,478 - INFO - __main__ - main.py:88 - main() - Connecting to PostgreSQL 2025-07-23 16:06:47,758 - INFO - __main__ - main.py:32 - setup_database() - Dropping and creating documents table 2025-07-23 16:06:48,014 - INFO - __main__ - main.py:41 - setup_database() - Creating vector index 2025-07-23 16:06:48,121 - INFO - __main__ - main.py:50 - insert_documents() - Getting embedding for: This is an example text for embedding. 2025-07-23 16:06:50,615 - INFO - httpx - _client.py:1025 - _send_single_request() - HTTP Request: POST https://your_openai_deployment_name.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-08-01-preview "HTTP/1.1 200 OK" 2025-07-23 16:06:50,790 - INFO - __main__ - main.py:57 - insert_documents() - Inserted: This is an example text for embedding. 2025-07-23 16:06:50,791 - INFO - __main__ - main.py:50 - insert_documents() - Getting embedding for: Another document for testing vector search. 2025-07-23 16:06:51,166 - INFO - httpx - _client.py:1025 - _send_single_request() - HTTP Request: POST https://your_openai_deployment_name.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-08-01-preview "HTTP/1.1 200 OK" 2025-07-23 16:06:51,332 - INFO - __main__ - main.py:57 - insert_documents() - Inserted: Another document for testing vector search. 2025-07-23 16:06:51,332 - INFO - __main__ - main.py:50 - insert_documents() - Getting embedding for: Sample content to demonstrate multiple inserts. 2025-07-23 16:06:58,770 - INFO - httpx - _client.py:1025 - _send_single_request() - HTTP Request: POST https://your_openai_deployment_name.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-08-01-preview "HTTP/1.1 200 OK" 2025-07-23 16:06:58,863 - INFO - __main__ - main.py:57 - insert_documents() - Inserted: Sample content to demonstrate multiple inserts. 2025-07-23 16:06:58,863 - INFO - __main__ - main.py:50 - insert_documents() - Getting embedding for: Yet another example for the database. 2025-07-23 16:06:59,243 - INFO - httpx - _client.py:1025 - _send_single_request() - HTTP Request: POST https://your_openai_deployment_name.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-08-01-preview "HTTP/1.1 200 OK" 2025-07-23 16:06:59,333 - INFO - __main__ - main.py:57 - insert_documents() - Inserted: Yet another example for the database. 2025-07-23 16:06:59,333 - INFO - __main__ - main.py:50 - insert_documents() - Getting embedding for: Final test document for embeddings. 2025-07-23 16:06:59,643 - INFO - httpx - _client.py:1025 - _send_single_request() - HTTP Request: POST https://your_openai_deployment_name.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-08-01-preview "HTTP/1.1 200 OK" 2025-07-23 16:06:59,734 - INFO - __main__ - main.py:57 - insert_documents() - Inserted: Final test document for embeddings. 2025-07-23 16:06:59,734 - INFO - __main__ - main.py:66 - query_similar_documents() - Getting embedding for query: vector search demonstration. 2025-07-23 16:07:00,090 - INFO - httpx - _client.py:1025 - _send_single_request() - HTTP Request: POST https://your_openai_deployment_name.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-08-01-preview "HTTP/1.1 200 OK" 2025-07-23 16:07:00,091 - INFO - __main__ - main.py:68 - query_similar_documents() - Querying similar documents 2025-07-23 16:07:00,468 - INFO - __main__ - main.py:74 - query_similar_documents() - Top 5 similar documents for query: 'vector search demonstration.' 2025-07-23 16:07:00,468 - INFO - __main__ - main.py:76 - query_similar_documents() - Content: Another document for testing vector search. 2025-07-23 16:07:00,468 - INFO - __main__ - main.py:76 - query_similar_documents() - Content: Yet another example for the database. 2025-07-23 16:07:00,468 - INFO - __main__ - main.py:76 - query_similar_documents() - Content: Final test document for embeddings. 2025-07-23 16:07:00,468 - INFO - __main__ - main.py:76 - query_similar_documents() - Content: Sample content to demonstrate multiple inserts. 2025-07-23 16:07:00,468 - INFO - __main__ - main.py:76 - query_similar_documents() - Content: This is an example text for embedding. 2025-07-23 16:07:00,549 - INFO - __main__ - main.py:109 - main() - Done Process finished with exit code 0
The log output captures the end-to-end execution flow of the embedding pipeline. It begins with the initialization of the Azure OpenAI client, followed by establishing a connection to the PostgreSQL database. The script drops and recreates the documents table, then creates a vector index to enable similarity search. For each document, the system generates an embedding using the Azure OpenAI embedding endpoint and inserts it into the database. These steps are repeated for multiple input texts. After storing all embeddings, a query embedding is generated for the phrase “vector search demonstration,” and a similarity search is performed using the stored vectors. The top five most similar documents are retrieved and printed. The process concludes with a final confirmation log indicating successful execution of the entire flow.
4. Conclusion
PgVector brings the power of vector similarity search into your existing PostgreSQL ecosystem. With just a few lines of SQL and Python, you can build intelligent applications powered by embeddings — such as semantic search, document similarity, and recommendation systems.
