Scaling a System from 500 to 7,500 Users: A Technical Deep Dive

When the Government of Guinea-Bissau decided to roll out our Public Financial Management system to all government employees, we had a problem: the system was designed for ~500 users, and we needed to support 7,500.

The deadline? Eight weeks.

No budget for new infrastructure. No time for a rewrite. Just optimization.

Here's how we did it.

Understanding the Baseline

Before optimizing anything, I needed to understand where the bottlenecks actually were. Gut feelings are usually wrong—you need data.

I set up comprehensive monitoring:

Azure Application Insights for API response times and error rates
pg_stat_statements for PostgreSQL query analysis
React Profiler for frontend rendering performance
Network tab analysis for payload sizes and waterfall patterns

The results were eye-opening:

Issue	Impact
N+1 queries in user listing	3-4s page loads
Missing database indexes	Full table scans on 100K+ rows
No query caching	Same expensive queries repeated constantly
Unoptimized React renders	500ms+ component updates
Oversized GraphQL responses	2MB+ payloads

Note

The biggest performance wins usually come from fixing obvious mistakes, not clever optimizations. Start with profiling.

Database: The Foundation

Strategic Indexing

The single biggest win came from proper indexing. Here's a real example from our transactions table:

sql

-- Before: Full table scan on every lookup
EXPLAIN ANALYZE 
SELECT * FROM transactions 
WHERE organization_id = 'org-123' 
AND status = 'pending' 
ORDER BY created_at DESC;

-- Result: Seq Scan on transactions
-- Execution Time: 234.567 ms

After adding a composite index:

sql

CREATE INDEX idx_transactions_org_status_created 
ON transactions (organization_id, status, created_at DESC);

-- Result: Index Scan using idx_transactions_org_status_created
-- Execution Time: 0.456 ms

That's a 512x improvement from a single index.

Pro Tip

Composite indexes should order columns from most selective to least selective. For queries with both equality and range conditions, put equality columns first.

Fixing N+1 Queries with DataLoader

Our GraphQL API had classic N+1 problems. When fetching a list of transactions, each one would trigger a separate query for its associated user:

typescript

// Before: N+1 queries (1 + N where N = number of transactions)
const resolvers = {
  Transaction: {
    user: async (transaction) => {
      // This runs once per transaction!
      return await User.findById(transaction.userId);
    }
  }
};

With 100 transactions, that's 101 database queries. Using DataLoader:

typescript

import DataLoader from 'dataloader';

// Create a batching loader
const createUserLoader = () => new DataLoader(async (userIds: string[]) => {
  const users = await User.find({ _id: { $in: userIds } });
  
  // Return users in the same order as requested IDs
  const userMap = new Map(users.map(u => [u._id.toString(), u]));
  return userIds.map(id => userMap.get(id) || null);
});

// Use in resolvers
const resolvers = {
  Transaction: {
    user: async (transaction, _, { loaders }) => {
      return loaders.user.load(transaction.userId);
    }
  }
};

Now those 100 transactions result in exactly 2 queries: one for transactions, one batched query for all users.

Caching Strategy

We implemented a multi-layer caching strategy, because different data has different freshness requirements.

Layer 1: Apollo Client Cache (Frontend)

GraphQL's normalized cache is powerful when configured correctly:

typescript

const cache = new InMemoryCache({
  typePolicies: {
    Query: {
      fields: {
        transactions: {
          // Cache separately by organization and status
          keyArgs: ['organizationId', 'status'],
          
          // Merge paginated results
          merge(existing = [], incoming) {
            return [...existing, ...incoming];
          }
        }
      }
    },
    Transaction: {
      fields: {
        // Cache user references by ID
        user: {
          merge: true
        }
      }
    }
  }
});

This means navigating between pages doesn't re-fetch data that's already loaded.

Layer 2: Redis for API Responses

For expensive queries that don't change frequently (like reports), we added Redis caching:

typescript

import Redis from 'ioredis';
const redis = new Redis(process.env.REDIS_URL);

const cacheMiddleware = async (req, res, next) => {
  const cacheKey = `api:${req.path}:${JSON.stringify(req.query)}`;
  
  // Check cache first
  const cached = await redis.get(cacheKey);
  if (cached) {
    return res.json(JSON.parse(cached));
  }
  
  // Intercept response to cache it
  const originalJson = res.json.bind(res);
  res.json = (body) => {
    // Cache for 5 minutes
    redis.setex(cacheKey, 300, JSON.stringify(body));
    return originalJson(body);
  };
  
  next();
};

Layer 3: Materialized Views for Reports

