SELECT content
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 10;

For a while, that is enough.

That simplicity breaks down as the workload becomes real. The table grows, filters become part of the query path, and recall starts affecting user experience. The index still has to stay fast while new data keeps arriving.

That is when vector search stops being a query pattern and becomes an index design problem.

Most vector search advice starts with algorithms: HNSW, IVFFlat, DiskANN, recall, latency. That is useful, but incomplete once vector search lives inside Postgres. Postgres developers do not choose algorithms in the abstract. They choose indexes under constraints: memory, recall, write volume, filter selectivity, and the operational cost of adding another system.

The right index is not the best ANN algorithm in isolation. It is the index that fits the constraint your workload hits first: memory, recall, writes, or filters.

This article maps those constraints to real Postgres index choices: what each one costs, when it becomes the binding variable, and which index type it points to.

When exact search stops being enough

Exact k-nearest neighbor search compares the query vector against every vector in the table. It gives perfect recall because it does not approximate the result set. It also scales linearly with the number of rows.

That tradeoff is fine early on. Exact search is the right starting point when the dataset is small, the query rate is low or you are still validating whether embeddings work for your application. It also gives you a useful baseline because the results are not affected by index tuning.

The problem shows up when the table grows into millions or tens of millions of vectors, or when users expect low latency. At that point, scanning every vector for every query becomes too expensive.

Approximate nearest neighbor search, or ANN search, exists for this moment. ANN indexes organize vectors ahead of time so the database can search only the most promising candidates instead of scanning the full table. The index gives up a small, controlled amount of accuracy in exchange for much lower query latency.

That is the first tradeoff: ANN is not magic. You are deciding how much recall you can afford to exchange for speed, memory efficiency, and lower infrastructure cost.

The four constraints behind every vector index

The right vector index is usually decided by four constraints: whether the working set fits in memory, how much recall the application needs, how often the data changes and how selective the surrounding filters are.

Memory

Memory is fast and low-latency, but expensive. SSDs are cheaper and can still work well for many workloads. Object storage is cheaper still, but its higher latency makes it a poor fit for index designs that require many small random reads.

Vector indexes do not all touch storage the same way. Graph-based indexes follow connections between vectors through the index. That access pattern works very well when the graph is in memory and becomes more expensive when each hop risks a disk read. Partitioning-based indexes group vectors into regions and scan the most promising ones, which can be more memory efficient but usually requires more tuning.

In Postgres, the practical question is whether the index working set fits comfortably in shared_buffers and the operating system page cache. If it does, an in-memory graph index can perform very well. If it does not, the storage access pattern starts to dominate the design.

Recall

Recall measures how close approximate search gets to exact search. Higher recall usually costs more because the index has to inspect more candidates, traverse more of a graph or scan more partitions.

For some applications, slightly lower recall is acceptable if latency improves dramatically. For others, especially RAG systems where missing the right document leads to a bad answer, recall is part of product quality.

The honest way to set this tradeoff is to measure against your own data. Embedding model, dimensionality, filters, and query distribution all affect the result.

Writes

Some vector workloads are mostly read-heavy. You build the index, query it many times, and update it occasionally. Other workloads change constantly. New documents arrive, old ones are deleted, embeddings are regenerated.

A structure optimized for high-recall reads may have higher write or maintenance costs. A lighter-weight index may be easier to update but require more tuning to reach the same recall.

Filters

Real Postgres queries rarely search vectors alone. A query might ask for the nearest vectors, but only within a specific customer, time range, tenant or category.

Those predicates change the shape of the search problem. If a filter is highly selective, it may be cheaper to narrow the rows first and then search. If the filter is broad, it may be better to use the vector index first and apply the filter after. The right plan depends on the data distribution, the selectivity of the filter, and the index available to the planner.

That is one reason vector benchmarks can vary so much. Vector search without filters is not the same workload as vector search inside a real application query.

That is why there is no universal best vector index. There is only the index that best matches the shape of your workload.

The ANN algorithms behind Postgres index choices

