Database Performance

This learning is based on a real production incident that affected customers.

Let’s examine how to properly review database changes and ensure consistent environments, using a real-world example where a missing index led to high CPU utilization and blocked transactions.

The Scenario

A developer added a new query for block finalization without proper indexing. While the changes were tested, they caused production issues because:

The missing index wasn’t caught during code review
Staging environment had different DB configuration (higher specs)
Local development had a much smaller dataset
The issue wasn’t detected in staging due to different configurations
No proper monitoring of DB performance after deployment

Before

query.sql


SELECT * FROM blocks
WHERE finalization_status IS NOT NULL
ORDER BY block_height DESC
LIMIT 100;

PR Comment

Choose the comment that you think is the most constructive and helpful.

Dionne Indigocommented on Apr 2, 2025

The query looks good, but we should test it with production data.

Mariette Bronzecommented on Apr 2, 2025

The query looks correct, but we should verify the performance.

Fionna Greencommented on Apr 2, 2025

The query looks good, but we need to add an index for finalization_status. Can you verify the query performance with EXPLAIN?

Lanny Bluecommented on Apr 2, 2025

Thanks for implementing the block finalization query! Before we merge, let’s ensure we understand the full impact:

Have you added the necessary index for finalization_status?
Can you share the EXPLAIN plan for this query?
Have you tested this with production-scale data?
Should we add monitoring for DB CPU utilization?

This will help us catch any performance issues before deployment.

Click here to learn more

Key Lessons

1. Database Performance

Always review and add necessary indexes
Use EXPLAIN to verify query performance
Test with production-scale data
Monitor DB performance after deployment

2. Environment Consistency

Keep staging and production configurations similar
Test with realistic data volumes
Monitor performance in all environments
Document environment differences

3. Code Review Best Practices

Review database changes carefully
Verify index requirements
Check query performance
Consider production impact

Tips for Reviewers

1. Ask Performance-Focused Questions

Have you added necessary indexes?
What’s the EXPLAIN plan look like?
How does it perform with production data?
Example: “Can you share the EXPLAIN plan for this query?“

2. Verify Testing Strategy

Is testing done with realistic data?
Are environments configured consistently?
Is there proper monitoring?
Example: “Have you tested with production-scale data?“

3. Document Dependencies

List required indexes
Note performance requirements
Document monitoring needs
Example: “This query needs an index on finalization_status”

Common Pitfalls to Avoid

1. Focusing Only on Functionality

❌ “The query works, let’s merge.”
✅ “The query works, but let’s verify its performance.”

2. Ignoring Environment Differences

❌ “It works in staging, so it’s good.”
✅ “It works in staging, but let’s verify the environments match.”

3. Missing Performance Impact

❌ “This is just a query change.”
✅ “This query change affects DB performance, let’s verify it.”

Remember: A good code review considers both functionality and performance. Understanding database impact and maintaining consistent environments helps prevent issues before they reach production!