Navigate the database landscape without getting paralyzed by options. Covers relational vs document vs columnar vs graph vs time-series databases, when each shines, migration patterns, and the selection framework that prevents 'we chose Mongo for everything' syndrome.
Choosing the right database is not a one-time decision but an ongoing alignment between data access patterns and system needs. This guide helps engineers select a database by focusing on what they are trying to achieve—whether it’s handling real-time analytics, scaling writes, ensuring consistency across regions, or supporting complex queries—rather than by listing database types. The decisions here directly affect latency, availability, consistency, and maintenance overhead.
The core of database selection lies in understanding how data is read and written. A system with high-frequency, low-latency reads benefits from a different database than one with infrequent, batched writes. Start with the access pattern and work outward.
For services that serve user-facing data—like session state, product listings, or real-time dashboards—low latency and high read throughput are critical. The database must serve many small queries with minimal overhead.
Use a key-value store when the data model is flat and access is primarily via a primary key or a composite key. For example, a user session service reads session_id to retrieve session data.
SELECT user_id, last_activity, preferences
FROM sessions
WHERE session_id = 'abc123';This query must execute in under 10ms on average, across thousands of requests per second. The database must support in-memory caching, indexing on the key, and efficient serialization.
If the same session data is accessed in a hot path—e.g., every user request reads session data—then caching layers (Redis, Memcached) become essential. A miss in the cache triggers a database lookup; a hit avoids a round-trip.
When reads are not just by key but by range of keys—e.g., fetching all sessions for a user or all sessions created in the last hour—then a B-tree index or LSM-tree becomes essential.
# Example: PostgreSQL with a B-tree index on session_id
CREATE INDEX idx_sessions_session_id ON sessions (session_id);The database must support row-level locking and concurrent reads without blocking writes. A common failure mode is hotspotting, where a few keys dominate the read load, causing cache thrashing and increased I/O.
To detect hotspotting, monitor the number of cache misses per key and index bloat. When a key is frequently accessed but not in the cache, the system experiences increased latency and CPU overhead. Fix by sharding the data across multiple database instances, using consistent hashing or range-based partitioning.
When data arrives in bursts—such as logs, sensor readings, or event streams—the database must handle many small writes with low latency and high throughput.
A write-optimized storage engine, such as an LSM-tree, excels here. Writes are first appended to an in-memory structure (memtable), then flushed to disk in sorted order (SSTables). Compaction merges overlapping SSTables to reduce reads.
Use batching to reduce round-trips. Instead of inserting one row at a time:
INSERT INTO events (timestamp, user_id, event_type, payload)
VALUES
('2023-10-05T12:00:00Z', 101, 'login', '{"device": "mobile"}'),
('2023-10-05T12:00:01Z', 102, 'page_view', '{"page": "/home"}');Batch inserts reduce network overhead and increase write throughput by 3–5× compared to single-row inserts.
The trade-off is increased read latency due to compaction overhead and multiple SSTables to scan. To mitigate, use columnar storage with compression and predicate pushdown.
For example, in a columnar database like Apache Parquet, queries that scan only a few columns (e.g., timestamp and event_type) avoid reading the entire payload field.
# Read only timestamp and event_type from Parquet file
SELECT timestamp, event_type
FROM events
WHERE timestamp > '2023-10-05T12:00:00Z';This query reads only the relevant column chunks, reducing I/O and memory usage. The database must support pushdown of filters and projection pruning.
When writes are asynchronous—e.g., using message queues (Kafka, RabbitMQ)—then the database must handle eventual consistency. A common failure is duplicate writes due to message retries. The system must use idempotent writes or deduplication keys.
Use upserts (INSERT ... ON CONFLICT DO UPDATE) to handle duplicates:
INSERT INTO events (id, timestamp, user_id, event_type, payload)
VALUES (12345, '2023-10-05T12:00:00Z', 101, 'login', '{}')
ON CONFLICT (id) DO UPDATE SET
timestamp = EXCLUDED.timestamp,
payload = EXCLUDED.payload;Failure occurs when the conflict resolution logic is missing—e.g., not updating payload on upsert, or not handling ON CONFLICT on multiple columns.
When data is analyzed—e.g., user behavior reports, cohort analysis, or real-time dashboards—the database must handle multi-table joins, aggregations, and window functions efficiently.
Use a row-store database with B-tree indexes, materialized views, and query planning.
For example, a query that computes daily active users by region:
SELECT
DATE(created_at) AS day,
region,
COUNT(DISTINCT user_id) AS dau
FROM user_sessions
WHERE created_at >= '2023-10-01'
GROUP BY day, region
ORDER BY day DESC;This query must return results in under 500ms, even with millions of rows.
To optimize, create composite indexes:
CREATE INDEX idx_user_sessions_date_region ON user_sessions (created_at, region, user_id);The database uses this index to scan only relevant rows and avoid sorting.
If the query is run frequently and data changes slowly, materialized views improve performance.
CREATE MATERIALIZED VIEW daily_dau AS
SELECT
DATE(created_at) AS day,
region,
COUNT(DISTINCT user_id) AS dau
FROM user_sessions
GROUP BY day, region;Refresh the view periodically using scheduled jobs or triggers.
Common failure mode: stale materialized views. The view is not updated in time, leading to outdated reports. Fix with incremental refresh:
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_dau;This command updates the view without locking reads, but requires tracking changes (e.g., via last_updated timestamp or LOGICAL replication).
For real-time analytics, use streaming databases that continuously ingest and update views. A query on a streaming table must process new data as it arrives.
Example: a dashboard that shows active users per minute:
CREATE TABLE active_users_stream (
minute TIMESTAMP,
user_count INT,
updated_at TIMESTAMP
);
-- Insert new counts every minute
INSERT INTO active_users_stream (minute, user_count, updated_at)
VALUES ('2023-10-05T12:00:00Z', 1247, '2023-10-05T12:01:00Z');The database must support continuous query processing and event-driven updates.
When data is accessed across multiple regions, or when high availability is required, the database must handle replication, failover, and consistency models.
Use replication to maintain copies of data across multiple nodes. A master-slave setup writes to the master and reads from slaves.
# Enable replication in PostgreSQL
pg_ctl start -D /var/lib/postgresql/data
pg_basebackup -h primary -D /var/lib/postgresql/data -P -X streamThe slave applies WAL (Write-Ahead Log) records from the master. This ensures point-in-time recovery and read scalability.
But replication introduces latency. When a write is committed on the master, it may take several seconds for the slave to catch up.
To reduce this, use synchronous replication:
ALTER SYSTEM SET synchronous_commit = on;
ALTER SYSTEM SET synchronous_standby_names = 'standby1, standby2';This guarantees that a write is committed only after at least two replicas have acknowledged the WAL record.
Failure mode: replication lag. A query on a slave returns data that is 2 seconds old. The system must detect lag and route writes to master while routing reads to slaves.
Use read replicas with load balancing and connection pooling (e.g., PgBouncer, HAProxy).
For global systems, use multi-master replication or sharding.
In sharded systems, data is partitioned by a shard key—e.g., user_id.
Each shard is a separate database instance. A query on user_id = 101 goes to the shard that owns that user.
The database must support cross-shard queries:
SELECT u.name, s.session_id, s.timestamp
FROM users u
JOIN sessions s ON u.id = s.user_id
WHERE u.region = 'US';This query requires query routing and coordinating across shards.
Failure mode: shard imbalance. Some shards grow much larger than others, causing hotspots. The system must rebalance data using data migration and consistent hashing.
Use data migration tools like pt-online-schema-change (Percona) or pg_repack.
# Rebalance shards using a central coordinator
# Send data from overfilled shard 1 to underfilled shard 2
pg_dump -h shard1 -U app user_data > user_data.dump
psql -h shard2 -U app -d app < user_data.dumpThe coordinator must track metadata (e.g., shard mapping, replication status) and handle failures during migration.
As systems evolve, data models change. The database must support schema changes with zero downtime and data consistency.
Use migrations to manage schema changes. A migration script is a versioned SQL file:
-- 2023-10-05-01-add-user-roles.sql
ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user';
UPDATE users SET role = 'admin' WHERE id IN (1, 2, 3);
ALTER TABLE users ALTER COLUMN role SET NOT NULL;Apply migrations using a migration tool such as Flyway, Liquibase, or custom scripts.
Common failure mode: migration rollback. A new version fails, and the system reverts to the old schema. But data is inconsistent: some rows have role column, others do not.
To avoid this, use backward-compatible schema changes:
DEFAULT valuesFor highly structured data, use JSONB columns to store nested or evolving data.
-- Store user preferences as JSONB
ALTER TABLE users ADD COLUMN preferences JSONB;
UPDATE users SET preferences = '{"theme": "dark", "notifications": true}' WHERE id = 101;Query the JSONB data using path expressions:
SELECT id, preferences->'theme' AS theme
FROM users
WHERE preferences ? 'notifications';This query uses indexing on JSONB (e.g., GIN index) for fast filtering.
Failure mode: JSON schema drift. Different services write different structures to the same JSONB column, causing inconsistencies and query complexity.
To manage this, use schema validation and migration scripts that normalize JSON data.
When selecting a database:
The database is not a black box. Its behavior emerges from the interplay of storage engines, indexing, replication, and query planning. Choose not just a product, but a pattern of interaction between data and system.
This page was rewritten on 15 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.
We use cookies for analytics (Google Analytics) and advertising (Google AdSense) to improve your experience and support free content. Privacy Policy