what an extension is

CREATE EXTENSION IF NOT EXISTS pgvector;
CREATE EXTENSION IF NOT EXISTS postgis;
\dx  -- list installed extensions, in psql
An extension is a packaged set of new types, functions, operators, and index methods that plugs into PostgreSQL's core — not a separate database, a separate process, or a separate thing to keep in sync. This is the mechanism that lets a single PostgreSQL instance absorb capabilities that would otherwise need a whole dedicated system next to it.

pgvector: vector similarity search

pgvector adds a vector column type and distance operators — the exact building block behind RAG (retrieval-augmented generation) and semantic search over embeddings. Given this site's focus, this is the extension most worth knowing well.
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding VECTOR(1536)   -- e.g. OpenAI text-embedding-3-small dimensionality
);

-- cosine distance — the standard metric for most text embedding models
SELECT content, embedding <=> '[0.01, -0.02, ...]' AS distance
FROM documents
ORDER BY embedding <=> '[0.01, -0.02, ...]'
LIMIT 5;
OperatorDistance metric
<->Euclidean (L2)
<#>negative inner product
<=>cosine distance
For anything past a small table, add an approximate-nearest-neighbor index — exact nearest-neighbor search over embeddings doesn't scale.
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
-- HNSW: better query performance, slower to build. ivfflat is the other option: faster to build, needs tuning (lists parameter).
This means a RAG pipeline's vector store and its regular relational data (user accounts, document metadata, permissions) can live in one PostgreSQL database instead of a separate dedicated vector database — one fewer moving part, and JOINs work normally between embeddings and everything else.

other extensions worth knowing exist

ExtensionAdds
PostGISgeographic/geometric types and spatial queries — the standard for location-based data in Postgres
pg_stat_statementstracks execution statistics for every query that's run — the standard way to find your actual slowest/most frequent queries in production, rather than guessing
uuid-ossp / pgcryptoUUID generation functions (gen_random_uuid() is now built into core as of PG13, making this less necessary than it used to be)
pg_trgmtrigram-based fuzzy string matching — typo-tolerant search and similarity scoring that full-text search's exact stemming doesn't cover

where to go from here

JSONB — another way semi-structured/AI-adjacent data lives inside PostgreSQL.
GPU Programming — the compute side of the embeddings pgvector searches over.