Supabase pgvector Slow Query: HNSW vs IVFFlat Under 100k Rows

Your Supabase embedding search is slow. You added an index. It’s still slow. Or worse — it got slower on your 50k-row table.

That’s one of the most common pgvector mistakes showing up in production right now, and the fix isn’t obvious until you understand what these indexes actually do under the hood.

Key Takeaways

• HNSW outperforms IVFFlat on datasets under 100k rows — no training phase required, and consistent query latency without list-tuning overhead.
• Running a Supabase pgvector embedding search on a small dataset under 100k rows without any index can actually outperform a misconfigured IVFFlat index, according to pgvector’s official documentation on index selection.
• The probes parameter in IVFFlat and ef_search in HNSW are the two most impactful tuning levers — wrong defaults account for most slow query complaints reported in Supabase community forums through early 2026.
• For datasets below 10k rows, sequential scans often beat both index types; the index-vs-scan crossover point matters more than index type selection.

Background: Why pgvector Index Choice Became a Crisis Point

pgvector shipped as a Postgres extension in 2021. By 2023, Supabase made it a first-class feature with one-click enablement — which meant thousands of developers who’d never thought about ANN (approximate nearest neighbor) algorithms suddenly had to choose between HNSW and IVFFlat without much guidance.

The problem compounded fast. Embedding search became the backbone of RAG pipelines, semantic search features, and recommendation systems, all being built at pace through 2024 and 2025. According to Supabase’s own documentation, pgvector supports vector_cosine_ops, vector_l2_ops, and vector_ip_ops operators — but the index type decision sits entirely with the developer.

Most tutorials defaulted to IVFFlat because it was the original index type supported by pgvector. HNSW support landed in pgvector 0.5.0 (released October 2023) and became the recommended default in most performance guides by mid-2024. But the ecosystem didn’t catch up cleanly. Stack Overflow questions about slow Supabase pgvector query performance spiked through 2025, with the majority pointing to index misconfiguration on datasets under 100k rows — exactly the size range where the choice matters most.

The current state: pgvector 0.7.x (running on most Supabase projects as of April 2026) supports both index types. Neither is universally faster. Dataset size, query patterns, and tuning parameters determine which wins.

Main Analysis

Why Small Datasets Break the IVFFlat Assumptions

IVFFlat works by clustering your vectors into lists during index creation (the training phase), then searching only a subset of those clusters at query time via the probes parameter. On large datasets — think 1M+ rows — this dramatically cuts search space. The math works.

On 50k rows? The training phase costs real time, the clusters are small, and you end up doing nearly as much work as a sequential scan anyway. According to pgvector’s official documentation, the recommended lists value is rows / 1000 for datasets over 1M rows, but just sqrt(rows) for smaller ones. A 50k-row table should use roughly 224 lists. Most developers copy-paste lists = 100 from tutorials written for large datasets and wonder why queries run at 80ms instead of 8ms.

The probes parameter defaults to 1. That means at query time, Postgres searches only 1 of your 224 clusters. Recall tanks. You’re not getting wrong results — you’re getting fast wrong results, which is arguably worse.

HNSW’s Structural Advantage on Smaller Data

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph structure where each node connects to its nearest neighbors. No training phase. No cluster assignment. The index builds incrementally as rows insert.

On a 50k-row Supabase table, HNSW index creation takes seconds rather than the minutes IVFFlat can require. More importantly, query performance is predictable. The ef_search parameter (default: 40) controls how many candidates the graph traversal considers. Bump it to 100 and recall improves measurably — without the catastrophic misses you get from low IVFFlat probes.

According to performance analysis by Dikhyant Krishnant Dalai on Medium (2024), HNSW delivered 2–3x lower p99 latency compared to IVFFlat on datasets in the 50k–200k range at equivalent recall levels. That’s exactly the zone where most Supabase RAG applications sit.

The tradeoff: HNSW uses more memory. Index size can run 2–3x larger than IVFFlat for the same dataset. On Supabase’s free and Pro tiers, that matters.

The Sequential Scan Threshold Nobody Talks About

