Insights:What Is Website Architecture and Database: One Diagram to Clarify the Basics
The Complete Path of a Single Request
- The browser initiates a request, which is resolved to an IP address via DNS.
- The request enters the CDN or reverse proxy; if cached, it returns directly.
- If not cached, it is forwarded to the web server (Nginx / Apache).
- The web server passes the request to the application process (PHP-FPM, Java container, etc.).
- The application calls the database or cache.
- Results propagate back layer by layer; cachable portions are written back to the cache layer.
Each layer has a latency budget. For common websites, the total budget should stay under 1 second: DNS and connection 100ms, TTFB 300ms, content transfer around 300ms.
Responsibilities by Layer
| Layer | Main Responsibilities | Common Bottlenecks |
|---|---|---|
| Access Layer | Static resources, caching, rate limiting | Misconfigured cache rules cause dynamic pages to be cached |
| Web Layer | Routing, static files, reverse proxy | Insufficient processes, overly long timeouts |
| Application layer | Business logic, template rendering | Loop queries, redundant queries |
| Data layer | Read/write, indexing, transactions | Full table scans, missing indexes, lock contention |
| Cache layer | Hot data, page caching | Low hit rate, cache stampede |
More layers aren't always better. Two layers are enough for a small site; each additional layer introduces another single point of failure.
Database Read/Write Path
Steps involved in a query:
- Obtain a connection from the connection pool.
- Parse the SQL and check the query cache (if enabled).
- The optimizer generates an execution plan.
Key takeaway: If the execution plan shows a full-table scan, performance will drop sharply as data grows—and it happens suddenly.
Why indexes speed things up
An index replaces "row-by-row comparison" with "ordered-structure location." The cost is:
- Slower writes: every insert must maintain the index.
- Larger footprint: indexes consume additional storage.
- Harder choices: too many indexes can confuse the optimizer into picking a bad plan.
Rule of thumb: index high-frequency exact-match queries and foreign keys; use composite indexes for range queries, placing the most frequently filtered column first.
Three most common bottlenecks
- Querying the database inside a loop: one DB round-trip per list item—20 rows means 20 round-trips.
- Large tables without indexes: e.g., an articles table sorted by time but only has the primary-key index.
- Cache–data inconsistency: failing to invalidate the cache after updates, so users see stale content.
Boundaries
Architecture optimization has diminishing returns and depends on traffic and data volume. For a small site with a few thousand daily visitors, well-tuned indexes and caching are usually enough—you don't need sharding or message queues from day one.
Related reading: