Most performance problems do not begin in the browser, the load balancer, or the application framework. They begin quietly in the database: a query that scans too much data, an index that supports yesterday’s access pattern, a transaction held open while code waits on something else.

That is why database design deserves a central place in system design. Your application can have clean PHP services, sensible API boundaries, and containers that start in seconds, but a database that does unnecessary work will eventually make all of those strengths feel irrelevant.

Performance is usually a data-access problem

Backend performance is fundamentally about the amount of useful work performed for each request. When an API endpoint needs one customer and their recent orders, the ideal path is direct: locate the customer efficiently, retrieve the required orders efficiently, return a deliberately small response.

The expensive path is less obvious. It might load every order, filter in PHP, make extra queries for each related record, serialize unnecessary columns, and keep a transaction open during the process. Each individual decision may look harmless. Together, they create slow requests, busy database connections, and unstable latency under load.

A useful design question is: what data does this operation need, and how will the database find it? Asking that before writing an endpoint often prevents a great deal of later tuning.

Model for the queries you need to run

Normalization remains a strong default because it protects data integrity and makes updates easier to reason about. But a schema should also reflect how the system reads and writes data. A perfectly normalized model can still perform poorly if common queries lack appropriate indexes or require excessive joins for a simple request.

Suppose an order history API commonly fetches a customer’s newest orders:

SELECT id, status, total_amount, created_at
FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC
LIMIT 20;

An index beginning with customer_id and including created_at supports the filtering and ordering pattern:

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at DESC);

The exact index behavior depends on the database engine, so it should be checked with that engine’s query-plan tools. The broader lesson is stable: index the predicates and ordering patterns that matter, rather than indexing every column in hope.

Every index has a cost. It consumes storage, adds work to inserts and updates, and can complicate operational choices. An index is justified when it supports a known and important access pattern. It is not a substitute for understanding the query.

Read query plans before guessing

When a query is slow, inspect its execution plan. Look for full scans on large tables, joins performed in an unexpected order, sorts over far more rows than the endpoint returns, and estimates that differ sharply from reality. Query plans turn performance discussions from intuition into evidence.

Also inspect the query itself. Selecting * is convenient but often wasteful. API handlers should request the columns they actually need, particularly when tables contain large text fields, JSON documents, or fields intended only for internal workflows.

Keep database work close to the database

A common backend mistake is fetching a broad dataset and applying filtering, grouping, or pagination in PHP. PHP is excellent for application rules and response composition. The database is usually better at set-based filtering, aggregation, ordering, and joining.

For example, pagination should normally happen in SQL, with a deterministic order. For deep result sets, cursor-based pagination is often more predictable than a large offset because it lets the next request continue from a known position.

SELECT id, status, created_at
FROM orders
WHERE customer_id = ?
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This requires an ordering that is stable even when multiple rows share the same timestamp. The pair created_at, id makes the boundary explicit. The matching index should be designed and verified for the database in use.

Avoid the hidden cost of N+1 queries

N+1 queries occur when code loads a collection and then issues another query for every item in that collection. An endpoint may look fast with three records in development and become painfully slow with hundreds in production.

In PHP, the safer default is to fetch related data in a planned query, a constrained join, or a small number of batched queries. Prepared statements should still be used for values supplied at runtime:

$statement = $pdo->prepare(
    'SELECT id, status, total_amount, created_at
     FROM orders
     WHERE customer_id = :customer_id
     ORDER BY created_at DESC
     LIMIT 20'
);

$statement->execute(['customer_id' => $customerId]);
$orders = $statement->fetchAll(PDO::FETCH_ASSOC);

This improves both safety and clarity. It also gives the team one visible query to measure, explain, and optimize.

Transactions should be short and purposeful

Transactions protect consistency, but they are not a general wrapper for all request processing. A transaction that includes remote HTTP calls, file processing, lengthy calculations, or user interaction can hold locks longer than necessary. Under concurrency, that creates contention and can turn normal traffic into a backlog.

Define the smallest unit of work that must succeed or fail together. Validate input before opening the transaction when possible. Perform only the required database changes inside it, commit promptly, and handle failures with a clear rollback path.

$pdo->beginTransaction();

try {
    $update->execute(['status' => 'paid', 'id' => $orderId]);
    $insertEvent->execute(['order_id' => $orderId, 'type' => 'paid']);

    $pdo->commit();
} catch (Throwable $exception) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    throw $exception;
}

For operations that may be retried, design for idempotency. A retry after a timeout must not accidentally create duplicate orders, payments, or events. Unique constraints and carefully chosen idempotency keys are often more reliable than application-level assumptions.

Make operational limits part of the design

Docker makes it easy to run an application and database together, but containers do not remove database constraints. Connection limits, disk latency, memory pressure, backups, migrations, and replica lag remain system-design concerns.

Connection management is particularly important for PHP applications. If each application process can create connections without a clear limit, a traffic spike can overwhelm the database before CPU usage looks alarming. Size application concurrency around the database’s connection capacity, leave room for administrative access, and measure active connections during realistic load.

Caching can reduce repeated reads, but it should follow understanding, not replace it. Cache data with a clear ownership model, expiration strategy, and invalidation approach. A cache that cannot safely become stale is not a simple cache; it is another consistency system that must be designed accordingly.

Design for change, not just today’s benchmark

The most durable database decisions make future changes safer. Use migrations that can be applied repeatedly and reviewed like application code. Add constraints where the business rule belongs in the data model. Monitor slow queries and error rates. Test representative data volumes, not only empty local databases.

The database is not a passive storage layer at the end of an architecture diagram. It is an active execution engine, a consistency boundary, and often the most valuable source of truth in the system. Treat it with that level of care, and it becomes the unsung hero: doing less unnecessary work, preserving correctness under pressure, and giving the rest of the application room to stay fast.