Best Practices for Requests and Storage#

I. On Request Methods#

1. POST vs PUT Feature Comparison#

2. Example Comparison#

3. Misconceptions and Supplements#

• POST can also update: REST does not prohibit POST for updates, but PUT/PATCH are more semantically explicit
• Partial updates: Standard approach is PATCH, because PUT requires full replacement (missing fields will be deleted)
• PUT for resource creation: Allowed, but only if client can provide a unique and final URI
• Some API practices: Only expose POST + PATCH, ignoring PUT
• Idempotency is important for network retries (PUT is safe, POST is not)

Quick Decision Guide#

Client specifies full URI + idempotent (safe retry) + full replacement  →  PUT
URI determined by server + may change state / create resource          →  POST
Partial update + idempotent                                             →  PATCH

II. Backend Storage#

1. Core Principles#

2. Database Selection Decision Tree#

Complex data relationships? Many joins?
├─ Yes → Relational database (PostgreSQL / MySQL)
│        ├─ Strong consistency + complex transactions → PostgreSQL
│        └─ Read-heavy + mature ecosystem → MySQL

Large data volume (TB-level)? Heavy write pressure?
├─ Yes → Distributed database
│        ├─ Strong transactions + horizontal scaling → TiDB / CockroachDB
│        └─ Eventual consistency + ultra-high throughput → Cassandra / HBase

Flexible data structure? No schema?
├─ Yes → Document database (MongoDB)
│        └─ Suitable for: Logs, user profiles, CMS

Need full-text search?
├─ Yes → Elasticsearch (dual-write with primary storage)

Need caching + high performance?
├─ Yes → Redis
│        └─ Suitable for: Sessions, counters, leaderboards, distributed locks

Need graph relationships (social, recommendation)?
├─ Yes → Graph database (Neo4j / Amazon Neptune)

Time-series data (IoT, monitoring)?
├─ Yes → Time-series database (InfluxDB / TimescaleDB / Prometheus)

3. Tiered Storage Architecture#

                    Request Path
                       │
                       ▼
┌──────────────────────────────────────────────────────┐
│                    L1: Cache Layer                    │
│   Redis / Memcached                                   │
│   - TTL 5-15 minutes                                   │
│   - Cache penetration/breakdown/avalanche protection   │
└──────────────────────────────────────────────────────┘
                       │ Miss
                       ▼
┌──────────────────────────────────────────────────────┐
│                    L2: Hot Storage                   │
│   PostgreSQL / MySQL (SSD, master-slave cluster)      │
│   - Last 7-30 days data                               │
│   - Directly accessed by online services              │
└──────────────────────────────────────────────────────┘
                       │ Archival policy
                       ▼
┌──────────────────────────────────────────────────────┐
│                    L3: Warm Storage                  │
│   Archive tables / Partitioned tables / Object storage│
│   - 30 days ~ 1 year data                             │
│   - Available for offline analysis                    │
└──────────────────────────────────────────────────────┘
                       │ Long-term retention
                       ▼
┌──────────────────────────────────────────────────────┐
│                    L4: Cold Storage                  │
│   Object storage (Glacier Deep Archive)               │
│   - Data older than 1 year                            │
│   - Retrieval time 12-48 hours                       │
└──────────────────────────────────────────────────────┘

4. Relational Database Best Practices#

Table Design#

-- ✅ Recommended: Soft delete + audit fields
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    status SMALLINT DEFAULT 1,           -- 1:active 2:disabled 3:deleted
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,              -- NULL = not deleted
    version INT DEFAULT 1                -- Optimistic lock
);

-- ✅ Use UUID as business ID (prevents enumeration / sharding-compatible)
-- ❌ Avoid auto-increment ID exposing business volume

Index Strategy#

Index Golden Rules:

• ≤ 5 indexes per table
• Remove duplicate/redundant indexes decisively
• Use EXPLAIN to verify execution plans
• Monitor index hit rate (unused for > 30 days → drop)

Preventing Slow Queries#

-- Set statement timeout
SET statement_timeout = '5s';

-- ❌ Avoid
SELECT * FROM orders;                           -- Full table scan
SELECT COUNT(*) FROM huge_table;                -- Large table count
WHERE name LIKE '%keyword%';                    -- Leading wildcard
WHERE YEAR(created_at) = 2025;                  -- Function breaks index

-- ✅ Recommended
SELECT id, email, status FROM orders LIMIT 100; -- Limit fields + pagination
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';

5. Redis Caching Best Practices#

Data Structure Selection#

The Three Cache Problems (Penetration, Breakdown, Avalanche)#

# 1. Cache penetration (querying non-existent data)
# Solution: Bloom filter or cache null values (short TTL)
if not bloom_filter.might_contain(key):
    return None
value = cache.get(key)
if value == "NULL":
    return None

# 2. Cache breakdown (hot key expires)
# Solution: Mutex lock / logical expiration
value = cache.get(key)
if value is None:
    with redis_lock(f"lock:{key}"):
        value = db.query()
        cache.set(key, value, ttl=300)

# 3. Cache avalanche (many keys expire simultaneously)
# Solution: Add random value to TTL
ttl = 3600 + random.randint(0, 300)

Memory Control#

# Set max memory
maxmemory 4gb

# Eviction policy (choose one)
maxmemory-policy allkeys-lru     # Most common
# volatile-lru    # Only for keys with expiry
# allkeys-random  # Not recommended
# noeviction      # Reject writes when memory full

6. Writes and Transactions#

Transaction Boundaries#

# ✅ Recommended: Short transactions
@transaction.atomic
def create_order(user_id, items):
    # 1. Create order (1 INSERT)
    order = Order.objects.create(user_id=user_id)
    # 2. Batch insert order items (N INSERTs)
    OrderItem.objects.bulk_create(items)
    # 3. Deduct inventory (1 UPDATE)
    # Total transaction time < 100ms

# ❌ Avoid: Long transactions
@transaction.atomic
def bad_operation():
    # 1. Call external API (may take 5s)
    # 2. Send email (may take 2s)
    # 3. Update database (1 UPDATE)
    # → Holds locks for 7s, blocking other requests

Write Optimization#

7. High Availability and Backup#

           ┌─────────────┐
           │  Load Balancer │
           └──────┬──────┘
                  │
    ┌─────────────┼─────────────┐
    │             │             │
┌───▼───┐     ┌───▼───┐     ┌───▼───┐
│ Master│ ◄─► │ Slave │     │Slave  │  ← Read-write separation
└───┬───┘     └───────┘     └───────┘
    │
    │ Async replication / Semi-sync
    ▼
┌───────────┐
│  Backup   │  ← Daily full backup + WAL archive
└───────────┘

Backup Strategy#

⚠️ Verify backups: Conduct at least one recovery drill per month

Monitoring Metrics#

8. Common Anti-Patterns#

9. Decision Quick Reference#