Skip to content
Tutorials Center
My learning

Topics

Lesson 57 of 90 ยท Extensions and Ecosystem

pgvector and PostGIS

Add vector search and maps to PostgreSQL: pgvector columns, distance operators, HNSW and IVFFlat indexes, plus PostGIS geometry and geography basics.

Two third-party extensions turn PostgreSQL into a specialist database without leaving SQL. pgvector stores lists of numbers called vectors and finds the ones closest to a query vector. That is the building block of semantic search, recommendations and retrieval for AI applications. PostGIS adds geographic types and hundreds of functions, so you can store places, measure distances on the Earth and ask which points fall inside which regions.

Neither extension ships with PostgreSQL, and neither can run in the browser, so every PostgreSQL block in this lesson is read-only. The live editors use plain SQLite to rebuild the core idea of each one from scratch: nearest-neighbour ranking with distance formulas, and great-circle distance between cities. Doing the maths once by hand makes the extensions' operators easy to understand. For a broader introduction to location queries in portable SQL, see Geospatial SQL.

pgvector: vectors and nearest neighbours

An embedding is a list of numbers that an AI model produces for a piece of content, such as a sentence, an image or a product. Content with similar meaning gets vectors that point in similar directions. The database does not create embeddings; your application asks a model for them and stores the result. The database's job is to find the nearest vectors quickly.

SQL
CREATE EXTENSION IF NOT EXISTS vector;     -- note: the extension is named "vector"

CREATE TABLE articles (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title     text NOT NULL,
    embedding vector(3)                    -- real models use hundreds or thousands of dimensions
);

INSERT INTO articles (title, embedding) VALUES
  ('Baking sourdough bread', '[0.95, 0.10, 0.00]'),
  ('Easy pasta recipes',     '[0.80, 0.20, 0.10]');

The number in vector(3) is the number of dimensions. Every value in a column must have exactly that many, and it has to match what your embedding model produces (often 384, 768, 1024 or 1536). A vector column can hold up to 16,000 dimensions, but indexes support up to 2,000, which covers most models. The extension also offers smaller types (halfvec for half precision, sparsevec, and bit vectors) for saving space.

Distance operators

pgvector defines operators that return a distance, where smaller means closer:

Operator Distance Typical use
<-> Euclidean (L2) Straight-line distance between points
<=> Cosine distance Direction similarity, the usual choice for text embeddings
<#> Negative inner product Normalized vectors; negated so that smaller still means closer
<+> Taxicab (L1) Rarely used

A nearest-neighbour query sorts by the distance to a query vector and takes the first few rows:

SQL
SELECT title, embedding <=> '[0.85, 0.15, 0.05]' AS distance
FROM articles
ORDER BY embedding <=> '[0.85, 0.15, 0.05]'
LIMIT 5;

Cosine distance is 1 - cosine similarity, so if you want a similarity score between -1 and 1, compute 1 - (embedding <=> query).

You can reproduce the arithmetic yourself. The next editor stores five tiny three-number "embeddings" and calculates all three distances with ordinary SQL, the same formulas that the pgvector operators use.

SQL Nearest neighbours by hand

Press Run (or Ctrl + Enter) to see the result here.

Look at the first two results. By Euclidean distance the pasta article is closest; by cosine distance the bread article wins by a hair. The two metrics measure different things: L2 cares about how far apart the points are, cosine only cares about the angle between the arrows. Text embedding models are usually trained with cosine similarity in mind, so cosine (or the inner product on normalized vectors) is the safe default. Always check your model's documentation.

Without an index, PostgreSQL computes the distance to every row and sorts them. That is exact, and perfectly fine for a few tens of thousands of rows. For millions of rows you need an approximate nearest neighbour (ANN) index: it finds very close neighbours much faster, but it might occasionally miss a true neighbour. pgvector has two index types.

Exact scan versus ANN index

No index Exact. Reads every row.
HNSW Graph of neighbours. Fast and accurate.
IVFFlat Clusters. Smaller and quicker to build.

ANN trades a little recall for a lot of speed.

1 / 2
SQL
-- HNSW: build once, no training data needed, usually the best default
CREATE INDEX articles_embedding_hnsw
    ON articles USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- IVFFlat: groups vectors into lists; create it AFTER the table has data
CREATE INDEX articles_embedding_ivf
    ON articles USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

-- Trade recall for speed per session or per query
SET hnsw.ef_search = 100;      -- default is 40
SET ivfflat.probes = 10;       -- default is 1

The operator class (vector_cosine_ops, vector_l2_ops, vector_ip_ops) must match the operator in your ORDER BY, otherwise the index is ignored. HNSW builds slower and uses more memory than IVFFlat but gives better speed for the same recall. IVFFlat needs representative data when it is built, because it picks its cluster centres from existing rows.

Two practical cautions. First, filtering is tricky: an ANN index returns its nearest candidates first, and a WHERE clause is applied afterwards, so a very selective filter can leave you with fewer rows than the LIMIT. Newer pgvector versions have settings to scan further when that happens; for highly selective filters a plain B-tree on the filter column or partitioning may serve you better. Second, vectors are large (a 1,536-dimension vector is about 6 KB), so a table of millions of embeddings is a table of many gigabytes. Plan memory and storage accordingly. BRIN, Hash and SP-GiST Indexes compares index types more broadly.

PostGIS: places on a spherical planet

PostGIS stores spatial data in two main types:

  • geometry works on a flat plane. Distances are in the units of the coordinate system, and calculations are fast.
  • geography works on the Earth's curved surface. Distances come back in metres, which is what most applications want, but some functions are not available.

Locations are identified by a spatial reference system ID (SRID). The one you will use most is 4326, the GPS standard (WGS 84), which uses longitude and latitude in degrees.

