What a vector database is and when a business needs one: pgvector or a dedicated service, hybrid search and running costs

Many businesses hear they "need a vector database" for smart search or an AI assistant, and buy a dedicated service before knowing what they need to find. Others keep a keyword box even though customers ask in natural sentences.

This article explains embeddings, similarity measures, HNSW and IVFFlat indexes and hybrid search, then compares pgvector with a dedicated service. Main sources: the documentation of pgvector, Supabase, PostgreSQL, OpenAI, Google, Pinecone, Qdrant and Weaviate, plus official pricing pages, checked up to October 21, 2026.

Click Star already has articles on databases for online operations and on Supabase Select 2026; RAG has its own article. Click Star sells AI integration and data dashboard services, so it has a related interest; this article does not rank products.

Key points

• A vector database stores embeddings (lists of numbers representing the meaning of text) and finds the records closest to a query. Per the pgvector README it searches exactly by default; an HNSW or IVFFlat index trades some recall for speed.
• Vectors normalized to length 1 (like OpenAI's) give the same ranking under cosine and inner product. When unsure, Supabase suggests cosine as the default.
• Hybrid search runs keyword and vector search separately and then merges the lists. Pinecone says each method misses what the other catches, so product codes and proper names still need a keyword layer.
• You do not need one yet if the corpus is small, questions are mostly exact codes or names, or PostgreSQL full-text search is enough.
• pgvector keeps vectors next to business data; a dedicated service adds features but also a system to sync, secure and pay for.
• Changing the embedding model means re-embedding the whole corpus: Google states that 2 Gemini models have incompatible embedding spaces.

Embeddings and similarity measures

An embedding turns text into a list of numbers so that texts with similar meaning sit close together. According to the Supabase concepts page (read October 21, 2026), the gte-small model outputs 384 dimensions, and similarity is then computed with vector math such as cosine distance.

OpenAI's documentation (read October 21, 2026) lists text-embedding-3-small at 1536 dimensions and text-embedding-3-large at 3072. The dimensions parameter shortens a vector: on the MTEB benchmark, the large model cut to 256 dimensions still beats the unshortened ada-002 at 1536, according to OpenAI.

pgvector supports L2 distance (<->), negative inner product (<#>), cosine (<=>) and several other measures. OpenAI recommends cosine; because its vectors are normalized to length 1, cosine can be computed as an inner product and ranks the same as Euclidean distance.

Approximate indexes: HNSW, IVFFlat and the accuracy-speed trade-off

The pgvector README says pgvector performs exact nearest neighbor search by default, with perfect recall. Adding an approximate index trades some recall for speed, and results can differ from those before the index existed.

HNSW is a multilayer graph (Malkov and Yashunin, arXiv 1603.09320, version of August 14, 2018) with logarithmic complexity scaling. The README says it has a better trade-off than IVFFlat but builds slower and uses more memory. Defaults are m = 16, ef_construction = 64 and ef_search = 40; raising ef_search improves recall but slows queries.

IVFFlat divides vectors into lists and scans only the few lists closest to the query. It builds faster and lighter than HNSW but has a weaker trade-off. The README names 3 conditions: create the index after data exists, set lists near rows/1000 (up to 1 million rows), and set probes near the square root of lists. The default probes is only 1.

Supabase recommends HNSW for its robustness when data changes, and says you may skip an index if the corpus is small, queries are few, or you need 100% accuracy.

A filter trap: the README explains that approximate indexes apply filters after scanning. If a condition matches 10% of rows and ef_search is 40, only about 4 rows remain on average. Since version 0.8.0, iterative index scans keep scanning until enough results are found.

For measurements, Supabase's benchmark (compute add-on page, read October 21, 2026) used HNSW, accuracy 0.99 and 1536-dimension OpenAI embeddings: the Micro add-on (1 GB RAM) handled 15,000 vectors at 480 QPS, Medium (4 GB) 100,000 vectors at 240 QPS, and 2XL (32 GB) 1,000,000 vectors at 560 QPS. These are Supabase's numbers on the dbpedia dataset.

Hybrid search: keywords, vectors and how to merge them

Vectors capture meaning but can miss a typed product code, proper name or error line. Pinecone (read October 21, 2026) says keywords match specific tokens, dense vectors match by meaning, and each method misses what the other catches.

PostgreSQL ships with full-text search (tsvector, tsquery). Its documentation (read October 21, 2026) says LIKE has no ranking and is slow without an index, while GIN is the preferred text index.

The ts_rank function ranks by matching-term frequency and, per the documentation, uses no global information; Click Star's assessment (not a measurement) is that it differs from the BM25 of Weaviate and Pinecone, so test it on your data.

Merging comes in several variants. Supabase uses Reciprocal Rank Fusion (RRF): the score is the sum of 1/(k + rank) over each list, with k defaulting to 50 and weights to 1. Qdrant offers RRF and DBSF and treats RRF as the safe default without an evaluation set. Weaviate uses an alpha parameter (1 is pure vector, 0 is pure keyword).

Pinecone notes that "hybrid search" is not one fixed method and RRF is just one way to merge, so ask a vendor which signals and how they are merged.

As evidence, Anthropic (September 19, 2024) wrote that embeddings plus BM25 beat embeddings alone; the contextualized version cut top-20 retrieval failures from 5.7% to 2.9%. This is one company's experiment, so it gives no figure for Vietnamese.

For Vietnamese, Supabase's hybrid example uses the to_tsvector('english', ...) configuration, and the PostgreSQL pages read never mention Vietnamese. The unaccent module can strip diacritics; test with real questions.

When you do not need a vector database yet

You do not need one in 3 common situations.

• Small corpus: Anthropic wrote (2024) that a knowledge base under 200,000 tokens (about 500 pages) can go straight into the prompt. Limits and prices have changed since, so check current ones.
• Questions are mostly codes, names or contract numbers: keywords or full-text search are enough and easy to explain.
• No sample question set yet: without one you cannot tell whether vectors beat keywords.

pgvector or a dedicated vector service

The README states that pgvector keeps vectors with the rest of your data, with ACID, point-in-time recovery and JOINs. Each 1536-dimension vector takes 4 × dimensions + 8 bytes (6,152 bytes); a non-partitioned table is capped at 32 TB. Indexes support up to 2,000 dimensions for vector and 4,000 for halfvec.

Dedicated services describe what they add: Qdrant has filterable HNSW and multi-stage queries, Pinecone has namespaces for multi-tenancy.

The practical difference is where data lives. With pgvector, business rows and vectors share one database, so deleting a customer and their vectors can happen in 1 transaction (Click Star's inference from ACID). A dedicated service needs a sync process, and sync bugs are hard to see.

Click Star's view. If the data already sits in PostgreSQL at a scale of tens to a few hundred thousand vectors, pgvector is usually a reasonable start: fewer systems, 1 permission layer, 1 backup schedule. A dedicated service deserves a look when vectors are the core product, load exceeds 1 PostgreSQL machine, or specialized features are needed.

Cost from official pricing pages

Prices were read on October 21, 2026 from each vendor's pricing page; vendors meter differently, so they cannot be compared directly.

• Supabase: Free is 0 USD with 500 MB of database; projects pause after 1 week of inactivity. Pro starts at 25 USD per month with 8 GB of disk, then 0.125 USD per GB, plus 10 USD of compute credits.
• Supabase compute per month: Micro 10 USD, Small 15 USD, Medium 60 USD, 2XL 410 USD.
• Pinecone: Starter is free, Builder is a flat 20 USD, Standard has a 50 USD monthly minimum, storage is 0.33 USD per GB per month, plus read and write units.
• Embedding: OpenAI lists text-embedding-3-small at about 62,500 pages per USD (800 tokens per page).

Storage is easy to overlook. Click Star calculated from the pgvector formula (indexes excluded): 100,000 vectors at 1536 dimensions take about 615 MB and 1,000,000 take about 6.15 GB; so 100,000 such vectors already exceed the 500 MB Supabase Free limit. With halfvec (2 × dimensions + 8 bytes) that falls to about 3.08 GB.

Operations: re-embedding, size, backup, permissions

Changing the model means re-embedding. The Gemini API documentation (read October 21, 2026) states that the embedding spaces of gemini-embedding-001 and gemini-embedding-2 are incompatible, so an upgrade requires re-embedding all data. Supabase likewise says comparing embeddings from different models gives meaningless results.

The pgvector README shows how to keep several models: an untyped vector column, a model_id column and a partial index per model, so you can embed in parallel and then switch queries. Shortening dimensions saves space, but if you shorten manually, OpenAI says you must re-normalize the length.

Changed data means changed vectors. Supabase's automatic embeddings guide states that an embedding can be temporarily out of sync with the content until the new one is generated.

Backup. The README says pgvector uses the WAL, so point-in-time recovery works. According to Supabase, Pro keeps 7 days of daily backups, Team 14 days and Enterprise up to 30 days; Free has none. PITR is an add-on, about 100 USD per month for 7 days, and needs at least the Small compute add-on.

Row-level permissions. Supabase requires RLS on every table in an exposed schema, which includes vector tables. The README warns that sharing an approximate index across tenants can affect each other's recall; separate them with partitions or separate tables.

What to do: a 30-day plan

• Days 1 to 5: write 30 to 50 real questions and record the correct documents for each.
• Days 6 to 12: test PostgreSQL full-text search on real data (diacritics handled), record the hit rate.
• Days 13 to 20: enable pgvector on a small table with 1 embedding model; record the dimension count and model version.
• Days 21 to 25: test hybrid search with RRF against each method alone; add HNSW only when sequential scans are slow.
• Days 26 to 30: calculate storage, check RLS, test a backup restore, then decide.

How to measure: share of sample questions answered correctly in the top 5, p95 latency, table and index size, time to re-embed the corpus.

Common mistakes

1. Buying a dedicated service before a sample question set shows keywords are not enough.
2. Dropping the keyword layer and then failing to find product codes or proper names.
3. Creating IVFFlat on an empty table or leaving probes at the default of 1.
4. Filtering after an approximate index without checking how many results come back.
5. Mixing embeddings from 2 models in the same index.
6. Not calculating vector storage, then exceeding the plan's disk limit.
7. Leaving vector tables outside RLS because they are "just numbers".

What is not known

Click Star found no independent measurement of vector search quality in Vietnamese for Vietnamese businesses, and no opened source on PostgreSQL full-text configuration for Vietnamese. Test which embedding model fits with real questions.

Click Star has not re-run Supabase's benchmark and has no pgvector comparison with each dedicated service under the same load. Anthropic's numbers are one company's, from 2024, mostly English.

Click Star found no RLS equivalent in the dedicated services it opened (Pinecone describes namespaces and metadata filtering) and did not read Pinecone, Qdrant or Weaviate permissions in depth; check before moving sensitive data.