How to Index Vectors
Last verified 13 Jul 2026
DigitalOcean Managed PostgreSQL for vector search uses the same managed PostgreSQL engine available under Managed Databases, with the pgvector and pgvectorscale extensions for storing and querying vector embeddings alongside relational data.
PostgreSQL stores vector embeddings in vector columns. After you create a PostgreSQL vector database cluster, enable the vector extension, and create a table with a vector column, you can insert rows with embeddings using standard SQL. Make sure you have set your environment variables and verified the connection.
Most applications generate embeddings in application code using providers such as DigitalOcean Serverless Inference, OpenAI, Cohere, Voyage, or sentence-transformers, and then insert the resulting float arrays into PostgreSQL.
To start indexing vectors, first go to the Vector Databases page and select the cluster you want to use.
Then, go to the Network Access tab, find the trusted source you want to connect to, and then open a terminal session.
Index a Single Vector
Use a single-row insert when you want to add one document at a time.
To index a single vector, insert the document fields you want to store and an embedding value that matches the dimension of the table’s vector column.
For example, the following query inserts one row into the documents table:
INSERT INTO documents (id, title, body, source, embedding)
VALUES (
'doc-1',
'Introduction to PostgreSQL vector search',
'PostgreSQL stores vector embeddings in vector columns.',
'blog',
'[0.0123, -0.0456, 0.0789, ...]'::vector
);The embedding value must match the dimension you configured for the column. For example, if the column is embedding vector(1024), each inserted row must provide a 1024-dimensional vector.
Use Python when your application generates embeddings before inserting rows.
To index a single vector, provide the row fields you want to store and an embedding value generated by your embedding function:
import os
import psycopg
conninfo = (
f"host={os.environ['PGHOST']} "
f"port={os.environ.get('PGPORT', '25060')} "
f"user={os.environ['PGUSER']} "
f"password={os.environ['PGPASSWORD']} "
f"dbname={os.environ['PGDATABASE']} "
f"sslmode={os.environ.get('PGSSLMODE', 'require')}"
)
embedding = embed_one("Introduction to PostgreSQL vector search")
with psycopg.connect(conninfo) as conn:
with conn.cursor() as cur:
cur.execute(
"""
INSERT INTO documents (id, title, body, source, embedding)
VALUES (%s, %s, %s, %s, %s::vector);
""",
(
"doc-1",
"Introduction to PostgreSQL vector search",
"PostgreSQL stores vector embeddings in vector columns.",
"blog",
embedding,
),
)In this example, embed_one() represents your application’s embedding function. Replace it with the function or API call you use to generate embeddings. The generated embedding must match the dimension of the table’s vector column.
Bulk Index Vectors
Use bulk inserts when you need to index more than a few rows. Bulk inserts are faster than sending one insert per row because PostgreSQL can process multiple rows in fewer transactions.
For example, the following examples bulk insert rows into a table named documents with a 1024-dimensional embedding column:
Use a multi-row INSERT statement for small batches:
INSERT INTO documents (id, title, body, source, embedding)
VALUES
(
'doc-1',
'Introduction to PostgreSQL vector search',
'PostgreSQL stores vector embeddings in vector columns.',
'blog',
'[0.0123, -0.0456, 0.0789, ...]'::vector
),
(
'doc-2',
'Hybrid search basics',
'Hybrid search combines keyword and vector search.',
'docs',
'[0.0211, -0.0322, 0.0654, ...]'::vector
);Use COPY for larger ingestion jobs. COPY loads rows from a file more efficiently than individual inserts.
First, create a documents.csv file with the rows you want to insert:
doc-1,Introduction to PostgreSQL vector search,PostgreSQL stores vector embeddings in vector columns.,blog,"[0.0123,-0.0456,0.0789,...]"
doc-2,Hybrid search basics,Hybrid search combines keyword and vector search.,docs,"[0.0211,-0.0322,0.0654,...]"Then, load the file into the documents table:
\copy documents (id, title, body, source, embedding) FROM 'documents.csv' WITH (FORMAT csv);Use executemany() for small-to-medium batches.
First, create a docs list with one object for each row you want to insert. Each object should include the document metadata and embedding values like this:
docs = [
{
"id": "doc-1",
"title": "Introduction to PostgreSQL vector search",
"body": "PostgreSQL stores vector embeddings in vector columns.",
"source": "blog",
"embedding": [0.0123, -0.0456, 0.0789, "..."],
},
{
"id": "doc-2",
"title": "Hybrid search basics",
"body": "Hybrid search combines keyword and vector search.",
"source": "docs",
"embedding": [0.0211, -0.0322, 0.0654, "..."],
},
]Then, insert the rows:
import os
import psycopg
conninfo = (
f"host={os.environ['PGHOST']} "
f"port={os.environ.get('PGPORT', '25060')} "
f"user={os.environ['PGUSER']} "
f"password={os.environ['PGPASSWORD']} "
f"dbname={os.environ['PGDATABASE']} "
f"sslmode={os.environ.get('PGSSLMODE', 'require')}"
)
rows = [
(
doc["id"],
doc["title"],
doc["body"],
doc["source"],
doc["embedding"],
)
for doc in docs
]
with psycopg.connect(conninfo) as conn:
with conn.cursor() as cur:
cur.executemany(
"""
INSERT INTO documents (id, title, body, source, embedding)
VALUES (%s, %s, %s, %s, %s::vector);
""",
rows,
)For larger ingestion jobs, use PostgreSQL’s COPY support from your application or load data from a file with psql.
For large initial loads, create the table and load data before creating IVFFlat indexes. IVFFlat uses the data present at build time to choose list centroids. For HNSW indexes, you can create the index before or after loading data, but loading data first can reduce ingestion overhead.
After large inserts, run ANALYZE so PostgreSQL has current table statistics:
ANALYZE documents;After you index vectors, query vectors either using HNSW search, IVFFLAT search, or using pgvectorscale.