DB & API Management

Why It Matters: Performance affects every user’s perception of your application. Slow load times, unresponsive interfaces, and long data-fetching delays can lead to user frustration, higher bounce rates, and system inefficiencies. Optimizing app performance improves usability, encourages adoption, and reduces infrastructure costs—especially as the user base scales.

Efficient Data Management

Optimize database queries:

1. Avoid large joins or nested subqueries—especially in real-time reports.

2. Use pagination and limit clauses for large datasets.

✅ More Efficient: JOIN Method

Finding Top-Performing Employees

Let’s solve the following query problem:

Retrieve all employees who have completed at least one project with a budget over $50,000

SELECT e.emp_name
FROM employees e
JOIN projects p ON e.emp_id = p.project_lead_id
WHERE p.project_budget > 50000;

Advantages:

• Executes in a single, optimized operation.

• Scales better with larger datasets by processing sets instead of rows individually.

Why this is the better choice:

JOIN operations allow the database to combine and filter data in one go, significantly reducing overhead. Instead of checking conditions row-by-row like a subquery, the optimizer uses joins to streamline execution.

❌ Less Efficient: Subquery Method

SELECT emp_name
FROM employees
WHERE emp_id IN (
    SELECT project_lead_id
    FROM projects
    WHERE project_budget > 50000
);

Drawbacks:

• The inner query is evaluated repeatedly for each row in employees.

• May lead to unnecessary full scans of the projects table.

Why this is problematic:

When the projects table holds hundreds of thousands of entries, this subquery could strain resources—slowing execution due to repetitive lookups and poor cache utilization.

Optimize API Performance

1. Batch requests instead of making multiple network calls.

2. Use filtered REST endpoints to return only what’s necessary.

3. Monitor latency and throughput using tools like (for pro-dev users):

   • Postman Monitor

   • API Gateway dashboards (AWS/GCP)

✅ Optimized (Batching & Filtering)

// Single batch endpoint returns required combined data
fetch('/api/user/123/overview?include=activity,notifications');

Backend returns only needed data:

{
  "user": { "id": 123, "name": "Jane" },
  "activity": [...],
  "notifications": [...]
}

Tips Applied:

1. Batch requests: Combined user, activity, and notifications into one call.

2. Filtered response: Only included relevant fields via query param ?include=....

❌ Inefficient (Multiple Calls)

// Called individually — creates 3 network requests
fetch('/api/user/123');
fetch('/api/user/123/activity');
fetch('/api/user/123/notifications');

Why it's bad:

• Causes more round trips to the server.

• Slower page load times due to sequential or parallel wait.

• Increases server load and risk of timeouts.

Background Jobs

Offload heavy or non-blocking operations (like PDF generation or data export) to background.

Practical Use: Schedule overnight data aggregation or preloading of reports rather than doing it in real-time

Real-Time Monitoring

Track key metrics like:

• Response time

• Memory usage

• API failure rates

• Load time by user geography

Monitor frontend and backend separately to localize bottlenecks

Scalability

Choose the right database for the right workload:

• Relational DBs (PostgreSQL, MySQL) for structured data and transactions.

• NoSQL DBs (MongoDB, DynamoDB) for flexible schema and high write throughput.

• Time-series DBs (InfluxDB, TimescaleDB) for metrics and logs.

Use Relational DB

E-commerce Application

E.g., PostgreSQL or MySQL

Scenario: Managing orders and payments

• You need to ensure data consistency, enforce relationships between tables (like users, orders, products), and perform complex SQL queries.

• Transactions, foreign keys, and structured schemas are critical for reliable financial records.

Table Example:

Orders Table:
+----------+------------+------------+
| Order ID | User ID    | Total      |
+----------+------------+------------+
| 12345    | 1001       | $249.99    |
+----------+------------+------------+

When the data need structure and relationships → Use Relational DB

Use NoSQL DB

E.g., MongoDB or DynamoDB

Scenario: Storing real-time product reviews or user activity logs

• Data varies in structure and changes frequently.

• Requires fast writes, flexible schema, and horizontal scaling.

Document Example in MongoDB:

{
  "product_id": "A123",
  "reviews": [
    { "user": "Tom", "rating": 5, "comment": "Great product!" },
    { "user": "Anna", "rating": 4 }
  ]
}

When the data need speed, flexibility, and scale → Use NoSQL DB

Archival strategies:

• By archiving old data, you can maintain an organized and clutter-free system, making it easier to access and manage the most current and important information.

• Avoid loading historical data unless explicitly requested.

Good Approach (Optimized)

Scenario: A Sales Dashboard App

Your app shows sales data to regional managers.

By default, the app only loads the latest 30 days of sales data. If the user selects a filter like "View past year", then and only then the app fetches historical data from the database.

Bad Approach (Inefficient)

When a manager opens the dashboard, the app loads all sales data from the past 5 years, including data the user may never need. Result: The app becomes slow, the backend works harder, and the user just wanted last month's performance.

Geographic Scalability

• Support multi-region and multi-lingual support for users.

• Implement localization support (timezone, currency) if expanding internationally.