The point of understanding ANN algorithms is not to memorize every paper. It is to understand why each index behaves differently as your workload changes. Most of the indexes discussed below fall into two broad patterns.

Graph-based indexes, such as HNSW and DiskANN-style designs, search by moving through connections between nearby vectors. Spatial partitioning indexes, such as IVFFlat and SPANN-style designs, divide the vector space into regions and search the most promising ones.

That distinction matters because graph-based indexes tend to optimize for high recall when the working set is hot, while partitioning-based indexes often trade more tuning for lower memory and maintenance overhead.

Each algorithm below is best understood as a response to a specific pressure: memory, write cost, disk access, or update churn.

HNSW: When the index fits in memory

Your dataset fits in memory and you need high recall at high query throughput. HNSW is built for this.

Hierarchical Navigable Small Worlds organizes vectors as a layered graph where each node connects to nearby vectors across multiple levels of granularity. A query enters at the top layer, moves toward the target neighborhood, then descends to finer layers until it converges on the best candidates.

The layered structure is what gives HNSW its speed-recall profile. The upper layers help the search move quickly across the vector space. The lower layers refine the candidate set around the target neighborhood. When the graph is in memory, that traversal can be fast and accurate.

The tradeoffs show up on the write side and at scale. Each node stores multiple edge pointers, so the index carries a higher memory footprint than simpler partitioning-based alternatives. Inserts and deletes require maintaining graph structure, which makes writes more expensive. And when the index grows beyond available memory, latency can climb.

In pgvector, HNSW is often the first ANN index Postgres developers try when query latency and recall matter most. For a practical look at how it performs, see Vector Database Basics: HNSW.

IVFFlat: When memory and writes matter more

Your write throughput matters, or your index cannot comfortably fit in memory. IVFFlat is worth considering.

IVF stands for inverted file. The basic idea is to partition the vector space into lists, then search only the most promising lists at query time. In pgvector, this index type is exposed as ivfflat.

Compared with HNSW, IVFFlat is usually lighter to build and maintain. Inserts are simpler because adding a vector means assigning it to a list rather than updating a graph of neighboring nodes.

The tradeoff is that recall is more sensitive to tuning. If you create 1,000 lists and set probes = 10, the query searches a small fraction of the partitioned index. Increasing probes gives the query more chances to find the true nearest neighbors, but it also pushes the query closer to a broader scan. IVFFlat tuning is about finding the lowest probes value that still meets your recall target.

That is the core IVFFlat tradeoff: lower memory and maintenance overhead, but more responsibility for tuning lists and probes against your workload.

DiskANN: When the index needs to live partly on disk

HNSW assumes the graph fits comfortably in memory. At tens of millions of high-dimensional vectors, that often stops being practical.

DiskANN, developed at Microsoft Research, was built for this case. It is a graph-based algorithm designed for datasets too large to fit entirely in RAM. At a high level, it keeps enough compressed information in memory to guide the search while storing more of the full index and vector data on SSD.

The lesson for Postgres developers is the storage pattern. A vector index that works well in RAM may behave very differently when the query path depends on repeated disk reads. Disk-aware indexes are designed around that constraint instead of treating it as an afterthought.

DiskANN still carries higher update costs than many partitioning-based approaches. But for read-heavy workloads on large datasets, it explains the shape of the problem that disk-aware Postgres vector indexing is trying to solve. See Understanding DiskANN for a deeper look.

SPFresh: The update problem at scale

Large vector indexes create another problem: updates.

Many ANN systems handle inserts and deletes by buffering changes, maintaining secondary structures, or periodically rebuilding parts of the index. Those approaches can work, but at very large scale they require either accepting stale index state or paying an increasingly expensive maintenance cost to keep the index current.

SPFresh, from Microsoft Research, is one such direction. It builds on partitioning-oriented ideas to reduce the need for global rebuilds, incrementally rebalancing partitions as vectors are inserted, deleted, or updated. Partition assignments are not fixed. They can drift and be corrected over time.

