Database Performance

Database Performance is the management of resources so the system can retrieve and save data quickly, supporting increased usage volumes without affecting user experience.

Cheat Sheet: Core Performance Concepts

Practical Examples

1. Query Optimization with Indexing

Scenario: The users table has 10 million records. Searching for a user by email takes more than 3 seconds. Practice: Using the EXPLAIN or EXPLAIN ANALYZE command will show that the Database is performing a Sequential Scan (scanning every row). Solution: Create an Index on the email column.

CREATE INDEX idx_users_email ON users(email);

After creating the Index, the Database can search for data through a B-Tree structure, reducing search time from seconds to just a fraction of a millisecond (Index Scan).

2. Caching Expensive Queries

Scenario: The system's dashboard must display a daily sales summary, which involves a complex calculation query (Aggregations) that takes 5 seconds to run. Practice: Instead of having the Database recalculate every time a user opens the Dashboard, we can apply Redis for Caching. Workflow:

1. Application checks if sales data is in Redis.
2. If yes (Cache Hit): Send data to the user immediately.
3. If no (Cache Miss): Send Query to the Database, store the result in Redis (setting a Time-To-Live or TTL, e.g., 10 minutes), and send data to the user. Result: The system can support many simultaneous users without creating a load on the Database.

3. Connection Pooling for High Concurrency

Scenario: The application experiences a sudden spike in traffic, causing the system to show a "Too many connections" error. Practice: Most databases have limits on supporting concurrent connections. Creating new connections has high overhead at the Network and Authentication levels. Solution: Place a Connection Pooler (like PgBouncer for PostgreSQL) as a middleman between the Application and Database.

• Application will connect to the Connection Pool.
• Connection Pool will manage and reuse actual connections to the Database.
• When there are too many requests, they will be queued instead of overloading and crashing the Database.

4. Avoiding N+1 Query Problem

Scenario: Displaying a list of articles along with the author's name through an ORM. Practice: Retrieving data the wrong way can result in a massive amount of SQL queries. Bad Practice (N+1):

const posts = await Post.find(); // 1 Query
for (let post of posts) {
  const author = await User.findById(post.authorId); // N Queries
}

Good Practice (Eager Loading / Joins):

const posts = await Post.find().populate('author'); // 1 Optimized Query

Adjusting the Query Shape with Joins or Eager loading significantly reduces the time the system spends communicating with the Database.