Install pgvector and Define a Vector Column

~18 min · pgvector, setup, sql

Level 0Scout

0 XP0/41 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Two minutes from zero

Install the extension once per database, then add a vector(N) column. The dimension is fixed at column-creation time and must match your embedding model exactly. Get this wrong and inserts will throw a runtime error.

Storage characteristics

A 1024-dim float vector is 4 KB on disk. A million chunks ≈ 4 GB before indexes; expect 1.5–2x that with HNSW. Postgres handles this fine on commodity hardware. Plan disk accordingly.

Code

Bring up pgvector·sql

-- Run inside psql for the target database
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
    id          bigserial PRIMARY KEY,
    source      text        NOT NULL,
    chunk_index int         NOT NULL,
    text        text        NOT NULL,
    metadata    jsonb       DEFAULT '{}'::jsonb,
    embedding   vector(1024) NOT NULL,
    updated_at  timestamptz DEFAULT now()
);

CREATE INDEX ON chunks USING gin (metadata jsonb_path_ops);
CREATE INDEX ON chunks (source, chunk_index);

Insert a chunk from Python·python

import psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect('postgresql://localhost/myapp')
register_vector(conn)

with conn.cursor() as cur:
    cur.execute(
        'INSERT INTO chunks (source, chunk_index, text, metadata, embedding) '
        'VALUES (%s, %s, %s, %s, %s)',
        (chunk['metadata']['source'], chunk['metadata']['chunk_index'],
         chunk['text'], json.dumps(chunk['metadata']), vec),
    )
conn.commit()

External links

Exercise

Spin up a local Postgres (Docker, brew, or any flavor you have). Install pgvector. Create the chunks table above. Insert 50 rows from Python. Confirm with SELECT count(*).

Progress

Progress is local-only — sign in to sync across devices.

← PreviousWhen SQL Beats a Pure Vector DB Next →HNSW vs IVFFlat: Picking the Index

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.