SQL or NoSQL? Choosing a database for your project
Relational (SQL) databases keep data in tables, enforce relationships and protect consistency with ACID guarantees, which makes them the natural choice for invoices, stock, staff and orders. NoSQL offers flexible schemas as documents, key-value pairs or graphs and wins with very large scale, variable structure or specific access patterns. For most small and medium projects a relational database such as PostgreSQL is both sufficient and easier to maintain.
- Money and stock require consistency
- PostgreSQL stores JSON too these days
- NoSQL is chosen for access patterns, not fashion
- Migrating data costs more than rewriting code
What a relational database does
A relational database keeps data in tables of rows and columns and enforces the relationships between them: an order must belong to a customer, a stock movement to a product. These systems provide ACID guarantees — a transaction either happens completely or not at all.
In practice: if reducing stock and issuing an invoice are one transaction, a power cut cannot leave half a record behind.
When NoSQL makes sense
NoSQL is not one thing; it covers document stores, key-value stores, column families and graph databases. Its strengths are flexible schemas, speed for particular access patterns and horizontal scaling. The trade-off between consistency and availability in distributed systems is described by the CAP theorem.
Good fits: sessions and caching, very high-volume event logs, content whose structure keeps changing, search and recommendation layers.
A decision table
| Situation | Recommendation |
|---|---|
| Invoices, stock, payroll, orders | Relational (PostgreSQL, MySQL) |
| Sessions, caches, counters | Key-value (Redis) |
| Content with a shifting schema | Document store or PostgreSQL JSONB |
| Huge event streams, logs | Column family / time series |
"Both?" — usually the right answer
Modern relational databases store flexible data too: PostgreSQL's JSONB type keeps and queries loosely structured fields inside a table. Most projects therefore run on one database, with Redis added for caching. Keeping two databases in sync usually creates more problems than it solves.
Questions to ask
- Are there mandatory relationships in the data? (Then relational.)
- Are money, stock or legal records involved? (Then ACID is required.)
- How many records will exist in a year? (Millions are not a problem for relational systems.)
- Which system does the team genuinely know?
- How will backup and restore be tested?
A database is not a fashion choice: moving data later costs more than rewriting the software.
Sources
- Wikipedia, Relational database
- PostgreSQL, JSON types
- Wikipedia, CAP theorem
Frequently asked questions
When does a spreadsheet stop being enough?
When more than one person edits the same file and you need to know who changed what.
PostgreSQL or MySQL?
Both are mature. PostgreSQL stands out for complex queries and JSON support, MySQL for hosting availability.
Is NoSQL faster?
For specific access patterns, yes. For general business queries relational databases usually win.
What happens as data grows?
Indexes and query tuning come first; most small businesses never hit a real scaling wall after that.