SQL
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE stores (
    id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     text NOT NULL,
    location geography(Point, 4326) NOT NULL
);

-- ST_MakePoint takes (longitude, latitude): x first, then y
INSERT INTO stores (name, location) VALUES
  ('Berlin shop', ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326)),
  ('Paris shop',  ST_SetSRID(ST_MakePoint( 2.3522, 48.8566), 4326)),
  ('Warsaw shop', ST_SetSRID(ST_MakePoint(21.0122, 52.2297), 4326));

Longitude comes first. People say "lat, long", but PostGIS points, like x, y on a graph, are written longitude first. Swapping them puts Berlin in the Indian Ocean, and nothing complains.

Distance and radius queries

SQL
-- Distance in metres between two stores
SELECT ST_Distance(a.location, b.location) AS metres
FROM stores AS a, stores AS b
WHERE a.name = 'Berlin shop' AND b.name = 'Paris shop';

-- Which stores are within 600 km of Berlin?
SELECT name
FROM stores
WHERE ST_DWithin(
        location,
        ST_SetSRID(ST_MakePoint(13.4050, 52.5200), 4326)::geography,
        600000                         -- metres
      );

-- The three nearest stores to a customer, nearest first
SELECT name
FROM stores
ORDER BY location <-> ST_SetSRID(ST_MakePoint(14.0, 51.0), 4326)::geography
LIMIT 3;

Use ST_DWithin for "within a radius" instead of ST_Distance(...) < x, because only ST_DWithin can use a spatial index. The <-> operator does the same job for "nearest N". Both rely on a GiST index:

SQL
CREATE INDEX stores_location_gix ON stores USING gist (location);

GIN and GiST Indexes explains how GiST handles two-dimensional data.

The great-circle distance between two points on a sphere has a closed form called the haversine formula, and SQLite has the maths functions to compute it. This stand-in does what ST_Distance does on a perfect sphere (PostGIS uses the slightly flattened WGS 84 spheroid, so its answers differ by a fraction of a percent).

SQL Distances between cities (haversine)

Press Run (or Ctrl + Enter) to see the result here.

The bounding-box query at the end is the key idea behind a spatial index: a GiST index stores the rectangle around every shape, uses it to throw away most rows cheaply, and only then does the exact (more expensive) distance or containment test on the few that remain.

Shapes, areas and containment

Points are only the start. PostGIS also stores lines (LineString) and areas (Polygon), and gives you functions that combine them:

SQL
-- Which delivery zone contains this address?
SELECT z.name
FROM delivery_zones AS z
WHERE ST_Contains(z.area::geometry,
                  ST_SetSRID(ST_MakePoint(13.41, 52.52), 4326));

-- How large is the zone, in square kilometres?
SELECT name, ST_Area(area::geography) / 1e6 AS km2 FROM delivery_zones;

-- Read and write the standard text format
SELECT ST_AsText(location::geometry) FROM stores LIMIT 1;    -- POINT(13.405 52.52)
SELECT ST_GeomFromText('POINT(13.405 52.52)', 4326);

Common spatial predicates include ST_Contains, ST_Within, ST_Intersects, ST_Touches and ST_Overlaps. Most return a boolean, so they work as JOIN conditions: "join every order to the delivery zone it falls in" is a single ST_Contains join, which a GiST index makes fast.

Choosing what you need

If you only need to know the distance between two coordinates now and then, a haversine function in plain SQL is enough and costs you no extension. As soon as you need indexed "within a radius" queries, polygons or map tooling, PostGIS pays for itself. Likewise, for a few thousand embeddings an exact scan is fine; reach for an HNSW or IVFFlat index when the table and the latency grow. Check that your host allows these extensions first (see Extensions); both are widely supported on managed services.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In pgvector, which operator returns the cosine distance?

    <=> is cosine distance, <-> is Euclidean (L2) distance and <#> is the negative inner product.
  2. 2. You created an HNSW index with vector_cosine_ops, but queries use ORDER BY embedding <-> query. What happens?

    The operator class must match the operator in ORDER BY. An index built for cosine distance does not help a Euclidean query, so the planner falls back to an exact scan.
  3. 3. What is the trade-off of an approximate nearest-neighbour index such as HNSW?

    ANN indexes accept a small loss of recall in exchange for much faster queries. Settings such as hnsw.ef_search let you trade speed for accuracy.
  4. 4. Which order do you pass the coordinates in ST_MakePoint?

    PostGIS follows the x, y convention, so longitude (x) comes first. Swapping them silently puts the point in the wrong place.
  5. 5. Why prefer ST_DWithin(a, b, 5000) over ST_Distance(a, b) < 5000 in a WHERE clause?

    ST_DWithin is index-aware, so the GiST index can discard far-away rows before any exact distance is computed. Comparing ST_Distance forces the function to run on every row.

Summary

  • pgvector adds the vector(n) type and the operators <-> (L2), <=> (cosine), <#> (negative inner product) and <+> (L1); nearest-neighbour search is ORDER BY embedding <=> query LIMIT k.
  • Without an index the search is exact. HNSW and IVFFlat indexes make it approximate but fast; the operator class must match the operator you sort by.
  • Embeddings come from a model in your application, and the dimension must match the model.
  • PostGIS adds geometry (flat) and geography (spherical, metres) types, SRID 4326 for GPS coordinates, and functions such as ST_Distance, ST_DWithin, ST_Contains and ST_AsText.
  • Points are written longitude first. Use ST_DWithin and GiST indexes for radius and nearest-neighbour queries.
  • Both are third-party extensions, so check your hosting provider's allowlist before relying on them.

Next: Foreign Data Wrappers and Cross-Database Access, where PostgreSQL queries data that lives somewhere else.