The Database Decision That Shapes Everything
The database type selection is one of the most consequential early decisions in web application development — it shapes the data model, the query patterns, the scaling approach, and the operational requirements of the application. Getting it wrong doesn’t prevent the application from being built; it means building on a foundation that creates increasingly significant friction as the application grows and the mismatch between the database’s strengths and the application’s needs becomes more apparent.
The conventional wisdom that ‘relational databases are for structured data, document databases are for flexible data, and graph databases are for relationships’ provides a starting framework that’s worth interrogating against the specific application being built. Most web applications have some combination of structured and flexible data, some relationships between entities, and a scaling requirement that’s best understood after the application has grown rather than predicted before it’s launched.
Relational Databases (SQL): The Structured Default
PostgreSQL, MySQL, and SQLite represent the relational database tier: they store data in tables with defined columns and types, enforce relationships between tables through foreign keys, support transactions that guarantee data consistency across multiple operations, and are queried through SQL (Structured Query Language) whose declarative nature allows expressing complex multi-table queries without specifying how the database should execute them.
Relational databases are the right choice when: data has a clear structure that’s well-defined upfront (or can be incrementally migrated as it evolves through schema migrations), data integrity constraints are important (ensuring that an order always references a valid customer, that a quantity can’t be negative), complex querying across multiple related entities is required (reporting, analytics, dashboards), and ACID transactions are needed (financial applications where partial state updates would be data corruption).
Document Databases (NoSQL): The Flexible Alternative
MongoDB, Firestore (Firebase), and CouchDB represent the document database tier: they store data as JSON-like documents that can have different structures from each other, don’t require schema definition before inserting data, and support horizontal scaling approaches that relational databases handle less naturally. They’re appropriate when the data schema is genuinely variable (different users have different attributes, products have category-specific attributes), when the data access pattern retrieves complete documents rather than joining across many tables, and when the development speed of schema-free insertion is valued over the data integrity guarantees that schemas provide.
Document databases are often chosen for the wrong reasons: ‘we don’t know what our data will look like yet’ is not the same as ‘our data is genuinely variable’ — most applications whose data structure is initially unclear eventually converge on a stable structure that a relational database would serve well. The flexibility of document databases is sometimes chosen to defer the schema design work that relational databases require upfront, at the cost of creating data consistency problems that the schema would have prevented.
NewSQL and Distributed Databases: Scale-Out With SQL
PlanetScale, CockroachDB, Spanner, and Neon represent the NewSQL tier: databases that provide horizontal scaling (distributing data across multiple servers for performance and availability) while maintaining SQL compatibility and ACID transaction guarantees that traditional relational databases provide only on single nodes or small clusters. These databases address the limitation that PostgreSQL and MySQL face at very large scale without requiring migration to NoSQL’s different data model.
NewSQL databases are appropriate when: the application has grown to scales where single-node relational databases are a performance bottleneck, relational data model and SQL query capabilities need to be preserved, and geographic distribution (serving data from multiple regions close to users) is required. For most web applications — including applications with millions of users — a well-configured single-node PostgreSQL instance handles the load comfortably; the scale where NewSQL provides genuine value over properly tuned relational databases is reached by a minority of web applications.
The Practical Starting Point
For most new web applications: start with PostgreSQL. It handles the data volumes of most successful web applications without scaling concerns, supports JSON columns that provide document-style flexibility within a relational database when genuinely needed, has excellent tooling and hosting support (Supabase, Railway, Heroku, AWS RDS, and most cloud providers support PostgreSQL), and provides the data integrity guarantees that prevent a class of application bugs that document databases allow.
The decision to use a different database should be motivated by a specific requirement that PostgreSQL doesn’t serve: if the data genuinely has highly variable structure that would require many nullable columns or EAV (entity-attribute-value) patterns in a relational schema, a document database is worth considering. If the application needs to store and query graph-structured data (social networks, recommendation systems, knowledge graphs) with complex relationship traversals, a graph database (Neo4j, ArangoDB) addresses this more naturally than either relational or document alternatives. The default that requires specific motivation to deviate from is Postg