Documentation Index

Fetch the complete documentation index at: https://resources.devweekends.com/llms.txt

Use this file to discover all available pages before exploring further.

​

Database Deep Dives

​

Section 1: PostgreSQL Internals

​

1.1 MVCC (Multi-Version Concurrency Control)

​

1.2 WAL (Write-Ahead Log)

1. Transaction begins. Client issues UPDATE accounts SET balance = 500 WHERE id = 42.
2. PostgreSQL modifies the row in the shared buffer pool (in-memory copy of the data page).
3. A WAL record describing this change is written to the WAL buffer (in memory).
4. When the transaction commits, the WAL buffer is flushed to disk (pg_wal/ directory). This is the fsync call — the moment the change is durable.
5. The actual data page in the buffer pool is not immediately written to disk. It will be flushed later by the background writer or checkpointer.
6. On crash, PostgreSQL replays WAL records from the last checkpoint to bring data files up to date.

​

1.3 VACUUM — Dead Tuple Cleanup

• Table bloat: a 1 GB table grows to 10 GB as dead tuples accumulate.
• Index bloat: indexes still point to dead tuples, making them larger and slower.
• Transaction ID wraparound: PostgreSQL uses 32-bit transaction IDs. After ~2 billion transactions, IDs wrap around and old transactions appear to be in the “future.” If this happens, PostgreSQL shuts down to prevent data corruption. VACUUM marks old transaction IDs as “frozen” to prevent wraparound.

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * table_size

ALTER TABLE large_events SET (
  autovacuum_vacuum_scale_factor = 0.01,    -- trigger at 1% dead tuples instead of 20%
  autovacuum_vacuum_threshold = 1000,       -- lower base threshold
  autovacuum_analyze_scale_factor = 0.005,  -- update statistics more frequently
  autovacuum_vacuum_cost_delay = 2          -- reduce throttling (default 20ms)
);

​

1.4 The Query Planner

​

1.5 EXPLAIN ANALYZE Deep Dive

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

1. Actual rows vs estimated rows. If the planner estimated 100 rows but got 50,000, the plan is based on wrong statistics. Run ANALYZE on the table.
2. Loops. A Nested Loop with loops=10000 means the inner operation ran 10,000 times. Multiply the inner node’s time by the loop count for the true cost.
3. Buffers: shared hit vs shared read. shared hit = read from buffer cache (fast). shared read = read from disk (slow). A high shared read count means the working set does not fit in shared_buffers.
4. Sort Method: external merge disk. The sort spilled to disk because work_mem was too small. Increase work_mem for this session or globally.
5. Seq Scan with Filter: rows removed by filter. If a sequential scan filtered out 99% of rows, an index would be far more efficient.

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

​

1.6 Connection Management

• Transaction pooling (most common): A server connection is assigned to a client for the duration of one transaction, then returned to the pool. Best for web workloads with short transactions.
• Session pooling: A server connection is assigned for the entire client session. Needed for features that require session state (prepared statements, temp tables, SET commands).
• Statement pooling: A connection is assigned per statement. Most aggressive but breaks multi-statement transactions.

​

1.7 Partitioning

• Range partitioning: Divide by value ranges. Most common for time-series data: one partition per month, per week, or per day.
• List partitioning: Divide by discrete values. Partition by region (us-east, eu-west) or status (active, archived).
• Hash partitioning: Distribute evenly across N partitions based on a hash of the partition key. Good for even distribution when there is no natural range or list.

• Tables over 100 million rows where queries consistently filter by the partition key.
• Time-series data where old partitions can be cheaply dropped (DROP TABLE is instant; DELETE FROM ... WHERE date < X on a 500M row table takes hours and creates massive dead tuple bloat).
• When you need different storage strategies for hot vs cold data (recent partitions on SSD, old partitions on cheaper storage).

​

1.8 Extensions

• PostGIS: Geospatial data types and queries. ST_DWithin(point, location, 5000) finds everything within 5 km. Powers location features at companies from Instacart to OpenStreetMap.
• pg_trgm: Trigram-based fuzzy text search. Enables LIKE '%search%' queries to use an index (normally impossible with leading wildcards). Create a GIN trigram index and suddenly fuzzy search is fast.
• TimescaleDB: Turns PostgreSQL into a time-series database with automatic partitioning (hypertables), compression (90%+ space reduction on old data), and continuous aggregates. If you are choosing between PostgreSQL + TimescaleDB and InfluxDB, keep it in the PostgreSQL ecosystem unless you have a strong reason not to.
• pgvector: Vector similarity search for AI/ML embeddings. Store embeddings alongside relational data and query both in a single SQL statement. Rapidly becoming the default for RAG (Retrieval-Augmented Generation) applications that do not need billion-vector scale.

​

Section 2: MongoDB Patterns

​

2.1 The Document Model

• Hierarchical data: A product with variants, each variant with sizes, each size with inventory by warehouse. In PostgreSQL, that is 4 tables and 3 JOINs. In MongoDB, it is one document.
• Flexible schema: An e-commerce platform where shoes have size and color, electronics have voltage and wattage, and books have isbn and author. In relational, you either use sparse columns (wasteful) or an EAV pattern (painful). In MongoDB, each document has exactly the fields it needs.
• Read-heavy with denormalization: When 90% of your operations are “fetch this entire thing and display it,” embedding all the data in one document means one read, no JOINs.

• Many-to-many relationships: Students and courses. If a student document embeds course references and a course document embeds student references, updating a course name means updating every student document that references it. This is the relational model’s strength and the document model’s weakness.
• Data that is frequently updated in nested arrays: Updating the 500th element of a 1000-element embedded array is expensive. MongoDB must rewrite the entire document.
• Complex analytical queries with JOINs across collections: MongoDB’s $lookup (the equivalent of JOIN) is significantly slower than relational JOINs and does not support the same optimization strategies.

​

2.2 Schema Design Patterns

​

2.3 Write Concerns and Read Concerns

​

2.4 Aggregation Pipeline

​

2.5 Sharding

1. High cardinality: A boolean field (is_active) is a terrible shard key — all data lands on two chunks. A user ID or order ID has millions of distinct values.
2. Even distribution: Monotonically increasing keys (timestamps, ObjectId) cause all new writes to hit the same shard (the one owning the highest range). This creates a “hot shard” bottleneck.
3. Query isolation: The shard key should appear in most queries. If your queries filter by user_id, shard on user_id so queries hit a single shard. If you shard on order_id but query by user_id, every query must scatter to all shards and gather results — a scatter-gather query that does not scale.

​

2.6 Change Streams

​

2.7 Common Gotchas

• Unbounded arrays: Embedding an ever-growing array (all user activity, all comments) into a single document eventually hits MongoDB’s 16 MB document size limit. Even before hitting the limit, updates to large arrays are slow because MongoDB rewrites the entire document. Use the Bucket or Outlier pattern instead.
• Missing compound indexes: If you query by { user_id, status, created_at }, an index on just user_id forces MongoDB to scan all of that user’s documents to filter by status and created_at. Create a compound index matching your query pattern.
• WiredTiger cache sizing: WiredTiger (MongoDB’s storage engine) defaults to using 50% of RAM minus 1 GB for its internal cache. On a 64 GB server, that is ~31 GB. If your working set (frequently accessed data + indexes) exceeds this, performance degrades dramatically as WiredTiger constantly reads from and evicts to disk. Monitor wiredTiger.cache.bytes currently in the cache and ensure it does not stay near the maximum.

​

Section 3: DynamoDB Strategies

​

3.1 Access Patterns First

1. Get user by ID
2. Get all orders for a user, sorted by date
3. Get all comments on a post
4. Get a user’s most recent 10 posts
5. Get all users in an organization

​

3.2 Single-Table Design

