How to Choose a Database for an AI-Built App

Pick a relational database (Postgres) by default. Move to a document database only if your data is genuinely unstructured and schema-less. Add a vector database, or a vector extension on top of Postgres, only once you have a real semantic search or retrieval-augmented generation (RAG) feature to build. Most AI-built apps never need that third thing on day one, and bolting it on later is a smaller job than founders expect. The rest of this post is the decision framework for making that call without a database background.

This is a different question than "vector vs regular database"

If you already know you need semantic search and just want to compare vector databases against traditional ones, read vector database vs regular database instead. This post answers an earlier question: which database should you start with at all, before you know if you'll need a vector store, and before you've decided whether you even need a backend. If you haven't settled that second question yet, do I need a backend for my app covers it. Assume here that you do need a database. The question is which kind.

Three kinds of database, three different jobs

• Relational (Postgres, MySQL): rows and tables with fixed columns, strict relationships between them, and strong guarantees that a transaction either fully happens or doesn't happen at all. Good for users, orders, invoices, bookings, anything with clear structure and things that reference each other.

• Document (MongoDB, Firestore): flexible, schema-less JSON-like records. Good when every record can look different and you don't want to define columns up front, like activity logs, CMS content, or configuration blobs.

• Vector (Pinecone, Weaviate, or pgvector on top of Postgres): stores embeddings, numeric representations of meaning, and finds records that are semantically similar rather than exactly matching. Good for one job: search and retrieval based on meaning, not keywords.

Notice that only one of these three is a general-purpose default. Relational databases handle document-shaped data reasonably well using a JSON column. Document databases handle relational data badly once you need joins across records. Vector databases don't handle either well; they exist to answer one kind of query. That asymmetry is why the framework below starts with Postgres and only branches away from it when there's a specific reason to.

The decision framework

Work through these in order. Stop at the first step that applies to your app.

1. Does your app have users, accounts, payments, bookings, orders, or anything where two records need to reference each other correctly and never get out of sync? Use a relational database (Postgres). This covers the large majority of founder apps: SaaS tools, marketplaces, internal tools, CRMs, booking systems.

2. Is most of your data genuinely unpredictable in shape, arriving as loosely structured JSON that changes field by field, with little need to query across records by relationship? Consider a document database. Examples: a tool ingesting arbitrary third-party API payloads, a flexible form builder storing arbitrary field sets, an activity/event log with varying payloads per event type.

3. Does your app need to find records by meaning rather than exact match: 'show me support tickets like this one,' 'find products similar to what the user just viewed,' or does it answer questions by retrieving relevant chunks of text and feeding them to an LLM (RAG)? If yes, you need vector search capability, but not necessarily a separate vector database.

4. If you answered yes to step 3, how much vector search will you actually do? Under roughly a few hundred thousand vectors, or search that doesn't need sub-50ms latency at high concurrency, add the pgvector extension to Postgres rather than standing up a separate vector database. You get one database to manage, one backup process, and joins between your relational data and your embeddings in the same query.

5. Only reach for a dedicated vector database (Pinecone, Weaviate, Qdrant, Milvus) if you're past pgvector's comfortable range: tens of millions of vectors, need for specialized indexing at scale, or a team that wants vector search as an isolated, independently scaled service. Most apps in their first one to two years of life never hit this step.

6. If you answered no to steps 1 through 3, meaning your app has almost no structured relationships and no semantic search, a lightweight option (SQLite, or a simple hosted Postgres instance used loosely) is still usually the least risky choice, because it keeps the door open if requirements grow, which they usually do.

When an AI app actually needs a vector database

This is the step people get wrong most often, in both directions. "AI-built app" does not mean "needs a vector database." The AI part is frequently just an LLM API call in your code, generating text, classifying input, or filling out a form. None of that requires storing vectors anywhere. You need vector storage specifically when your app has to retrieve relevant content by meaning before it can answer, which is the definition of RAG: search a knowledge base semantically, then hand the results to the model as context.

Concretely, you need it if you're building: a chatbot that answers from your own documents or knowledge base, a support tool that finds similar past tickets, a recommendation feature based on content similarity rather than fixed categories, or search that needs to understand "cheap flights to somewhere warm" without those exact words appearing in your data. You don't need it for: form-based apps that call an LLM to summarize or generate text, workflow automation, most CRUD apps with an AI feature bolted on for convenience, or apps where a SQL `WHERE` clause and a few filters already answer the user's question. If you're not sure which camp you're in, ask whether your app's core value is finding the right needle in a haystack of unstructured text. If not, skip the vector database entirely.

SQL vs NoSQL for an AI app, in plain terms

The SQL vs NoSQL debate predates AI tooling by two decades and most of the old arguments still apply. SQL (relational) databases enforce structure before you save data, which catches bugs early and makes reporting easier later. NoSQL (document) databases let you save first and figure out structure later, which is faster to prototype but pushes data-integrity problems downstream, exactly when you have the least context to debug them.

