Database Performance Fundamentals
Original educational reference guide
This guide presents practical concepts, trade-offs, and review questions for software practitioners.
Each chapter is written as a standalone learning section.
Page 1
1. Measure Before Tuning
Performance work begins with evidence. Capture slow queries, execution frequency, latency
percentiles, rows examined, lock waits, and resource utilization. A query that is individually slow but
runs once a day may matter less than a moderately slow query executed thousands of times per
minute. Establish a representative workload before changing indexes or configuration.
Review questions
- What is the main purpose of measure before tuning?
- Which trade-off in this area should a team make explicit?
- What evidence or measurement would show that the approach is working?
Practical exercise
Choose a small real or hypothetical system. Identify one current weakness related to this chapter, propose a limited
improvement, describe how you would test it, and state what result would justify keeping the change.
Page 2
2. Indexes
Indexes trade write cost and storage for faster lookup. Index columns used selectively in filters, joins,
and ordering, but avoid creating an index for every query. Composite index order matters because
databases can efficiently use leading columns for many access patterns. Remove redundant indexes
after confirming they are not supporting constraints or important queries.
Review questions
- What is the main purpose of indexes?
- Which trade-off in this area should a team make explicit?
- What evidence or measurement would show that the approach is working?
Practical exercise
Choose a small real or hypothetical system. Identify one current weakness related to this chapter, propose a limited
improvement, describe how you would test it, and state what result would justify keeping the change.
Page 3
3. Query Plans
Execution plans reveal how the optimizer intends to access tables, join data, and sort results. Look
for unexpected full scans, large intermediate row counts, repeated nested operations, and sorts that
spill to disk. Estimated and actual row counts can differ when statistics are stale or data is highly
skewed. Use plans to form hypotheses, then benchmark changes.
Review questions
- What is the main purpose of query plans?
- Which trade-off in this area should a team make explicit?
- What evidence or measurement would show that the approach is working?
Practical exercise
Choose a small real or hypothetical system. Identify one current weakness related to this chapter, propose a limited
improvement, describe how you would test it, and state what result would justify keeping the change.
Page 4
4. Schema Design
A clear relational schema protects data integrity and makes common operations predictable.
Normalize data to avoid inconsistent duplication, then denormalize selectively when measurements
justify it. Choose data types that match the domain and avoid storing structured values in opaque
strings. Constraints such as foreign keys and uniqueness rules prevent invalid states from spreading
into application logic.
Review questions
- What is the main purpose of schema design?
- Which trade-off in this area should a team make explicit?
- What evidence or measurement would show that the approach is working?
Practical exercise
Choose a small real or hypothetical system. Identify one current weakness related to this chapter, propose a limited
improvement, describe how you would test it, and state what result would justify keeping the change.
Page 5
5. Transactions and Locking
Transactions provide atomicity but can create contention when they stay open too long or touch
resources in inconsistent order. Keep transactional work focused and avoid slow network calls inside
a transaction. Understand the isolation level used by the application and the anomalies it prevents.
Monitor deadlocks and lock waits rather than treating them as random failures.
Review questions
- What is the main purpose of transactions and locking?
- Which trade-off in this area should a team make explicit?
- What evidence or measurement would show that the approach is working?
Practical exercise
Choose a small real or hypothetical system. Identify one current weakness related to this chapter, propose a limited
improvement, describe how you would test it, and state what result would justify keeping the change.
Page 6
6. Connection Management
Opening a database connection is relatively expensive, so applications usually use a bounded pool.
A larger pool is not always faster; too many active sessions can overwhelm the database and
increase contention. Set acquisition and query timeouts, release connections promptly, and monitor
pool saturation. Capacity should be coordinated across all application instances.
Review questions
- What is the main purpose of connection management?
- Which trade-off in this area should a team make explicit?
- What evidence or measurement would show that the approach is working?
Practical exercise
Choose a small real or hypothetical system. Identify one current weakness related to this chapter, propose a limited
improvement, describe how you would test it, and state what result would justify keeping the change.
Page 7
7. Caching
Caching can reduce database load for frequently read data, but it introduces invalidation and
consistency questions. Define what staleness is acceptable and how entries expire or are refreshed.
Cache keys must include every dimension that affects the result. Protect against stampedes when
many requests simultaneously miss an expensive entry.
Review questions
- What is the main purpose of caching?
- Which trade-off in this area should a team make explicit?
- What evidence or measurement would show that the approach is working?
Practical exercise
Choose a small real or hypothetical system. Identify one current weakness related to this chapter, propose a limited
improvement, describe how you would test it, and state what result would justify keeping the change.
Page 8
8. Operational Checklist
Track database CPU, memory, disk latency, connection counts, replication health, backups, and
storage growth. Test restoration procedures instead of assuming backups are usable. Review the
slow-query workload regularly because application behavior changes over time. Performance tuning
is an ongoing feedback loop between measurements, application changes, and database design.
Review questions
- What is the main purpose of operational checklist?
- Which trade-off in this area should a team make explicit?
- What evidence or measurement would show that the approach is working?
Practical exercise
Choose a small real or hypothetical system. Identify one current weakness related to this chapter, propose a limited
improvement, describe how you would test it, and state what result would justify keeping the change.
Page 9
Conclusion and Further Practice
Use this guide as a starting point rather than a substitute for experience. Select one chapter, apply its
ideas to a small project, record the result, and discuss the trade-offs with a peer. Revisit the guidance
as the system and team evolve. Good engineering practice depends on context, evidence, clear
communication, and incremental improvement.
Suggested follow-up: write a one-page retrospective describing the problem, the change attempted,
the evidence collected, and what you would do differently next time.
Page 10