PGVECTOR

pgvector PostgreSQL extension vector similarity native.

Definition

pgvector features: (1) Vector type: CREATE TABLE items (embedding vector(1536)); supports up to 16000 dimensions (HNSW indexable limit 2000), supports half-precision (halfvec 16-bit), binary (bit), sparsevec sparse. (2) Distance operators: <-> (L2 distance), <#> (negative inner product), <=> (cosine distance), <+> (L1 Manhattan), <~> (Hamming for binary), <%> (Jaccard binary). (3) Index types: IVFFLAT (Inverted File flat, faster build slower query), HNSW (Hierarchical Navigable Small World, slower build faster query, recommended). (4) Index parameters: HNSW (m, ef_construction, ef), IVFFLAT (lists). (5) Filtering: combined with standard SQL WHERE clauses, JOIN with regular relational tables (vendors, users, products etc.). (6) Quantization: Scalar Quantization SQ via half-precision halfvec 16-bit 2x compression, Binary Quantization via bit type 32x compression. (7) Performance: pgvector 0.7+ ~50% faster HNSW vs 0.5, comparable Pinecone within 2x throughput. Cloud-managed PostgreSQL support: AWS RDS PostgreSQL (since 15.2 native), Azure Database for PostgreSQL (Flexible Server), GCP Cloud SQL PostgreSQL, Aiven, Neon, Supabase, Crunchy Data Postgres, Timescale.

Origin

pgvector published 2021 by Andrew Kane (Postgres community contributor) ; pgvector 0.5 adds HNSW September 2023 (major milestone) ; pgvector 0.7 July 2024 quantization halfvec + bit ; ~most deployed vector DB 2024 due to cloud-managed Postgres ubiquity.

Example in context

B2B SaaS startup uses AWS RDS PostgreSQL 16 + pgvector 0.7: embed customer support tickets via OpenAI text-embedding-3-small (1536 dim), HNSW index, SQL query 'SELECT * FROM tickets ORDER BY embedding <=> $1 LIMIT 10 WHERE customer_id = $2' returns top-10 semantically similar tickets joined with customer context, no separate vector DB to manage.

Related terms

• Redis Stack — in-memory alternative.