Supabase pgvector Query Latency: 800ms to 12ms on Small Tables

Query times were clocking 800ms on a table with fewer than 50,000 rows. Same OpenAI text-embedding-3-small embeddings. Same 1536-dimension vectors. The only difference after the fix? 12ms. That 65x gap isn’t from hardware or scale — it’s from misunderstanding how pgvector actually works on small tables in Supabase.

This matters because the OpenAI embedding API has become the default starting point for AI-powered search in 2026. According to Supabase’s own documentation, pgvector is the recommended path for storing and querying embeddings directly inside Postgres — and it’s genuinely excellent. But the default configuration is tuned for large-scale deployments, not the small-to-medium tables most developers actually ship first.

The thesis is straightforward: pgvector with 1536-dimension OpenAI embeddings requires a fundamentally different indexing strategy on small tables than what you’d use at scale. Most tutorials skip this entirely.

What’s covered here:

• Why 1536-dimension vectors create specific indexing pressure in pgvector
• How IVFFlat vs HNSW index choice changes everything at small table sizes
• When NOT to index — and why that’s often the right call under 10K rows
• Configuration parameters that actually move the latency needle

Key Takeaways

• Supabase’s pgvector with OpenAI’s 1536-dimension embeddings defaults to index settings optimized for large datasets, which actively hurts query latency on small tables.
• According to Supabase’s AI documentation, IVFFlat requires at least lists × 10 rows to probe effectively — so an IVFFlat index on a 5,000-row table with 100 lists means searching 1,000 rows through unnecessary overhead.
• HNSW indexes (standard in Supabase’s managed Postgres since pgvector 0.5.0, released late 2023) consistently outperform IVFFlat on both recall and latency for tables under ~500K rows.
• For tables under 10,000 rows, sequential scans with no index frequently beat indexed searches — the index overhead exceeds the search savings.
• Tuning probes (IVFFlat) or ef_search (HNSW) at query time — not just at index creation — is the most underused latency lever in the Supabase pgvector stack.

Why 1536 Dimensions Create Unique Index Pressure

High-dimensional vectors break the assumptions most database indexes rely on. In lower-dimensional spaces — say, 128 dims — approximate nearest neighbor algorithms prune search space aggressively. At 1536 dimensions, the curse of dimensionality means distances between random vectors cluster tightly together. Pruning becomes less effective.

Concretely: with 1536-dimension vectors, pgvector’s IVFFlat needs to probe more lists to maintain acceptable recall. Supabase’s documentation recommends setting probes to roughly sqrt(lists) at query time. With lists = 100, that’s probes = 10 — meaning pgvector scans 10% of your index partitions. On a 5,000-row table, that’s potentially 500 rows scanned through index overhead that costs more than just scanning all 5,000 rows sequentially.

The math is uncomfortable. But it’s the math.

IVFFlat vs HNSW: The Decision That Actually Matters

This is the core trade-off for query latency optimization on small tables.

The practical upshot: if your table has fewer than 500,000 rows, HNSW is almost always the better choice. According to Supabase’s AI documentation, HNSW builds a graph structure that supports fast traversal with high recall — and unlike IVFFlat, it doesn’t partition your data into clusters that need careful tuning to stay balanced.

IVFFlat’s advantage is build speed and memory efficiency. At 10M+ rows, that matters a lot. At 50,000 rows, it doesn’t matter at all — and the query performance difference is significant.

The Under-10K Row Case: Skip the Index

Counterintuitive but data-backed: for tables under roughly 10,000 rows with 1536-dimension vectors, a sequential scan often beats both IVFFlat and HNSW on query latency.

pgvector’s sequential scan (SELECT ... ORDER BY embedding <=> $1 LIMIT 10) reads every row and computes cosine similarity. At 10,000 rows × 6KB per vector, that’s ~60MB of data — easily held in Postgres’s shared buffer cache on any modern instance. The index overhead (graph traversal for HNSW, cluster lookup for IVFFlat) can exceed the cost of the raw scan.

Supabase’s documentation explicitly notes this: “For small tables, a sequential scan may be faster than an index scan.” The threshold isn’t exact — it depends on your Postgres instance size, cache configuration, and query patterns — but 10,000 rows is a reasonable rule of thumb.

The action is simple: benchmark both. Run EXPLAIN ANALYZE on your similarity query with and without the index, then decide based on actual execution times, not assumptions.

