NoSQL Databases

// ─────────────────────────────────────────────────────────────────
// STRING — simple key-value pairs
// ─────────────────────────────────────────────────────────────────
SET user:1001:name "Rahul Sharma"
GET user:1001:name           -- returns "Rahul Sharma"
SET user:1001:session "tok_abc123xyz" EX 3600   -- expires in 3600 seconds
TTL user:1001:session        -- returns remaining TTL in seconds
INCR page:homepage:visits    -- atomic increment (great for counters)
INCRBY order:5001:attempts 1 -- increment by specific amount
SETNX config:maintenance "1" -- SET if Not eXists (atomic, for distributed locks)

// ─────────────────────────────────────────────────────────────────
// HASH — nested key-value (object/struct)
// ─────────────────────────────────────────────────────────────────
HSET user:1001 name "Rahul Sharma" email "rahul@email.com" city "Bengaluru" tier "Gold"
HGET  user:1001 name              -- "Rahul Sharma"
HGETALL user:1001                 -- all fields and values
HMGET user:1001 name email        -- multiple fields at once
HINCRBY user:1001 order_count 1   -- increment a hash field atomically
HDEL user:1001 tier               -- delete one field

// USE CASE: Caching database objects
// Instead of serialising the entire user object as JSON,
// store individual fields as a hash. Can update one field (e.g., city)
// without reading and rewriting the entire object.

// ─────────────────────────────────────────────────────────────────
// LIST — ordered collection (deque)
// ─────────────────────────────────────────────────────────────────
RPUSH notifications:user:1001 "Order #5001 delivered" "Review reminder"
LPUSH feed:user:1001 "new_post:P123"    -- prepend (most recent first)
LRANGE feed:user:1001 0 19              -- get first 20 items (pagination)
LPOP  job_queue                         -- pop from left (FIFO queue)
RPOPLPUSH src_queue processing_queue    -- atomic move (reliable queue)
LLEN  feed:user:1001                    -- length of list

// USE CASE: Activity feeds, message queues, recent items lists
// Twitter/Instagram-style home feed: LPUSH new posts, LRANGE for pagination

// ─────────────────────────────────────────────────────────────────
// SET — unordered collection of unique strings
// ─────────────────────────────────────────────────────────────────
SADD user:1001:interests "cricket" "movies" "biryani"
SISMEMBER user:1001:interests "cricket"  -- 1 (yes) or 0 (no)
SMEMBERS user:1001:interests             -- all members
SCARD user:1001:interests                -- count of members
SINTER user:1001:interests user:1002:interests  -- intersection (common interests)
SUNION user:1001:interests user:1002:interests  -- union
SDIFF  user:1001:interests user:1002:interests  -- difference

// USE CASE: Unique visitor tracking, tag systems, friend recommendations
// "Which articles has this user already seen?" — store article IDs in a set
// O(1) membership check: SISMEMBER