For AI-built apps specifically, there's an added wrinkle: AI coding tools are noticeably better at generating correct, safe SQL migrations and queries than at reasoning about implicit document schemas, because SQL schemas are explicit and self-documenting in a way JSON blobs aren't. If you're relying on an AI tool to write and modify your database layer as your app grows, a relational schema gives that tool (and you, reading its output) something concrete to check against. That's a practical reason, not a theoretical one, to lean relational when a non-developer is directing the build.

Postgres as a default, worked example

A common default in AI app builders today is Postgres provisioned automatically, often through Supabase, which packages managed Postgres with authentication, file storage, and row-level security out of the box. It's worth naming as a concrete example because it shows how far a single relational database now stretches. Supabase's free tier includes a full, unrestricted Postgres database (500 MB storage) with the pgvector extension available from the start, so a founder can build the relational core of an app and add basic semantic search later without provisioning a second system ([Supabase pricing](https://supabase.com/pricing)). pgvector itself has matured substantially: recent versions added iterative index scans for filtered queries, parallel index builds, and half-precision vector storage that roughly halves storage cost with minimal accuracy loss ([pgvector project](https://github.com/pgvector/pgvector)). None of this is a pitch for one vendor. The point is structural: "Postgres plus an extension when needed" is now a genuinely viable path from zero to a real RAG feature, without ever touching a second database product.

What to do if you picked wrong

Founders overweight this risk. A wrong first choice is rarely a rewrite; it's usually an addition or a targeted migration, and both are manageable without deep database expertise if you follow a few rules.

1. Chose Postgres, now need vector search: add the pgvector extension to your existing database. This is a config change, not a migration. Your relational data doesn't move.

2. Chose Postgres, now realize part of your data is genuinely document-shaped: use a JSONB column for that part rather than switching databases entirely. Postgres handles semi-structured JSON well enough that a full migration is rarely justified.

3. Chose a document database, now need real relationships and transactions: this is the migration that actually hurts, because you're re-deriving a schema from data that was never forced to have one. Budget real time for it, start by exporting a sample and manually mapping fields to tables before writing any migration script, and do it before your data volume grows further, not after.

4. Chose a dedicated vector database, but your data volume never justified it: most vector database vendors support standard export formats; moving embeddings into a pgvector table is usually a bulk import job, not a rebuild, once you strip out vendor-specific indexing config.

5. In any of these cases, keep a written record of your current schema before you touch anything. If you built the app with an AI tool, ask it to generate that schema documentation first; it's a five-minute step that saves hours when something breaks mid-migration.

The underlying reason this is manageable is that most AI-built apps are small enough, at the point where the wrong choice becomes visible, that migration means moving thousands or low millions of rows, not billions. That's a weekend project with the right tool, not a rearchitecture. It gets harder the longer you wait, which is the real argument for starting with the boring, structured option and only adding specialized pieces once a specific feature demands them. If your app is also running slower than expected around this point, that's frequently a database or query problem rather than a fundamental architecture one; see why is my AI-built app slow for how to diagnose it before assuming you need to switch databases at all. And if cost is part of what's driving the decision, how much does it cost to run an AI-built app breaks down where database spend typically shows up. Database choice is also separate from how you split your app into services in the first place; monolith vs microservices is the wider structural question worth settling alongside it.

None of this is a one-time decision you need to agonize over before writing a line of code. It's a default (Postgres), one clear trigger for branching (real semantic search), and a manageable path back if you branch too early or too late. If you're earlier in the process and still mapping out your whole build, how to build an app with AI is the wider starting point this post assumes you've already been through.

FAQ

Do I need a vector database for my AI app?

Only if your app does semantic search or retrieval-augmented generation, meaning it has to find relevant content by meaning before answering, not just call an LLM to generate or summarize text. If that's not your core feature, a relational database is enough, and you can add pgvector to it later if that changes.

Is Postgres good enough for an AI-built app?

Yes, for most AI-built apps Postgres is enough on its own, and with the pgvector extension it also handles moderate-scale semantic search, so many apps never need a second database at all.

What's the difference between SQL and NoSQL for an AI app?

SQL (relational) databases enforce a fixed structure and handle relationships between records reliably; NoSQL (document) databases store flexible, schema-less records that are faster to prototype but harder to keep consistent as the app grows. Most AI-built apps with users, accounts, or transactions are better served by SQL.

Can I switch databases later if I choose the wrong one?

Usually yes, and it's smaller than it sounds. Adding vector search to an existing Postgres database is a config change. Moving from a document database to relational once you need real structure is the harder case, so it's worth documenting your schema early to make that switch easier if it comes up.

What database does Supabase use?

Supabase provisions a standard, unrestricted Postgres database for every project, including on its free tier, with the pgvector extension available if you later need semantic search.