Database Caching Integration

Database caching integration is the practice of placing a cache layer between an application and a database to reduce latency, decrease load on the database, and improve throughput under read-heavy workloads. It matters when database queries become a bottleneck—when 30% or more of request latency comes from database roundtrips, or when the database CPU exceeds 70% utilization during peak traffic. This integration is not merely adding a cache; it is the orchestration of data flow, consistency, and failure handling across a distributed system.

Query-Level Caching

Caching individual query results at the application or proxy layer allows fast retrieval of frequently accessed data. The most effective pattern involves tagging cache entries with a canonical query key, derived from SQL and parameters. A query such as SELECT * FROM users WHERE id = $1 AND active = $2 becomes a cache key like users:id=123:active=true.

The cache must be primed on read misses. A typical integration uses a middleware layer (e.g., a database access layer in Go, or a Spring Data interceptor) to intercept SELECT queries. When a query is executed, the system first checks the cache before hitting the database.

// Example: Go application using a custom query cache
func (c *CachedDB) Query(ctx context.Context, query string, args ...interface{}) (Rows, error) {
    key := cacheKey(query, args...)
    if cached, ok := c.cache.Get(key); ok {
        return cached, nil
    }

    rows, err := c.db.QueryContext(ctx, query, args...)
    if err != nil {
        return nil, err
    }

    // Store result in cache with 10-minute TTL
    c.cache.Set(key, rows, 10*time.Minute)
    return rows, nil
}

The cache key must be deterministic. The following SQL query produces different keys when parameters are reordered:

SELECT * FROM products WHERE category = 'electronics' AND price > 100

If the parameters are passed as ["electronics", 100], the key is products:category=electronics:price=100. If the order changes to (100, "electronics"), the key becomes products:category=100:price=electronics, causing cache misses for identical queries.

Cache invalidation is critical. A common failure mode is stale data due to missing invalidation. When a product is updated, the cache must be invalidated for all queries that could return it. A single UPDATE products SET name = 'Wireless Earbuds' WHERE id = 5 must trigger invalidation of:

• products:id=5
• products:category=audio:price=50
• products:category=electronics

This is often handled via triggers or application-level events.

Cache-Warmup Strategies

A cold cache leads to high latency during traffic spikes. To avoid this, systems pre-populate the cache during deployment or maintenance windows.

A common warmup script uses a cron job that runs a set of high-traffic queries and stores their results in the cache:

#!/bin/bash
set -euo pipefail

# Fetch product IDs from the most popular categories
product_ids=$(psql -c "SELECT id FROM products WHERE category IN ('electronics', 'books', 'clothing') LIMIT 100" -t)

# For each product ID, load the full record into cache
while IFS= read -r id; do
    psql -c "SELECT * FROM products WHERE id = $id" -t | \
    curl -X PUT \
         -H "Content-Type: application/json" \
         -d @- \
         http://cache-api:8080/set?key=products:id=$id
done <<< "$product_ids"

Warmup can also be triggered by a deployment script. After a new version of the application is deployed, a POST /warmup endpoint is called:

{
  "queries": [
    "SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2023-01-01'",
    "SELECT COUNT(*) FROM users WHERE last_active > NOW() - INTERVAL '1 week'"
  ],
  "strategy": "batched",
  "ttl": "1h"
}

The cache layer responds with a 202 Accepted, and the warmup runs asynchronously.

Cache Invalidation Patterns

Cache invalidation must handle both write-side updates and query-specific events. Two dominant patterns are write-through and event-driven.

In write-through caching, every INSERT, UPDATE, or DELETE operation on the database is mirrored to the cache immediately.

