Prev Next

Database / pgvector basics Interview Questions

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
OperatorNameFormulaBest for
<->L2 (Euclidean) distancesqrt(sum((a-b)^2))General purpose; when vector magnitude carries meaning
<#>Negative inner product-sum(a*b)Recommendation systems; normalised vectors
<=>Cosine distance1 - (a.b / |a||b|)Text embeddings; magnitude-independent similarity
<+>L1 (Manhattan) distancesum(|a-b|)Robust to outliers; sparse-ish vectors
<~>Hamming distanceCount of differing bitsBinary vectors (bit type only)
<%>Jaccard distance1 - |A intersect B| / |A union B|Binary vectors; set-like similarity
-- L2 distance: find 5 nearest neighbours to '[3,1,2]'
SELECT id, content, embedding <-> '[3,1,2]' AS distance
FROM documents
ORDER BY distance
LIMIT 5;

-- Cosine distance: best for text embeddings
SELECT id, content, embedding <=> '[3,1,2]' AS cosine_dist
FROM documents
ORDER BY cosine_dist
LIMIT 5;

-- Inner product: NOTE <#> returns NEGATIVE inner product
-- Negate the result to get actual similarity score:
SELECT id, -(embedding <#> '[3,1,2]') AS inner_product_sim
FROM documents
ORDER BY embedding <#> '[3,1,2]'  -- ASC = most similar (most negative)
LIMIT 5;

-- Get actual distance value (not just ordering):
SELECT id, embedding <-> '[3,1,2]' AS l2_dist,
            embedding <=> '[3,1,2]' AS cos_dist
FROM documents
LIMIT 10;

Why does pgvector use the <#> operator for inner product and return a NEGATIVE value?
Which pgvector operator is most commonly recommended for text embedding similarity search?

Invest now in Acorns!!! 🚀 Join Acorns and get your $5 bonus!
Acorns Logo

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Earn passively and while sleeping

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Robinhood Logo

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull Logo

Webull! Receive free stock by signing up using the link: Webull signup.

More Related questions...

What is pgvector and what problem does it solve for developers? What are vector embeddings and why are they central to pgvector's use? How do you install and enable pgvector in PostgreSQL? What data types does pgvector provide and how do you define vector columns? What distance operators does pgvector provide and when do you use each? How do you insert and update vector data in pgvector? How do you perform a basic nearest-neighbour search with pgvector? What is the difference between exact and approximate nearest-neighbour search in pgvector? What is the HNSW index in pgvector and how do you create and tune it? What is the IVFFlat index in pgvector and how does it compare to HNSW? How do you use pgvector with Python and psycopg2? How do you use pgvector with SQLAlchemy and Python ORMs? How do you combine vector similarity search with SQL filters (hybrid search) in pgvector? How do you bulk-load vectors efficiently into pgvector? What operator classes must you use when creating pgvector indexes and why do they matter? How does pgvector fit into a RAG (Retrieval-Augmented Generation) pipeline? What is cosine distance vs cosine similarity and which does pgvector return? How do you check and monitor pgvector index creation progress? What is the pgvector maximum supported dimensions limit and how do you handle high-dimensional vectors? How do you use pgvector with LangChain for building AI applications? How does pgvector handle NULL values in vector columns? What are partial indexes in pgvector and when should you use them? How do you perform vector arithmetic and other vector functions in pgvector? How does pgvector compare to dedicated vector databases like Pinecone, Weaviate, and Qdrant? What is the difference between L1 and L2 distance in pgvector? How do you store and query vector embeddings with pgvector in a real schema design? What is halfvec and when should you use it to reduce storage costs? How do you handle vector dimensionality mismatches in pgvector? How do you use pgvector with Django? What are common performance tuning techniques for pgvector at scale? How do you implement semantic search with pgvector and a similarity threshold? How do you use pgvector with asyncpg or asyncio in Python? What is vector quantisation and how does pgvector support binary quantisation? How does pgvector integrate with managed PostgreSQL services? How do you use the inner product operator <#> with pgvector and when is it appropriate? How do you combine pgvector with full-text search (hybrid keyword + semantic search)? What PostgreSQL configuration parameters affect pgvector performance? How do you implement recommendation systems using pgvector? How do you use EXPLAIN and EXPLAIN ANALYZE to debug pgvector queries? What are best practices for a production pgvector deployment?
Show more question and Answers...

Integration

Comments & Discussions

Interviews Questions

About Javapedia.net

Javapedia.net is for Java and J2EE developers, technologist and college students who prepare of interview. Also this site includes many practical examples.

This site is developed using J2EE technologies by Steve Antony, a senior Developer/lead at one of the logistics based company.

contact: javatutorials2016[at]gmail[dot]com

Kindly consider donating for maintaining this website. Thanks.

Copyright © 2026, javapedia.net, all rights reserved. privacy policy.

DMCA.com Protection Status