< Back to blog home Fujitsu PostgreSQL blog

How to store and query embeddings in PostgreSQL without losing your mind

Gary Evans | June 30, 2025

You can efficiently store, query, and manage vector embeddings in PostgreSQL for powerful similarity searches –without sacrificing performance or maintainability.

Done carelessly, it turns into schema sprawl and performance cliffs. We'll walk through schema design, storing embeddings efficiently, indexing them to stay fast at scale, and querying them without giving up and moving everything to a Python notebook.

Choosing the right data type: Why vector wins

If you're using pgvector, use the vector type it provides. You get a fixed-length vector column, distance operators like <=> (cosine distance), and support for approximate nearest neighbor (ANN) indexes. You could store an embedding as a float8[] array or JSONB list, but both are unindexed and slower to query.

pgvector provides two ANN index types for vector similarity search. IVFFlat partitions vectors into lists and searches the nearest ones. HNSW (Hierarchical Navigable Small World) builds a multi-layer graph and generally offers a better speed-recall trade-off, making it the better default for most production workloads. IVFFlat builds faster and uses less memory.

A vector(1536) takes just over 6KB before table and index overhead, so a million vectors require more than 6GB of vector storage alone. Higher dimensions also mean larger indexes and longer builds. Test against your retrieval workload before fixing the column dimension.

pgvector provides two ANN index types for vector similarity search. IVFFlat partitions vectors into lists and searches the nearest ones. HNSW (Hierarchical Navigable Small World) builds a multi-layer graph and generally offers a better speed-recall trade-off, making it the better default for most production workloads. IVFFlat builds faster and uses less memory.

Decide dimension count early. A vector(1536) takes just over 6KB before table and index overhead, so a million vectors require more than 6GB of vector storage alone. Higher dimensions also mean larger indexes and longer builds. Test against your retrieval workload before fixing the column dimension.

Schema design: Embeddings inline or in a separate table?

Say you've got a table of boat brokerage listings and want to store descriptions as embeddings. You can add a vector column to the existing table, or store embeddings separately and reference the original row by ID. If they're generated once and remain stable, the same table is simplest. If you're iterating on models, testing chunking, or updating embeddings independently of the source record, a separate table avoids locking or bloating your main table.

This lets you split a long description into chunks, keep their order, run batch updates, and re-embed only certain chunks without overloading your base table.

Chunking strategy: Don’t let your embeddings fall apart

Poor chunking gives you noisy embeddings, degraded similarity scores, and inconsistent retrieval. The worst thing you can do is chunk by fixed character count, slicing sentences in half and destroying context.

Chunk by sentence instead, using nltk.sent_tokenize() or spaCy, so each embedding represents a full thought. With a token-limited model, use tiktoken to estimate tokens per sentence and assemble chunks that fit, ideally ending on a sentence boundary. Some overlap helps where continuity matter.

Practical example: Storing embeddings with Python and SQL

Here's one way to embed these chunks from within PostgreSQL, using regex to chunk by sentence instead of bringing a tokenizer into the database, for a reason we'll come to shortly:

The case against in-database embedding at scale

At scale, embedding text from within PostgreSQL has sharp edges.

First, performance. Each ai.openai_embed call is a synchronous request to an external endpoint, so embedding thousands of sentences means thousands of blocking HTTP calls tying up backends. PostgreSQL is a transactional engine, not an orchestrator for network-heavy LLM calls.

Then there's dependency management. Running Python inside PostgreSQL means managing the modules and paths available to the database's Python environment, so you'll hit missing modules, path issues, or serialization limits with third-party packages. This function fails for that reason:

These are solvable but fragile at scale, which is why I used regex above. It's more practical to embed in Python outside the database and write the results back, where you can batch requests and handle failures without tying up PostgreSQL.

PostgreSQL's extensibility still makes in-database embedding compelling for proofs of concept. Define a plpython3u function, load the OpenAI SDK, and embed text from SQL. Just don't take it into production at volume without knowing what it costs.

Querying with similarity

Once your embeddings are stored, you’ll want to query them using similarity search. pgvector supports three relevant distance operators:

