How pgvector extends PostgreSQL

Transcript

1. How pgvector extends PostgreSQL Thiago Colares (thicolares.com) – Nov 11, 2025 AtyimoLAB: Big Data Science Research, PGCOMP UFBA
2. 1. What is pgvector? 2
3. pgvector overview 3 ▣ Open-source vector similarity search for PG ▣ Find by characteristics, not exact matches ▣ Handle vectors alongside regular data ▣ Exact and approximate nearest neighbor search ▣ Various vector distance functions https://github.com/pgvector/pgvector
4. PostgreSQL with pgvector 4 Get images, text, audio… Transform into embedding Save embedding in the database Embedding Model (OpenAI, Gemini, In-house, Open-source ones etc.) App Get query Search embedding in the database App 🏞📄 🎶 [0.5,0.3,...,0.1] [0.6,0.2,...,0.9] "flying whales" [0.5,0.3,...,0.1] [0.6,0.2,...,0.9] [0.4,0.3,...,0.1]
5. SQL-visible type The vector data type Internal representation 5 CREATE TABLE document_embeddings ( id integer not null primary key, embedding vector(1536) not null ); typedef struct Vector { int32 vl_len_; int16 dim; int16 unused; float x[FLEXIBLE_ARRAY_MEMBER]; } Vector;
6. Search with a distance function 6 https://github.com/pgvector/pgvector?tab=readme-ov-file#querying Search query example Supported distance functions <-> L2 distance (euclidian) <#> (negative) inner product <=> cosine distance <+> L1 distance <~> Hamming distance (bin. vectors) <%> Jaccard distance (bin. vectors) SELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;
7. Exact nearest neighbor search (kNN, "flat search") 7 Redis: https://www.youtube.com/watch?v=9NvO-VdjY80
8. HNSW Approximate nearest neighbor search (ANN) 8 Redis: https://www.youtube.com/watch?v=9NvO-VdjY80 Supported index types are: • HNSW • IVFFlat
9. DEMO 9 https://github.com/thicolares/pgvector_experiment
10. 2. PG extensions boilerplate 10
11. Minimal directory structure (C-based) 11 https://www.postgresql.org/docs/current/extend-pgxs.html. Rust: https://crates.io/crates/cargo-pgx myextension/ ├─ Makefile ├─ myextension.control ├─ sql/ │ └─ myextension--1.0.sql ├─ src/ │ └─ myextension.c ← how to compile the C code ← metadata about the extension ← create functions, types... ← implementation of C functions
12. ▣ The official build infrastructure for extensions ▣ Makefile definitions provided by PostgreSQL ▣ What to build / Where to install extension files ▣ PostgreSQL handles paths/config ▣ Else, hardcode set install paths: Build with PGXS (Extension System) 12 https://www.postgresql.org/docs/current/extend-pgxs.html install: cp my_ext.so /usr/lib/postgresql/16/lib/ cp my_ext.control /usr/share/postgresql/16/extension/
13. Makefile with PGXS (key lines) 13 https://www.postgresql.org/docs/current/extend-pgxs.html EXTENSION = vector MODULE_big = vector PG_CONFIG ?= pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) # name; vector.control file; dest. prefix/share/vector: # build a single shared library named vector (e.g. vector.so): # get PostgreSQL’s inc. dirs, compiler flags, and inst. paths: # get the path to PostgreSQL's PGXS Makefile fragment: # loads the PGXS build system:
14. Distribution ▣ PostgreSQL has no official registry (like PyPI, npm) ▣ PGNX (PostgreSQL Extension Network) ▣ Source with PGXS Makefile (Github etc.) ▣ Binary packages (apt-get, homebrew, pgxman…) 14 https://pgxn.org/ https://pgxman.com/
15. 3. Defining types, functions, and operators 15
16. Defining type 16 https://www.postgresql.org/docs/18/sql-createtype.html -- sql/vector.sql CREATE TYPE vector ( INPUT = vector_in, -- text → internal OUTPUT = vector_out, -- internal → text TYPMOD_IN = vector_typmod_in, -- check params RECEIVE = vector_recv, -- bin → internal SEND = vector_send, -- internal → bin STORAGE = external );
17. Defining operator 17 https://www.postgresql.org/docs/18/sql-createoperator.html -- sql/vector.sql CREATE OPERATOR <-> ( LEFTARG = vector, -- left operand data type RIGHTARG = vector, -- right operand data type PROCEDURE = l2_distance, COMMUTATOR = '<->' );
18. Defining functions (without index) 18 https://www.postgresql.org/docs/18/sql-createoperator.html -- sql/vector.sql CREATE FUNCTION l2_distance(vector, vector) RETURNS float8 AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; // C implementation FUNCTION_PREFIX PG_FUNCTION_INFO_V1(l2_distance); Datum l2_distance(PG_FUNCTION_ARGS) { ...
19. Defining functions (with index) 19 https://www.postgresql.org/docs/18/sql-createopclass.html -- sql/vector.sql CREATE OPERATOR CLASS vector_l2_ops FOR TYPE vector USING hnsw AS FUNCTION 1 vector_l2_squared_distance(vector, vector); ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ...
20. 5. Wiring things together 20
21. At extension installation time ▣ You run CREATE EXTENSION vector ▣ PosgtreSQL runs vector.sql ▣ CREATE TYPE vector… triggers INSERT INTO pg_type… ▣ CREATE FUNCTION… triggers INSERT INTO pg_proc… ▣ CREATE OPERATOR CLASS… triggers INSERT INTO pg_opclass… ▣ And so on… ▣ Metadata stored, but no C code executed yet! 21
22. At index-creation time ▣ Records that this index uses hnsw + vector_l2_ops ▣ Stores metadata in pg_index and pg_class 22 CREATE INDEX idx ON table USING hnsw (embedding vector_l2_ops); ^ ^^^^^^^^^^^^^ | operator class column
23. At query execution time (at runtime) ▣ ▣ Planner: Ah! There’s an HNSW index! ▣ Executor: Which operator class? → vector_l2_ops ▣ What is FUNCTION 1 of vector_l2_ops? → vector_l2_squared_distance □ Looks up pg_proc → probin="/path/vector.so", prosrc="..." etc. □ Loads the C function ▣ Perform HNSW and L2 calls the function during search 23 SELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;
24. 4. Takeaways 24
25. pgvector 25 ▣ Open-source vector similarity search for Postgres ▣ Handle vectors alongside regular data ▣ Exact and approximate nearest neighbor search ▣ Various vector distance functions ▣ Single-precision, half-precision, bin., sparse vectors ▣ PG features: ACID, JOINs, point-in-time recover…
26. Building PostgreSQL extensions 26 ▣ Handful official Makefile boilerplate (PGXS) ▣ Define with types, functions, operators with SQL ▣ C, SQL, PL languages (limited), Rust (via pgx) Flow: ▣ Installation: store metadata ▣ Runtime: context → metadata → find/run implementation https://www.postgresql.org/docs/current/extend-pgxs.html https://www.postgresql.org/docs/18/extend.html https://docs.rs/pgx/latest/pgx/
27. References 27 ▣ GitHub - pgvector/pgvector: Open-source vector similarity search for Postgres ▣ Exact vs Approximate Nearest Neighbors in Vector Databases ▣ https://github.com/thicolares/pgvector_experiment ▣ PostgreSQL: Documentation: 18: 36.18. Extension Building Infrastructure ▣ PostgreSQL: Documentation: 18: CREATE TYPE ▣ PostgreSQL: Documentation: 18: CREATE OPERATOR CLASS ▣ cargo-pgx - crates.io: Rust Package Registry ▣ Integrated Vector Database - Azure Cosmos DB | Microsoft Learn ▣ PGXN
28. Thanks! Any questions? Thiago Colares (thicolares.com) – Nov 11, 2025 AtyimoLAB: Big Data Science Research, PGCOMP UFBA 28