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:
The results were eye-opening:
Database: The Foundation
Strategic Indexing
The single biggest win came from proper indexing. Here's a real example from our transactions table:
-- 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:
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.
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:
// 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:
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:
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:
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:
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:
// 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:
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:
// 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):
We hit our target without adding any new infrastructure. The same servers that struggled with 500 users now comfortably handle 7,500.
Key Takeaways
What I'd Do Differently
If I had more time, I would have:
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.