Database Complete Comparison 2025: PostgreSQL vs MySQL vs MongoDB vs Redis — Selection Guide

         Consistency (C)
            /\
           /  \
          /    \
    CP   / CAP  \ CA
  system/ impossi\ system
        /ble     \
       /________\
  Partition(P)   Availability(A)

┌─────────────────────────────────────────────┐
│                 PostgreSQL                   │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  │
│  │ Backend  │  │ Backend  │  │ Backend  │  │
│  │ Process  │  │ Process  │  │ Process  │  │
│  └────┬─────┘  └────┬─────┘  └────┬─────┘  │
│       │             │             │         │
│  ┌────┴─────────────┴─────────────┴────┐    │
│  │          Shared Memory              │    │
│  │  ┌──────────┐  ┌──────────────┐    │    │
│  │  │ Shared   │  │    WAL       │    │    │
│  │  │ Buffers  │  │  Buffers     │    │    │
│  │  └──────────┘  └──────────────┘    │    │
│  └─────────────────────────────────────┘    │
│                                             │
│  ┌─────────────────────────────────────┐    │
│  │     Background Workers              │    │
│  │  WAL Writer | Checkpointer |        │    │
│  │  Autovacuum | Stats Collector       │    │
│  └─────────────────────────────────────┘    │
└─────────────────────────────────────────────┘

-- Set transaction isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Verify MVCC behavior
BEGIN;
SELECT xmin, xmax, ctid, * FROM orders WHERE id = 1;
-- xmin: creation transaction ID, xmax: deletion transaction ID
-- ctid: physical location (page, offset)
COMMIT;

-- Create table with JSONB column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    metadata JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create GIN index (optimizes JSONB search)
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- JSONB query examples
SELECT name, metadata->>'category' AS category,
       (metadata->'specs'->>'weight')::numeric AS weight
FROM products
WHERE metadata @> '{"category": "electronics"}'
  AND (metadata->'specs'->>'weight')::numeric < 500;

-- JSONB aggregation
SELECT metadata->>'category' AS category,
       COUNT(*),
       AVG((metadata->'price'->>'amount')::numeric) AS avg_price
FROM products
GROUP BY metadata->>'category';

-- Install pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Table with vector column
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536)  -- OpenAI text-embedding-3-small dimensions
);

-- HNSW index (fast approximate nearest neighbor search)
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- IVFFlat index (memory efficient)
CREATE INDEX ON documents
USING ivfflat (embedding vector_l2_ops)
WITH (lists = 100);

-- Cosine similarity search
SELECT id, title,
       1 - (embedding <=> query_embedding) AS similarity
FROM documents
ORDER BY embedding <=> query_embedding
LIMIT 10;

-- Hybrid search (vector + full-text)
SELECT id, title,
       ts_rank(to_tsvector('english', content), plainto_tsquery('english', 'search term')) AS text_score,
       1 - (embedding <=> query_embedding) AS vector_score
FROM documents
WHERE to_tsvector('english', content) @@ plainto_tsquery('english', 'search term')
ORDER BY vector_score * 0.7 + text_score * 0.3 DESC
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Trigram index
CREATE INDEX idx_products_name_trgm ON products
USING GIN (name gin_trgm_ops);

-- Similarity search (typo-tolerant)
SELECT name, similarity(name, 'PostgreSLQ') AS sim
FROM products
WHERE name % 'PostgreSLQ'
ORDER BY sim DESC;

