pgvector¶
PostgreSQL 17 with pgvector, for when you would rather keep embeddings next to the rest of your data than run a second database.
spin up pgvector # database, extension already installed
spin cli pgvector # psql, inside the container
spin url pgvector # print the connection string
Ports¶
| Service | Host port | Container | Env var |
|---|---|---|---|
| pgvector | 5433 |
5432 | PGVECTOR_PORT |
5433, not 5432: the postgres stack has that one, and every stack has to
be able to run beside every other. So psql -h localhost alone reaches
postgres; this one needs -p 5433.
Credentials¶
| What | Default |
|---|---|
| User | spinup |
| Password | spinup |
| Database | spinup |
These are development defaults on a local machine. Change them in
spin env pgvector --edit, and never expose this stack to a network you do
not control.
The extension¶
The image makes vector available but does not install it — a fresh database
lists it in pg_available_extensions with an empty installed_version. This
stack ships init/01-vector.sql, which installs it on first start, so there is
nothing for you to run:
The script also installs it into template1, so any database you create later
inherits it and CREATE DATABASE app does not hand you a Postgres with no
vector type.
Querying¶
CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));
INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
-- nearest neighbours by L2 distance
SELECT id, embedding <-> '[3,1,2]' AS distance
FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
The three operators are <-> L2, <=> cosine, <#> negative inner product.
For anything beyond a few thousand rows, add an index:
Seeding¶
Same contract as the postgres stack: anything in init/ — .sql, .sql.gz
or .sh — runs once, in alphabetical order, the first time the data volume is
created. 01-vector.sql is already there, so name yours 02- or later.
Storage¶
One named volume, pgvector-data, at /var/lib/postgresql/data. spin down
keeps it; only spin destroy deletes it.
Gotchas¶
init/runs only on an empty data volume. Edit01-vector.sql, or add a seed script, after the stack has started once and nothing happens.spin destroy pgvectorfirst, thenspin up. This surprises everyone at least once.- The env vars are
PGVECTOR_*, notPOSTGRES_*, deliberately: compose gives your shell precedence over the env file, so aPOSTGRES_PASSWORDyou exported for something else would otherwise reach in and change this stack. PGVECTOR_USER,PGVECTOR_PASSWORDandPGVECTOR_DBare only read when the data volume is empty — the same rule as thepostgresstack.- Restoring a dump into a database created outside this container means
running
CREATE EXTENSION vector;yourself; onlytemplate1was seeded. - No GUI here. The
postgresstack already ships pgAdmin on8080andadminersits on8084; a third admin container would cost a port and a few hundred MB for nothing. From either one, connect to hosthost.docker.internal, port5433— notlocalhost, which inside those containers is the container itself. - Pinned to
pg17, notlatest: the tag family runspg13…pg18, and the pgvector version rides along with it.