Query-Time Parameter Tuning

Index creation parameters (lists for IVFFlat, m and ef_construction for HNSW) get all the attention. But query-time parameters move the needle just as much — and you can change them without rebuilding the index.

For IVFFlat: SET ivfflat.probes = 20; before your query increases recall at the cost of latency. The default is 1. At small tables, bumping this to sqrt(lists) or higher dramatically improves result quality without much latency penalty, since the table is small anyway.

For HNSW: SET hnsw.ef_search = 100; controls how many candidates the graph traversal considers. The default is 40. Higher values improve recall; lower values drop latency. For a 50,000-row table, ef_search = 100 adds maybe 2–3ms and noticeably improves top-10 result quality.

Neither parameter requires a schema change or downtime. They’re session-level settings — set them per query in your Supabase edge function or server-side code.

Practical Implications

The small-table scenario is almost universal at launch. Most teams shipping AI search in 2026 start with a knowledge base, a document store, or a product catalog that fits comfortably under 100,000 rows. This isn’t an edge case — it’s the default starting state.

This approach can fail when teams copy index configurations from large-scale tutorials without adjusting for their actual row counts. The IVFFlat defaults in most documentation are optimized for millions of rows. Applying them to thousands actively degrades performance.

Scenario 1: Knowledge base with ~5,000 documents. Skip the index entirely. A sequential scan on 5K rows with 1536-dimension vectors takes under 20ms on Supabase’s smallest Pro instance. Add the index only when the table crosses ~10,000 rows — and when you do, start with HNSW using m = 16, ef_construction = 64.

Scenario 2: Product catalog with 50,000–200,000 SKUs. HNSW is the right call. Create the index with m = 16, ef_construction = 200 for better recall, then set hnsw.ef_search = 80 at query time. Expect sub-30ms similarity searches without any caching layer.

Scenario 3: Scaling past 500,000 rows. This is where IVFFlat’s memory efficiency becomes relevant. Set lists = ceil(sqrt(row_count)) as a starting point — roughly 700 lists for 500K rows — and tune probes based on your recall/latency trade-off. Monitor index build time: at this scale, HNSW builds can take 30+ minutes on a standard Supabase instance.

This isn’t always the answer, either. Teams running on memory-constrained instances, or with extremely high write volumes, may find HNSW’s memory footprint is a real constraint — not a theoretical one. Benchmark on your actual instance before committing.

What to watch over the next few months:

• pgvector 0.8.x is expected to ship improvements to HNSW build speed in 2026, which would remove the main remaining argument for IVFFlat at medium table sizes.
• Supabase’s vector column type inference (currently in preview) may auto-select index strategy based on table size — worth tracking in their changelog.
• OpenAI’s text-embedding-3-small at reduced dimensions (512 or 256 dims via the dimensions parameter) cuts vector storage by 67–83% and meaningfully reduces query latency without catastrophic recall loss for most use cases. The 1536-dimension default isn’t always necessary.

Conclusion

The core findings:

• IVFFlat on small tables is frequently the worst of three options — sequential scan and HNSW both beat it under 200K rows in most benchmarks
• HNSW is the safe default for any Supabase pgvector deployment with 10K–500K rows
• Tables under 10K rows don’t need an index — the sequential scan wins
• Query-time parameters (ef_search, probes) are the fastest latency wins and require zero schema changes

Over the next 6–12 months, expect HNSW to become the unambiguous default recommendation from Supabase’s documentation as pgvector’s build performance improves. The case for IVFFlat will narrow to very large tables (1M+ rows) and memory-constrained environments.

One concrete action: run EXPLAIN (ANALYZE, BUFFERS) on your next similarity query. If you see “Index Scan using ivfflat_idx” with actual time over 50ms on a table under 100K rows, you’ve found your problem. Switch to HNSW or drop the index entirely and measure again.

The pgvector latency problem isn’t about Supabase being slow. It’s about applying large-scale indexing logic to small-scale data. Match your index strategy to your actual table size, and the latency problems mostly disappear.

What’s your current table size, and which index type are you running? That one question will tell you more about your latency profile than any other diagnostic.

References: Supabase AI & Vectors Documentation | Vector Embeddings with OpenAI and Supabase — DEV Community

References

1. AI & Vectors | Supabase Docs
2. Vector Embeddings (with OpenAI and Supabase) - Part 3 (storing & searching) - DEV Community

Photo by Surface on Unsplash