Database performance problems rarely arrive as a single dramatic outage. More often, they appear as small compromises: a report that takes a little longer, an API endpoint with occasional slow responses, a query that works in development but degrades under production data. The tempting response is to add an index, increase a timeout, or cache the result.

Those measures can help, but predictable performance usually comes from something more durable: refactoring the data model and query patterns so they match the work the system actually performs.

Start with the workload, not the schema

A database schema is not a museum piece. It records earlier assumptions about relationships, ownership, and access patterns. As a product evolves, those assumptions can become expensive. A table that began as a simple log may become a central source for dashboards, exports, and background jobs. A flexible JSON column may become the place where critical filters live.

Before changing tables, identify the operations that matter. Focus on concrete questions:

• Which endpoints are slow or have unpredictable latency?
• Which jobs scan more rows than expected?
• Which queries run frequently enough that modest inefficiency becomes costly?
• Which data relationships are difficult to express or enforce?

This prevents a common mistake: optimizing a query that is interesting rather than important. A query plan, application tracing, slow-query logging, and production-like data volumes provide much better guidance than intuition alone.

Read query plans as design feedback

An execution plan is not merely a tuning artifact. It is feedback on whether the physical layout supports the question being asked. A full table scan is not automatically wrong; scanning a small lookup table may be entirely reasonable. It becomes concerning when a selective request repeatedly examines a large portion of a growing table.

Consider an API that fetches recent paid orders for one customer:

SELECT id, total_cents, paid_at
FROM orders
WHERE customer_id = :customer_id
  AND status = 'paid'
ORDER BY paid_at DESC
LIMIT 20;

If this is a frequent request, an index aligned with the filter and ordering can reduce unnecessary work. The exact choice depends on the database engine and existing indexes, but the principle is stable: index columns should serve a known access path, not simply mirror every field used somewhere in application code.

An index such as (customer_id, status, paid_at) may support this pattern well. Adding individual indexes on all three columns may be less useful because the engine still has to combine or discard many candidates. Verify the plan after the change; do not assume the index is used just because it exists.

Make data shape reflect business rules

Performance and maintainability often improve together when the schema expresses clear ownership. If an invoice has one customer, store the customer reference on the invoice. If an order has multiple line items, use a related table with a foreign key. If a relationship is many-to-many, model the join explicitly and give it constraints that prevent duplicates where duplicates are invalid.

Ambiguous data shape creates expensive application logic. For example, storing a changing set of relational attributes inside a JSON document can make early development fast, but it can complicate filtering, indexing, validation, and reporting later. JSON is useful for genuinely variable payloads. It is a poor substitute for columns that drive core queries or business rules.

Refactoring toward explicit columns does not require an all-at-once rewrite. A safer migration sequence is usually:

1. Add the new nullable column or table.
2. Deploy code that writes both old and new representations.
3. Backfill existing data in controlled batches.
4. Validate that the representations agree.
5. Switch reads to the new structure.
6. Remove the old path only after it is no longer needed.

This approach is less glamorous than a large migration, but it makes rollback and diagnosis far more manageable.

Design migrations for live systems

Schema changes are operational changes. A migration that is harmless on a local database can lock a busy table, consume excessive I/O, or fail halfway through when applied to a large production dataset.

Prefer small, reversible changes. Separate schema changes from large data movement when possible. Backfill in batches keyed by a stable primary key, and make the job safe to resume. For instance, instead of updating every old record in one statement, process bounded ranges and record progress through an application job or a dedicated migration mechanism.

while (true) {
    $rows = $repository->findUnmigratedOrders(500);

    if ($rows === []) {
        break;
    }

    foreach ($rows as $order) {
        $repository->populateNewFields($order);
    }
}

The details depend on the ORM and database, but the operational idea matters: bounded work reduces lock duration, limits transaction size, and makes retries practical. Ensure the update is idempotent. If a worker retries after a timeout, repeating a batch should lead to the same correct state rather than duplicate records or conflicting values.

Keep application and database changes compatible

Deployments become risky when code assumes the new schema exists before the migration runs, or when a migration removes a column still used by older application instances. In systems with rolling deployments, use an expand-and-contract pattern: introduce compatible structures first, update all readers and writers, then retire obsolete structures later.

This is especially important for APIs. A database refactor should not silently change response semantics, pagination behavior, or ordering guarantees. If a query is rewritten, retain explicit ordering. Without ORDER BY, row order is not a contract, even if it appears stable during testing.

Avoid solving every problem with denormalization

Denormalization can make high-value reads faster, but it trades query complexity for consistency work. A stored aggregate such as orders.total_cents may be sensible when it is updated transactionally with line-item changes. A copied customer name on every order may be sensible for an immutable historical snapshot. Both decisions need a clearly defined source of truth.

Use denormalization when measurement shows it is valuable and the update rules are understandable. Otherwise, normalized data with appropriate indexes is often easier to repair, evolve, and trust.

Make performance a property of the system

Predictable performance is not achieved by one heroic query optimization. It comes from habits: measuring real workloads, checking execution plans, adding constraints, making migrations resumable, and treating deployment compatibility as part of database design.

The most effective refactors make the system easier to explain. When a developer can clearly answer where a fact lives, how it changes, which query retrieves it, and what protects its integrity, performance work becomes less reactive. The database stops being a hidden source of surprises and becomes a dependable part of the architecture.