SPFresh is not implemented in Postgres today. But it is not purely academic either. The ideas behind it have already shaped how production vector systems outside Postgres are being designed. Turbopuffer is one example: an object-storage-first vector search service whose architecture is built around centroid-based indexing and minimizing storage round trips. Turbopuffer is not a Postgres system. But the tradeoffs it navigates (high-update workloads, disk-based search, incremental index maintenance without global rebuilds) are real problems the Postgres ecosystem will need to address as vector workloads become more dynamic.

This is worth tracking because the maintenance cost of a vector index is not static. It grows with update frequency and dataset size. For read-heavy workloads on stable datasets, this is not a near-term concern. For teams with high insert and delete rates (documents being added, embeddings regenerated, records retired), it is worth understanding now, before the index becomes the bottleneck.

The Postgres vector search stack

The algorithms above map to real problems Postgres developers run into. HNSW is useful for in-memory performance, IVFFlat for lighter-weight indexing and write-sensitive workloads, and DiskANN-style designs for larger datasets where memory becomes the constraint.

Here is how the Postgres ecosystem addresses those problems today.

pgvector

pgvector is the starting point. It adds a native vector column type to Postgres and supports both HNSW and IVFFlat indexes directly.

An HNSW index looks like this:

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

For IVFFlat, you define the number of lists and tune the number of probes:

CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);
SET ivfflat.probes = 10;

The query planner can use these indexes for nearest-neighbor queries, and you can combine vector search with standard SQL filters, joins and CTEs in the same query. For many teams already running Postgres, this can remove the need to operate a separate vector database.

pgvector can start to show limits at larger scale, especially with high-dimensional embeddings at tens of millions of rows and indexes that no longer fit comfortably in memory. That is the problem pgvectorscale was built to address.

pgvectorscale

The DiskANN section above describes a specific problem: vector workloads that have grown too large to keep the working index in memory. For Postgres, pgvectorscale addresses that directly. It introduces a StreamingDiskANN index type that keeps a compressed representation in memory to guide search while storing the full index on disk.

On a Tiger Data benchmark of 50 million Cohere embeddings at 768 dimensions, Postgres with pgvector and pgvectorscale achieved 28x lower p95 latency and 16x higher query throughput compared to Pinecone's storage-optimized index at 99% recall. This was a vendor-run benchmark. Treat it as directionally useful, not universally predictive. Results will vary with embedding model, dimensionality, filters, recall target, and hardware.

The relevant point is that pgvectorscale stays inside the Postgres operational model. It remains composable with pgvector data types and standard SQL patterns. If your index has outgrown memory, you do not need a different system. You need a different index type.

pg_textsearch and ParadeDB

Vector similarity handles the semantic side of search well, but it is not the whole retrieval problem. Keyword-based retrieval still matters. It catches exact matches that embeddings miss, and for many queries, users know precisely what they are looking for.

This is where pg_textsearch and ParadeDB come in.

pg_textsearch, also from Tiger Data, brings BM25-based search into Postgres. BM25 accounts for term frequency saturation and document length normalization, which is why it is often a stronger ranking model for keyword search than simple term matching.

ParadeDB takes a related position as a Postgres distribution, bundling pg_search for BM25-based full-text search and pg_analytics for analytical query execution. If you want Elasticsearch-style search quality and are open to running a Postgres distribution rather than adding individual extensions, ParadeDB belongs on your evaluation list. When you are operating a small dataset, BM25 relevance ranking may not be a key requirement and pg_search will suffice. However, pg_textsearch is a better option when you need true BM25 relevance ranking with term saturation (how many times a term appears) or document length normalization to match the experience of Lucene (that powers Elasticsearch) or the algorithms that power Google.

The real payoff of having both vector search and BM25 inside Postgres is hybrid search: combining vector similarity and keyword scoring in a single query. For many RAG applications, this is often a stronger retrieval pattern than vector search alone because each approach covers the other's blind spots. Vector search captures semantic meaning. BM25 catches exact matches.

A simple hybrid search pattern in SQL