Some reports required aggregating millions of rows. Instead of computing on every request, we pre-computed into materialized views:

sql

CREATE MATERIALIZED VIEW monthly_transaction_summary AS
SELECT 
  organization_id,
  date_trunc('month', created_at) as month,
  status,
  COUNT(*) as transaction_count,
  SUM(amount) as total_amount
FROM transactions
GROUP BY organization_id, date_trunc('month', created_at), status;

-- Refresh daily via cron job
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_transaction_summary;

Query time went from 12 seconds to 50 milliseconds.

Frontend Optimizations

Backend changes only get you so far. The frontend needed work too.

React.memo and useMemo

Components were re-rendering unnecessarily. Adding memoization fixed it:

typescript

// Before: Re-renders on every parent update
const TransactionRow = ({ transaction }) => {
  const formattedAmount = formatCurrency(transaction.amount);
  const formattedDate = formatDate(transaction.createdAt);
  
  return (
    <tr>
      <td>{transaction.id}</td>
      <td>{formattedAmount}</td>
      <td>{formattedDate}</td>
    </tr>
  );
};

// After: Only re-renders when transaction changes
const TransactionRow = React.memo(({ transaction }) => {
  const formattedAmount = useMemo(
    () => formatCurrency(transaction.amount),
    [transaction.amount]
  );
  
  const formattedDate = useMemo(
    () => formatDate(transaction.createdAt),
    [transaction.createdAt]
  );
  
  return (
    <tr>
      <td>{transaction.id}</td>
      <td>{formattedAmount}</td>
      <td>{formattedDate}</td>
    </tr>
  );
});

Virtual Scrolling

Instead of rendering 7,500 rows in a table, we used react-window for virtual scrolling:

typescript

import { FixedSizeList } from 'react-window';

const TransactionList = ({ transactions }) => (
  <FixedSizeList
    height={600}
    itemCount={transactions.length}
    itemSize={60}
    width="100%"
  >
    {({ index, style }) => (
      <TransactionRow 
        style={style} 
        transaction={transactions[index]} 
      />
    )}
  </FixedSizeList>
);

The browser only renders ~15 rows at a time, regardless of total data size.

Bundle Splitting

Our initial JavaScript bundle was 2.3MB. Using dynamic imports, we got it under 500KB for the initial load:

typescript

// Before: Everything loaded upfront
import { HeavyChart } from './components/HeavyChart';

// After: Loaded only when needed
const HeavyChart = dynamic(() => import('./components/HeavyChart'), {
  loading: () => <ChartSkeleton />,
  ssr: false // Chart library doesn't support SSR
});

The Results

After six weeks of optimization (with two weeks for testing and rollback planning):

Metric	Before	After	Improvement
Page Load Time	4.2s	0.8s	5.25x
API Response (p95)	1.2s	180ms	6.7x
Database Queries/Request	47	8	5.9x
JS Bundle Size	2.3MB	480KB	4.8x
Concurrent Users	500	7,500	15x

We hit our target without adding any new infrastructure. The same servers that struggled with 500 users now comfortably handle 7,500.

Key Takeaways

Measure Before Optimizing

Profiling revealed that our intuitions were wrong. The biggest issue wasn't what we expected.

Indexes Are Not Optional

Proper database indexing is foundational. It's not premature optimization—it's basic hygiene.

Caching Is About Trade-offs

Every cache adds complexity. Choose cache invalidation strategies based on how stale your data can be.

Frontend Matters

Server response time is only half the story. A slow frontend negates fast APIs.

What I'd Do Differently

If I had more time, I would have:

Set up proper load testing earlier (we discovered issues late)
Implemented connection pooling more aggressively
Added more granular caching with shorter TTLs
Used read replicas for report queries

But with eight weeks and a hard deadline, we prioritized impact over perfection. Sometimes shipping on time with 90% of the optimization is better than missing the deadline chasing the last 10%.

Building something that needs to scale? Let's chat about architecture and performance.

The deadline? Eight weeks.

No budget for new infrastructure. No time for a rewrite. Just optimization.

Here's how we did it.

Understanding the Baseline

Before optimizing anything, I needed to understand where the bottlenecks actually were. Gut feelings are usually wrong—you need data.

I set up comprehensive monitoring:

Azure Application Insights for API response times and error rates
pg_stat_statements for PostgreSQL query analysis
React Profiler for frontend rendering performance
Network tab analysis for payload sizes and waterfall patterns

The results were eye-opening:

