Insights:Comparing 3 Website Architecture & Database Approaches
Option 1: Single-Server Direct Connection (App + Database on Same Machine)
Architecture: Web app and database run on the same server with no caching layer.
- Best for: Fewer than a few thousand daily visits, under one million rows of data.
- Advantages: Simple deployment, fewer failure points, lowest cost.
- Disadvantages: App and database compete for resources; high traffic slows both down.
- Typical bottleneck: Slow queries max out CPU, dragging down web response times.
When it's enough: Admin backends, corporate websites, small content sites.
Option 2: Read/Write Splitting / Dedicated Database
Architecture: App and database on separate machines; master handles writes, replicas handle reads.
- Best for: Read-heavy workloads, millions to tens of millions of rows.
- Advantages: Read pressure is distributed, scaling path is clear.
- Disadvantages: Master-slave lag can cause stale reads; operations complexity increases.
- Typical bottleneck: Replica lag prevents reading newly committed content.
Note: On content sites, querying replicas right after publishing may show "published successfully" but still display old pages. Force critical paths through the master.
Option 3: Cache-First (Page Cache + Data Cache)
Architecture: Cache static and semi-static pages at the edge; cache hot data at the app layer.
- Best for: Workloads with clear hotspots and controllable content update frequency.
- Advantages: Database load drops significantly, response times improve dramatically.
- Disadvantages: Cache consistency requires careful design; invalidation must be proactive after updates.
- Typical bottleneck: Poor cache key design leads to per-user duplicate caches.
Note: Caching is not a replacement for databases—it simply intercepts requests before they reach the DB.
Comparison of the Three Approaches
| Dimension | Single-Server | Read/Write Split | Cache-First |
|---|---|---|---|
| Data Volume Suitability | Below 1 million rows | 1–10 million rows | Unlimited, depends on hot data |
| Concurrent Capacity | Low | Medium | High |
| Operations Cost | Low | Medium | Medium-high |
| Consistency Risk | Low | Medium (master-slave lag) | High (cache invalidation) |
| Migration Risk | Low | Medium | High |
| Primary Benefit | Simplicity | Read scaling | Response time |
Recommended Implementation Order
- First, write queries well and add proper indexes (highest ROI, lowest risk).
- Then add page-level caching to handle repeated accesses.
- If data volume continues growing, split the database next.
- Only then consider table sharding, database sharding, or a dedicated search service.
Skipping the first two steps and jumping straight to later stages usually turns a simple problem into an unnecessarily complex one.
Decision Criteria
- Is database CPU usage consistently above 60%?
- Are slow queries concentrated in only a few SQL statements?
- Do pages have high repetition that caching could handle?
- Does your team have someone who can troubleshoot master-slave failures at midnight?
If the answer is "no" for any item, don't implement the corresponding solution yet.
Boundaries
All three approaches address capacity and response-time issues; they do not solve content quality or search-engine rankings. No matter how good the architecture is, pages with no value won't attract traffic.
Related reading: