Storing OpenAI embeddings in Postgres with Go

To store OpenAI embeddings in Postgres with Go, you need three moving parts: a call to the OpenAI embeddings endpoint, a pgvector-backed Postgres table, and a small Go service that persists the vectors. This walkthrough builds a minimal but production-realistic pipeline you can drop into a codebase, covering schema, insertion, and cosine similarity search without the boilerplate bloat.

Step 1: Provision Postgres and enable pgvector

You need Postgres 12+ and the pgvector extension. On a fresh instance, install the extension from your package manager or use a prebuilt image.

# Using the official pgvector image
docker run -d --name pgvec -p 5432:5432 -e POSTGRES_PASSWORD=secret ankane/pgvector:latest

Connect and create the extension:

psql "postgres://postgres:secret@localhost:5432/postgres" -c "CREATE EXTENSION IF NOT EXISTS vector;"

If you run managed Postgres (RDS, Cloud SQL), check that your version supports pgvector or load it as a trusted extension. Without it, you cannot use the vector type or the indexing methods that make similarity queries fast. The extension adds a new column type and distance operators; it is not a separate service to maintain.

Step 2: Install Go dependencies and configure the embeddings client

Initialize a Go module and pull in pgx for database access and the official OpenAI Go SDK.

go mod init embedstore
go get github.com/openai/openai-go
go get github.com/jackc/pgx/v5

Configure the client. Use an environment variable for the API key. If you want a single OpenAI-compatible endpoint with automatic fallback across providers, you can point the base URL at n4n.ai; the embeddings request shape is identical.

package main

import (
	"context"
	"os"

	openai "github.com/openai/openai-go"
	"github.com/openai/openai-go/option"
)

func newEmbedClient() *openai.Client {
	apiKey := os.Getenv("OPENAI_API_KEY")
	// Optional: swap to n4n.ai or any OpenAI-compatible gateway
	// baseURL := "https://api.n4n.ai/v1"
	// return openai.NewClient(option.WithAPIKey(apiKey), option.WithBaseURL(baseURL))
	return openai.NewClient(option.WithAPIKey(apiKey))
}

We use text-embedding-3-small as the default model. It returns 1536-dimensional vectors. Keep the dimension fixed in your schema; changing models later means a migration because the vector length is baked into the column type.

Step 3: Generate and store OpenAI embeddings in Postgres with Go

Define a table that holds the embedding and a reference to the source text.

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536)
);

Create an index for approximate nearest neighbor search. IVFFlat is good for moderate datasets; HNSW is better at scale.

CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Now write the Go function that embeds a string and inserts a row. The OpenAI SDK returns a slice of floats. pgx accepts a string representation of the vector: [0.1,0.2,...].

import (
	"errors"
	"fmt"
	"strconv"
	"strings"

	"github.com/jackc/pgx/v5"
)

func storeDoc(ctx context.Context, conn *pgx.Conn, client *openai.Client, content string) error {
	resp, err := client.Embeddings.New(ctx, openai.EmbeddingNewParams{
		Input: openai.String(content),
		Model: "text-embedding-3-small",
	})
	if err != nil {
		return fmt.Errorf("embed: %w", err)
	}
	if len(resp.Data) == 0 {
		return errors.New("no embedding returned")
	}
	vec := resp.Data[0].Embedding
	vecStr := "[" + strings.Join(floatSlice(vec), ",") + "]"

	_, err = conn.Exec(ctx,
		"INSERT INTO documents (content, embedding) VALUES ($1, $2)",
		content, vecStr)
	return err
}

func floatSlice(v []float64) []string {
	out := make([]string, len(v))
	for i, f := range v {
		out[i] = strconv.FormatFloat(f, 'f', -1, 64)
	}
	return out
}

This is the core of how you store OpenAI embeddings in Postgres with Go. The conversion to a bracketed comma-separated string is required because pgx does not ship a native vector type encoder; the extension parses it server-side. Batch inserts by issuing multiple Exec calls inside a transaction for throughput, or use COPY for bulk loads.

Handle context cancellation explicitly. If the embeddings API hangs, your ctx should have a timeout so the database transaction does not block. Wrap the call in a context.WithTimeout of 10–30 seconds depending on payload size.

Step 4: Query nearest neighbors with cosine distance

Retrieve the top-k most similar documents to a query string. Embed the query, then run a SQL query using the <=> cosine distance operator.

func search(ctx context.Context, conn *pgx.Conn, client *openai.Client, query string, k int) ([]string, error) {
	resp, err := client.Embeddings.New(ctx, openai.EmbeddingNewParams{
		Input: openai.String(query),
		Model: "text-embedding-3-small",
	})
	if err != nil {
		return nil, err
	}
	vec := resp.Data[0].Embedding
	vecStr := "[" + strings.Join(floatSlice(vec), ",") + "]"

	rows, err := conn.Query(ctx,
		`SELECT content, 1 - (embedding <=> $1) AS similarity
		 FROM documents
		 ORDER BY embedding <=> $1
		 LIMIT $2`, vecStr, k)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var results []string
	for rows.Next() {
		var content string
		var sim float64
		if err := rows.Scan(&content, &sim); err != nil {
			return nil, err
		}
		results = append(results, fmt.Sprintf("%s (sim=%.3f)", content, sim))
	}
	return results, rows.Err()
}

The <=> operator computes cosine distance; 1 - distance gives similarity. For a dataset of millions of rows, tune the IVFFlat probes or switch to HNSW. Note that the query vector must match the stored dimension exactly, or Postgres throws a mismatch error at runtime.

Step 5: Verify the pipeline end to end

Write a small main that inserts two known sentences and queries for a paraphrase.

func main() {
	ctx := context.Background()
	conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
	if err != nil { log.Fatal(err) }
	defer conn.Close(ctx)

	client := newEmbedClient()
	if err := storeDoc(ctx, conn, client, "The cat sat on the mat"); err != nil { log.Fatal(err) }
	if err := storeDoc(ctx, conn, client, "A dog played in the park"); err != nil { log.Fatal(err) }

	results, err := search(ctx, conn, client, "A feline rested on the rug", 1)
	if err != nil { log.Fatal(err) }
	fmt.Println("Top match:", results)
}

Run it:

export OPENAI_API_KEY=sk-...
export DATABASE_URL="postgres://postgres:secret@localhost:5432/postgres"
go run .

Expected output names the cat sentence as the top match with similarity above 0.5. If you get a distance operator error, confirm pgvector is installed and the column type is vector(1536).

Verification checklist

• SELECT count(*) FROM documents; returns the inserted rows.
• SELECT vector_dims(embedding) FROM documents LIMIT 1; returns 1536.
• Query latency is sub-100ms on a few thousand rows without full index scans.
• Re-running the query with a clearly unrelated phrase (“stock market futures”) returns the dog sentence or low similarity scores.

Operational notes

Don’t store embeddings as JSON or float[]. The vector type uses roughly half the space and supports indexed distance ops. Keep the model name in a side table if you mix dimensions across collections.

For high write throughput, use COPY with the vector text format instead of per-row INSERT. For reads, set ivfflat.probes to 10 or higher to trade recall for speed. Monitor index build time; IVFFlat needs a representative sample of rows before CREATE INDEX for good centroid selection.

If you need to rotate providers or avoid rate limits, an OpenAI-compatible gateway with fallback saves rewriting the client. The code above already isolates the client constructor, so the swap is one line. That is the whole path to store OpenAI embeddings in Postgres with Go and run semantic search on your own data.