CREATE OR REPLACE FUNCTION cache_user_update()
RETURNS TRIGGER AS $$
BEGIN
    -- Update cache entry
    PERFORM http_post(
        'http://cache-api:8080/set',
        jsonb_build_object(
            'key', 'users:id=' || NEW.id,
            'value', row_to_json(NEW),
            'ttl', '1h'
        )
    );

    -- Invalidate related queries
    PERFORM http_post(
        'http://cache-api:8080/invalidate',
        jsonb_build_object(
            'pattern', 'users:category=',
            'value', NEW.category
        )
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_updated
AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION cache_user_update();

The http_post function is a wrapper that calls a REST API to the cache service, which can be implemented using any standard HTTP client.

Event-driven invalidation uses a message queue (e.g., Kafka, RabbitMQ) to decouple cache updates from database operations. A user_updated event is published to the topic user.events after a UPDATE:

{
  "event": "user.updated",
  "timestamp": "2023-10-05T14:30:00Z",
  "user_id": 123,
  "old": { "name": "Alice", "email": "[email protected]" },
  "new": { "name": "Alice Smith", "email": "[email protected]" }
}

A consumer service reads from the queue and updates the cache:

# Python consumer using Kafka
from confluent_kafka import Consumer

c = Consumer({
    'bootstrap.servers': 'kafka:9092',
    'group.id': 'cache-invalidator',
    'auto.offset.reset': 'earliest'
})

c.subscribe(['user.events'])

while True:
    msg = c.poll(timeout=1.0)
    if msg is None:
        continue

    event = json.loads(msg.value())
    key = f"users:id={event['user_id']}"

    # Update cache
    requests.put(
        "http://cache-api:8080/set",
        json={
            "key": key,
            "value": event["new"],
            "ttl": "1h"
        }
    )

    # Invalidate pattern
    requests.post(
        "http://cache-api:8080/invalidate",
        json={
            "pattern": "users:category=",
            "value": event["new"]["category"]
        }
    )

This pattern decouples cache updates from the main database transaction, reducing latency and increasing resilience.

Cache Consistency and Failure Modes

Cache consistency is the most subtle failure mode. A cache may return stale data even when the database is up to date. The following are common failure scenarios:

• Cache misses after write: A new user is created, but the cache is not updated. Subsequent queries for that user return old data.
• Stale reads: A cache entry is updated, but the database is not. A SELECT query returns data that hasn't been flushed to the database.
• Cache miss on primary key: The cache uses a key format that assumes a specific order of parameters, but the application code or API layer passes parameters in a different order.

A sharp edge: cache entries may be invalidated but never evicted. A CACHE SET key=value ttl=5m keeps the entry in memory for 5 minutes, but if the cache is full, old entries are evicted only when new ones are added. A query that runs every 30 seconds may not trigger eviction, causing a memory leak.

Another failure mode is cache stampedes. When a cache key is invalidated, multiple requests may concurrently miss the cache and execute the same query, leading to redundant database load.

To mitigate this, the cache layer supports dual cache hits: a GET request returns a 200 OK with Cache-Control: max-age=300 and a ETag header. The client uses If-None-Match to avoid re-fetching unchanged data.

GET /users/123 HTTP/1.1
Host: api.example.com
If-None-Match: "abc123"

HTTP/1.1 304 Not Modified
ETag: "abc123"
Cache-Control: max-age=300

A 304 Not Modified response avoids sending the full payload, reducing bandwidth and latency.

Cache Layer Configuration

The cache layer is configured via a standard interface, typically a REST API or a simple config file. The following cache.yaml configures a Redis-backed cache service:

cache:
  backend: redis
  uri: redis://cache.example.com:6379
  default_ttl: 1h
  max_entries: 1000000
  eviction_policy: lru
  stats:
    enabled: true
    interval: 30s
    endpoint: /stats
  warmup:
    enabled: true
    on_startup: true
    queries:
      - "SELECT * FROM categories"
      - "SELECT id, name FROM users WHERE last_active > NOW() - INTERVAL '1 day'"

The cache service exposes a /stats endpoint that returns:

{
  "hits": 1234567,
  "misses": 89123,
  "evictions": 23456,
  "hit_rate": 0.93,
  "memory_used_mb": 1450,
  "cache_size": 1000000,
  "uptime": "24h30m12s"
}

The application monitors this endpoint to detect cache degradation. A drop in hit_rate below 0.9 triggers an alert.

Cache Monitoring and Diagnostics

A common diagnostic tool is a GET /debug/cache endpoint that returns the current state of the cache:

GET /debug/cache?key=users:id=123 HTTP/1.1
Host: cache.example.com
Accept: application/json

Response:

{
  "key": "users:id=123",
  "found": true,
  "value": {
    "id": 123,
    "name": "Alice Smith",
    "email": "[email protected]",
    "created_at": "2023-01-15T10:00:00Z"
  },
  "timestamp": "2023-10-05T14:30:00Z",
  "ttl": "1h",
  "expires": "2023-10-05T15:30:00Z",
  "from": "redis",
  "cache_hit": true
}

If the cache is not found, the response is 200 OK with {"found": false}.

A POST /debug/cache/invalidate endpoint accepts a JSON list of keys or patterns:

{
  "keys": [
    "users:id=123",
    "products:category=electronics"
  ],
  "patterns": [
    "users:category=",
    "products:category="
  ]
}

This endpoint returns a 202 Accepted and triggers background invalidation.

Cache Integration with Database Transactions

When a transaction spans multiple cache operations, consistency must be maintained. A common failure is cache inconsistency during transaction rollback.

Consider a transaction that updates a user and their orders:

BEGIN;

UPDATE users SET balance = balance - 100 WHERE id = 123;
UPDATE orders SET status = 'shipped' WHERE user_id = 123;

-- Cache updates
-- POST /set?key=users:id=123
-- POST /set?key=orders:user_id=123
-- POST /invalidate?pattern=orders:user_id=

COMMIT;

If the second UPDATE fails and the transaction is rolled back, the cache now holds data that does not reflect the database state.

To fix this, the cache layer supports transactional cache updates via a CACHE TX command:

CACHE TX
  SET users:id=123
    VALUE '{"id":123,"name":"Alice","balance":900}'
    TTL 1h
  SET orders:user_id=123
    VALUE '[]'
    TTL 30m
  INVALIDATE
    PATTERN 'orders:user_id='
    VALUE '123'
  COMMIT;

The cache service applies all operations atomically. If any operation fails, the entire transaction is rolled back.

This ensures that cache and database remain consistent across ACID transactions.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.