Building a Semantic Search API with Spring Boot and pgvector - Part 2: Designing the PostgreSQL Schema

Why the database layer matters In a semantic search system, the database schema isn’t just storage. It defines how embeddings are stored, indexed, and queried. Many tutorials treat the database as a detail - create a table, add a vector column, and move on. But when search quality depends on how vectors are stored and compared, the schema becomes a core architectural decision. The schema determines what the system can do and what it cannot. A missing index means slow queries at scale. A missing status column means no visibility into embedding failures. A poorly typed metadata column means filters that silently break. Every column and every index in this schema exists because a specific part of the system depends on it. Running pgvector locally Before any migrations run, the database needs to support vector operations. That means PostgreSQL with the pgvector extension installed. Using pgvector lets us keep embeddings in the same database as the documents. This avoids the complexity of r