Picking a database is one of the few early decisions that’s genuinely expensive to undo. Change frameworks and you rewrite some code; change databases and you migrate every record, rewrite every query, and retest everything that touches data. After 20 years of building PHP applications, from small business sites to multi-tenant SaaS, my default advice is simple: start with a relational database unless you have a specific reason not to. This guide is about recognising those reasons.
The five main types of database
1. Relational (MySQL, PostgreSQL, MariaDB, SQL Server). Data lives in tables with fixed columns, linked by keys. You get joins, transactions and strong consistency: an order and its payment are saved together or not at all. Best for: most business applications, e-commerce, anything with money, users and permissions.
2. Document (MongoDB, Couchbase, Firestore). Data lives as JSON-like documents that can differ in shape and nest freely. Best for: content whose structure varies record to record (product catalogues with very different attributes, CMS content, event payloads) and teams that iterate on the data shape quickly.
3. Key-value and cache (Redis, Memcached). Extremely fast lookups by key, usually in memory. Best for: sessions, caching, rate limiting, queues and leaderboards. It almost always sits next to your main database, not instead of it.
4. Search engines (Elasticsearch, OpenSearch, Meilisearch). Built for full-text search, typo tolerance, relevance ranking and filters. Best for: site search, product search and log analysis. Like a cache, it’s usually a second store fed from your main database.
5. Vector databases (pgvector, Pinecone, Qdrant, Weaviate). Store embeddings, which are lists of numbers that represent meaning, and find the closest ones fast. Best for: AI features such as semantic search, recommendations and RAG chatbots. More on this below.
Five questions that decide it
- Is your data relational? If records reference each other (customers → orders → items → payments), relational wins. Rebuilding joins in application code is where document-database projects get painful.
- How much does consistency matter? If two people buying the last item at the same time is a problem, you need transactions. Relational databases do this by default; document databases support it, but you have to design for it.
- How stable is the shape of your data? A fixed shape suits tables. A shape that varies a lot by record suits documents, though modern MySQL and PostgreSQL JSON columns cover many of these cases too.
- What does your team already know? A database your team understands deeply beats a “better” one they’re learning in production. Operational experience (backups, indexing, upgrades) matters as much as features.
- What will you need in 2–3 years? Think about reporting, analytics, search and AI features. You rarely need to choose for them now, but avoid choices that make them hard later.
Common combinations that work
- MySQL or PostgreSQL alone: enough for the large majority of websites and apps.
- Relational + Redis: add caching and sessions when traffic grows.
- Relational + a search engine: when users need fast, forgiving search across lots of content.
- Relational + vectors: for AI features, either inside the same database or in a dedicated vector store.
What AI features need from a database
If you plan an AI chatbot or semantic search, you need to store embeddings and find the most similar ones to a question. You have three options:
- Inside your existing relational database. PostgreSQL has the pgvector extension. Even stock MySQL 8 can do it for small and medium knowledge bases by storing vectors as JSON and computing similarity in SQL. I build exactly this in Episode 3: Embeddings & Your First Similarity Search and Episode 4: Scaling the Vector Search in MySQL.
- A dedicated vector database. Worth it at hundreds of thousands of vectors or more, when you need approximate nearest neighbour (ANN) indexes for speed.
- A managed AI search service. The fastest to start, but you depend on one vendor.
For most teams starting out, option 1 is right: one database, one backup, one set of skills. The full walkthrough is in Build a RAG-Powered Support Bot in PHP & MySQL.
My default recommendations
- Business app, SaaS, e-commerce: PostgreSQL or MySQL. Add Redis when you need caching.
- Content-heavy site with varied structures: MySQL or PostgreSQL with JSON columns first; MongoDB if documents really dominate.
- Real-time features (chat, presence, counters): your main database plus Redis.
- AI search or chatbot: your existing relational database plus vectors, until scale forces a dedicated store.
Frequently Asked Questions
Default to SQL (MySQL or PostgreSQL) unless your data genuinely varies in shape or you need a specific NoSQL strength. Most applications are relational at heart, and SQL databases now handle JSON well too.
Not in general. Each is faster for different access patterns: MongoDB for reading whole nested documents, MySQL for joins and transactional workloads. Good indexing matters more than the engine for most apps.
Not at the start. For thousands of documents, vectors stored in MySQL or PostgreSQL work well. Move to a dedicated vector database when you reach hundreds of thousands of vectors or need very low latency.
Yes, and most larger apps do: a relational database as the source of truth, plus Redis for caching, plus search or vectors where needed. Just keep one system as the source of truth.
