How to Set Up Database Optimization in ECC: Complete Implementation Guide

Question

Master database optimization in ECC with this guide. Learn to implement selective query projection, N+1 prevention, Redis caching, and atomic transactions for peak performance.

Accepted Answer

Database optimization in ECC is achieved through four core patterns—selective query projection, N+1 query prevention, cache-aside with Redis, and atomic transactions—implemented across distinct repository, service, and API layers.

The ECC ( Enterprise Component Collection) repository provides structured backend development patterns that guide developers toward efficient data access. According to the source code in affaan-m/ECC, the database optimization techniques are located within the Backend Development Patterns skill and demonstrate production-ready implementations using TypeScript and Supabase.

The Four Core Patterns for Database Optimization in ECC

Query Optimization: Select Only Required Columns

Reduce I/O and memory pressure by projecting specific columns rather than using SELECT *. In src/lib/repo/marketRepository.ts, the optimized pattern explicitly lists fields to leverage database indexes and minimize payload size.

// src/lib/repo/marketRepository.ts
const { data } = await supabase
  .from('markets')
  .select('id, name, status, volume')   // Select only needed fields
  .eq('status', 'active')
  .order('volume', { ascending: false })
  .limit(10);

This pattern, documented in the skill file【33†L33-L41】, ensures the query optimizer can use covering indexes and reduces network transfer overhead.

Eliminate the classic performance anti-pattern where iterating over parent records triggers individual queries for each child. The MarketService in src/services/marketService.ts demonstrates batching creator lookups into a single query.

// src/services/marketService.ts
const markets = await getMarkets();                     // Single query for markets
const creatorIds = markets.map(m => m.creator_id);
const creators = await getUsers(creatorIds);           // Single batched query
const creatorMap = new Map(creators.map(c => [c.id, c]));

markets.forEach(m => {
  m.creator = creatorMap.get(m.creator_id);
});

This implementation replaces the naïve per-market lookup (see the "FAIL: BAD" example【48†L52-L56】) with a single batched query (the "PASS: GOOD" example【57†L58-L65】), cutting database round-trips from O(n) to O(1).

Cache-Aside Strategy: Redis Layer Implementation

Implement a decorator pattern to wrap your base repository with Redis caching. The CachedMarketRepository in src/lib/repo/cachedMarketRepository.ts implements the cache-aside pattern, checking the cache before falling back to the database.

// src/lib/repo/cachedMarketRepository.ts
class CachedMarketRepository implements MarketRepository {
  constructor(private baseRepo: MarketRepository, private redis: RedisClient) {}

  async findById(id: string): Promise<Market | null> {
    const cached = await this.redis.get(`market:${id}`);
    if (cached) return JSON.parse(cached);

    const market = await this.baseRepo.findById(id);
    if (market) await this.redis.setex(`market:${id}`, 300, JSON.stringify(market));
    return market;
  }

  async invalidateCache(id: string): Promise<void> {
    await this.redis.del(`market:${id}`);
  }
}

This mirrors the Redis Caching Layer example【71†L71-L84】 and the Cache-Aside snippet【84†L84-L90】, providing sub-millisecond reads for frequently accessed entities while maintaining data consistency through explicit invalidation.

Transaction Pattern: Atomic Database Operations

Ensure consistency across multiple table writes by wrapping operations in database transactions. The createMarketWithPosition function in src/lib/transactions/marketTransaction.ts uses Supabase RPC to execute atomic stored procedures.

// src/lib/transactions/marketTransaction.ts
async function createMarketWithPosition(
  marketData: CreateMarketDto,
  positionData: CreatePositionDto
) {
  const { data, error } = await supabase.rpc('create_market_with_position', {
    market_data: marketData,
    position_data: positionData,
  });

  if (error) throw new Error('Transaction failed');
  return data;
}

This implements the transaction pattern illustrated in the skill file【68†L68-L84】 and the corresponding SQL function【86†L86-L100】, guaranteeing that either both records persist or neither does, preventing partial data states.

Architectural Layering in ECC

The database optimization patterns rely on strict separation of concerns across four layers:

Repository Layer – All data access is abstracted through interfaces like MarketRepository, allowing database swaps without affecting business logic.
Service Layer – MarketService orchestrates repositories, applies caching strategies, and handles N+1 prevention through batch operations.
API/Middleware Layer – Next.js API routes or Express handlers invoke services and manage HTTP concerns, delegating data access to the service layer.
Cache & Error Handling – The CachedMarketRepository decorates base repositories with Redis lookups, while centralized ApiError and errorHandler (using src/lib/logging/logger.ts) ensure consistent failure logging.

This modular architecture means you can migrate from Supabase to native PostgreSQL or add additional caching layers without modifying service or API code, preserving your optimization guarantees.

Summary

Query optimization requires explicit column selection in Supabase queries to minimize I/O and leverage indexes.
N+1 prevention is solved by batching related entity lookups into single queries using ID mapping techniques.
Cache-aside pattern wraps repositories with Redis decorators to serve hot data from memory while maintaining database consistency.
Atomic transactions use Supabase RPC calls to ensure multi-table writes succeed or fail together.
Layered architecture separates concerns across repository, service, and API boundaries, enabling database technology swaps without business logic changes.

Frequently Asked Questions

How does the N+1 query problem manifest in ECC applications?

The N+1 problem occurs when code fetches a list of records (N), then iterates through them to fetch related data, issuing one additional query per record (total N+1 queries). In ECC, this is solved by extracting all foreign keys from the initial result set, batching the related queries into a single getUsers(creatorIds) call, then mapping relationships in memory using a Map data structure.

When should I use the cache-aside pattern versus querying the database directly?

Use cache-aside when reading frequently accessed, relatively static data that can tolerate brief staleness (300-second TTL in the examples). Query the database directly for real-time critical operations or when cache invalidation would add unacceptable complexity. The CachedMarketRepository pattern allows you to decorate specific methods while leaving others uncached, providing granular control.

Can I use these optimization patterns with databases other than Supabase?

Yes. The repository pattern abstracts the underlying data source, so MarketRepository implementations can be swapped between Supabase, PostgreSQL, or other databases. The transaction pattern uses Supabase RPC in the example, but equivalent implementations using native database transactions would follow the same architectural principles in src/lib/transactions/marketTransaction.ts.

How does ECC handle transaction failures and database errors?

ECC uses centralized error handling through ApiError classes and the errorHandler middleware, implemented in the logging utilities. Transaction failures trigger explicit error throws (e.g., throw new Error('Transaction failed')), which are caught by the error handling layer and logged consistently via src/lib/logging/logger.ts, ensuring observability without leaking sensitive database details to the client.