One common way to merge vector and keyword results is Reciprocal Rank Fusion, or RRF.

RRF avoids averaging scores across different scales. Instead, it combines rank positions. A result that appears near the top of either list gets a boost.

The exact syntax depends on which BM25 extension you use, but the query shape looks like this:

WITH keyword_results AS (
  SELECT
    id,
    content,
    paradedb.score(id) AS bm25_score,
    ROW_NUMBER() OVER (ORDER BY paradedb.score(id) DESC) AS keyword_rank
  FROM documents
  WHERE content @@@ 'vector search'
  LIMIT 60
),
vector_results AS (
  SELECT
    id,
    content,
    1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity_score,
    ROW_NUMBER() OVER (ORDER BY embedding <=> '[0.1, 0.2, ...]') AS vector_rank
  FROM documents
  LIMIT 60
),
combined AS (
  SELECT
    COALESCE(k.id, v.id) AS id,
    COALESCE(k.content, v.content) AS content,
    COALESCE(1.0 / (60 + k.keyword_rank), 0) +
    COALESCE(1.0 / (60 + v.vector_rank), 0) AS rrf_score
  FROM keyword_results k
  FULL OUTER JOIN vector_results v ON k.id = v.id
)
SELECT id, content
FROM combined
ORDER BY rrf_score DESC
LIMIT 10;

This retrieves candidates from both systems, ranks them separately, and merges the ranked lists.

This is one of the strongest reasons to keep search in Postgres. Your embeddings, documents, metadata filters, joins, keyword search, and application data can live in one query model.

Learn more: how to build Hybrid Search in Postgres using pg_textsearch and pgvectorscale, and why hybrid search outperforms vector-only retrieval.

What this guide does not decide for you

No article can tell you the right vector index without your data.

Embedding model, dimensionality, filter selectivity, recall target, update rate, hardware, concurrency, and query distribution all change the answer. Even two datasets with the same number of rows can behave differently if their vectors cluster differently or their filters have different selectivity.

The point of this guide is not to replace benchmarking. It is to help you know what to benchmark first. Start with the simplest index that matches the shape of your workload. Measure it against exact search where possible. Tune recall and latency together. Then move to a more specialized index only when the workload gives you a reason.

Which Postgres vector index should you use?

The path usually looks like this: start with exact search while the dataset is small, move to HNSW when latency requires ANN, consider IVFFlat when memory or write cost matters more, evaluate disk-aware indexing when the working set outgrows memory, and add BM25 when retrieval quality needs more than semantic similarity alone.

Where things stand and where they are going

The practical rule is simple: benchmark the workload you actually run, not the cleanest version of vector search.

Start with exact search while the dataset is small. Move to HNSW when latency requires ANN. Consider IVFFlat when memory or write cost matters more. Evaluate StreamingDiskANN when the working set outgrows memory. Add BM25 when retrieval quality needs more than semantic similarity.

The one gap that remains is what SPFresh points toward: high-update workloads at scale without global index rebuilds. That capability is not yet in Postgres, but it is already showing up in production vector systems outside the Postgres ecosystem.

Whether it eventually appears as an extension, a fork or something nobody has named yet, the pattern is familiar: a hard problem gets real and someone in this community builds the thing.

Want to dig in further? Look at Tiger Data docs for pgvectorscale and pg_textsearch.

We're launching a free tier. No credit card, no time limit, no catch.

AI development moves at the speed of thought. You’re constantly experimenting, testing a vector search strategy, forking a database to try a new schema, or spinning up an instance for a weekend project. The friction of “will this cost me money?” shouldn’t slow you down.

Today, we’re launching the Tiger Free Plan, a fully managed Postgres built for how AI development actually works: experimental, iterative, and increasingly agent-driven. It’s the same Tiger managed cloud experience developers already love, now free for every idea.

The Tiger Free Plan should be the database you reach for every time you want to test an idea. 

What Makes This Free Plan Different

Most free database tiers are built for yesterday’s users. They gate core features, expire trials, or remind you what you don’t get. We built ours for how development actually happens today.

