No One Database Fits All
The database is the most critical infrastructure decision in your application. Choose wrong, and you face painful migrations, performance problems, and architectural limitations. The good news: most applications should start with PostgreSQL. The nuance: specialized workloads benefit from specialized databases. At Nexis Limited, we use PostgreSQL as our primary database across all products, supplemented by Redis (caching), and Elasticsearch (search) where needed.
Relational Databases (SQL)
PostgreSQL
Our default recommendation. PostgreSQL combines relational integrity with modern features: JSONB for semi-structured data, full-text search, GIS support (PostGIS), materialized views, and row-level security. It handles transactional workloads, analytical queries, and everything in between. If you are not sure which database to use, use PostgreSQL.
MySQL
MySQL is simpler than PostgreSQL and widely used in the web development ecosystem. It is the database behind WordPress, Drupal, and many PHP applications. MySQL excels at read-heavy workloads and has excellent replication support. However, PostgreSQL offers more advanced features and better standards compliance.
Document Databases (NoSQL)
MongoDB
MongoDB stores data as JSON-like documents with flexible schemas. It excels when your data does not fit neatly into tables — content management systems, product catalogs with varying attributes, or event logs. However, for most transactional applications, PostgreSQL with JSONB provides similar flexibility with stronger consistency guarantees.
Key-Value Stores
Redis
Redis is an in-memory key-value store used for caching, session management, real-time features, and message brokering. It is not a primary database — it supplements your main database for performance-critical operations. We use Redis extensively across all products for caching, pub/sub, and rate limiting.
Search Engines
Elasticsearch
Elasticsearch provides full-text search, log analysis, and analytics on large datasets. Use it when your search requirements exceed PostgreSQL's full-text search capabilities — complex relevance scoring, faceted search, or search across very large datasets. It is not a primary database; replicate searchable data from your primary database.
Decision Framework
| Requirement | Recommended Database |
|---|---|
| General web application | PostgreSQL |
| Caching and sessions | Redis |
| Full-text search | PostgreSQL (simple) or Elasticsearch (complex) |
| Time-series data | PostgreSQL + TimescaleDB or InfluxDB |
| Graph relationships | PostgreSQL (simple) or Neo4j (complex) |
| Analytics/OLAP | ClickHouse, BigQuery, or PostgreSQL (moderate scale) |
| Document storage | PostgreSQL JSONB or MongoDB |
Common Mistakes
- Choosing MongoDB because "flexible schema": Schema flexibility often leads to data inconsistency. PostgreSQL's JSONB provides flexibility when needed alongside relational structure.
- Using Redis as a primary database: Redis is in-memory and not designed for durability. Use it as a cache or session store, not for data you cannot afford to lose.
- Multiple databases too early: Start with one database. Add specialized databases only when specific requirements justify the operational complexity.
- Not using connection pooling: Both PostgreSQL and MySQL benefit significantly from connection pooling (PgBouncer, ProxySQL) in production.
Conclusion
Start with PostgreSQL. It handles more use cases capably than any other single database. Add Redis for caching when performance requires it. Add Elasticsearch when search requirements grow beyond PostgreSQL's capabilities. Add other specialized databases only when you have a specific requirement that justifies the operational complexity.
Need help with database architecture? Our team designs and operates database systems for production workloads.