Connect SPSync to PostgreSQL with pgvector

This destination is PostgreSQL with the pgvector extension. SPSync loads your lists into ordinary tables exactly as it does for PostgreSQL, then adds a vector column that holds an embedding of the text you choose, and keeps an HNSW index over it. Read the PostgreSQL guide for the firewall, TLS and cloud notes; this page covers what is different.

Before you start

PostgreSQL 15 or newer. pgvector itself runs on older versions, but SPSync updates rows in place with a MERGE statement, which Postgres supports from 15. On 13 or 14 the first sync works and the second fails. The connection test checks the version for you.
The pgvector extension available on the server. Managed services list it as a supported extension: Azure Database for PostgreSQL (allow-list it first), Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, Supabase, Neon. On a server you run, install the pgvector package for your Postgres version.
An embedding provider: an OpenAI key, an Azure OpenAI deployment, or the offline local model that needs no key.

Create the database and a least-privilege user

Create the role and database as on the PostgreSQL page, then enable the extension once, as an administrator. These statements are from the PostgreSQL and pgvector documentation and have not been run here against a test server.

CREATE ROLE spsync_loader LOGIN PASSWORD 'use-a-long-random-password';
CREATE DATABASE spsync OWNER spsync_loader;
\c spsync
CREATE EXTENSION IF NOT EXISTS vector;

Creating an extension normally needs a superuser or, on a managed service, the admin role. SPSync runs CREATE EXTENSION IF NOT EXISTS vector itself on every connection. If the extension already exists that statement needs no privilege, which is why you create it once as an administrator and let spsync_loader stay an ordinary owner. Without that, the first sync fails with a permission error. If you cannot hand over ownership of the database, the same schema grants as on the PostgreSQL page apply, and the role also needs CREATE on the schema to add the vector column and the sp_embedding_meta table.

Allow SPSync to reach it

As for PostgreSQL: TCP 5432, with the firewall, security group or allow-list for your host described in the PostgreSQL guide. The embedding provider is a separate path. openai and azure-openai need outbound HTTPS from the machine that runs SPSync to the provider, and local needs it once to download the model.

Encrypt the connection

Put sslmode=require in the connection string, or sslmode=verify-full with sslrootcert=/path/to/ca.pem to check the server’s identity. The details per host are in the PostgreSQL guide.

Authentication SPSync supports today

A role and password in the connection string, exactly as for PostgreSQL. Entra ID and cloud IAM database tokens are not supported. The embedding provider has its own credential: an OpenAI API key, or an Azure OpenAI key and endpoint. The local provider has none.

Connect from SPSync

HOST is your server’s name.

postgresql://spsync_loader:PASSWORD@HOST:5432/spsync?sslmode=require

Choose the columns to embed by their destination names, for example title and description, as spsync discover schema shows them.

Setting	What it is
Provider	`openai`, `azure-openai` or `local`.
Model	The model name. For `azure-openai`, the deployment name.
Dimensions	The vector width, for example 1536 for `text-embedding-3-small`. The column and index are built from it.
Columns	Which text columns to join, in order, as the text to embed.
API key	The provider key. `openai` and `azure-openai` only.
Azure OpenAI endpoint	`azure-openai` only.

Desktop app: open the Destination screen, choose Postgres + pgvector, paste the string into Connection string, fill in the embedding fields and choose Test connection. The form does not check that a key is present for the openai and azure-openai providers, so a missing key shows up at the first sync.

CLI:

export SPSYNC_DSN='postgresql://spsync_loader:PASSWORD@HOST:5432/spsync?sslmode=require'
export SPSYNC_EMBED_KEY='<provider key>'
spsync export-config --output replication.yaml \
  --site-url https://contoso.sharepoint.com/sites/x --list Projects \
  --pipeline-name spsync_dev --prefix dev \
  --destination-kind pgvector --connection-string-env SPSYNC_DSN --dataset-name sync \
  --pgvector-embedding-provider openai \
  --pgvector-embedding-model text-embedding-3-small \
  --pgvector-embedding-dimensions 1536 \
  --pgvector-embedding-columns title,description \
  --pgvector-api-key-env SPSYNC_EMBED_KEY \
  --client-id <entra-app-client-id>

AI assistant: with the admin MCP server connected, ask for the destination requirements, then let the assistant create the destination with the provider, model, dimensions and columns. You enter the connection string on the secure entry page, never in the chat.

SPSync re-embeds only the rows whose source text changed since the last round, so a steady sync costs little. Changing the model re-embeds every row. Changing the dimensions adds a fresh sp_embedding_v2 column, because Postgres cannot resize a vector column in place. A row deleted in SharePoint keeps its embedding and has sp_deleted_at set, as in the other database destinations, so add WHERE sp_deleted_at IS NULL to a similarity query.

What Test connection and doctor check

Both connect, run CREATE EXTENSION IF NOT EXISTS vector and read the server version. A pass means the host is reachable, TLS and the password are right, the extension is present and the server is PostgreSQL 15 or newer. It does not test the embedding provider’s key or create tables. spsync doctor reports the probe as destination-connectivity.

Common errors

What you see	What it means and the fix
`permission denied to create extension "vector"`	The role cannot create extensions. Create it once as an administrator (see above).
`extension "vector" is not available` or `could not open extension control file`	pgvector is not installed on the server, or on Azure it is not on the allow-list. Install or allow it, then retry.
`pgvector destination needs PostgreSQL 15 or newer`	The server is older. Upgrade it, or use the plain PostgreSQL destination without embeddings.
`syntax error at or near "MERGE"`	The same version problem, seen on the second sync.
`expected N dimensions, not M`	The model produces a different vector width from the one configured. Set the dimensions to the model’s size.
`401` or `Incorrect API key` from the provider	The embedding key is wrong. It is separate from the database password.
Errors in the PostgreSQL guide’s table	Connection, firewall, TLS and password errors are the same as for PostgreSQL.