Because the database has a new user. Developers aren’t working alone anymore. They’re building alongside agents that write code, run migrations, and query data through APIs. That changes what experimentation means. You don’t just need a place to store data, you need a system where your tools, scripts, and agents can spin up, fork, and reason over automatically.

The Tiger Cloud Free Plan is built for that reality. It’s not designed for production. It's designed for progress, for the 90 percent of work that happens before you know if something is worth scaling.

Other free tiers optimize for control. Ours optimizes for momentum. When you’re experimenting, you shouldn’t have to think about limits, billing, or setup. You should be able to fork your database, test a schema, or try a new retrieval strategy in seconds, whether you’re doing it yourself or your agent is doing it for you.

That’s why it includes the tools modern AI workflows actually rely on. You can fork your database to test safely and recover fast, run vector search to compare retrieval and embedding strategies in real time, and analyze live data with built-in time-series and columnar features so you can see what’s happening as it happens. Because Tiger Cloud runs the same APIs across every plan, moving from free to production is instant: no migration, no rework, no context lost.

This isn’t a teaser or a trial. It's part of a new architecture for Postgres, one built for builders: humans and agents alike. 

What’s Included

Compute & storage:

• Shared compute
• Up to 750 MB of storage per service
• Limit of 2 free services per account
• Available in us-east-1 (EU expansion coming soon)

Features that matter:

• Database forks: Branch your database like your code. Test safely, recover fast, or try something bold.
• AI-native retrieval: Build RAG and other AI-powered features with native hybrid and vector search support (pgvectorscale + BM25).
• Real-time analytics: Hypertables, continuous aggregates, and columnar storage. Run analytics in Postgres without extra systems.
• Insights: View performance on a per-query basis over time, and get optimization recommendations, so you spend less time guessing. 
• Automated Management: Upgrades, tuning, and maintenance handled for you, so your database always runs at its best.
• 50+ Postgres Extensions. All your favorite extensions, from PostGIS to pgvector, built in and ready to go.
• Connection management: Simplified, secure handling that just works.

What Happens When You Hit the Limits

At 750 MB, your service switches to read-only mode. You’ll get warnings as you approach the limit, and you can:

• Fork to an earlier point in time (PITR - 24-hour point in time recovery)
• Clean up data
• Or upgrade to a paid plan and migrate in minutes

Free services can be upgraded directly. When your project grows, you can quickly convert it in place to remove storage limits. Your free services coexist perfectly with your paid ones, so you can test safely alongside production.

Connection poolers are not included in the Free Plan. Dedicated support is not included either, but Free Plan users are encouraged to join our community Slack to ask questions, share ideas, and see what others are building.

Built for the builders: Developers and agents alike

If you're new to Tiger, you can start with the free plan or take a 30-day performance trial. If you're already a customer, you can add two free services right inside your existing account for testing, sandboxing, or side projects.

The Free Plan is our way of lowering the floor for experimentation. We want more developers and agents building together on postgres without friction. It's a small change with a big goal: making it effortless to start, learn, and build.

Get Started

From the command line via the Tiger CLI (Download from GitHub)

$ curl -fsSL https://cli.tigerdata.com | sh
$ tiger auth login 
$ tiger service create

From the cloud console:

1. Sign up
2. Select “Free Plan” (vs. the free 30-day trial)
3. Create a service and get building!

Frequently Asked Questions

About the Free Plan

1. What is included in the Free Plan?
   1. Up to 2 free services per account
   2. Developer experience features: database forks (24-hour PITR), Insights dashboard, pgvector, real-time analytics/time-series (hypertables, continuous aggregates, columnar storage), connection management
   3. MFA for secure login
   4. IP Allow list for security
   5. Database logs
   6. A large suite of Postgres extensions, like PostGIS and pg_cron
   7. Available in us-east-1 (EU region coming soon)
2. What is excluded in the Free Plan?
   1. Connection pooler
   2. Compute resizing
   3. Dedicated support (community Slack available)
   4. High-availability configurations
   5. Advanced security features
   6. Advanced enterprise features
