Database / pgvector basics Interview Questions
1. What is pgvector and what problem does it solve for developers?
pgvector is an open-source PostgreSQL extension that adds native vector storage and similarity search capabilities to PostgreSQL. It allows developers to store high-dimensional vector embeddings generated by machine learning models alongside conventional relational data, and query them using effi...
2. What are vector embeddings and why are they central to pgvector's use?
A vector embedding is a list of floating-point numbers produced by a machine learning model that encodes the semantic meaning of some input data. The defining property is that semantically similar inputs produce numerically similar vectors - meaning you can measure the 'closeness' of two concepts...
3. How do you install and enable pgvector in PostgreSQL?
pgvector installation has two steps: installing the extension binary on the server, and enabling it in each database where you want to use it. The extension name used in SQL is vector (not pgvector ). Installation methods Method Command Ubuntu/Debian (PostgreSQL APT repo) sudo apt install postgre...
4. What data types does pgvector provide and how do you define vector columns?
pgvector introduces several new data types to PostgreSQL for storing vector data. The primary type is vector , with additional types for half-precision and binary vectors added in later releases. pgvector data types Type Storage Precision Max dimensions Use case vector(n) 4 bytes per dimension 32...
5. What distance operators does pgvector provide and when do you use each?
pgvector defines several SQL operators for computing distance or similarity between vectors. The operator you choose affects both the mathematical semantics and which index types can accelerate the query. pgvector distance operators Operator Name Formula Best for L2 (Euclidean) distance sqrt(sum(...
6. How do you insert and update vector data in pgvector?
Vector values are inserted as SQL string literals in the format '[v1,v2,...,vn]' - a JSON array-like notation enclosed in single quotes. PostgreSQL automatically casts these to the vector type. -- Insert a single row with a vector literal INSERT INTO documents (content, embedding) VALUES ( 'Hello...
7. How do you perform a basic nearest-neighbour search with pgvector?
The fundamental pgvector query pattern combines a distance operator in the ORDER BY clause with LIMIT to retrieve the k nearest neighbours to a query vector. Without an index, this performs an exact sequential scan of all rows. -- K-Nearest Neighbour (KNN) query: find 5 most similar documents -- ...
8. What is the difference between exact and approximate nearest-neighbour search in pgvector?
pgvector supports two search modes with very different performance and accuracy characteristics. Choosing the right one depends on dataset size and whether you need perfect recall. Exact vs Approximate search Aspect Exact (sequential scan) Approximate (ANN with index) Method Computes distance to ...
9. What is the HNSW index in pgvector and how do you create and tune it?
HNSW (Hierarchical Navigable Small World) is the recommended index type for most pgvector workloads. It builds a multilayer graph structure where each layer is a navigable small world graph, enabling very fast approximate nearest-neighbour search with excellent recall. -- Create an HNSW index (ch...
10. What is the IVFFlat index in pgvector and how does it compare to HNSW?
IVFFlat (Inverted File Flat) is pgvector's other index type. It clusters vectors into lists using k-means, then searches only the closest lists to the query vector. It was the original pgvector index type but is now generally considered secondary to HNSW for most workloads. -- Create an IVFFlat i...
11. How do you use pgvector with Python and psycopg2?
The standard Python path for pgvector uses psycopg2 (or psycopg3) as the PostgreSQL driver, with the pgvector Python package providing type adapters that automatically convert Python lists to vector literals and back. # Install dependencies # pip install pgvector psycopg2-binary openai import psy...
12. How do you use pgvector with SQLAlchemy and Python ORMs?
SQLAlchemy is the most popular Python ORM and supports pgvector through the pgvector package, which provides a SQLAlchemy column type and custom operators. This allows defining vector columns declaratively and using Pythonic query expressions. # pip install pgvector sqlalchemy psycopg2-binary fro...
13. How do you combine vector similarity search with SQL filters (hybrid search) in pgvector?
One of pgvector's key advantages over standalone vector databases is the ability to combine vector similarity with arbitrary SQL predicates in a single query. This is called hybrid search or filtered vector search . -- Filtered vector search: find similar documents in a specific category SELECT i...
14. How do you bulk-load vectors efficiently into pgvector?
Inserting vectors one row at a time with individual INSERT statements is the slowest possible approach. For large datasets (thousands to millions of rows), bulk loading strategies dramatically reduce ingestion time. -- Method 1: Multi-row INSERT (batch inserts) INSERT INTO documents (content, emb...
15. What operator classes must you use when creating pgvector indexes and why do they matter?
When creating an HNSW or IVFFlat index on a vector column, you must specify an operator class that tells PostgreSQL which distance metric the index is optimised for. The operator class in the index must match the distance operator used in queries, otherwise the planner cannot use the index. pgvec...
16. How does pgvector fit into a RAG (Retrieval-Augmented Generation) pipeline?
RAG (Retrieval-Augmented Generation) is a technique that improves LLM responses by retrieving relevant documents from a knowledge base and including them as context in the prompt. pgvector serves as the vector store component, storing document embeddings and enabling semantic retrieval. RAG pipel...
17. What is cosine distance vs cosine similarity and which does pgvector return?
Cosine similarity and cosine distance are closely related but measure different things. pgvector's <=> operator returns cosine distance , not similarity. Understanding the relationship prevents confusion when interpreting results. Cosine similarity vs distance Metric Formula Range Interpretation ...
18. How do you check and monitor pgvector index creation progress?
Building HNSW or IVFFlat indexes on large tables can take significant time (minutes to hours). PostgreSQL provides the pg_stat_progress_create_index view to monitor progress in real time. -- Monitor index build progress: SELECT phase, round( 100.0 * blocks_done / NULLIF(blocks_total, 0 ), 1 ) AS ...
19. What is the pgvector maximum supported dimensions limit and how do you handle high-dimensional vectors?
pgvector has dimension limits that vary by data type. These limits are generous for current embedding models but may be a consideration for future or custom embeddings. Dimension limits by type Type Max dimensions Notes vector(n) 16,000 Standard; covers OpenAI (1536/3072), most models halfvec(n) ...
20. How do you use pgvector with LangChain for building AI applications?
LangChain provides a PGVector vector store implementation that wraps pgvector, making it easy to use pgvector as the backend for LangChain-based RAG applications, agents, and chatbots. # pip install langchain langchain-postgres langchain-openai from langchain_postgres import PGVector from langcha...
21. How does pgvector handle NULL values in vector columns?
pgvector follows standard PostgreSQL NULL semantics. Vector columns can contain NULL values, and NULL vectors are excluded from distance calculations and index scans. This is useful for records where an embedding has not yet been generated. -- Create table allowing NULLs (default behaviour) CREAT...
22. What are partial indexes in pgvector and when should you use them?
A partial index is a pgvector (or PostgreSQL) index that covers only a subset of rows, defined by a WHERE clause at index creation. This is useful for filtering common values efficiently or indexing only rows that have embeddings. -- Partial index: only index documents in a specific category CREA...
23. How do you perform vector arithmetic and other vector functions in pgvector?
pgvector exposes several SQL functions and operators for vector arithmetic - useful for computing centroids, adding noise, normalising vectors, or combining them mathematically before storage or search. -- Vector arithmetic operators: -- + : element-wise addition -- - : element-wise subtraction -...
24. How does pgvector compare to dedicated vector databases like Pinecone, Weaviate, and Qdrant?
pgvector vs dedicated vector databases is one of the most common architectural decisions for AI applications. The right choice depends on scale, existing infrastructure, and feature requirements. pgvector vs dedicated vector databases Factor pgvector Dedicated (Pinecone/Weaviate/Qdrant) Data co-l...
25. What is the difference between L1 and L2 distance in pgvector?
pgvector supports both L1 (Manhattan) distance and L2 (Euclidean) distance. They measure different things and have different properties that make each better suited for certain data types. L1 vs L2 distance comparison Property L1 (Manhattan) L2 (Euclidean) Formula sum(|a_i - b_i|) sqrt(sum((a_i -...
26. How do you store and query vector embeddings with pgvector in a real schema design?
A well-designed schema stores embeddings close to the source data they represent, includes metadata for filtering, and separates concerns cleanly. Here are common production schema patterns. -- Pattern 1: Embedding column on the same table as the content CREATE TABLE articles ( id BIGSERIAL PRIMA...
27. What is halfvec and when should you use it to reduce storage costs?
halfvec is a pgvector data type that stores each vector dimension as a 16-bit (half-precision) float instead of the standard 32-bit float. This halves storage requirements at the cost of a small precision reduction. -- Standard vector: 4 bytes per dimension -- halfvec: 2 bytes per dimension (50% ...
28. How do you handle vector dimensionality mismatches in pgvector?
pgvector enforces dimension consistency within typed columns - you cannot insert a 1024-dimensional vector into a VECTOR(1536) column. Understanding how to handle this prevents common insertion and query errors. -- FIXED-dimension column (recommended when all vectors have same size) CREATE TABLE ...
29. How do you use pgvector with Django?
The pgvector Python package includes a Django integration that provides a VectorField model field, enabling vector storage and similarity search within Django ORM queries. # pip i nstall pgvec t or dja n go psycopg 2- bi nar y # se tt i n gs.py - make sure dja n go uses Pos t greSQL : DATABASES =...
30. What are common performance tuning techniques for pgvector at scale?
As vector tables grow to millions of rows, several tuning techniques help maintain good query performance and manageable index build times. Performance tuning checklist Technique When to apply How HNSW index > ~100k rows or when speed needed CREATE INDEX USING hnsw with appropriate ops class ef_s...
31. How do you implement semantic search with pgvector and a similarity threshold?
Returning only results above a minimum similarity threshold prevents surfacing irrelevant results when no truly similar documents exist. This is preferable to always returning the top-k regardless of quality. -- Return results only within a distance threshold -- (distance < threshold means simila...
32. How do you use pgvector with asyncpg or asyncio in Python?
Modern Python web frameworks (FastAPI, Starlette, aiohttp) use async I/O. pgvector works with asyncpg (the high-performance async PostgreSQL driver) using the pgvector codec registration. # pip install asyncpg pgvector import asyncio import asyncpg from pgvector.asyncpg import register_vector fro...
33. What is vector quantisation and how does pgvector support binary quantisation?
Vector quantisation compresses full-precision vectors into more compact representations, trading some precision for dramatically reduced storage and faster distance computations. pgvector supports binary quantisation via the bit type. -- Binary quantisation: convert float vectors to binary (0/1 p...
34. How does pgvector integrate with managed PostgreSQL services?
pgvector is supported by all major managed PostgreSQL providers, though the setup process varies. This is one of pgvector's key practical advantages - you can enable vector search on your existing managed database without migrating to a new system. Managed service support Provider pgvector suppor...
35. How do you use the inner product operator <#> with pgvector and when is it appropriate?
The <#> operator computes the negative inner product (dot product) between two vectors. It is most useful with normalised vectors (unit vectors where magnitude = 1), in which case it is mathematically equivalent to cosine similarity but computed faster. -- <#> returns the NEGATIVE inner product -...
36. How do you combine pgvector with full-text search (hybrid keyword + semantic search)?
Combining vector semantic search with keyword full-text search (BM25/tsvector) produces better results than either alone. This hybrid search pattern handles both cases: queries that need exact keyword matches and queries that need semantic understanding. -- Hybrid search: combine semantic similar...
37. What PostgreSQL configuration parameters affect pgvector performance?
Several PostgreSQL-level settings directly impact pgvector query and index performance. Tuning these appropriately for a vector workload can yield significant speedups. Key PostgreSQL parameters for pgvector Parameter Default Recommended (vector workload) Effect maintenance_work_mem 64MB 2-8GB Me...
38. How do you implement recommendation systems using pgvector?
Recommendation systems find items similar to those a user has interacted with. pgvector is well-suited for this because item embeddings (trained on interaction data or content features) can be stored and queried with KNN search, with SQL filtering for business rules. -- Schema for a product recom...
39. How do you use EXPLAIN and EXPLAIN ANALYZE to debug pgvector queries?
EXPLAIN and EXPLAIN ANALYZE are essential for understanding whether pgvector queries are using indexes or falling back to slow sequential scans. Diagnosing this is often the first step in troubleshooting slow queries. -- Basic EXPLAIN: shows the plan without running the query EXPLAIN SELECT id, c...
40. What are best practices for a production pgvector deployment?
A checklist of best practices covers schema design, indexing, performance, operations, and application integration for reliable, performant pgvector deployments. Production best practices checklist Area Best practice Schema Store embeddings in the same table as the content for easy JOINs; use ON ...