// ─────────────────────────────────────────────────────────────────
// SORTED SET (ZSET) — set with float scores, ordered by score
// ─────────────────────────────────────────────────────────────────
ZADD leaderboard 8500 "player:rahul"
ZADD leaderboard 9200 "player:priya"
ZADD leaderboard 7800 "player:arjun"
ZRANK  leaderboard "player:priya"        -- 0 (rank, 0-indexed, lowest score first)
ZREVRANK leaderboard "player:priya"      -- 0 (rank from highest: priya is #1)
ZRANGE  leaderboard 0 2 WITHSCORES       -- bottom 3 with scores
ZREVRANGE leaderboard 0 9 WITHSCORES     -- top 10 with scores (leaderboard!)
ZINCRBY leaderboard 300 "player:arjun"  -- add 300 points to arjun's score
ZRANGEBYSCORE leaderboard 8000 9000     -- players with score between 8000-9000

// USE CASE: Leaderboards, rate limiting, priority queues, trending content
// Real-time game leaderboard: ZADD on every score update, ZREVRANGE for top-N
// Rate limiting: score = timestamp, count members in last minute window

// ─────────────────────────────────────────────────────────────────
// BITMAP — compact bit array operations
// ─────────────────────────────────────────────────────────────────
SETBIT user:1001:daily_login 20240315 1  -- user logged in on day 20240315
GETBIT user:1001:daily_login 20240315    -- 1 (they did)
BITCOUNT user:1001:daily_login           -- total days logged in

// USE CASE: Tracking daily active users, feature flags
// "How many users logged in today?" — BITCOUNT a shared bitmap
// Storage: 100 million users tracked in 12.5 MB (1 bit per user)

// ─────────────────────────────────────────────────────────────────
// PERSISTENCE OPTIONS
// ─────────────────────────────────────────────────────────────────
// RDB (snapshot): periodic dump of entire dataset to disk
//   Fast restarts, compact file, but may lose last few minutes of data on crash
// AOF (Append-Only File): log every write operation
//   More durable (can sync every second or every operation), larger file
// Both: use RDB for backups, AOF for durability (recommended production setup)

// DYNAMODB TABLE STRUCTURE:
// Every item has a primary key:
//   Simple primary key: just a partition key (PK)
//   Composite primary key: partition key (PK) + sort key (SK)
//
// Access patterns MUST be defined upfront — DynamoDB is query-first design

// EXAMPLE: E-commerce order system
// Table: Orders
// PK: customer_id (partition key — determines which node stores this item)
// SK: order_date#order_id (sort key — orders sorted by date within a customer)

// WRITE:
{
  "customer_id": "C001",                    // PK
  "order_date#order_id": "2024-03-15#O5001", // SK
  "status": "delivered",
  "total_amount": 280.00,
  "items": ["biryani", "raita"],            // flexible attributes
  "restaurant": "Biryani House"
}

// QUERY: "All orders for customer C001 in March 2024"
// KeyConditionExpression: customer_id = 'C001'
//                    AND begins_with(sort_key, '2024-03')
// → Efficiently retrieves all March 2024 orders for C001
// Scans only C001's partition, sorted by date — O(log n) + O(k results)

// GLOBAL SECONDARY INDEX (GSI): query by non-primary-key attributes
// GSI on status#order_date to find "all delivered orders in March 2024"
// GSI on restaurant to find "all orders from Biryani House"
// Each GSI is essentially a copy of the data sorted differently

// CAPACITY UNITS:
// Read Capacity Unit (RCU): 1 strongly consistent read of up to 4KB
// Write Capacity Unit (WCU): 1 write of up to 1KB
// On-demand mode: automatically scales, pay per request
// Provisioned mode: set RCUs and WCUs, pay for provisioned capacity

// DYNAMODB STREAMS: capture all changes as a stream
// Trigger Lambda functions on item changes
// Replicate to other systems (Elasticsearch for search, Redshift for analytics)
// Event-driven architecture patterns

// DOCUMENT STRUCTURE: embedded vs referenced
// Embed when: data is always accessed together, document count is bounded
// Reference when: data is large, accessed independently, or reused by many documents

// EMBEDDED (good when you always read the whole order):
{
  "_id": ObjectId("65f2a1b3..."),
  "order_id": "O5001",
  "customer": {
    "customer_id": "C001",
    "name": "Rahul Sharma",
    "city": "Bengaluru"
  },
  "restaurant": {
    "restaurant_id": "R01",
    "name": "Biryani House"
  },
  "items": [
    { "name": "Chicken Biryani", "qty": 1, "price": 280.00 },
    { "name": "Raita",           "qty": 1, "price":  40.00 }
  ],
  "total": 320.00,
  "status": "delivered",
  "created_at": ISODate("2024-03-15T14:32:00Z")
}

// CRUD OPERATIONS:
// INSERT:
db.orders.insertOne({ order_id: "O5002", status: "pending", total: 180 });
db.orders.insertMany([...array of documents...]);

// FIND (SELECT equivalent):
db.orders.find({ status: "delivered" })
db.orders.find({ "customer.city": "Bengaluru", total: { $gt: 300 } })
// Dot notation accesses embedded fields: "customer.city"

// PROJECTION (SELECT specific fields):
db.orders.find(
  { status: "delivered" },
  { order_id: 1, total: 1, "customer.name": 1, _id: 0 }
)

// QUERY OPERATORS:
// $gt, $lt, $gte, $lte, $ne, $in, $nin — comparison
// $and, $or, $not, $nor — logical
// $exists, $type — element operators
// $regex — pattern matching
// $elemMatch — match array elements

db.orders.find({
  status: { $in: ["delivered", "out_for_delivery"] },
  total:  { $gte: 200 },
  created_at: { $gte: ISODate("2024-01-01") }
})

// UPDATE:
db.orders.updateOne(
  { order_id: "O5001" },                     // filter
  { $set: { status: "delivered" },           // update operators
    $currentDate: { delivered_at: true } }
)
db.orders.updateMany(
  { "customer.city": "Bengaluru", status: "pending" },
  { $set: { priority: "high" } }
)
// UPDATE OPERATORS: $set, $unset, $inc, $push, $pull, $addToSet

// DELETE:
db.orders.deleteOne({ order_id: "O5001" })
db.orders.deleteMany({ status: "cancelled", created_at: { $lt: ISODate("2024-01-01") } })

// ─────────────────────────────────────────────────────────────────
// AGGREGATION PIPELINE — MongoDB's most powerful feature
// ─────────────────────────────────────────────────────────────────
db.orders.aggregate([
  // Stage 1: Filter (like WHERE)
  { $match: {
    status: "delivered",
    created_at: { $gte: ISODate("2024-01-01") }
  }},

  // Stage 2: Group (like GROUP BY + aggregate functions)
  { $group: {
    _id: "$customer.city",              // group by city
    total_revenue: { $sum: "$total" },
    order_count:   { $sum: 1 },
    avg_order:     { $avg: "$total" },
    max_order:     { $max: "$total" }
  }},

  // Stage 3: Sort (like ORDER BY)
  { $sort: { total_revenue: -1 } },     // descending

  // Stage 4: Limit (like LIMIT)
  { $limit: 10 },

  // Stage 5: Project (like SELECT with computed fields)
  { $project: {
    city: "$_id",
    total_revenue: 1,
    order_count: 1,
    avg_order: { $round: ["$avg_order", 2] },
    _id: 0
  }}
])
// Result: top 10 cities by revenue in 2024

// SINGLE FIELD INDEX:
db.orders.createIndex({ customer_id: 1 })   // ascending
db.orders.createIndex({ created_at: -1 })   // descending (latest first)

// COMPOUND INDEX (order of fields matters — leftmost prefix rule applies):
db.orders.createIndex({ "customer.city": 1, created_at: -1 })
// Efficiently serves: queries on city alone, queries on city+date
// Does NOT efficiently serve: queries on date alone (not leftmost)

// TEXT INDEX (full-text search):
db.restaurants.createIndex({ name: "text", description: "text" })
db.restaurants.find({ $text: { $search: "biryani chicken" } })
// Returns documents containing these words anywhere in indexed fields

// GEOSPATIAL INDEX (2dsphere — spherical coordinates):
db.restaurants.createIndex({ location: "2dsphere" })
db.restaurants.find({
  location: {
    $near: {
      $geometry: { type: "Point", coordinates: [77.5946, 12.9716] }, // Bengaluru
      $maxDistance: 5000  // 5km radius
    }
  }
})

// TTL INDEX (auto-expire documents):
db.sessions.createIndex({ created_at: 1 }, { expireAfterSeconds: 3600 })
// Sessions older than 1 hour are automatically deleted by MongoDB

// PARTIAL INDEX (only index documents meeting a condition):
db.orders.createIndex(
  { customer_id: 1 },
  { partialFilterExpression: { status: "pending" } }
)
// Only pending orders are in the index — much smaller, faster for pending order queries

// EXPLAIN to check index usage:
db.orders.find({ customer_id: "C001" }).explain("executionStats")
// Look for: "IXSCAN" (index used) vs "COLLSCAN" (full collection scan)
// totalDocsExamined should be close to nReturned for good index usage

// CASSANDRA DESIGN RULE: design tables around your queries, not your entities
// In SQL: design normalised entities, write any query
// In Cassandra: decide your queries first, design tables to answer them

// ─────────────────────────────────────────────────────────────────
// CASSANDRA TABLE STRUCTURE:
// Partition Key: determines which node stores this row (hash-based sharding)
// Clustering Key: sorts rows within a partition on disk
// ─────────────────────────────────────────────────────────────────

// QUERY 1: "Get all orders for a customer, sorted by date descending"
CREATE TABLE orders_by_customer (
    customer_id   UUID,
    order_date    TIMESTAMP,
    order_id      UUID,
    status        TEXT,
    total_amount  DECIMAL,
    restaurant    TEXT,
    PRIMARY KEY (customer_id, order_date, order_id)
    -- customer_id: PARTITION KEY (all rows for one customer on same node)
    -- order_date, order_id: CLUSTERING KEYS (sorted on disk within partition)
) WITH CLUSTERING ORDER BY (order_date DESC, order_id DESC);

// This query is fast (reads from one partition, pre-sorted):
SELECT * FROM orders_by_customer
WHERE customer_id = c1234
ORDER BY order_date DESC
LIMIT 20;

// This query is FORBIDDEN (no partition key filter = full cluster scan):
SELECT * FROM orders_by_customer WHERE status = 'pending'; -- ERROR or very slow

// QUERY 2: "Get all orders for a restaurant, sorted by date"
-- Same data, different table! Cassandra recommends denormalisation.
CREATE TABLE orders_by_restaurant (
    restaurant_id UUID,
    order_date    TIMESTAMP,
    order_id      UUID,
    customer_id   UUID,
    status        TEXT,
    total_amount  DECIMAL,
    PRIMARY KEY (restaurant_id, order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC);

-- When an order is placed: insert into BOTH tables
-- This is intentional denormalisation for query efficiency

// ─────────────────────────────────────────────────────────────────
// CASSANDRA WRITE PATH — why writes are so fast
// ─────────────────────────────────────────────────────────────────
// 1. Write to Commit Log (sequential append — extremely fast)
// 2. Write to Memtable (in-memory table for this column family)
// 3. Acknowledge to client ← happens here, before hitting disk!
// 4. Background: when memtable is full, flush to SSTable (Sorted String Table)
// 5. Background: compaction merges SSTables, removes tombstones (deleted rows)

// WHY IT'S FAST: steps 1-3 are all sequential writes or in-memory operations
// No random disk I/O for writes — all disk writes are sequential

// READ PATH (more complex than writes):
// 1. Check bloom filter — is this key potentially in this SSTable?
// 2. Check key cache — is the disk offset known?
// 3. Read row index from SSTable → find exact page
// 4. Read the actual data from disk page
// 5. Merge results from all SSTables (multiple versions may exist)
// Reads are more expensive than writes in Cassandra

// ─────────────────────────────────────────────────────────────────
// CASSANDRA CONSISTENCY LEVELS (tunable per query)
// ─────────────────────────────────────────────────────────────────
// ONE:    write/read succeeds when 1 replica responds
//         fastest, weakest consistency
// QUORUM: write/read succeeds when majority (floor(RF/2)+1) responds
//         balanced consistency and availability (RF=3 → 2 nodes needed)
// ALL:    write/read requires all replicas to respond
//         strongest, fails if any replica down
// LOCAL_QUORUM: quorum within the local data centre only
//         good for multi-DC deployments with cross-DC latency

-- Example queries with consistency levels:
INSERT INTO orders_by_customer (...) VALUES (...)
USING CONSISTENCY QUORUM;  -- write to majority of replicas

SELECT * FROM orders_by_customer
WHERE customer_id = ?
LIMIT 20
USING CONSISTENCY ONE;  -- read from nearest replica (fastest, possibly stale)

// HBASE ROW KEY: the primary access mechanism
// Rows are sorted lexicographically by row key
// Row key design is the most critical design decision in HBase

// TABLE: web_crawl
// Row key: reversed URL (com.example.www/page)
// Column family: info (metadata), content (page content), links (outbound links)

// ROW STRUCTURE:
// RowKey           | CF:info                  | CF:content     | CF:links
// com.google.www/  | title:"Google",lang:"en" | <html>...</html>| cnt:3
// com.twitter.www/ | title:"Twitter",lang:"en"| <html>...</html>| cnt:150

// MULTI-VERSION (timestamps):
// Each cell can have multiple versions (timestamped)
// Get the version at a specific timestamp:
// HBase.get(row, cf, col, timestamp)
// Default: return the latest version

// JAVA API (simplified):
Table table = connection.getTable(TableName.valueOf("orders"));

// PUT (write):
Put put = new Put(Bytes.toBytes("customer:C001:2024-03-15:O5001")); // row key
put.addColumn(Bytes.toBytes("order_info"),     // column family
              Bytes.toBytes("status"),          // qualifier
              Bytes.toBytes("delivered"));      // value
put.addColumn(Bytes.toBytes("order_info"),
              Bytes.toBytes("total"),
              Bytes.toBytes("320.00"));
table.put(put);

// GET (read by row key):
Get get = new Get(Bytes.toBytes("customer:C001:2024-03-15:O5001"));
Result result = table.get(get);
byte[] status = result.getValue(Bytes.toBytes("order_info"),
                                Bytes.toBytes("status"));

// SCAN (range scan on row keys):
Scan scan = new Scan();
scan.withStartRow(Bytes.toBytes("customer:C001:2024-03-01"));
scan.withStopRow(Bytes.toBytes("customer:C001:2024-03-31ÿ"));
// Scans all orders for C001 in March 2024 — leverages sorted row key order

// ROW KEY DESIGN FOR HOT SPOT AVOIDANCE:
// BAD: timestamp prefix — all recent writes go to same region (hot region)
//   20240315:C001:O5001
// GOOD: hash prefix + data
//   [md5(C001) first 4 chars]:C001:20240315:O5001
// Distributes writes across all regions evenly

// DATA MODEL:
// Nodes: represent entities — (Customer), (Restaurant), (MenuItem), (Order)
// Relationships: represent connections — [:PLACED], [:ORDERED], [:SERVES], [:FOLLOWS]
// Properties: key-value attributes on both nodes and relationships

// CREATE nodes:
CREATE (rahul:Customer {id: 'C001', name: 'Rahul Sharma', city: 'Bengaluru'})
CREATE (biryani_house:Restaurant {id: 'R01', name: 'Biryani House', rating: 4.5})
CREATE (chicken_biryani:MenuItem {id: 'M001', name: 'Chicken Biryani', price: 280})

// CREATE relationships:
MATCH (c:Customer {id: 'C001'}), (r:Restaurant {id: 'R01'})
CREATE (c)-[:PLACED_ORDER {order_id: 'O5001', date: '2024-03-15', total: 320}]->(r)

MATCH (r:Restaurant {id: 'R01'}), (m:MenuItem {id: 'M001'})
CREATE (r)-[:SERVES]->(m)

// ─────────────────────────────────────────────────────────────────
// TRAVERSAL QUERIES — where graph databases shine
// ─────────────────────────────────────────────────────────────────

// "What has Rahul ordered?" (one hop)
MATCH (c:Customer {name: 'Rahul Sharma'})-[:PLACED_ORDER]->(r:Restaurant)
-[:SERVES]->(m:MenuItem)
RETURN m.name, r.name

// "Who does Rahul follow?" (social graph, one hop)
MATCH (c:Customer {id: 'C001'})-[:FOLLOWS]->(followed:Customer)
RETURN followed.name, followed.city

// "Find friends of friends (2 hops)" — expensive in SQL, natural in graph
MATCH (c:Customer {id: 'C001'})-[:FOLLOWS*2]->(fof:Customer)
WHERE NOT (c)-[:FOLLOWS]->(fof) AND fof <> c
RETURN DISTINCT fof.name
// *2 = exactly 2 hops. *1..3 = 1 to 3 hops (variable length traversal)

// "Shortest path between two users" — impossible efficiently in SQL
MATCH path = shortestPath(
  (start:Customer {id: 'C001'})-[:FOLLOWS*]-(end:Customer {id: 'C099'})
)
RETURN path, length(path)

// "FRAUD DETECTION: find accounts sharing the same device ID"
MATCH (a1:Account)-[:USED_DEVICE]->(d:Device)<-[:USED_DEVICE]-(a2:Account)
WHERE a1 <> a2
  AND a1.flagged = false
  AND a2.flagged = true     -- one known fraudulent account
RETURN a1.id, a1.email, d.device_id
// Instantly finds accounts connected to known fraudsters through shared devices
// In SQL: would require multiple self-joins on a transactions table

// "RECOMMENDATION: customers who ordered from the same restaurant"
MATCH (target:Customer {id: 'C001'})
      -[:PLACED_ORDER]->(r:Restaurant)
      <-[:PLACED_ORDER]-(similar:Customer)
WHERE similar <> target
WITH similar, COUNT(r) AS shared_restaurants
ORDER BY shared_restaurants DESC
LIMIT 10
MATCH (similar)-[:PLACED_ORDER]->(rec_rest:Restaurant)
WHERE NOT (target)-[:PLACED_ORDER]->(rec_rest)
RETURN rec_rest.name, COUNT(*) AS popularity
ORDER BY popularity DESC
LIMIT 5
// "Restaurants ordered by similar customers that Rahul hasn't tried yet"

// INFLUXDB DATA MODEL:
// Measurement: like a table (cpu_usage, http_requests, order_rate)
// Tags: indexed metadata (host, region, service) — low cardinality
// Fields: actual measurements (value, count, latency) — high cardinality
// Timestamp: nanosecond precision

// WRITE (Line Protocol):
// measurement,tag_key=tag_value field_key=field_value timestamp
cpu_usage,host=web-01,region=ap-south-1 usage_percent=72.4,load_avg=2.1 1710499200000000000
http_requests,service=order-api,status=200 count=1450,avg_latency_ms=12.4 1710499200000000000
order_rate,city=Bengaluru placed=847,delivered=801,cancelled=23 1710499200000000000

// INFLUXQL QUERIES (SQL-like):
SELECT mean(usage_percent)
FROM cpu_usage
WHERE host='web-01'
  AND time >= now() - 1h
GROUP BY time(5m)
-- Average CPU usage per 5-minute window in the last hour

SELECT max(avg_latency_ms), count(count)
FROM http_requests
WHERE service='order-api'
  AND time >= '2024-03-15T00:00:00Z'
  AND time < '2024-03-16T00:00:00Z'
GROUP BY time(1h)
-- Hourly max latency and request count for March 15

// RETENTION POLICIES: automatically delete old data
CREATE RETENTION POLICY "30_days"
ON "metrics_db"
DURATION 30d
REPLICATION 1
DEFAULT;
-- Data older than 30 days is automatically purged

// ─────────────────────────────────────────────────────────────────
// TIMESCALEDB: time-series extension for PostgreSQL
// ─────────────────────────────────────────────────────────────────
-- TimescaleDB adds time-series superpowers to PostgreSQL
-- Use full SQL, JOINs with other PostgreSQL tables, same connection pooling

-- Create a hypertable (automatically partitioned by time):
CREATE TABLE cpu_metrics (
    time        TIMESTAMPTZ NOT NULL,
    host        TEXT NOT NULL,
    region      TEXT,
    usage_pct   DOUBLE PRECISION,
    load_avg    DOUBLE PRECISION
);
SELECT create_hypertable('cpu_metrics', 'time');
-- TimescaleDB automatically partitions by time chunks (e.g., 7-day chunks)
-- Each chunk is a separate PostgreSQL table internally
-- Old chunks can be compressed or moved to cheaper storage

-- CONTINUOUS AGGREGATE (materialised time-bucket views, auto-updated):
CREATE MATERIALIZED VIEW cpu_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS hour,
    host,
    AVG(usage_pct) AS avg_usage,
    MAX(usage_pct) AS peak_usage
FROM cpu_metrics
GROUP BY time_bucket('1 hour', time), host
WITH NO DATA;

-- Schedule automatic refresh:
SELECT add_continuous_aggregate_policy('cpu_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

-- QUERY the continuous aggregate (fast — reads pre-computed results):
SELECT hour, host, avg_usage
FROM cpu_hourly
WHERE hour >= NOW() - INTERVAL '24 hours'
ORDER BY hour DESC;

// INDEX A DOCUMENT (store a product):
PUT /products/_doc/P001
{
  "product_id": "P001",
  "name": "Chicken Biryani",
  "description": "Fragrant basmati rice cooked with tender chicken and aromatic spices",
  "restaurant": "Biryani House",
  "city": "Bengaluru",
  "price": 280,
  "rating": 4.5,
  "tags": ["non-veg", "rice", "spicy"],
  "available": true
}

// FULL-TEXT SEARCH (the killer feature):
GET /products/_search
{
  "query": {
    "match": {
      "description": "chicken rice spicy"  // tokenised, stemmed, scored by relevance
    }
  }
}

// MULTI-FIELD SEARCH WITH BOOSTING:
GET /products/_search
{
  "query": {
    "multi_match": {
      "query": "biryani",
      "fields": ["name^3", "description", "tags"]
      // ^3: name field matches are 3x more relevant than description matches
    }
  }
}

// FILTERED SEARCH (bool query: text search + exact filters):
GET /products/_search
{
  "query": {
    "bool": {
      "must": [
        { "match": { "name": "biryani" } }  // full-text on name
      ],
      "filter": [
        { "term":  { "city": "Bengaluru" } },  // exact match (not scored)
        { "term":  { "available": true } },
        { "range": { "price": { "gte": 100, "lte": 500 } } }
      ]
    }
  },
  "sort": [
    { "rating": "desc" },
    "_score"             // sort by rating, then relevance score
  ]
}

// AGGREGATIONS (like GROUP BY + analytics):
GET /products/_search
{
  "size": 0,             // don't return documents, only aggregations
  "aggs": {
    "by_city": {
      "terms": { "field": "city" },  // group by city
      "aggs": {
        "avg_price": { "avg": { "field": "price" } },
        "price_ranges": {
          "range": {
            "field": "price",
            "ranges": [
              { "to": 200 },
              { "from": 200, "to": 500 },
              { "from": 500 }
            ]
          }
        }
      }
    }
  }
}

// ELASTICSEARCH IN PRODUCTION:
// Never use as your primary transactional database — no ACID, no joins
// Use as a secondary index: write to PostgreSQL (source of truth),
// sync to Elasticsearch for search, use Elasticsearch for search queries only
// Tools for sync: Debezium (CDC from PostgreSQL to ES), Logstash, custom ETL

// SWIGGY PRODUCTION DATABASE ARCHITECTURE (typical for Indian unicorns):

// POSTGRESQL (primary transactional data):
// - Orders, customers, payments, restaurants, menu items
// - Full ACID, complex queries, reporting
// - Streamed to all other systems via Debezium CDC

// REDIS (caching + real-time features):
// - Session tokens (TTL-based auto-expiry)
// - OTP storage (short TTL, high throughput)
// - Restaurant availability flag (frequently checked, rarely changed)
// - Sorted sets for real-time order queue per delivery zone
// - Rate limiting (INCR with TTL per API key per minute)

// CASSANDRA (delivery tracking):
// - GPS location history: write 10 updates/second per active delivery agent
// - 50,000 active agents at dinner peak = 500,000 writes/second
// - Queries: latest location for an agent, location history for a delivery
// - Perfect fit: high write throughput, simple query by agent_id

// ELASTICSEARCH (search):
// - Restaurant search with full text, geo-filtering, cuisine facets
// - Menu item search with relevance ranking
// - Sync'd from PostgreSQL via Debezium → Kafka → Elasticsearch consumer

// MONGODB (content management):
// - Restaurant banners, promotional content, onboarding questionnaires
// - Schema evolves frequently, not transactional
// - Marketing team writes directly via a CMS

// INFLUXDB / PROMETHEUS (metrics):
// - API latency per endpoint per second
// - Database connection pool utilisation
// - Order funnel metrics (placed/confirmed/delivered rates per city per hour)
// - Never joins with business data — purely operational

// APACHE KAFKA (event streaming):
// - Order state machine events (ORDER_PLACED → CONFIRMED → PREPARING → ...)
// - Decouples producers from consumers
// - All other databases consume from Kafka to stay in sync

// SNOWFLAKE (data warehouse):
// - Historical data from all above systems via daily/hourly ETL
// - Business intelligence, executive dashboards, ML feature engineering
// - Columnar, massive parallelism, no production query load

// THE PRINCIPLE:
// PostgreSQL = source of truth for business entities
// Other databases = purpose-built views or caches of that truth