3. What are the specs of a free service?
   1. Each free service includes:
      1. Shared compute
      2. Storage: Up to 750 MB for user data
      3. Backup: 24-hour point-in-time recovery for forking
      4. Connections: Limited (exact number TBD, designed to balance usability and system health)
4. Is a credit card required to create Free Plan services?
   1. No. You can sign up and create free services without entering payment information.
5. What happens if I reach my storage limit?
   1. You'll receive warnings as you approach 750 MB. Once you hit the limit, your service switches to read-only mode. You can:
      1. Fork your service to an earlier point in time (within the 24-hour PITR window)
      2. Delete data to free up space before reaching the limit
      3. Upgrade to a Performance or Scale plan and “Convert to a standard service without storage limits”
6. Can I upgrade free services?
   1. Yes. If your project outgrows the Free Plan, you can:
      1. Switch to a Performance or Scale plan
      2. Convert your free service to a standard service to remove storage limitations and get access to higher levels of compute
7. What is the difference between Free Plan and Performance Plan?
   1. Performance Plan offers:
      1. Scalable compute (starting at 0.5 CPU / 2 GB RAM, up to 16 CPU / 128 GB RAM)
      2. Storage up to 16 TB (pay-as-you-go by the GB)
      3. Connection pooling
      4. High availability options
      5. Dedicated support
      6. Virtual Private Cloud peering
      7. Production-grade SLAs
   2. The Free Plan is optimized for experimentation; Performance is optimized for production.
8. Can I start a Performance trial if I already have a Free Plan?
   1. Yes! Every project gets one free trial of Performance or Scale. You can start it at any point, even after creating free services. Your free services remain active during and after the trial.
9. Does my free trial automatically convert to a Free Plan when my trial expires?
   1. If you start with a Performance or Scale trial, here's what happens when it ends:
      1. Trial services (on Performance/Scale) are paused, then eventually deleted
      2. Any free services remain active
      3. Your account converts to the Free Plan
      4. You can create up to 2 free services (if you haven't already)
      5. No charges occur unless you explicitly upgrade
10. Do I keep my free services if I upgrade my Free Plan to Performance or Scale?
    1. Yes! Free services are available on all plans. You can have up to 2 free services alongside your paid services.
11. If I am already a Tiger Data customer, can I also use the free services?
    1. Yes! Existing Performance, Scale, and Enterprise customers can create up to 2 free services within their accounts. Use them for testing, sandboxes, or side projects.
12. Will I have access to free services in my paid plan?
    1. Yes. All plans (Free, Performance, Scale, Enterprise) include access to up to 2 free services. They coexist with your development and production databases.
13. Should I start with the Free Plan or the Free Trial?
    1. Start with Free Plan if:
       1. You're prototyping or experimenting
       2. You're not sure what you're building yet
       3. You want to test Tiger Data without commitment
    2. Start with a Performance/Scale trial if:
       1. You're ready to test Tiger Cloud with production-like workloads
       2. You have large datasets to migrate
       3. You need higher compute or storage immediately
       4. You want to evaluate enterprise features
    3. You can always start a trial later. Each project gets one trial, and you choose when to use it.
14. Is Tiger Fluid Storage available on Performance and Scale plans?
    1. No, Tiger Fluid Storage is not available on Performance and Scale plans today.

About the authors

Hien Phan

An AI, Data, and Infrastructure Marketing Leader, Hien Phan is the Head of Marketing at Tiger Data. He has led Product, Partner, and Customer Marketing at Pinecone. He and his team launched a game-changing serverless architecture and introduced Pinecone Assistant, marking a significant leap in our product offerings.

At Amplitude, Hien’s team championed solutions that empowered product and marketing teams to excel in a product-led growth. This role sharpened his ability to drive growth through strategic marketing initiatives, solidifying that brand as an indispensable tool for product experimentation and analytics category.

Hien lives in the Bay Area with his two lovely dogs and makes a mean roasted chicken.