← Back to concept explainers

Concept Fable

pgvector

Conclusion first:pgvector is a PostgreSQL extension that adds vector storage and similarity search capabilities directly within a relational database. It supports multiple index types (HNSW, IVFFlat), multiple distance metrics (L2, inner product, cosine distance), and exact/approximate search. In enterprise scenarios, pgvector's greatest value lies in placing vector retrieval and existing business data within the same transaction, permission, and query system—you can use a single SQL statement to simultaneously perform JOINs, WHERE filtering, and vector similarity ranking, without needing to maintain synchronization between a dedicated vector database and a business database. It is suitable for scenarios where data volume is within the millions, the team already uses PostgreSQL extensively, and strong consistency and low-latency vector queries are required. When vector volume exceeds tens of millions and distributed or pure vector workloads are needed, a dedicated vector database should be considered.

pgvector · PostgreSQL vector extension

Read the story first. There is no rush to give a definition here; let the question grow on its own first.

Fable

The old accountant added a new counting rod to the old ledger

A century-old bank had a general ledger passed down through generations. Customers, transactions, contracts, guarantees and permissions all sat in the same set of ledgers, and the accountants knew every audit path. The new manager wanted to add one more capability at the counter: when a customer arrived, find the most similar past customers and see which notes suited them.

Someone proposed buying another similar cabinet. That cabinet calculated quickly, but the data in the general ledger had to be regularly copied over to it. The first time, a new client waited two hours before entering the similar cabinet; the second time, the copying missed the decimal in the silver amount; the third time, the cabinet rules and the general ledger rules were inconsistent, and clerks could see records in the similar cabinet that they should not have seen.

The old accountant said that starting a separate ledger looks new, but in reality it adds another river of transcription. Every bridge over that river will leak, slow down, and forget permissions. He suggested adding a set of similar counting rods to the old general ledger: beside each customer and each contract, place a string of feature beads, so that audits are still completed within the general ledger cabinet, just with an additional way to sort by degree of similarity.

The first trial was not smooth. When there were too few counting rods, similar people could not be found accurately; when there were too many, the old ledger cabinet drawers became heavy and the clerks turned pages more slowly. The old accountant did not dismantle the new counting rods. Instead, he built wooden racks for frequently checked accounts, made rare accounts slower to check, and kept the permission tags on the original cabinet.

After the new counting rods were connected, the counter could ask under the same set of accounting rules: who are the five customers most similar to this one, what have they bought, do they belong to a certain risk category, and which records the current clerk is authorized to view. The books did not need to be moved, and permissions did not need to be written again.

Of course, the old ledger is not omnipotent. Too many bead strings take up cabinet space, too frequent sorting slows down the accounting office, and the index racks also need maintenance. If the entire city's massive materials were pressed in, the ledger might not hold up.

The shopkeeper finally understood that the new capability grew on the skeleton of the old ledger, and the value was not just saving a cabinet, but preserving existing materials, permissions, and query habits. Being able to add a similar kind of lookup within the familiar general ledger was what made this renovation truly worry-free.

Later, when the draft bank served regular customers, the clerks could still use the old account rules to check identity, credit limits, and permissions—only now they asked one extra question at the same counter: who is this similar person? The new capability did not tear the old system apart; instead, it borrowed trust from the old system. The manager knew this kind of transformation suited places that already had a deep general ledger and did not want to open a separate set of rules.

Reveal

This story is about: pgvector

pgvector is a PostgreSQL extension that adds vector storage and similarity search capabilities directly within a relational database. It supports multiple index types (HNSW, IVFFlat), multiple distance metrics (L2, inner product, cosine distance), and exact/approximate search. In enterprise scenarios, pgvector's greatest value lies in placing vector retrieval and existing business data within the same transaction, permission, and query system—you can use a single SQL statement to simultaneously perform JOINs, WHERE filtering, and vector similarity ranking, without needing to maintain synchronization between a dedicated vector database and a business database. It is suitable for scenarios where data volume is within the millions, the team already uses PostgreSQL extensively, and strong consistency and low-latency vector queries are required. When vector volume exceeds tens of millions and distributed or pure vector workloads are needed, a dedicated vector database should be considered.

What matters is that it is not just a term, but a structural problem that repeatedly appears in real AI / Agent systems. Understanding it lets you judge when to add a model, when to change a process, and when to add governance.

Metaphor mapping

  • Century-old ledger: the company's existing PostgreSQL database, containing transactions, permissions, and reporting logic
  • New search cabinet: a dedicated vector database, powerful but separate from the business database
  • Scheduled synchronization bridge: ETL / CDC synchronization, introducing latency, consistency, and permission issues
  • Lost decimal places: data errors during sync, unacceptable in strongly consistent scenarios like finance
  • New calculating rods in the old ledger: pgvector as a PostgreSQL extension, with vector search natively embedded
  • ORDER BY embedding <-> query_vector: native SQL vector similarity sorting
  • Row-level security policies: using PostgreSQL's existing RLS for permission control over vector data
  • The cost is the scaling ceiling: pgvector is limited by PG’s single-machine scaling model and is suitable for millions of vectors

Andy Ren Xu's judgment

The practical value of this concept is that it helps you break "AI features that look smart" into deliverable, verifiable, and governable units of work.

PostgreSQLvector searchSQLembeddingsHNSWIVFFlat
京ICP备2026014272号