• Euclidean or L2 distance: <->
• Negative inner product: <#>
• Cosine distance: <=>

For text, cosine is usually a good starting point because it compares the direction of high-dimensional vectors rather than their magnitude. Similarity versus distance goes deeper, as does what you're actually embedding.

This is an exact nearest neighbor search: PostgreSQL calculates cosine distance against the stored vectors and returns the closest matches. The next step is indexing that vector similarity search so it stays practical as the dataset grows.

Indexing: IVFFlat and HNSW

Past tens of thousands of rows that's a brute-force scan and gets slow. The fix is an approximate nearest neighbor (ANN) index. Neither type guarantees the true nearest neighbors, which for most embedding work is fine. The trade-off is recall against query speed.

IVFFlat

IVFFlat clusters vectors and searches only the nearest lists. As a starting point, pgvector recommends roughly one list per 1,000 rows up to one million rows, then the square root of the row count above that.

Run ANALYZE after creation. For an index scan, the query needs to ORDER BY the distance operator directly in ascending order and include a LIMIT. At query time, ivfflat.probes controls how many lists are searched: higher values improve recall at the cost of speed. IVFFlat needs data before you build it because its clusters derive from the vectors already present.

HNSW

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph of neighboring vectors.

CREATE INDEX ON production.listing_embeddings
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

m controls connections per layer and ef_construction the candidate list during the build. Higher values can improve recall at the cost of build time and memory. At query time, hnsw.ef_search makes the same recall-speed trade-off.

Unlike IVFFlat, HNSW needs no training step and can be created on an empty table.

Choosing between them

Start with HNSW for most production workloads. Its better query performance-recall trade-off is usually worth the additional build time and memory, and not needing a training step makes it easier to work with as data changes. IVFFlat earns its place when build time or memory is constrained, particularly when you can accept the additional tuning and potentially lower recall in exchange.

Filtering and hybrid search

This is a key advantage of PostgreSQL over a standalone vector database such as Pinecone or Qdrant. Vector similarity can work directly with your relational data, including filters, joins, permissions, and full-text search, without synchronizing another data store.

Filtering alongside similarity search

An approximate index finds candidates by proximity, with no knowledge of your filter. With pgvector's approximate indexes, filtering is applied after the index scan. Apply a restrictive filter afterward and most candidates may be eliminated, leaving fewer rows than the LIMIT asked for.

SELECT le.listing_uid, l.name, le.chunk_text
      ,le.embedding <=> $1 AS distance
FROM production.listing_embeddings le
JOIN production.listing l ON l.uid = le.listing_uid
WHERE l.region = 'NZ'
  AND l.listed_at > now() - interval '2 months'
ORDER BY distance
LIMIT 10;

If that region and window are 1% of the table, an ANN search can struggle to return enough matches. pgvector's iterative index scans can scan further until enough results are found. You can also increase hnsw.ef_search or ivfflat.probes, index the filter column, use partial indexes for a few known values, or partition for many.

Combining vector and full-text search

Semantic search finds things that mean the same thing. Lexical search finds things that say the same thing. PostgreSQL has full-text search built in, so hybrid search can combine both in the same database without another search system.

WITH semantic AS (
    SELECT listing_uid, chunk_index,
           row_number() OVER (ORDER BY embedding <=> $1) AS rank
    FROM production.listing_embeddings
    ORDER BY embedding <=> $1 LIMIT 50
),
lexical AS (
    SELECT listing_uid, chunk_index,
           row_number() OVER (ORDER BY ts_rank(
               to_tsvector('english', chunk_text),
               plainto_tsquery('english', $2)) DESC) AS rank
    FROM production.listing_embeddings
    WHERE to_tsvector('english', chunk_text) @@ plainto_tsquery('english', $2)
    LIMIT 50
)
SELECT COALESCE(s.listing_uid, x.listing_uid) AS listing_uid,
       COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + x.rank), 0) AS score
FROM semantic s
FULL OUTER JOIN lexical x
  ON s.listing_uid = x.listing_uid AND s.chunk_index = x.chunk_index