Below roughly 10k rows, neither index helps. PostgreSQL’s query planner may ignore your index entirely and run a sequential scan — which is actually correct behavior. Sequential scans on small tables fit in memory and finish in under 5ms. An index lookup adds overhead.

This is why slow query issues on tiny datasets often persist after adding an index: the planner skips it. You can force index usage with SET enable_seqscan = off for testing, but the right answer is usually to accept sequential scans below 10k rows and only index once you’re past that threshold.

Comparison: HNSW vs IVFFlat for Supabase pgvector Under 100k Rows

The data points to a clear recommendation for the under-100k range: HNSW wins on simplicity and default performance. IVFFlat’s efficiency gains only materialize at scale it’ll never reach in this size range. A misconfigured IVFFlat index on 75k rows can run 4–5x slower than a default HNSW index on the same data, based on performance benchmarks documented in pgvector’s GitHub issue tracker through late 2024.

Practical Implications: Three Scenarios, Three Fixes

Scenario 1 — You’re getting slow queries on an existing IVFFlat index.

Don’t rebuild immediately. First, increase probes. Run SET ivfflat.probes = 10; in your session and re-run the slow query. If latency drops significantly, you’ve confirmed the diagnosis. Then decide: tune probes globally via ALTER DATABASE ... SET ivfflat.probes = 10, or migrate to HNSW. For anything under 100k rows, migrate.

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

Scenario 2 — You’re building a new Supabase RAG pipeline from scratch.

Start with HNSW. Don’t consider IVFFlat until you’re over 500k rows. Set ef_search between 40 and 100 depending on your recall requirements. According to Supabase’s official pgvector docs, m=16 and ef_construction=64 are solid starting defaults for most use cases.

Scenario 3 — Queries are slow and you have under 10k rows.

Check whether the index is being used at all with EXPLAIN ANALYZE. If the planner chose a sequential scan and queries are still slow, the problem isn’t the index. It’s probably the embedding dimension size, network latency to Supabase, or a missing WHERE clause that should be filtering rows before the vector search runs.

What to watch: Supabase is actively developing alternatives and has hinted at native vector quantization support in 2026 roadmap discussions. Scalar quantization — reducing 1536-dim float32 vectors to int8 — can cut index memory by 4x. That changes the HNSW memory concern entirely for small-tier projects.

This approach can fail when memory constraints on Supabase’s free tier push the HNSW index into swap, erasing the latency advantage entirely. If you’re on a memory-constrained plan and your dataset is approaching 200k rows, benchmark both options before committing. HNSW isn’t always the answer — it’s the right default until memory becomes the bottleneck.

Conclusion & Future Outlook

The Supabase pgvector slow query problem on small datasets is almost always a configuration issue, not a hardware one. The data is clear:

• HNSW beats IVFFlat under 100k rows in default configuration, often by 3–5x on p99 latency
• Sequential scans beat both below roughly 10k rows — stop fighting the query planner
• IVFFlat’s probes=1 default is a trap that silently degrades recall without obvious error signals
• Memory is the only real HNSW downside on Supabase’s smaller plans

Over the next 6–12 months, expect pgvector to ship better automatic index selection hints and potentially smarter default probes tuning. Supabase’s vector storage layer is also likely to evolve with quantization support, which will shift the HNSW memory equation significantly.

The action item is straightforward: audit your existing pgvector indexes today. Run \d+ your_table in the Supabase SQL editor and check what index type you’re actually running. If it’s IVFFlat on under 100k rows, schedule the migration to HNSW. It’s a 10-minute fix that can cut your embedding search latency by half.

What index configuration are you running on your current Supabase pgvector setup — and have you measured your actual recall rate, or just query speed?

References: Supabase pgvector documentation | Optimizing Vector Search at Scale — Dikhyant Krishnant Dalai, Medium

References

1. pgvector: Embeddings and vector similarity | Supabase Docs
2. Optimizing Vector Search at Scale: Lessons from pgvector & Supabase Performance Tuning | by Dikhyant

Photo by Ales Nesetril on Unsplash