Issue	Impact
N+1 queries in user listing	3-4s page loads
Missing database indexes	Full table scans on 100K+ rows
No query caching	Same expensive queries repeated constantly
Unoptimized React renders	500ms+ component updates
Oversized GraphQL responses	2MB+ payloads

Note

The biggest performance wins usually come from fixing obvious mistakes, not clever optimizations. Start with profiling.

Database: The Foundation

Strategic Indexing

The single biggest win came from proper indexing. Here's a real example from our transactions table:

sql

-- Before: Full table scan on every lookup
EXPLAIN ANALYZE 
SELECT * FROM transactions 
WHERE organization_id = 'org-123' 
AND status = 'pending' 
ORDER BY created_at DESC;

-- Result: Seq Scan on transactions
-- Execution Time: 234.567 ms

After adding a composite index:

sql

CREATE INDEX idx_transactions_org_status_created 
ON transactions (organization_id, status, created_at DESC);

-- Result: Index Scan using idx_transactions_org_status_created
-- Execution Time: 0.456 ms

That's a 512x improvement from a single index.

Pro Tip

Composite indexes should order columns from most selective to least selective. For queries with both equality and range conditions, put equality columns first.

Fixing N+1 Queries with DataLoader

Our GraphQL API had classic N+1 problems. When fetching a list of transactions, each one would trigger a separate query for its associated user:

typescript

// Before: N+1 queries (1 + N where N = number of transactions)
const resolvers = {
  Transaction: {
    user: async (transaction) => {
      // This runs once per transaction!
      return await User.findById(transaction.userId);
    }
  }
};

With 100 transactions, that's 101 database queries. Using DataLoader:

typescript

import DataLoader from 'dataloader';

// Create a batching loader
const createUserLoader = () => new DataLoader(async (userIds: string[]) => {
  const users = await User.find({ _id: { $in: userIds } });
  
  // Return users in the same order as requested IDs
  const userMap = new Map(users.map(u => [u._id.toString(), u]));
  return userIds.map(id => userMap.get(id) || null);
});

// Use in resolvers
const resolvers = {
  Transaction: {
    user: async (transaction, _, { loaders }) => {
      return loaders.user.load(transaction.userId);
    }
  }
};

Now those 100 transactions result in exactly 2 queries: one for transactions, one batched query for all users.

Caching Strategy

We implemented a multi-layer caching strategy, because different data has different freshness requirements.

Layer 1: Apollo Client Cache (Frontend)

GraphQL's normalized cache is powerful when configured correctly:

typescript

const cache = new InMemoryCache({
  typePolicies: {
    Query: {
      fields: {
        transactions: {
          // Cache separately by organization and status
          keyArgs: ['organizationId', 'status'],
          
          // Merge paginated results
          merge(existing = [], incoming) {
            return [...existing, ...incoming];
          }
        }
      }
    },
    Transaction: {
      fields: {
        // Cache user references by ID
        user: {
          merge: true
        }
      }
    }
  }
});

This means navigating between pages doesn't re-fetch data that's already loaded.

Layer 2: Redis for API Responses

For expensive queries that don't change frequently (like reports), we added Redis caching:

typescript

import Redis from 'ioredis';
const redis = new Redis(process.env.REDIS_URL);

const cacheMiddleware = async (req, res, next) => {
  const cacheKey = `api:${req.path}:${JSON.stringify(req.query)}`;
  
  // Check cache first
  const cached = await redis.get(cacheKey);
  if (cached) {
    return res.json(JSON.parse(cached));
  }
  
  // Intercept response to cache it
  const originalJson = res.json.bind(res);
  res.json = (body) => {
    // Cache for 5 minutes
    redis.setex(cacheKey, 300, JSON.stringify(body));
    return originalJson(body);
  };
  
  next();
};

Layer 3: Materialized Views for Reports

Some reports required aggregating millions of rows. Instead of computing on every request, we pre-computed into materialized views:

sql

CREATE MATERIALIZED VIEW monthly_transaction_summary AS
SELECT 
  organization_id,
  date_trunc('month', created_at) as month,
  status,
  COUNT(*) as transaction_count,
  SUM(amount) as total_amount
FROM transactions
GROUP BY organization_id, date_trunc('month', created_at), status;

-- Refresh daily via cron job
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_transaction_summary;

Query time went from 12 seconds to 50 milliseconds.

Frontend Optimizations

Backend changes only get you so far. The frontend needed work too.

React.memo and useMemo

Components were re-rendering unnecessarily. Adding memoization fixed it:

typescript