ORDER BY score DESC LIMIT 10;

That's reciprocal rank fusion. Something ranked highly by both scores higher than something ranked highly by one. It handles the user searching an exact model number that means nothing semantically, and the user describing what they want in none of your words.

For a RAG pipeline, retrieval quality matters more than index tuning alone. Improving RAG in PostgreSQL looks at how to improve that retrieval, while the journey from embeddings to answers follows the wider RAG pipeline. PostgreSQL 18's semantic capabilities take that further without adding another system.

Future-proofing your vector store with versioning embeddings

Embeddings are not static. They're a product of the embedding model, the preprocessing, and the chunking method, and a change in any of these shifts the vector space so similarity scores stop being comparable across versions. If you're comparing models such as text-embedding-3-small with a domain-tuned alternative, store the embeddings separately or tag them clearly.

The simplest approach is a model version column included in your queries; you can also use separate indexes or partition tables by version. Treat embeddings as reproducible artifacts rather than values to overwrite, preserving the ability to compare models, A/B test retrieval, or roll back. In regulated environments or systems where explainability matters, that history becomes necessary rather than helpful.

Security and governance for embedded content

An embedding derived from sensitive text inherits its sensitivity, so the security practices you already apply to enterprise databases apply to vector data too. Access controls need to govern retrieval, while audit logging provides a record where explainability matters.

Data sovereignty also applies to where embeddings are stored and processed — one reason an enterprise PostgreSQL platform becomes relevant.

Operating a vector database in production

Re-embedding is the big one. When an embedding model is replaced, you may need to regenerate every vector and rebuild its indexes. At scale that's a project, and versioning makes it survivable. Watch query latency and resource use as vector data grows.

Large vector columns and indexes also affect backup capacity and recovery times, so test both as the database grows. Access controls need to cover the vector data and retrieval queries as well as the source content.

Production brings familiar database concerns:

• Operational continuity once the retrieval layer feeds a business dependency.
• Long-term version support to avoid forced upgrades mid re-embedding cycle.
• Lifecycle management so PostgreSQL upgrades are planned rather than reactive.
• Platform stability once retrieval becomes business-critical.

For teams consolidating vector workloads, moving embeddings between PostgreSQL platforms is a data migration like any other; the retrieval logic is usually harder than the vector columns.

Wrapping up: PostgreSQL as a vector store done right

The best thing about using PostgreSQL for embedding storage is mixing vector search with traditional SQL logic. Want the 10 most similar boats, but only those for sale in a certain region and listed in the last 2 months? That's a WHERE clause, without exporting data to a separate vector database or keeping two systems in sync.

Use the vector type, separate your schema when needed, choose HNSW or IVFFlat based on the workload, combine similarity with filters and full-text search, version your embeddings, and plan for the operational work before it arrives. And best of all, you stay close to your data, where it belongs.

Running vector workloads on an enterprise PostgreSQL platform

Everything here works on community PostgreSQL with pgvector. What changes at the platform level is what happens around the workload once the business depends on it.

Fujitsu Enterprise Postgres is 100% compatible with community PostgreSQL, so pgvector and its ecosystem tooling work unchanged. Transparent data encryption, data masking, and dedicated audit logging add controls around vector embeddings derived from sensitive data.

Long version support, 24/7 global support and deployment across on-premises, hybrid, multi-cloud, Kubernetes and OpenShift address the continuity, lifecycle and residency requirements that arrive with production vector workloads.

Index behavior is worth judging against your own vectors rather than anyone's benchmark. Try Fujitsu Enterprise Postgres and test your embedding workload directly.

Frequently asked questions about Postgres vector embeddings

Other blog posts in this series

• Why Enterprise AI starts with the database - Not the data swamp
• Understanding similarity vs distance in PostgreSQL vector search
• What is usually embedded in vector search: Sentences, words, or tokens?
• Embedding content in PostgreSQL using Python: Combining markup chunking and token-aware chunking
• From embeddings to answers: How to use vector embeddings in a RAG pipeline with PostgreSQL and an LLM
• Improving RAG in PostgreSQL: From basic retrieval to smarter context