• Get user profile: GetItem(PK=USER#123, SK=PROFILE)
• Get all orders for user: Query(PK=USER#123, SK begins_with ORDER#)
• Get all items in an order: Query(PK=ORDER#2024-001, SK begins_with ITEM#)
• Get all members of an org: Query(PK=ORG#acme, SK begins_with MEMBER#)

​

3.3 Partition Key Design

• High-cardinality keys: Use user IDs, order IDs, or UUIDs — not statuses, dates, or categories.
• Write sharding: If you must use a low-cardinality key (e.g., tracking page views per URL), append a random suffix: URL#homepage#3 (where 3 is random between 0-9). This distributes writes across 10 partitions. Reads require querying all 10 suffixes and aggregating — a trade-off that is worth it for high-write scenarios.
• Composite keys: Combine fields to increase cardinality. TENANT#acme#USER#123 is better than TENANT#acme alone.

​

3.4 Global Secondary Indexes (GSI)

• GSIs are eventually consistent — there is no strongly consistent read option for GSIs. If you write an item and immediately query the GSI, you might not see it yet (typically milliseconds, but during high load it can be longer).
• GSIs have their own provisioned throughput. If the GSI is throttled, writes to the base table are also throttled (backpressure).
• You can project a subset of attributes to the GSI (reducing cost and improving performance) or project all attributes.
• Maximum 20 GSIs per table.

​

3.5 Local Secondary Indexes (LSI)

​

3.6 DynamoDB Streams

• Event-driven architectures: New order inserted -> Stream triggers Lambda -> Lambda sends confirmation email.
• Cross-region replication: DynamoDB Global Tables use Streams under the hood.
• Materialized views: Stream changes to build denormalized views in another table or in Elasticsearch for full-text search.
• Audit trail: Stream all changes to S3 for compliance and forensics.

​

3.7 DAX (DynamoDB Accelerator)

​

3.8 Capacity Modes

​

3.9 Cost Optimization

• Avoid Scan operations. A Scan reads every item in the table and charges for every read capacity unit consumed. Always use Query or GetItem with a key condition.
• Use sparse indexes. A GSI only contains items that have the GSI’s key attributes. If only 5% of items have a premium_tier attribute, a GSI on premium_tier is 95% smaller than the base table — dramatically cheaper to read.
• Compress large items. DynamoDB charges per KB of read/write capacity consumed. Compressing a 10 KB JSON attribute to 2 KB saves 80% on throughput costs for that attribute.
• Use ProjectionExpression. Only retrieve the attributes you need, not the entire item. Reduces data transfer and read capacity consumption.
• Time-to-Live (TTL). Automatically delete expired items at no cost (DynamoDB handles deletion in the background without consuming write capacity).

​

Section 4: Redis Architecture

​

4.1 Data Structures Beyond Key-Value

• Strings: Basic key-value. But also atomic counters (INCR), bit operations, and values up to 512 MB.
• Hashes: A hash map within a single key. Perfect for objects: HSET user:123 name "Jane" email "jane@co.com". Get individual fields without deserializing the whole object.
• Lists: Doubly-linked lists. LPUSH / RPOP gives you a queue. LRANGE gives you the last N items. Used for activity feeds, recent items.
• Sets: Unordered unique collections. SADD, SINTER (intersection), SUNION. Used for tags, mutual friends, unique visitors.
• Sorted Sets: Sets with a score for each member. ZADD leaderboard 1500 "player1". ZRANGE leaderboard 0 9 REV gives the top 10. O(log N) insert and rank query. The data structure behind leaderboards, priority queues, and rate limiters.
• HyperLogLog: Probabilistic data structure for cardinality estimation. Count unique visitors with 0.81% standard error using only 12 KB of memory, regardless of the actual count. PFADD visitors "user1" "user2". PFCOUNT visitors returns the approximate unique count.
• Streams: An append-only log data structure (added in Redis 5.0). Think Kafka-lite. Supports consumer groups, acknowledgment, and message replay. Used for event sourcing, activity streams, and lightweight message queues.
• Bitmaps: Bit arrays. Set and get individual bits. SETBIT active:2024-04-09 user_id 1. Count bits with BITCOUNT. Used for tracking daily active users (1 bit per user, 1 million users = 125 KB per day).

​

4.2 Persistence

• appendfsync always: fsync after every write. Most durable, slowest.
• appendfsync everysec: fsync every second. Loses at most 1 second of data on crash. The recommended default.
• appendfsync no: Let the OS decide when to flush. Fastest, least durable.

​

4.3 Eviction Policies

​

4.4 Redis Cluster

• Client-side (smart clients): The client library (like redis-py-cluster or ioredis) knows the slot-to-node mapping and routes requests directly to the correct node. Lower latency (no proxy hop), but the client must handle cluster topology changes.
• Proxy-based (Twemproxy, Redis Cluster Proxy): A proxy sits between clients and the cluster, handling routing transparently. Simpler client code, but adds a network hop and a single point of failure.

​

4.5 Redis Sentinel

1. Multiple Sentinel instances (minimum 3 for quorum) monitor the master.
2. When a Sentinel detects the master is unreachable, it asks other Sentinels to confirm (to avoid false positives from network issues).
3. If a quorum of Sentinels agree the master is down, one Sentinel is elected to perform the failover.
4. The elected Sentinel promotes the best replica (most up-to-date data) to master and reconfigures the other replicas to follow the new master.
5. Clients using Sentinel-aware drivers are notified of the new master address.

​

4.6 Pub/Sub vs Streams

​

4.7 Lua Scripting

-- KEYS[1] = rate limit key, ARGV[1] = max requests, ARGV[2] = window in seconds
local current = redis.call('INCR', KEYS[1])
if current == 1 then
  redis.call('EXPIRE', KEYS[1], ARGV[2])
end
if current > tonumber(ARGV[1]) then
  return 0  -- rate limited
end
return 1  -- allowed

​

4.8 Common Patterns

​

4.9 Memory Optimization

• Key naming conventions: Use user:123:profile not the_profile_data_for_user_id_one_hundred_twenty_three. Short, consistent key names save significant memory at scale. At 100 million keys, saving 20 bytes per key name saves 2 GB.
• Ziplist encoding: Redis automatically uses a compact encoding (ziplist) for small Hashes, Lists, and Sorted Sets. A Hash with fewer than hash-max-ziplist-entries (default 128) entries and values smaller than hash-max-ziplist-value (default 64 bytes) is stored as a ziplist instead of a hash table — using 5-10x less memory. Keep small objects small to benefit from this.
• TTL strategies: Set TTLs aggressively. Every key without a TTL is a potential memory leak. Use SCAN periodically to find keys without TTLs that should have them.
• Compression: For large values (JSON blobs > 1 KB), compress with gzip or LZ4 at the application level before storing in Redis. Redis does not compress values internally.

​

Section 5: When to Choose What

​

Decision Matrix

​

Concrete Scenarios

​

Polyglot Persistence

• PostgreSQL for the core transactional data (users, orders, billing).
• Redis for caching, sessions, rate limiting, and real-time features.
• Elasticsearch (or MongoDB with Atlas Search) for full-text search.
• DynamoDB for high-throughput, low-latency specific access patterns (API keys, feature flags).
• S3 for blob storage (images, files, backups).

​

Migration Strategies

1. Dual-write phase: Write to both old and new databases. Read from old. Verify data consistency between them.
2. Shadow read phase: Read from both databases. Return old database results to users. Compare results in the background and log discrepancies.
3. Cutover: Switch reads to the new database. Keep the old database running as a fallback.
4. Cleanup: After a soak period (weeks, not days), decommission the old database.

​

Section 6: Database Scaling Decision Tree

​

The Escalation Ladder

• Add missing indexes for common query patterns.
• Rewrite N+1 query patterns into batch queries or JOINs.
• Add ANALYZE / update statistics so the query planner makes better choices.
• For MongoDB: ensure compound indexes match your query predicates and sort order.
• For DynamoDB: verify GSIs cover your access patterns (a missing GSI means a Scan instead of a Query — orders of magnitude slower).

• PostgreSQL: streaming replication to one or more replicas. Route read queries to replicas, writes to primary. Watch for replication lag on time-sensitive reads.
• MongoDB: read preference secondaryPreferred or secondary. Same caveat about eventual consistency.
• DynamoDB: eventually consistent reads (default) are already distributed. Use DAX for caching hot reads.

• Cache database query results with a TTL matching your staleness tolerance.
• Use cache-aside pattern: check cache first, fall through to database on miss, populate cache on read.
• For write-heavy data that is read often: consider write-through caching.

• PostgreSQL: range partitioning by date (most common), list partitioning by tenant/region, hash partitioning for even distribution.
• MongoDB: already uses sharding for this (see Step 5). Internal WiredTiger data partitioning is automatic.
• DynamoDB: already partitioned internally by partition key. The analog here is redesigning your partition key for better distribution.

• PostgreSQL: application-level sharding (route queries based on a shard key in your app code), or use Citus for distributed PostgreSQL.
• MongoDB: native sharding with a shard key. The shard key choice is irreversible and determines everything — choose carefully (see Section 2.5).
• DynamoDB: sharding is automatic and invisible (DynamoDB handles partition management). Your role is to choose a partition key that distributes evenly.

• Your access patterns are fundamentally mismatched with your database’s strengths (e.g., graph traversals in a relational database, ad-hoc analytics in DynamoDB).
• You have exhausted all tuning, replication, caching, and partitioning options.
• The cost of maintaining the workarounds exceeds the cost of migration.

• Relational database struggling with time-series write throughput -> TimescaleDB or InfluxDB.
• MongoDB struggling with complex cross-collection JOINs -> PostgreSQL.
• PostgreSQL struggling with sub-millisecond key-value lookups at massive scale -> DynamoDB or Redis.
• Any SQL database struggling with graph traversals (friends-of-friends queries) -> Neo4j or Neptune.

​

Quick Reference: Scaling Decision Tree

Your database is slow. What is the bottleneck?
|
+-- Read latency (individual queries are slow)
|   |
|   +-- Missing index? --> Add index (Step 1)
|   +-- Stale statistics? --> ANALYZE (Step 1)
|   +-- Bad query plan? --> Rewrite query (Step 1)
|   +-- Table too large for efficient scans? --> Partition (Step 4)
|
+-- Read throughput (too many concurrent reads)
|   |
|   +-- Same data read repeatedly? --> Add cache (Step 3)
|   +-- Diverse reads, high volume? --> Add read replicas (Step 2)
|   +-- Single hot key/item? --> Cache that specific key (Step 3)
|
+-- Write throughput (too many concurrent writes)
|   |
|   +-- Single hot table/partition? --> Partition or reshard (Step 4/5)
|   +-- Overall write volume exceeds one server? --> Shard (Step 5)
|   +-- Write pattern mismatch? --> Different DB (Step 6)
|
+-- Storage (data exceeds single server capacity)
|   |
|   +-- Cold data can be archived? --> Partition + drop old partitions (Step 4)
|   +-- All data is hot? --> Shard (Step 5)
|
+-- Connection exhaustion
    |
    +-- Too many clients? --> Connection pooler (PgBouncer, RDS Proxy)
    +-- Serverless/Lambda? --> RDS Proxy or switch to DynamoDB (Step 6)

​

Section 7: Connection Pool Sizing Formula

​

Little’s Law Applied to Database Connections

L = lambda * W

• L = average number of items in the system (connections in use)
• lambda = arrival rate (queries per second)
• W = average time each item spends in the system (average query duration)

pool_size = throughput * average_query_latency

minimum_pool_size = (queries_per_second) * (average_query_duration_in_seconds)

​

Concrete Examples

• Your app handles 500 requests/second.
• Each request makes 1 database query averaging 10 ms (0.01 seconds).
• Throughput (queries/second) = 500.
• pool_size = 500 * 0.01 = 5 connections.

• 2,000 requests/second, each making 2 database queries.
• Average query latency: 25 ms (0.025 seconds).
• Throughput = 4,000 queries/second.
• pool_size = 4,000 * 0.025 = 100 connections.

• Same 2,000 requests/second, 2 queries each.
• Average query latency: 5 ms, but p99 latency: 200 ms.
• Using average: pool_size = 4,000 * 0.005 = 20 connections.
• But during p99 spikes: burst_pool_size = 4,000 * 0.200 = 800 connections.

​

The Practical Formula

recommended_pool_size = (queries_per_second * average_latency_seconds) * safety_multiplier

where safety_multiplier = 1.5 to 3.0 depending on:
  - Traffic burstiness (steady = 1.5, bursty = 2.5-3.0)
  - Query latency variance (low variance = 1.5, high p99/avg ratio = 2.5+)
  - Tolerance for queuing (zero tolerance = higher multiplier)

​

The PostgreSQL-Specific Constraint

max_useful_connections = (2 * cpu_cores) + number_of_disks

​

Section 8: Common Production Issues by Database

​

PostgreSQL: Long-Running Transactions Blocking VACUUM

1. An idle transaction holds a snapshot for 6 hours.
2. During those 6 hours, autovacuum runs but cannot remove any dead tuples from the last 6 hours of updates.
3. Dead tuples accumulate. Table bloat grows. Sequential scans slow down because they must skip millions of dead tuples.
4. Index bloat grows because indexes still point to dead tuples.
5. In extreme cases: transaction ID wraparound approaches. PostgreSQL enters “safety shutdown” mode and refuses all writes until a manual VACUUM FREEZE completes.

-- Find long-running transactions
SELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 10;

-- Check dead tuple accumulation
SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

• Set idle_in_transaction_session_timeout = '5min' to automatically kill idle transactions.
• Configure statement_timeout as a safety net for runaway queries.
• Monitor pg_stat_activity for transactions older than your acceptable threshold.
• In PgBouncer: use transaction pooling mode, which returns connections to the pool after each transaction (making long-lived idle transactions impossible from pooled clients).

​

MongoDB: Unbounded Array Growth

1. Documents start small (a few KB) and queries are fast.
2. Over weeks or months, arrays grow to thousands of elements. Documents bloat to several MB.
3. Every update rewrites the entire document. Appending one element to a 5 MB document means writing 5 MB. WiredTiger’s document-level concurrency control means all updates to that document are serialized.
4. If a document hits the 16 MB BSON limit, all further updates fail. Your application starts throwing errors.
5. Even before hitting the limit, large documents cause WiredTiger cache pressure. The cache stores uncompressed documents — a 5 MB document on disk might be 20 MB in the WiredTiger cache.

// Find oversized documents in a collection
db.users.aggregate([
  { $project: { docSize: { $bsonSize: "$$ROOT" }, _id: 1 } },
  { $sort: { docSize: -1 } },
  { $limit: 10 }
]);

// Find documents with large arrays
db.users.aggregate([
  { $project: { arraySize: { $size: "$activityLog" }, _id: 1 } },
  { $sort: { arraySize: -1 } },
  { $limit: 10 }
]);

• Use the Bucket Pattern: group entries into time-based buckets (one document per day/hour) instead of one growing array per entity.
• Use the Subset Pattern: keep only the last N items embedded; archive older items to a separate collection.
• Use the Outlier Pattern: flag documents that exceed a threshold and store overflow data externally.
• Set a hard application-level limit: { $push: { activityLog: { $each: [newEntry], $slice: -100 } } } keeps only the last 100 entries.

​

DynamoDB: Hot Partitions

1. DynamoDB distributes provisioned throughput evenly across partitions. With 10,000 WCU and 10 partitions, each partition gets ~1,000 WCU.
2. A flash sale drives all writes to PK=PRODUCT#hot-item. That single partition receives 5,000 WCU of traffic.
3. The partition throttles. ProvisionedThroughputExceededException errors spike. The application retries with exponential backoff, increasing latency.
4. Adaptive capacity (DynamoDB’s automatic rebalancing) kicks in, but it takes minutes — an eternity during a flash sale.
5. If you are on provisioned capacity, increasing the table’s WCU does NOT help because the new capacity is still distributed evenly. You are paying for 50,000 WCU but only one partition is hot.

• Enable DynamoDB Contributor Insights — it shows the most accessed partition keys over time.
• CloudWatch: compare ConsumedWriteCapacityUnits (table-level) with ThrottledRequests. If throttling occurs while consumed capacity is well below provisioned, you have a hot partition.
• Check your partition key’s cardinality distribution. A partition key with 90% of items under one value is a time bomb.

• Design partition keys for high cardinality from the start (user IDs, order IDs, UUIDs — not statuses or dates).
• Use write sharding: append a random suffix (PK=PRODUCT#hot-item#7) to distribute across N partitions. Reads require querying all N suffixes and merging.
• For genuinely hot items (a single viral product page), put DAX in front for reads and use SQS to buffer and smooth writes.
• Switch to on-demand capacity mode for unpredictable or spiky workloads — it handles partition-level throttling more gracefully.

​

Redis: Memory Fragmentation

1. Your application writes and deletes keys of varying sizes over time. Small keys, large keys, keys that grow and shrink.
2. The memory allocator (jemalloc by default) allocates memory in fixed-size classes. A 50-byte value gets a 64-byte allocation. A 70-byte value gets a 128-byte allocation. The wasted space in each allocation is internal fragmentation.
3. Over time, keys are deleted and new keys of different sizes are created. The freed memory may not match the sizes needed for new allocations. Memory becomes a Swiss cheese of small free gaps that cannot be coalesced. This is external fragmentation.
4. Redis reports used_memory = 8 GB but the OS reports used_memory_rss = 14 GB. You are paying for 14 GB of RAM but only 8 GB holds useful data.
5. If maxmemory is set to 10 GB, Redis thinks it has 2 GB of headroom. But the OS is already at 14 GB. If the server only has 16 GB of RAM, the OOM killer may terminate Redis.

> INFO memory
used_memory:8589934592
used_memory_rss:14495514624
mem_fragmentation_ratio:1.69
mem_allocator:jemalloc-5.2.1

# Fragmentation ratio > 1.5 is problematic
# Fragmentation ratio < 1.0 means Redis is using swap (even worse)

• Redis 4.0+ active defragmentation: Enable with activedefrag yes. Redis reorganizes memory in the background, moving allocations to reduce fragmentation. Configure active-defrag-threshold-lower 10 (start defragmentation when fragmentation exceeds 10%) and active-defrag-cycle-min 5 (use at least 5% of CPU for defragmentation).
• Avoid frequent large-to-small value changes. If a key alternates between a 10 KB value and a 100-byte value, the allocator wastes space on the larger allocation class.
• Prefer fixed-size data patterns. Hashes with consistent field counts, Sorted Sets with consistent element sizes, and Strings with consistent value lengths fragment less.
• Schedule periodic restarts for Redis instances used as caches (not data stores). A restart loads the RDB snapshot into fresh, defragmented memory. This is a blunt instrument but effective.
• Monitor mem_fragmentation_ratio in your alerting. Alert at 1.5, page at 2.0.

​

Quick Reference: Production Issue Checklist

​

Interview Questions

​

Interview Deep-Dive Questions

​

How would you debug a PostgreSQL query that was fast yesterday but is slow today, with no schema or code changes?

• Run ANALYZE on the involved tables and re-execute. If it fixes the problem, the root cause was stale statistics. Set more aggressive autovacuum_analyze_scale_factor on these tables.

• Check pg_stat_user_tables for n_dead_tup. If it is high, VACUUM has not kept up.

​

Follow-up: What is correlation in PostgreSQL statistics and how can it cause a plan change?

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'your_table';

​

Follow-up: How would you set up proactive monitoring so this kind of regression does not page you at 3 AM?

1. Query performance baselines with pg_stat_statements. Track mean_exec_time and calls for your top 50 queries. Alert when any query’s mean execution time exceeds 3x its rolling 7-day average. This catches plan regressions within minutes.
2. auto_explain extension. Configure auto_explain.log_min_duration = '1s' to automatically log the full EXPLAIN plan for any query exceeding 1 second. When a regression happens, you already have the bad plan in the logs — no need to reproduce it.
3. Statistics freshness monitoring. Track last_autoanalyze in pg_stat_user_tables. Alert if any high-traffic table has not been analyzed in over 24 hours, or if n_dead_tup / n_live_tup exceeds 10%. This catches the root cause (stale stats, vacuum falling behind) before it causes a plan regression.

​

Going Deeper: You have auto_explain enabled and you can see that the planner’s row estimate was off by 1000x. Statistics are fresh. What else could cause such a massive estimation error?

• Correlated columns. PostgreSQL assumes column values are independent. If you query WHERE country = 'Japan' AND language = 'Japanese', PostgreSQL multiplies the selectivity of each predicate independently. It might estimate 2% of rows match country = 'Japan' and 1% match language = 'Japanese', giving 0.02% combined. But in reality, 95% of Japan rows have Japanese — the actual selectivity is close to 2%, not 0.02%. That is a 100x error. PostgreSQL 14+ has extended statistics (CREATE STATISTICS ... (dependencies, ndistinct, mcv) ON country, language FROM users) that model multi-column correlations.
• Parameterized queries with generic plans. When using prepared statements, PostgreSQL starts with custom plans (per-parameter) but after 5 executions switches to a generic plan (parameter-agnostic). If the generic plan is optimal for typical values but terrible for an outlier value (e.g., status = 'deleted' matches 0.01% of rows but the generic plan is optimized for status = 'active' which matches 90%), you get a bad plan for the outlier. Setting plan_cache_mode = force_custom_plan forces per-parameter planning at the cost of planning overhead.
• Statistics target too low. The default default_statistics_target is 100, meaning PostgreSQL samples 300 * 100 = 30,000 rows for histogram construction. For a table with 500M rows and a highly skewed distribution, 30,000 samples may miss important value frequencies. Increase the target: ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000.

​

Walk me through how you would design a DynamoDB table for a multi-tenant SaaS platform where each tenant can have vastly different data volumes.

• Use on-demand mode so hot tenants pay per request and do not affect cold tenants’ throughput.
• For enterprise tenants with strict SLAs, provision a separate table (table-per-tenant for your top 5% tenants). This gives hard capacity isolation at the cost of operational complexity.
• Use DynamoDB resource-based policies and AWS Organizations for billing isolation.

​

Follow-up: A large tenant does a bulk import of 10 million records. How do you handle this without impacting other tenants?

1. Use BatchWriteItem with rate limiting. Do not just fire-hose writes. Implement client-side rate limiting (e.g., 500 WCU/sec) with exponential backoff on UnprocessedItems. This keeps writes below the partition-level throughput ceiling.
2. Spread writes across shards. If the tenant has 16 shards, distribute bulk writes evenly across all 16 partition keys. This gives 16x the per-partition throughput ceiling.
3. Temporarily scale up. If using provisioned mode, increase WCU before the import and scale back down after. If using on-demand, DynamoDB auto-scales but has a warm-up period — if the table has been seeing 1,000 WCU and you suddenly hit 50,000, you will be throttled until adaptive scaling kicks in (minutes). Pre-warm by gradually ramping up write traffic.
4. Consider an offline path. For truly massive imports, write to S3 and use the DynamoDB Import from S3 feature (launched 2023). It provisions temporary capacity separate from the online table, avoiding any impact on live traffic. The data appears in the table once the import completes.
5. If isolation is critical, import into a staging table first. Validate the data, then use DynamoDB Streams to replicate the items into the production table at a controlled rate.

​

Follow-up: How would you implement per-tenant rate limiting at the DynamoDB level?

​

Your company runs a critical MongoDB replica set. The primary just went down. Walk me through exactly what happens next and what could go wrong.

• No majority. If you have a 3-node set and 2 nodes go down, the remaining node cannot form a majority and will not become primary. The replica set is read-only until a majority of nodes recover. This is why the minimum production deployment is 3 nodes across at least 2 data centers (or 2 nodes + 1 arbiter).
• Writes lost during failover. If the old primary accepted writes with w: 1 (the default) and went down before replicating those writes to a secondary, those writes are gone. When the old primary comes back, it has writes that the new primary does not have — these become rollback writes and are placed in a rollback directory. This is why w: "majority" matters for critical data.
• Driver connection handling. Client drivers need to discover the new primary. Smart drivers (using the replica set connection string) handle this automatically — they detect the topology change and redirect writes. But there is a brief window where the application throws connection errors. If your application does not retry writes, user requests fail during failover. If it does retry writes, they must be idempotent or you risk duplicate operations.
• Election ties and priority. If two secondaries have the same optime (are equally up-to-date), priority settings determine who wins. If priority is misconfigured, a secondary in a remote data center might become primary, increasing latency for all write operations. Set priority: 0 on nodes that should never become primary (analytics replicas, cross-region replicas used only for reads).

​

Follow-up: After failover, the old primary comes back online with writes that the new primary does not have. What happens to those writes?

​

Follow-up: How would you design your application to handle the 10-15 second failover window gracefully?

​

Explain the Redis single-threaded model. How can it be fast when it only uses one core?

1. All data is in memory. The bottleneck for most databases is disk I/O. Redis eliminates this entirely. An in-memory hash lookup takes ~100 nanoseconds. At that speed, a single core can process hundreds of thousands of operations per second because the CPU never waits for anything.
2. No context switching overhead. A multi-threaded design with locks would spend more time on lock acquisition, cache invalidation, and context switching than on actual data operations. For sub-microsecond operations, the coordination cost of multithreading exceeds the operation cost itself.
3. No lock contention. Since one thread processes all commands sequentially, there is no need for mutexes, spinlocks, or atomic operations on data structures. Every command sees a consistent state. This is what makes Lua scripts and MULTI/EXEC transactions atomic for free — nothing else can interleave.
4. Pipeline-friendly. Clients can send multiple commands without waiting for individual responses. The event loop processes them in sequence and sends all responses back in one batch. Pipelining amortizes the network round-trip cost, and the single thread chews through pipelined commands at memory speed.

​

Follow-up: If Redis is single-threaded, how does it handle persistence (RDB snapshots, AOF rewrites) without blocking clients?

​

Follow-up: A developer proposes running multiple Redis instances on the same server to utilize all CPU cores. What are the trade-offs?

• Uses all cores. An 8-core server runs 8 Redis instances, each handling its own keyspace.
• Each instance has its own event loop, so a slow command on one instance does not affect others.

• Memory overhead. Each instance has its own memory space. You cannot share data between instances without network calls. If you have 64 GB of RAM and run 8 instances, each gets ~8 GB (minus OS overhead). No benefit of shared caching.
• Operational complexity. 8 instances means 8 ports, 8 configs, 8 monitoring endpoints, 8 persistence schedules. Your deployment and monitoring tooling must handle this.
• fork() contention. If multiple instances trigger RDB snapshots simultaneously, the server faces multiple fork() calls, potentially doubling memory usage across all instances at once. Stagger snapshot schedules.
• Application routing. The application must know which instance holds which data. This is essentially manual sharding, with all the routing complexity that entails.

​

You are migrating a PostgreSQL table with 2 billion rows from one database to another with zero downtime. How?

• Set up logical replication from the source table using PostgreSQL’s logical replication or a tool like pglogical. Alternatively, use pg_dump --no-synchronized-snapshots in parallel segments to speed up the initial copy.
• For a 2B row table, the initial copy might take hours or days depending on row size and network bandwidth. The key is to start the CDC capture before the bulk copy begins, so that any writes during the copy are recorded.

• While the bulk copy runs, the source continues receiving writes. Logical replication automatically streams these changes (inserts, updates, deletes) to the target.
• After the initial copy completes, the replication stream catches up. Monitor the replication lag until it drops to sub-second.

• Deploy application code that reads from both databases for a percentage of traffic. Compare results. Log discrepancies. This catches issues like missing rows, data type mismatches, or timezone handling differences.
• Run consistency checks: SELECT COUNT(*), checksum comparisons on key columns, and spot-check critical business queries.

• When confident in data consistency, perform the cutover in a maintenance window as small as possible:
  1. Stop writes to the source table (or set it to read-only).
  2. Wait for the last CDC events to replicate (seconds).
  3. Verify final consistency.
  4. Switch the application’s write connection to the new database.
  5. Switch reads to the new database.
• Total write downtime: seconds, not minutes. If you need truly zero write downtime, use a dual-write approach: the application writes to both databases simultaneously during cutover, and you switch reads once you verify consistency.

• Keep the old database running for at least 2 weeks. If anything goes wrong, the rollback is: switch connections back to the old database. If you used dual-write during cutover, both databases have the latest data and rollback is instant.

• Sequences. If the new table uses a sequence for primary keys, ensure the sequence value is set higher than the maximum migrated ID. Otherwise, new inserts collide with migrated rows.
• Triggers and constraints. Logical replication replays raw DML. If the target table has triggers that fire on INSERT (audit logging, denormalization), they will fire for every replicated row — potentially billions of trigger executions. Disable non-essential triggers during the bulk copy phase.
• Large objects and TOAST. Rows with large text or JSONB columns are stored in TOAST tables. Logical replication handles this, but it increases replication bandwidth significantly. Monitor network throughput.
• Schema differences. If the target schema has additional NOT NULL columns or stricter constraints, replicated rows may violate them. Validate schema compatibility before starting.

​

Follow-up: How would you handle the migration if you also need to transform the data during migration — for example, splitting one table into two?

​

Going Deeper: The dual-write approach has a well-known consistency problem. What is it and how do you mitigate it?

1. Write to the source of truth first, then asynchronously replicate. Make Database A the authoritative source. After writing to A, publish an event (to a transactional outbox table in A, then to Kafka). A consumer applies the write to B. If B fails, the event stays in the queue and retries. This is the transactional outbox pattern — it guarantees at-least-once delivery to B at the cost of temporary inconsistency.
2. Reconciliation job. Run a periodic job that compares the two databases and fixes discrepancies. This is your safety net — the dual-write handles the happy path, the reconciliation catches edge cases.
3. Accept temporary inconsistency. If the application can tolerate a few seconds of divergence (and for most applications it can), the combination of “write to both + reconciliation” is sufficient. Perfect consistency across two independent databases requires distributed transactions, which most teams rightly avoid.

​

Your team uses Redis for session storage. An engineer proposes switching to DynamoDB to reduce operational burden. Evaluate this proposal.

• Operational simplicity. DynamoDB is fully managed — no patching, no failover configuration, no memory sizing, no eviction policy tuning. Redis (even managed ElastiCache) requires capacity planning, persistence configuration, and monitoring for fragmentation and memory pressure.
• Durability by default. DynamoDB writes are durable to disk. If a node fails, no data is lost. Redis, even with AOF, can lose the last second of data. For session data in a regulated environment (healthcare, finance), durability matters.
• Scaling is automatic. DynamoDB on-demand mode scales to any traffic level without intervention. Redis requires manual scaling (resizing the instance or adding cluster nodes).
• TTL built-in. DynamoDB’s TTL feature automatically deletes expired sessions at zero cost. In Redis, expired keys are cleaned up lazily (on access) or by the active expiry cycle, which can lag under memory pressure.

• Latency. Redis serves sessions in 0.1-0.5 ms. DynamoDB serves them in 3-10 ms. That is a 10-50x latency increase on every authenticated request. If your API’s p99 target is 50 ms and session lookup adds 10 ms, you have used 20% of your latency budget on one operation.
• Cost at scale. At 100,000 session reads per second (a large web app), Redis on a single r6g.xlarge costs roughly $300/month. DynamoDB on-demand at 100K RCU/sec costs roughly$5,000/month. Provisioned mode reduces this but requires capacity planning — negating the “less operational burden” benefit.
• Data structure richness. If sessions contain complex data (nested objects, arrays, counters that increment), Redis Hashes with HINCRBY and HSET are more expressive than DynamoDB’s update expressions. If sessions are simple key-value blobs, this does not matter.

​

Follow-up: The engineer says “latency does not matter because we will put DAX in front of DynamoDB and get the same latency as Redis.” Is this correct?

1. Session writes still hit DynamoDB directly. DAX is a read-through/write-through cache. Writes go to DynamoDB (3-10 ms) and the DAX cache is updated asynchronously. If a user logs in (write) and immediately makes a request (read), the read might hit DAX before the write has propagated — returning a stale “no session” result. This is a consistency window of typically <10 ms but it can cause authentication flicker.
2. DAX adds another managed service. You have DynamoDB + DAX. The “reduce operational burden” argument weakens because now you are operating a cache in front of a database… which is what you were doing with Redis in the first place, just with different branding.
3. DAX cost. DAX runs on dedicated nodes (dax.r5.large ~$200/month per node, minimum 3 nodes for production). Adding$600+/month for DAX on top of DynamoDB costs approaches or exceeds managed Redis pricing.

​

What are the real-world differences between PostgreSQL’s SERIALIZABLE isolation and DynamoDB’s transactions?

• Provides true serializability — the gold standard of isolation. The result is equivalent to some serial ordering of all concurrent transactions, even though they actually executed concurrently.
• Implemented via SSI (Serializable Snapshot Isolation). PostgreSQL tracks read and write dependencies between concurrent transactions. If it detects a dependency cycle that could produce a non-serializable result, it aborts one transaction with a serialization failure. The application must retry.
• Scope: any number of tables, any number of rows, any query pattern. You can read 50 tables and write 30 in a single serializable transaction. The database guarantees correctness.
• Cost: higher abort rates under contention. The more concurrent transactions touch overlapping data, the more serialization failures you get. At extreme contention, throughput collapses.

• Provides atomicity and isolation for a batch of up to 100 items across one or more tables. Either all operations succeed or none do.
• Isolation is “serializable” at the item level — concurrent transactions on the same items are serialized. But there is no dependency tracking across arbitrary queries. You specify exactly which items participate in the transaction upfront by their primary keys.
• No read-then-write logic inside the transaction itself. You can do conditional writes (ConditionExpression) to check a value before writing, but you cannot do a query, make a decision, and then write — that must be done in application code across two API calls.
• Cost: transactions consume 2x the normal WCU/RCU. A transaction that writes 5 items costs 10 WCU. DynamoDB also has a 25-item limit per TransactWriteItems call (though you can include up to 100 items across reads and writes in a mixed transaction).

​

Follow-up: When would you recommend DynamoDB transactions over PostgreSQL SERIALIZABLE?

​

A Redis Cluster node goes down and some commands start returning MOVED and ASK errors. Explain what is happening and how the client should handle it.

• The client’s slot-to-node mapping is stale (a node went down and slots were reassigned).
• The client has not yet discovered the cluster topology.

1. Send ASKING to the target node (this tells the target to accept a command for a slot it does not yet fully own).
2. Re-send the original command to the target node.
3. Do NOT update the slot map. Unlike MOVED, ASK is temporary. The slot is still being migrated. Future commands for other keys in this slot should still go to the old node (if those keys have not migrated yet).

​

Follow-up: What happens to in-flight commands when a Redis Cluster node crashes mid-pipeline?

• The first 50 commands were executed. If they were writes, those writes are in the node’s memory and AOF (if enabled). Whether they survive depends on the persistence configuration and whether the replica received them before the crash.
• The remaining 50 commands get a connection error. The client receives errors for these commands.
• The client cannot know exactly which commands succeeded and which did not without checking the data. This is the pipeline atomicity problem — pipelines are NOT transactions. Each command in a pipeline is independent.

​

Your PostgreSQL database is approaching transaction ID wraparound. Explain the urgency and your remediation plan.

-- Check how close each database is to wraparound
SELECT datname, age(datfrozenxid) AS xid_age,
       2000000000 - age(datfrozenxid) AS remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Check per-table freeze status
SELECT schemaname, relname, age(relfrozenxid) AS xid_age
FROM pg_stat_user_tables
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

• age(datfrozenxid) > 200 million: investigate why autovacuum is not freezing. Tune it.
• age(datfrozenxid) > 1 billion: urgent. Autovacuum should be running aggressively. Check for blockers.
• age(datfrozenxid) > 2 billion - 1 million: critical. PostgreSQL will start emitting warnings in the logs and will eventually enter read-only “safety mode.” Immediate intervention required.

1. Check pg_stat_activity for long-running transactions and terminate them. Any open transaction holds back the freeze horizon.
2. Check if autovacuum is running on the offending tables. If not, start a manual VACUUM FREEZE on the table with the oldest relfrozenxid.
3. Increase autovacuum_freeze_max_age awareness — this is the threshold where autovacuum becomes aggressive about freezing. Default is 200 million. If your tables are well past this, autovacuum should already be trying, but something is blocking it.

1. Run VACUUM FREEZE manually on the worst tables. On a huge table, this can take hours. Use VERBOSE to monitor progress.
2. Increase autovacuum_max_workers and reduce autovacuum_vacuum_cost_delay to let autovacuum work faster across all tables.
3. If the database is in read-only mode: the only option is running VACUUM FREEZE. No writes are possible until the freeze completes. This is why prevention is critical.

• Monitor age(datfrozenxid) as a Tier 1 metric. Alert at 500 million, page at 1 billion.
• Tune autovacuum aggressively on high-write tables (lower autovacuum_vacuum_scale_factor, higher autovacuum_vacuum_cost_limit).
• Set idle_in_transaction_session_timeout to prevent long-running transactions from blocking VACUUM.
• For very large tables (hundreds of millions of rows), partition them. VACUUM FREEZE runs per-partition, making it more manageable.

​

Follow-up: Why can long-running transactions prevent VACUUM FREEZE from doing its job?

​

Compare how PostgreSQL, MongoDB, and DynamoDB handle a scenario where you need to count the total number of items matching a condition across your entire dataset.

1. Query with Select: 'COUNT' — this still reads every matching item (consuming read capacity for each item), it just does not return the data. On millions of items, this is expensive and slow.
2. Scan with a FilterExpression — even worse. Reads every item in the table, applies the filter, and counts matches. For a billion-item table, this costs thousands of dollars in read capacity.

​

Follow-up: What if you need real-time counts displayed on a dashboard — say, “orders shipped today” updating every second?

​

Going Deeper: The counter approach can drift if the application crashes between writing the order and incrementing the counter. How do you handle this?

1. Transactional outbox. In PostgreSQL, update both the orders table and the counter table in the same transaction. They are either both committed or both rolled back. Atomic, no drift. This is the simplest and most correct approach when both writes are in the same database.
2. Event-driven counter. Do not increment the counter in the application. Instead, use CDC (DynamoDB Streams, PostgreSQL logical replication, MongoDB Change Streams) to capture the order insert event and have a consumer increment the counter. The consumer processes events with at-least-once semantics, so make the counter update idempotent (e.g., track the last processed event ID and skip duplicates).
3. Periodic reconciliation. Accept that the counter might drift and run a reconciliation job hourly that does the full COUNT(*) scan and corrects the counter. For a dashboard that says “~15,234 orders shipped today,” being off by a handful is acceptable. For a financial report, it is not.

​

Advanced Interview Scenarios

​

Your application’s p99 latency spikes every day at 2 AM. Nothing in the application code runs at that time. The database is PostgreSQL. What is happening?

• Autovacuum anti-wraparound. PostgreSQL has a hard-coded escalation: when a table’s relfrozenxid age exceeds autovacuum_freeze_max_age (default 200 million), autovacuum ignores its normal cost-based throttling and runs at full speed to prevent transaction ID wraparound. This “emergency autovacuum” consumes massive I/O. On a busy table, this can saturate the disk for 30-60 minutes. Check pg_stat_user_tables for last_autovacuum timestamps clustering around 2 AM, and look at pg_stat_progress_vacuum during the spike.
• Checkpoint storms. PostgreSQL’s checkpointer writes all dirty pages from shared buffers to disk periodically (controlled by checkpoint_timeout, default 5 minutes, and max_wal_size). If a large batch of dirty pages accumulated during daytime activity and the checkpoint fires during a low-traffic window, the sudden burst of disk writes spikes latency for concurrent queries. Check pg_stat_bgwriter for checkpoints_req (forced checkpoints, which are the disruptive kind) vs checkpoints_timed. Tune checkpoint_completion_target to 0.9 to spread checkpoint writes over a longer window.
• RDS or cloud-provider maintenance windows. On AWS RDS, minor engine patches, OS patching, and multi-AZ failover tests often run in the default maintenance window (which many teams never change from the 2-4 AM default). A multi-AZ failover causes a 15-30 second connection drop. Check the AWS RDS Events console.

​

Follow-up: How would you distinguish between a checkpoint storm and an autovacuum storm using only pg_stat views?

​

Follow-up: The team wants to “just disable autovacuum on that table to stop the spikes.” Why is this dangerous?

​

You inherit a MongoDB cluster that is sharded on { created_at: 1 }. The team says queries are slow. What is your diagnosis before you look at a single query?

1. Write hot shard. One shard absorbs 100% of write traffic. It becomes the bottleneck. Adding more shards does not help — the writes still go to the newest chunk range. The moveChunk balancer constantly tries to migrate chunks from the hot shard to cold shards, but it cannot keep up with the ingest rate. You see balancerStatus showing continuous chunk migrations and moveChunk operations consuming I/O on the hot shard.
2. Scatter-gather reads. Any query that filters by something other than created_at (which is almost every query — “find me user X’s documents,” “find documents with status Y”) must scatter to all shards because the shard key tells MongoDB nothing about where user X’s data lives. These scatter-gather queries get linearly slower as you add shards. The team adds shards for capacity but queries get slower — the opposite of what they expected.
3. Jumbo chunks. If many documents are created within the same second (high-throughput systems), they share the same created_at value and land in the same chunk. That chunk cannot be split because all its documents have the same shard key value. It becomes a jumbo chunk that cannot be migrated, further imbalancing the cluster.

​

Follow-up: How do you actually migrate to a new shard key on a live MongoDB cluster without downtime?

​

Follow-up: The team pushes back — “we chose created_at because all our queries filter by time range.” Is their reasoning valid?

​

A Redis Lua script that implements a distributed lock is intermittently failing to release the lock, causing downstream services to hang for 30 seconds (the lock TTL). How do you debug this?

if redis.call("GET", KEYS[1]) == ARGV[1] then
  return redis.call("DEL", KEYS[1])
else
  return 0
end

​

Follow-up: The team says “just use a PostgreSQL advisory lock instead of Redis — it is simpler and has real transactions.” When is this the right call?

​

Follow-up: How would you monitor distributed lock health in production?

1. Lock acquisition latency (p50, p99). If this starts climbing, it means contention is increasing or the Redis cluster is under pressure.
2. Lock hold duration vs TTL ratio. If the median hold time is 8 seconds and TTL is 10 seconds, you are dangerously close to TTL expiry during normal operations. Alert when the p99 hold duration exceeds 50% of TTL.
3. Lock release failure rate. The percentage of release operations where the ownership token does not match (the lock expired and was re-acquired). This is the leading indicator of correctness bugs. Any non-zero rate should trigger investigation.
4. Lock contention rate. How often an acquisition attempt is blocked (lock already held). High contention means either the critical section is too slow or too many processes are competing. Consider sharding the lock (per-user instead of global) or redesigning the critical section to be shorter.

​

Your DynamoDB bill tripled last month. No one deployed new features. Find the root cause.

​

Follow-up: How would you set up cost alerting to catch this before the monthly bill arrives?

1. AWS Budgets with a DynamoDB-specific budget. Set a monthly budget for DynamoDB and configure alerts at 50%, 80%, and 100% thresholds with SNS notifications to Slack or PagerDuty. This catches overall cost growth.
2. CloudWatch per-table alarms. Create alarms on ConsumedWriteCapacityUnits and ConsumedReadCapacityUnits at the table level and GSI level. Alert when any metric exceeds 120% of the 7-day rolling average. This catches per-table anomalies before they aggregate into a bill surprise.
3. AWS Cost Explorer with daily granularity. Set up a daily report filtered to DynamoDB. Enable “group by resource” to see cost per table. The bill tripling happened over 30 days — with daily cost monitoring, you would have caught it on day 2 when the daily cost jumped from $140 to$420.

​

Follow-up: The team is on provisioned capacity and is considering switching to on-demand to “never think about capacity again.” Walk them through the trade-offs.

​

You are designing a system that needs to keep PostgreSQL, Redis, and Elasticsearch in sync. When a user updates their profile, the relational data, the cache, and the search index all need to reflect the change. How do you ensure consistency?

​

Follow-up: The outbox table is growing fast because events are being produced faster than the worker can process them. How do you scale the worker?

1. Partition the outbox by aggregate ID. Run multiple worker instances, each responsible for a range of aggregate IDs (consistent hashing). This parallelizes processing while guaranteeing ordering within each aggregate (a user’s profile updates are processed in order by one worker). This is essentially the Kafka consumer group pattern implemented at the outbox level.
2. Batch processing. Instead of processing one event at a time, the worker reads 100 events, batches the Redis updates (PIPELINE), batches the Elasticsearch updates (_bulk API), and marks all 100 as processed in one UPDATE statement. Batching can improve throughput 10-50x by amortizing network round-trips.
3. Switch to CDC. If the outbox polling pattern hits its scaling limit, migrate to Debezium + Kafka. Kafka’s partitioned consumer groups provide natural parallelism, backpressure handling, and replay capability. This is the “graduated” version of the outbox pattern.

​

Follow-up: How do you detect when PostgreSQL, Redis, and Elasticsearch have drifted out of sync?

1. Sample-based comparison. Query a random sample of 1,000 user IDs from PostgreSQL. For each, fetch the Redis cached version and the Elasticsearch indexed version. Compare key fields (name, email, updated_at). Log discrepancies.
2. Checksum comparison. For each user, compute a hash of the canonical fields in PostgreSQL. Store the hash as a field in the Redis entry and the Elasticsearch document. The reconciliation job queries Elasticsearch for documents where the stored hash does not match the current PostgreSQL hash. This is more efficient than fetching and comparing full records.
3. Event lag monitoring. Track the outbox’s oldest unprocessed event. If it is more than N minutes old, alert. This is the leading indicator — if the worker is behind, drift is accumulating. Monitor processed_at - created_at as a latency metric in your dashboard.

​

Your PostgreSQL table has 200 million rows. An engineer adds range partitioning by month to speed things up. Queries are now SLOWER. What went wrong?

​

Follow-up: When is partitioning genuinely the right call, and what prerequisites must be true?

1. All hot-path queries include the partition key in their WHERE clause. This is non-negotiable. If even one critical query does not filter by the partition key, that query gets worse.
2. The table is large enough that partition-level indexes are faster. Rule of thumb: partitioning helps when the full table’s index does not fit in shared_buffers but individual partition indexes do. A 200M row table with a 20 GB index that only has 8 GB of shared_buffers benefits from partitioning — each partition’s 500 MB index fits comfortably. A 10M row table with a 1 GB index that fits in memory does not benefit.
3. You need lifecycle management. This is the strongest argument for partitioning: dropping old data. DROP TABLE partition_2023_01 is instant and creates zero dead tuples. DELETE FROM events WHERE created_at < '2023-02-01' on a 200M row table takes hours, creates millions of dead tuples, and triggers an aggressive autovacuum. If you have a retention policy, partitioning is almost always worth it for the lifecycle benefit alone, even if query performance stays the same.

​

Follow-up: Can you have a global unique index across all partitions in PostgreSQL?

​

You run a Redis-backed leaderboard for a mobile game with 10 million players. An engineer suggests switching the Sorted Set to a PostgreSQL table with an index on score because “we need persistent rankings and Redis might lose data.” Evaluate this.

• ZADD (update score): O(log N) = ~23 operations deep in a skip list. In practice, 0.01-0.05 ms.
• ZREVRANK (get player’s rank): O(log N). 0.01-0.05 ms.
• ZREVRANGE 0 99 (top 100 leaderboard): O(log N + 100). 0.05 ms.
• Memory: ~10M members * (~50 bytes per member + score) = ~700 MB. Fits on one instance.

• Update score: UPDATE players SET score = X WHERE id = Y. Requires an index update, WAL write, potential page split. 1-5 ms.
• Get player rank: SELECT COUNT(*) FROM players WHERE score > (SELECT score FROM players WHERE id = Y). This is a full index scan of all rows with higher scores. For a median player (rank 5M), this scans 5 million index entries. 2-10 seconds.
• Top 100: SELECT * FROM players ORDER BY score DESC LIMIT 100. Uses the index. 1-5 ms. This one is comparable.

1. Use Redis with AOF (appendfsync everysec). Maximum data loss is 1 second. For a game leaderboard, this is acceptable. If a player’s score update from 0.8 seconds ago is lost due to a crash, the impact is negligible.
2. Use AWS MemoryDB instead of ElastiCache Redis. MemoryDB writes to a distributed, durable transaction log (similar to DynamoDB’s durability model). It is Redis-API compatible with multi-AZ durability. Zero data loss on node failure. This addresses the engineer’s concern without sacrificing Redis’s data structure advantages.
3. If regulatory or compliance requirements demand PostgreSQL as the system of record, write scores to both PostgreSQL (source of truth) and Redis (leaderboard). PostgreSQL stores the canonical score and handles disputes/audits. Redis serves the real-time leaderboard. Reconcile periodically. This is polyglot persistence — each database doing what it does best.

​

Follow-up: How would you handle leaderboard sharding if the game grows to 500 million players?

1. Regional leaderboards. Most games have natural sharding by region (NA, EU, Asia). Each region has its own Redis instance with its own Sorted Set. Players see their regional rank. A global leaderboard is computed periodically by merging the top N from each region. This works because most players care about their regional rank; only the top 1% care about global rank.
2. Tiered leaderboard. A top-10,000 leaderboard in one Sorted Set (updated in real-time, tiny data). Below that, bucket players into score ranges (e.g., players with scores 1000-2000). Each bucket is a separate Sorted Set on a different shard. A player’s approximate rank is: (number of players in higher buckets) + (rank within their bucket). This gives O(1) for the bucket lookup plus O(log N) within the bucket.
3. Redis Cluster with hash tags. Shard the Sorted Set across multiple nodes using hash tags. But this defeats the purpose — Sorted Set operations are per-key, and a Sorted Set split across nodes loses its ordering guarantees. Approach 1 or 2 is preferred.

​

Follow-up: What happens to the leaderboard during a Redis failover? Players will see stale or missing ranks.

1. Read from replicas during failover. Configure the application to fall back to a replica for leaderboard reads if the primary is unreachable. The rank will be slightly stale (by the replication lag, typically <1 second) but available. Writes queue in the application and flush when the new primary is ready.
2. Cache the last known leaderboard. The top-100 leaderboard changes slowly (once every few seconds at most). Cache the rendered leaderboard in the application or a CDN with a 5-second TTL. During failover, the CDN serves the slightly stale leaderboard. Players do not notice.
3. Accept temporary unavailability for rank lookups. Individual rank queries (“my rank”) are hard to cache. During failover, return “rank unavailable, try again shortly” in the UI. Most games already handle this gracefully with a loading spinner.

​

A developer inserts 10 million documents into MongoDB in a loop, one at a time, and it takes 3 hours. They expected it to take 10 minutes. What is wrong and how do you fix it?

// 1. Drop non-essential indexes (keep only _id and shard key)
// 2. Batch inserts
const BATCH_SIZE = 5000;
for (let i = 0; i < documents.length; i += BATCH_SIZE) {
  const batch = documents.slice(i, i + BATCH_SIZE);
  await db.collection.insertMany(batch, {
    ordered: false,  // unordered is faster (parallel server-side processing)
    writeConcern: { w: 1 }
  });
}
// 3. Rebuild indexes
await db.collection.createIndex({ userId: 1, createdAt: -1 });

​

Follow-up: What is the difference between ordered and unordered bulk writes, and when would you prefer ordered?

​

Follow-up: The developer asks “should I use mongoimport instead of writing application code for bulk loads?”

​

You are on-call and get paged: “Redis latency spiked from 0.2ms to 50ms.” What are the first five things you check, in order?

​

Follow-up: How do you prevent the KEYS command from ever being used in production?

1. Rename the command. In redis.conf: rename-command KEYS "" (disables it entirely) or rename-command KEYS KEYS_DANGER_DO_NOT_USE. This prevents accidental use. Any code trying to use KEYS gets an error.
2. Code review enforcement. Add a linter rule or a grep check in CI that flags any use of KEYS in application code. The alternative is always SCAN with a cursor, which returns results incrementally without blocking the event loop.
3. Redis ACLs (Redis 6+). Create application-specific users that do not have permission for dangerous commands: ACL SETUSER appuser ~* +@all -@dangerous. The @dangerous category includes KEYS, FLUSHALL, FLUSHDB, DEBUG, and other commands that should never run in production.

​

Follow-up: The latency spike lasts exactly 200ms and happens once per minute. SLOWLOG is empty. What specific Redis internal event takes exactly ~200ms?

1. Active expiry cycle. Redis runs an active key expiration cycle 10 times per second (every 100ms). In each cycle, it samples 20 keys with TTLs and deletes expired ones. If more than 25% of sampled keys are expired, it loops again (up to a time limit of 25ms by default, or 250ms if hz is tuned up). On a Redis instance with millions of keys all expiring around the same time (a common pattern when TTLs are set to round numbers like 60s, 300s, or 3600s), the expiry cycle can consume its full time budget. The solution: add jitter to TTLs (TTL = base_ttl + random(0, 30)). This spreads expirations over time instead of creating thundering herd expiry.
2. Lazy freeing of large keys (Redis 4.0+). When a large key is deleted or expires, Redis 4.0+ can free the memory asynchronously using lazyfree-lazy-expire yes. But if this is not enabled, deleting a Hash with 1 million fields blocks the event loop for the duration of the memory deallocation (100-300ms for a 500 MB key). The SLOWLOG would not show this because the DEL command itself reports as fast — the time is spent in memory management after the command completes. Enable lazyfree-lazy-expire yes, lazyfree-lazy-server-del yes, and lazyfree-lazy-user-del yes to move large key deletion to a background thread.

​

Curated Resources