// Before: Re-renders on every parent update
const TransactionRow = ({ transaction }) => {
  const formattedAmount = formatCurrency(transaction.amount);
  const formattedDate = formatDate(transaction.createdAt);
  
  return (
    <tr>
      <td>{transaction.id}</td>
      <td>{formattedAmount}</td>
      <td>{formattedDate}</td>
    </tr>
  );
};

// After: Only re-renders when transaction changes
const TransactionRow = React.memo(({ transaction }) => {
  const formattedAmount = useMemo(
    () => formatCurrency(transaction.amount),
    [transaction.amount]
  );
  
  const formattedDate = useMemo(
    () => formatDate(transaction.createdAt),
    [transaction.createdAt]
  );
  
  return (
    <tr>
      <td>{transaction.id}</td>
      <td>{formattedAmount}</td>
      <td>{formattedDate}</td>
    </tr>
  );
});

Virtual Scrolling

Instead of rendering 7,500 rows in a table, we used react-window for virtual scrolling:

typescript

import { FixedSizeList } from 'react-window';

const TransactionList = ({ transactions }) => (
  <FixedSizeList
    height={600}
    itemCount={transactions.length}
    itemSize={60}
    width="100%"
  >
    {({ index, style }) => (
      <TransactionRow 
        style={style} 
        transaction={transactions[index]} 
      />
    )}
  </FixedSizeList>
);

The browser only renders ~15 rows at a time, regardless of total data size.

Bundle Splitting

Our initial JavaScript bundle was 2.3MB. Using dynamic imports, we got it under 500KB for the initial load:

typescript

// Before: Everything loaded upfront
import { HeavyChart } from './components/HeavyChart';

// After: Loaded only when needed
const HeavyChart = dynamic(() => import('./components/HeavyChart'), {
  loading: () => <ChartSkeleton />,
  ssr: false // Chart library doesn't support SSR
});

The Results

After six weeks of optimization (with two weeks for testing and rollback planning):

Metric	Before	After	Improvement
Page Load Time	4.2s	0.8s	5.25x
API Response (p95)	1.2s	180ms	6.7x
Database Queries/Request	47	8	5.9x
JS Bundle Size	2.3MB	480KB	4.8x
Concurrent Users	500	7,500	15x

We hit our target without adding any new infrastructure. The same servers that struggled with 500 users now comfortably handle 7,500.

Key Takeaways

Measure Before Optimizing

Profiling revealed that our intuitions were wrong. The biggest issue wasn't what we expected.

Indexes Are Not Optional

Proper database indexing is foundational. It's not premature optimization—it's basic hygiene.

Caching Is About Trade-offs

Every cache adds complexity. Choose cache invalidation strategies based on how stale your data can be.

Frontend Matters

Server response time is only half the story. A slow frontend negates fast APIs.

What I'd Do Differently

If I had more time, I would have:

Set up proper load testing earlier (we discovered issues late)
Implemented connection pooling more aggressively
Added more granular caching with shorter TTLs
Used read replicas for report queries

But with eight weeks and a hard deadline, we prioritized impact over perfection. Sometimes shipping on time with 90% of the optimization is better than missing the deadline chasing the last 10%.

Building something that needs to scale? Let's chat about architecture and performance.

Scaling a System from 500 to 7,500 Users: A Technical Deep Dive

Understanding the Baseline

Database: The Foundation

Strategic Indexing

Fixing N+1 Queries with DataLoader

Caching Strategy

Layer 1: Apollo Client Cache (Frontend)

Layer 2: Redis for API Responses

Layer 3: Materialized Views for Reports

Frontend Optimizations

React.memo and useMemo

Virtual Scrolling

Bundle Splitting

The Results

Key Takeaways

Measure Before Optimizing

Indexes Are Not Optional

Caching Is About Trade-offs

Frontend Matters

What I'd Do Differently

Related Articles

Multi-Tenant Architecture: Patterns and Pitfalls

Enjoyed this article?

Scaling a System from 500 to 7,500 Users: A Technical Deep Dive

Understanding the Baseline

Database: The Foundation

Strategic Indexing

Fixing N+1 Queries with DataLoader

Caching Strategy

Layer 1: Apollo Client Cache (Frontend)

Layer 2: Redis for API Responses

Layer 3: Materialized Views for Reports

Frontend Optimizations

React.memo and useMemo

Virtual Scrolling

Bundle Splitting

The Results

Key Takeaways

Measure Before Optimizing

Indexes Are Not Optional

Caching Is About Trade-offs

Frontend Matters

What I'd Do Differently

Related Articles

Multi-Tenant Architecture: Patterns and Pitfalls

Enjoyed this article?