🏛️ Relational Databases
The databases relational organize data into tables with a rigid schema, use SQL as the standard language, and guarantee ACID properties. They’re the foundation of most enterprise applications and the most mature model on the market.
🎯 Main Relational DBMSs
PostgreSQL
The most complete open-source option. Extensions (PostGIS, pgvector), advanced types, native JSON. Ideal if you want flexibility without giving up SQL.
MySQL
Popular, simple to operate, with a huge ecosystem. First choice for web apps, CMSs, and LAMP stacks. Excellent read performance.
SQL Server
Native integration with the Microsoft ecosystem (.NET, Azure, Power BI). Strong in Windows corporate environments.
Oracle
Enterprise. RAC, Data Guard, advanced partitioning. High cost, but unparalleled mission-critical capabilities.
SQLite
Embedded database, zero configuration, single file. Perfect for mobile apps, IoT, prototyping, and testing. No server required.
✅ USE WHEN
- • Highly structured data with clear relationships
- • Referential integrity and consistency are priorities
- • OLTP workloads (many short, concurrent transactions)
- • Complex queries with JOINs, aggregations, and subqueries
❌ DO NOT USE WHEN
- • Schema changes constantly and unpredictably
- • Massive horizontal scaling (thousands of nodes) is required
- • Data is predominantly unstructured (logs, varied JSON)
- • Sub-millisecond latency is critical (cache, sessions)
📄 Document Databases
Databases for documents store data as JSON/BSON, without a rigid schema. Each document can have a different structure, which provides flexibility for scenarios where data evolves rapidly or varies between records.
🎯 Main Document DBMSs
MongoDB
The most popular. Flexible schema, powerful aggregation pipeline, native sharding. Rich ecosystem with Atlas (cloud), Compass (GUI), and drivers for every language.
Couchbase
Combines documents with an in-memory cache. N1QL (SQL-like query), multi-datacenter replication. Well suited to scenarios that need low latency with complex documents.
🔍 Document vs. Relational: When to Choose
Choose Document when...
- • Data has a variable structure (product catalog with different attributes)
- • Rapid prototyping without migrations
- • Data is naturally hierarchical (posts with nested comments)
- • Horizontal scaling is a priority
Choose Relational when...
- • Relationships between entities are complex and frequent
- • Transactional consistency is mandatory
- • Data has a stable, well-defined schema
- • Queries with many JOINs are the norm
⚡ Key-Value and Cache
Databases key-value are the simplest conceptually: a key maps to a value. This simplicity enables low latency sub-millisecond and massive throughput. They are the foundation of real-time cache, session, and queue systems.
🎯 Main Key-Value DBMSs
Redis
In-memory, sub-millisecond latency. Rich data structures (strings, hashes, lists, sets, sorted sets). Pub/Sub, Streams, Lua scripting. The Swiss Army knife of caching.
DynamoDB
AWS serverless, auto-scaling, single-digit ms SLA. Key-value + document model. Pay-per-request or provisioned capacity. Zero ops.
📊 Use Cases
- • Application cache: API responses, results of heavy queries, rendered pages
- • User sessions: fast, expiring storage for session data
- • Queues and messaging: Redis Lists and Streams for asynchronous processing
- • Real-time counters: page views, rate limiting, leaderboards
- • Pub/Sub: real-time notifications, chat, events
💡 Cache Patterns
- Cache-Aside (Lazy Loading): The app checks the cache first. If it doesn't find anything (miss), it fetches the data from the database, writes it to the cache, and returns it. Simple and efficient.
- Write-Through: Every write goes to the cache AND the database at the same time. Cache is always up to date, but writes are slower.
- Write-Behind: Writes go to the cache immediately. The database is updated asynchronously. There is a risk of data loss if the cache fails before the sync.
- TTL (Time-To-Live): Set expiration to avoid stale data. Default: 5-15 min for read data, 30-60s for volatile data.
📊 Columnar Databases
Databases columnar store data by column instead of by row. This enables extreme compression and very fast scans for analytical queries that access only a few columns in tables with billions of rows. They are the foundation of Modern Data Warehouses.
🎯 Main Columnar DBMSs
BigQuery
Google serverless. Automatic scaling, standard SQL, GCP integration. Pay per query executed. Ideal for ad hoc analytics.
Redshift
AWS data warehouse. Managed cluster, Redshift Spectrum for S3, integration with the AWS ecosystem.
ClickHouse
Open-source, created by Yandex. Exceptional OLAP performance, aggressive compression. Strong in logs, metrics, and real-time analytics.
Snowflake
Multi-cloud, separation of storage and compute. Independent scaling, data sharing across organizations. Excellent UX.
📊 Important Data
Columnar databases can be 100x faster than row stores for aggregations on wide tables. In a table with 50 columns, if the query uses only 3, the columnar store reads only 6% of the data. Columnar compression reduces storage by up to 10x because similar values are stored together.
🕸️ Graphs and Time Series
Specialized models for data with complex relationships (graphs) or time-indexed data (time series). Each solves specific problems far more efficiently than a generic relational database.
🕸️ Graph Databases
Neo4j
The leader in graph databases. Uses Cypher as its query language, optimized for path traversal and pattern matching.
- • Social networks: friends of friends, connection suggestions, degrees of separation
- • Recommendations: “people who bought X also bought Y” with graph traversal
- • Fraud detection: identify suspicious connection patterns between accounts
- • Knowledge graphs: relationships between entities, ontologies, semantic search
⏱️ Time Series
InfluxDB
Native time series support. Flux query language, automatic retention, downsampling. Strong for infrastructure and IoT metrics.
TimescaleDB
A PostgreSQL extension. Full SQL + hypertables optimized for time series. The best of both worlds: familiar SQL + time-series performance.
- • System metrics: CPU, memory, disk, network in Grafana dashboards
- • IoT: sensors, telemetry, high-frequency device data
- • Time windows: moving averages, interval-based aggregations, anomaly detection
- • Finance: stock prices, ticks, candlesticks with time resolution
⚖️ How to Choose
There is no perfect DBMS for everything. The choice depends on data model, from the access pattern and non-functional requirements. Many modern architectures use polyglot persistence: each service chooses the ideal database for its use case.
📊 Comparison Table
| DBMS | Model | Strengths | Attention |
|---|---|---|---|
| PostgreSQL | Relational | Standard SQL, extensions | Fine-tuning |
| MySQL | Relational | Popular, simple | Resources by engine |
| MongoDB | Document | Flexible, aggregation | Multi-column transactions |
| Redis | Key-value | In-memory, minimum latency | Persistence/HA |
| ClickHouse | Columnar | Fast analytics | OLAP only |
| Neo4j | Graph | Efficient paths | Cost of dense graphs |
| Elasticsearch | Search | Full-text, aggregations | Does not replace ACID |
🎯 Recommendations by Scenario
- • OLTP (transactional): PostgreSQL - full SQL, ACID, extensions, active community
- • CMS / Catalog: MongoDB - flexible schema for varied content, rapid iteration
- • Cache / Sessions: Redis - in-memory, native TTL, rich data structures
- • Data Warehouse: BigQuery or ClickHouse - analytics at scale, columnar compression
- • Graphs / Relationships: Neo4j - efficient traversal, expressive Cypher, visualization
- • Time Series: TimescaleDB - PostgreSQL SQL + time-optimized hypertables
- • Full-text Search: Elasticsearch - inverted indexing, scoring, aggregations, facets