A database can work perfectly with 500 records and still become the reason your company loses money when it reaches 5 million. An API can return the right response in development and still collapse when 10,000 users arrive at once. Production exposes assumptions that development quietly hides.

Server racks representing production infrastructure
The failure often comes from many small costs multiplied by traffic; fix the workload and its limits before simply adding hardware.

The Production Failure Pattern

Inexperienced teams often optimize for “does it work?” before they optimize for “does it keep working under pressure?” That difference is where many expensive failures begin.

A developer writes a query that works. A product manager sees the correct screen. Tests pass. The system ships. Months later, the table grows, traffic increases, several users perform the same operation simultaneously, and suddenly the database becomes the bottleneck for the entire application.

1. Full-Table Scans Everywhere

Bad practice: repeatedly asking the database to inspect every row to find a small set of records.

Simple example: imagine a library with 500,000 books and you ask the librarian for one book by title. Without a catalogue, they walk through every shelf until they find it. An index is the catalogue.

SELECT * FROM users WHERE email = 'person@example.com';

Without an appropriate index, this can become a full-table scan as the table grows.

Fix: index columns used frequently for filtering, joins, ordering, and uniqueness, while checking actual query plans with tools such as EXPLAIN and EXPLAIN ANALYZE.

Consequence: higher CPU and I/O, slower requests, database saturation, larger infrastructure bills, and eventually timeouts.

2. N+1 Queries

Bad practice: fetching a list and then performing another query for each item.

Simple example: instead of asking a waiter for ten meals in one order, you call the waiter ten separate times. The food is the same; the overhead is not.

SELECT * FROM orders;
-- then one customer query for every order

Fix: use joins, eager loading, batching, or carefully designed data-access methods. Measure query counts instead of assuming an ORM is doing something efficient.

Consequence: one page can turn into hundreds or thousands of database round trips, increasing latency and connection pressure.

3. SELECT *

Bad practice: retrieving every column when the application needs only two or three.

Simple example: if you need someone's name, do not move their entire filing cabinet across town.

Fix: select only the fields required by the operation. This reduces network transfer, memory usage, serialization work, and database I/O.

4. Unbounded Queries and Missing Pagination

Bad practice: returning every record from an endpoint.

Scenario: GET /users returns 50 users in development. Two years later it returns 4 million.

Fix: paginate deliberately. Use limit/offset where appropriate, and cursor/keyset pagination for large or frequently changing datasets. Put sensible maximum page sizes on the server; never trust the client to choose an unlimited limit.

Consequence: memory spikes, slow responses, excessive bandwidth, timeouts, and expensive database work.

5. Long Transactions

Bad practice: opening a transaction and keeping it alive while doing slow work such as network calls, file processing, or user interaction.

Simple example: one person holds the only checkout counter open while they walk around the supermarket looking for items. Everyone else waits.

Fix: keep transactions short, perform external calls outside the transaction when safe, and commit only the atomic database work that actually needs transactional guarantees.

Consequence: locks stay around longer, connections remain occupied, contention increases, and unrelated requests become slow.

6. Lock Contention and Deadlocks

Bad practice: allowing concurrent operations to acquire locks in inconsistent ways or update large ranges unnecessarily.

Simple example: two people each hold one key and wait for the other person's key. Nobody can continue.

Fix: keep transactions short, access shared resources in a consistent order, update only the rows required, use appropriate isolation levels, and monitor lock waits and deadlocks.

Consequence: stalled requests, retries, transaction rollbacks, cascading latency, and sometimes application-wide incidents.

7. Missing Constraints and Integrity Rules

Bad practice: trusting application code alone to prevent invalid data.

Fix: use primary keys, foreign keys, unique constraints, check constraints, and appropriate nullability rules. Application validation improves user experience; database constraints protect the data itself.

Consequence: duplicate records, orphaned data, inconsistent relationships, and expensive cleanup later.

8. Bad Indexes Are Also a Problem

Bad practice: indexing everything.

Indexes speed reads but consume storage and add work to inserts, updates, and deletes. Five indexes on a table do not automatically make it five times faster.

Fix: index based on real query patterns, composite-column ordering, selectivity, and measured execution plans. Remove unused or redundant indexes.

9. Missing Connection Pooling

Bad practice: opening a fresh database connection for every request or allowing unlimited connections.

Simple example: instead of keeping a small fleet of taxis available, you manufacture a new taxi every time someone needs a ride.

Fix: use bounded connection pools, configure sensible pool sizes, timeouts, and lifecycle management, and understand the maximum connection capacity of the database.

10. No Timeouts

Bad practice: letting database or API operations wait indefinitely.

Fix: define connection, query, upstream, and request timeouts. A failed request is often safer than a request that occupies scarce resources forever.

11. Retry Storms

Bad practice: retrying every failure immediately and indefinitely.

Simple example: if a shop is closed and 1,000 people keep knocking every second, the knocking does not reopen the shop; it makes the crowd harder to manage.

Fix: use bounded retries, exponential backoff with jitter, idempotency keys, circuit breakers where appropriate, and clear retryable/non-retryable error classes.

12. Weak API Contracts

Bad practice: APIs that accept unpredictable input, return inconsistent shapes, expose internal database structures, or change responses without versioning or compatibility planning.

Fix: define schemas, validate input at the boundary, standardize errors, document contracts, version breaking changes, and keep database implementation details behind the API.

13. Missing Rate Limits

Bad practice: allowing a client to call expensive endpoints without limits.

Scenario: one buggy frontend loop sends 500 requests per second to a search endpoint. The database does not know that the traffic came from one bug; it simply does the work.

Fix: rate-limit by user, API key, IP, tenant, or endpoint cost. Add quotas for particularly expensive operations.

14. No Input Validation or Size Limits

Bad practice: accepting enormous payloads, invalid filters, unexpected types, or expensive query parameters.

Fix: validate types, ranges, lengths, allowed fields, sort directions, page sizes, and request body sizes before expensive processing begins.

15. Treating Caching as a Database Fix

Bad practice: putting Redis in front of an inefficient query and declaring the database problem solved.

Fix: optimize the source query first, then cache expensive stable results where caching actually fits. Define TTLs, invalidation rules, cache keys, and memory limits.

16. No Backup and Restore Testing

Bad practice: saying “we have backups” without proving that they can be restored.

Simple example: owning a spare tyre is not useful if you have never checked whether it fits the car.

Fix: automate backups, keep copies isolated from the primary system, encrypt them, define retention, and perform scheduled restore drills. Measure RPO and RTO.

17. Schema Changes Without Migration Discipline

Bad practice: manually changing production tables or deploying application code that assumes a schema change exists before the migration is safely applied.

Fix: version migrations, make deployments backward-compatible where necessary, understand locking behaviour of the target database, and rehearse large migrations against production-sized data.

18. No Observability

Bad practice: discovering database trouble only after users complain.

Fix: monitor latency, error rate, throughput, connection usage, slow queries, lock waits, cache hit rates, replication lag, disk usage, and saturation. Logs tell you what happened; metrics show how often; traces show where time went.

The API Layer Has Its Own Failure Modes

The Real Cost

These mistakes cost more than server bills. They consume engineering hours, create customer-facing downtime, cause data cleanup projects, increase support volume, delay releases, and force teams to scale infrastructure to compensate for inefficient software.

The most expensive production bug is often not a dramatic bug. It is a small inefficiency repeated millions of times.

The Production Checklist

Production engineering is not about predicting every failure. It is about refusing to build systems that depend on everything going right.

Sources and further reading