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...
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 )....
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....
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....
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....
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....
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....
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....
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....
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...
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....
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 ....
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....
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...
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....
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.
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....
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....
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....
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....
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...
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.
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....
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....
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....
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....
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 #...
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...
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....
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.
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.
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....
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....
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.
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.
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...
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....
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...