-- Range partitioning example
CREATE TABLE orders (
    id BIGSERIAL,
    order_date DATE NOT NULL,
    customer_id INTEGER NOT NULL,
    total_amount NUMERIC(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

-- Create partitions
CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE orders_2025_q2 PARTITION OF orders
    FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- Automatic partition management (pg_partman)
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
    p_parent_table => 'public.orders',
    p_control => 'order_date',
    p_type => 'native',
    p_interval => '3 months'
);

┌─────────────────────────────────────────────┐
│                MySQL Server                  │
│  ┌───────────────────────────────────────┐  │
│  │          Connection Pool              │  │
│  │   Thread 1 | Thread 2 | Thread N     │  │
│  └───────────────────────────────────────┘  │
│  ┌───────────────────────────────────────┐  │
│  │     Query Cache (removed in 8.0+)    │  │
│  │          Parser -> Optimizer          │  │
│  └───────────────────────────────────────┘  │
│  ┌───────────────────────────────────────┐  │
│  │            InnoDB Engine              │  │
│  │  ┌──────────┐  ┌──────────────────┐  │  │
│  │  │ Buffer   │  │  Change Buffer   │  │  │
│  │  │ Pool     │  │                  │  │  │
│  │  └──────────┘  └──────────────────┘  │  │
│  │  ┌──────────┐  ┌──────────────────┐  │  │
│  │  │ Redo Log │  │  Undo Log        │  │  │
│  │  └──────────┘  └──────────────────┘  │  │
│  └───────────────────────────────────────┘  │
└─────────────────────────────────────────────┘

-- MySQL: UPSERT (INSERT ... ON DUPLICATE KEY)
INSERT INTO products (id, name, stock)
VALUES (1, 'Widget', 100)
ON DUPLICATE KEY UPDATE stock = stock + VALUES(stock);

-- PostgreSQL: UPSERT (INSERT ... ON CONFLICT)
INSERT INTO products (id, name, stock)
VALUES (1, 'Widget', 100)
ON CONFLICT (id)
DO UPDATE SET stock = products.stock + EXCLUDED.stock;

-- Train model with HeatWave ML
CALL sys.ML_TRAIN('my_schema.customer_data',
    'churn_label',
    JSON_OBJECT('task', 'classification'),
    @model_handle);

-- Run prediction
CALL sys.ML_PREDICT_ROW(
    JSON_OBJECT('age', 35, 'tenure', 24, 'monthly_charge', 79.99),
    @model_handle,
    @prediction);

SELECT @prediction;

-- Check Group Replication status
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE
FROM performance_schema.replication_group_members;

-- Single-primary mode (default, recommended)
-- One write node, rest are read-only
SET GLOBAL group_replication_single_primary_mode = ON;

-- ProxySQL routing rules
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES
    (1, 1, '^SELECT .* FOR UPDATE', 0),   -- Writer group
    (2, 1, '^SELECT', 1);                  -- Reader group

-- Server group configuration
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES
    (0, 'primary.mysql.local', 3306, 1),    -- Writer
    (1, 'replica1.mysql.local', 3306, 50),  -- Reader
    (1, 'replica2.mysql.local', 3306, 50);  -- Reader

// MongoDB document structure — embedding pattern
db.orders.insertOne({
  orderId: 'ORD-2025-001',
  customer: {
    name: 'John Dev',
    email: 'john@example.com',
    address: {
      street: '123 Tech Street',
      city: 'San Francisco',
      zipCode: '94102',
    },
  },
  items: [
    {
      productId: 'PROD-001',
      name: 'Wireless Keyboard',
      price: 89.0,
      quantity: 2,
    },
    {
      productId: 'PROD-002',
      name: 'Monitor Stand',
      price: 45.0,
      quantity: 1,
    },
  ],
  payment: {
    method: 'card',
    status: 'completed',
    paidAt: ISODate('2025-03-15T10:30:00Z'),
  },
  totalAmount: 223.0,
  createdAt: ISODate('2025-03-15T10:25:00Z'),
})

// Create vector search index
db.collection.createSearchIndex({
  name: 'vector_index',
  definition: {
    mappings: {
      dynamic: true,
      fields: {
        embedding: {
          type: 'knnVector',
          dimensions: 1536,
          similarity: 'cosine',
        },
      },
    },
  },
})

// Vector search query
db.documents.aggregate([
  {
    $vectorSearch: {
      index: 'vector_index',
      path: 'embedding',
      queryVector: queryEmbedding, // 1536-dimensional vector
      numCandidates: 100,
      limit: 10,
      filter: { category: 'tech' },
    },
  },
  {
    $project: {
      title: 1,
      content: 1,
      score: { $meta: 'vectorSearchScore' },
    },
  },
])

// Monthly revenue analysis pipeline
db.orders.aggregate([
  // Period filter
  {
    $match: {
      createdAt: {
        $gte: ISODate('2025-01-01'),
        $lt: ISODate('2026-01-01'),
      },
      'payment.status': 'completed',
    },
  },
  // Decompose order items
  { $unwind: '$items' },
  // Group by month
  {
    $group: {
      _id: {
        year: { $year: '$createdAt' },
        month: { $month: '$createdAt' },
        category: '$items.category',
      },
      totalRevenue: { $sum: { $multiply: ['$items.price', '$items.quantity'] } },
      orderCount: { $sum: 1 },
      avgOrderValue: { $avg: { $multiply: ['$items.price', '$items.quantity'] } },
    },
  },
  // Sort
  { $sort: { '_id.year': 1, '_id.month': 1 } },
  // Transform output
  {
    $project: {
      _id: 0,
      period: { $concat: [{ $toString: '$_id.year' }, '-', { $toString: '$_id.month' }] },
      category: '$_id.category',
      totalRevenue: 1,
      orderCount: 1,
      avgOrderValue: { $round: ['$avgOrderValue', 0] },
    },
  },
])

// Shard key selection — consider cardinality, distribution, query patterns
sh.enableSharding('ecommerce')

// Hash sharding (even distribution)
sh.shardCollection('ecommerce.orders', { customerId: 'hashed' })

// Range sharding (optimized for range queries)
sh.shardCollection('ecommerce.logs', { timestamp: 1 })

// Zone sharding (region-based data separation)
sh.addShardTag('shard-us', 'AMERICAS')
sh.addShardTag('shard-eu', 'EUROPE')
sh.addTagRange('ecommerce.users', { region: 'US' }, { region: 'UT' }, 'AMERICAS')

-- Strings: cache, sessions, counters
SET user:session:abc123 "session_data" EX 3600
INCR page:views:homepage

-- Hashes: object storage
HSET user:1001 name "John Dev" email "john@dev.com" login_count 42
HINCRBY user:1001 login_count 1

-- Sorted Sets: leaderboards, rankings
ZADD leaderboard 9500 "player:alice"
ZADD leaderboard 8700 "player:bob"
ZADD leaderboard 9200 "player:charlie"
ZREVRANGE leaderboard 0 9 WITHSCORES  -- Top 10

-- Streams: event streaming
XADD mystream * sensor_id "temp-01" temperature "23.5" humidity "45"
XREAD COUNT 10 BLOCK 5000 STREAMS mystream 0

-- HyperLogLog: approximate unique counts
PFADD unique:visitors:2025-03-23 "user1" "user2" "user3"
PFCOUNT unique:visitors:2025-03-23

-- Redis JSON
JSON.SET product:001 $ '{"name":"Wireless Keyboard","price":89.00,"tags":["electronics","keyboard"],"specs":{"weight":350,"wireless":true}}'
JSON.GET product:001 $.specs.weight

-- Redis Search index creation
FT.CREATE idx:products ON JSON PREFIX 1 product:
  SCHEMA
    $.name AS name TEXT SORTABLE
    $.price AS price NUMERIC SORTABLE
    $.tags[*] AS tags TAG
    $.specs.weight AS weight NUMERIC

-- Full-text search
FT.SEARCH idx:products "@name:keyboard @price:[50 100]"

-- Vector search (Redis Stack)
FT.CREATE idx:docs ON HASH PREFIX 1 doc:
  SCHEMA
    title TEXT
    embedding VECTOR HNSW 6 TYPE FLOAT32 DIM 1536 DISTANCE_METRIC COSINE

-- KNN search
FT.SEARCH idx:docs "*=>[KNN 10 @embedding $query_vec AS score]"
  PARAMS 2 query_vec "\x00\x00..."
  SORTBY score
  RETURN 2 title score

-- Redis TimeSeries
TS.CREATE temperature:sensor1 RETENTION 86400000 LABELS sensor_id temp-01 location office
TS.ADD temperature:sensor1 * 23.5
TS.RANGE temperature:sensor1 - + AGGREGATION avg 60000  -- 1-minute average

# Recommended redis.conf settings
save 900 1       # RDB if 1+ changes in 900 seconds
save 300 10      # RDB if 10+ changes in 300 seconds
appendonly yes   # Enable AOF
appendfsync everysec  # fsync every second (performance/safety balance)

# Create 6-node cluster (3 masters + 3 replicas)
redis-cli --cluster create \
  node1:6379 node2:6379 node3:6379 \
  node4:6379 node5:6379 node6:6379 \
  --cluster-replicas 1

┌───────────────────────────────────────────┐
│              DynamoDB Table                │
│  ┌─────────────────────────────────────┐  │
│  │  Partition Key (PK)  Sort Key (SK)  │  │
│  ├─────────────────────────────────────┤  │
│  │  USER#001            PROFILE        │  │
│  │  USER#001            ORDER#001      │  │
│  │  USER#001            ORDER#002      │  │
│  │  USER#002            PROFILE        │  │
│  │  USER#002            ORDER#001      │  │
│  └─────────────────────────────────────┘  │
│                                           │
│  GSI: OrderDate-Index                     │
│  ┌─────────────────────────────────────┐  │
│  │  OrderDate (PK)     UserId (SK)     │  │
│  └─────────────────────────────────────┘  │
└───────────────────────────────────────────┘

import boto3

dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('EcommerceTable')

# Store user profile
table.put_item(Item={
    'PK': 'USER#001',
    'SK': 'PROFILE',
    'name': 'John Dev',
    'email': 'john@dev.com',
    'type': 'USER'
})

# Store order in the same table
table.put_item(Item={
    'PK': 'USER#001',
    'SK': 'ORDER#2025-03-15#ORD001',
    'items': [{'name': 'Keyboard', 'price': 89.00}],
    'total': 89.00,
    'status': 'completed',
    'type': 'ORDER'
})

# Query all orders for a user
response = table.query(
    KeyConditionExpression='PK = :pk AND begins_with(SK, :sk)',
    ExpressionAttributeValues={
        ':pk': 'USER#001',
        ':sk': 'ORDER#'
    }
)

┌────────┐  ┌────────┐  ┌────────┐
│ Node A │──│ Node B │──│ Node C │
│ Token: │  │ Token: │  │ Token: │
│ 0-33   │  │ 34-66  │  │ 67-100 │
└───┬────┘  └───┬────┘  └───┬────┘
    │           │           │
    └───────────┼───────────┘
            Gossip Protocol

-- Create keyspace (with replication strategy)
CREATE KEYSPACE ecommerce WITH replication = {
    'class': 'NetworkTopologyStrategy',
    'us-east': 3,
    'eu-west': 3
};

-- Create table (partition key + clustering key)
CREATE TABLE ecommerce.user_activity (
    user_id UUID,
    activity_date DATE,
    activity_time TIMESTAMP,
    activity_type TEXT,
    details MAP<TEXT, TEXT>,
    PRIMARY KEY ((user_id, activity_date), activity_time)
) WITH CLUSTERING ORDER BY (activity_time DESC)
  AND compaction = {'class': 'TimeWindowCompactionStrategy',
                    'compaction_window_size': 1,
                    'compaction_window_unit': 'DAYS'};

┌─────────────────────────────────────────────────┐
│                  Application Layer               │
└───────┬──────────┬──────────┬──────────┬────────┘
        │          │          │          │
   ┌────▼────┐ ┌──▼───┐ ┌───▼───┐ ┌───▼────┐
   │PostgreSQL│ │Redis │ │MongoDB│ │Elastic │
   │(Orders/  │ │(Cache)│ │(Prods)│ │(Search)│
   │ Payment) │ │      │ │       │ │        │
   └─────────┘ └──────┘ └───────┘ └────────┘

Write (Command):                  Read (Query):
┌──────────┐                     ┌──────────┐
│  Client   │                     │  Client   │
└────┬─────┘                     └────┬─────┘
     │                                │
┌────▼─────┐                     ┌────▼─────┐
│ Command  │                     │  Query   │
│ Service  │                     │ Service  │
└────┬─────┘                     └────┬─────┘
     │                                │
┌────▼─────┐  ──Events──▶  ┌─────▼─────┐
│PostgreSQL│              │  Redis /   │
│ (Source  │              │  MongoDB   │
│  of Truth)│              │ (Read Model)│
└──────────┘              └───────────┘

import redis
import psycopg2
import json

r = redis.Redis(host='localhost', port=6379)

def get_product(product_id):
    # 1. Check cache
    cache_key = f"product:{product_id}"
    cached = r.get(cache_key)
    if cached:
        return json.loads(cached)

    # 2. Cache miss — query DB
    conn = psycopg2.connect("dbname=ecommerce")
    cur = conn.cursor()
    cur.execute("SELECT * FROM products WHERE id = %s", (product_id,))
    product = cur.fetchone()

    if product:
        # 3. Store in cache (TTL 1 hour)
        r.setex(cache_key, 3600, json.dumps(product))

    return product

def update_product(product_id, data):
    # 1. Update DB
    conn = psycopg2.connect("dbname=ecommerce")
    cur = conn.cursor()
    cur.execute("UPDATE products SET name=%s WHERE id=%s",
                (data['name'], product_id))
    conn.commit()

    # 2. Invalidate cache
    r.delete(f"product:{product_id}")