craft2prints/DATABASE_OPTIMIZATION_QUICK_START.md

Database Optimization - Quick Start Guide

1. Apply Indexes (5 minutes)

Run this SQL directly on your PostgreSQL database:

# Option A: Using psql
psql -U username -d craft2prints -f DATABASE_OPTIMIZATION_MIGRATION.sql

# Option B: Using Prisma
npx prisma db execute --stdin < DATABASE_OPTIMIZATION_MIGRATION.sql

Result: Queries will be 10-100x faster for large datasets!

---

2. Add Pagination to Critical Pages (15 minutes)

Example: Admin Orders Page

File: src/app/admin/orders/page.tsx

import Link from 'next/link';
import { prisma } from '@/lib/prisma';
import AdminNav from '@/components/AdminNav';

export default async function AdminOrdersPage({ 
  searchParams 
}: { 
  searchParams: { page?: string } 
}) {
  const page = Math.max(1, parseInt(searchParams.page || '1'));
  const pageSize = 50;
  const skip = (page - 1) * pageSize;

  // Fetch orders AND total count in parallel
  const [orders, total] = await Promise.all([
    prisma.order.findMany({
      select: {
        id: true,
        email: true,
        total: true,
        status: true,
        createdAt: true,
        items: {
          select: {
            productName: true,
            quantity: true
          }
        }
      },
      orderBy: { createdAt: 'desc' },
      skip,
      take: pageSize
    }),
    prisma.order.count()
  ]);

  const totalPages = Math.ceil(total / pageSize);

  return (
    <div className="mx-auto max-w-6xl px-6 py-12">
      <AdminNav />
      <h1 className="font-display text-3xl mb-8">Orders</h1>

      {/* Orders List */}
      <div className="divide-y divide-line border-t border-line">
        {orders.map((order) => (
          <div key={order.id} className="py-4 flex justify-between items-center">
            <div>
              <p className="font-medium">{order.id}</p>
              <p className="text-sm text-muted">{order.email}</p>
            </div>
            <div className="text-right">
              <p className="font-mono">£{(order.total / 100).toFixed(2)}</p>
              <p className="text-xs text-muted">{order.status}</p>
            </div>
          </div>
        ))}
      </div>

      {/* Pagination */}
      <div className="mt-8 flex justify-center gap-2">
        {page > 1 && (
          <Link
            href={`/admin/orders?page=${page - 1}`}
            className="px-4 py-2 border border-line hover:border-clay"
          >
            ← Previous
          </Link>
        )}

        <div className="flex items-center gap-1">
          {Array.from({ length: Math.min(5, totalPages) }).map((_, i) => {
            const pageNum = i + 1;
            return (
              <Link
                key={pageNum}
                href={`/admin/orders?page=${pageNum}`}
                className={`px-3 py-2 border ${
                  page === pageNum
                    ? 'border-clay bg-clay text-paper'
                    : 'border-line hover:border-clay'
                }`}
              >
                {pageNum}
              </Link>
            );
          })}
        </div>

        {page < totalPages && (
          <Link
            href={`/admin/orders?page=${page + 1}`}
            className="px-4 py-2 border border-line hover:border-clay"
          >
            Next →
          </Link>
        )}
      </div>

      <p className="text-sm text-muted text-center mt-4">
        Showing {skip + 1}-{Math.min(skip + pageSize, total)} of {total} orders
      </p>
    </div>
  );
}

---

3. Optimize Existing Queries (10 minutes)

Admin Products Page

Before (Slow):

const products = await prisma.product.findMany();
// Fetches: id, slug, name, category, colors (large JSON), colorPhotos (huge!), etc.
// For 1000 products: ~10MB data transferred

After (Fast):

const products = await prisma.product.findMany({
  select: {
    id: true,
    slug: true,
    name: true,
    category: true,
    basePrice: true,
    showOnProducts: true,
    showOnPersonalised: true
    // Skip large JSON fields
  },
  take: 50,
  skip: (page - 1) * 50
});
// For 1000 products: ~100KB data transferred (100x smaller!)

Orders with Items

Before (N+1 problem):

const orders = await prisma.order.findMany();
// 1 query

orders.forEach(order => {
  const items = await prisma.orderItem.findMany({
    where: { orderId: order.id }
  });
  // N more queries (if 1000 orders, that's 1001 queries!)
});

After (Single query):

const orders = await prisma.order.findMany({
  include: { items: true }  // Join in single query
});
// Just 1 query instead of 1001!

---

4. Add Caching (20 minutes)

Install Node Cache

npm install node-cache

Create Cache Utility

File: lib/cache.ts

import NodeCache from 'node-cache';

// Cache with 10 minute TTL
const cache = new NodeCache({ stdTTL: 600 });

export async function getCachedProducts() {
  // Check cache first
  const cached = cache.get('all_products');
  if (cached) return cached;

  // If not cached, fetch from DB
  const products = await prisma.product.findMany({
    select: { id: true, name: true, slug: true, basePrice: true }
  });

  // Store in cache
  cache.set('all_products', products);
  return products;
}

export async function getCachedCategories() {
  const cached = cache.get('categories');
  if (cached) return cached;

  const categories = await prisma.category.findMany({
    orderBy: { name: 'asc' }
  });

  cache.set('categories', categories);
  return categories;
}

// Clear cache when data changes
export function invalidateCache(key: string) {
  cache.del(key);
}

Use Cache in Pages

import { getCachedProducts } from '@/lib/cache';

export default async function ProductsPage() {
  const products = await getCachedProducts(); // Uses cache if available
  // ...
}

Invalidate on Update

import { invalidateCache } from '@/lib/cache';

export async function updateProduct(id: string, data: any) {
  await prisma.product.update({ where: { id }, data });
  invalidateCache('all_products'); // Clear cache
}

---

5. Monitor Performance (10 minutes)

Add Query Logging

File: lib/prisma.ts

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: [
    { level: 'query', emit: 'event' },
    { level: 'error', emit: 'stdout' },
    { level: 'warn', emit: 'stdout' }
  ]
});

prisma.$on('query', (e) => {
  if (e.duration > 1000) {
    // Log slow queries (>1 second)
    console.warn(`SLOW QUERY [${e.duration}ms]: ${e.query}`);
  }
});

export default prisma;

---

6. Performance Checklist

• Run DATABASE_OPTIMIZATION_MIGRATION.sql to add indexes
• Add pagination to /admin/orders page (50 items per page)
• Add pagination to /admin/products page (50 items per page)
• Add pagination to /admin/designs page (50 items per page)
• Optimize product queries to use select instead of fetching all fields
• Install and configure node-cache for frequent queries
• Cache categories, collections, and products
• Add query duration logging to identify slow queries
• Set database connection timeout to 30 seconds
• Schedule weekly VACUUM ANALYZE task

---

Expected Performance Improvements

Change	Impact	Effort
Add indexes	40-60x faster	5 min
Pagination (50 items)	20-30x faster	15 min each page
Use select field	10-20x less data	10 min per query
Redis caching	100-1000x for cached	20 min
Query logging	Identify bottlenecks	5 min

Total effort: ~2 hours
Performance gain: 50-100x for most operations

---

Troubleshooting

Pagination not working?

// Make sure to use BOTH skip and take
skip: (page - 1) * pageSize,
take: pageSize

Indexes not helping?

# Rebuild indexes
REINDEX INDEX index_name;

# Analyze tables
ANALYZE;

Queries still slow?

// Add timing logs
const start = Date.now();
const result = await prisma.order.findMany({...});
console.log(`Query took ${Date.now() - start}ms`);

---

Next: Advanced Optimizations

Once these are done, consider:

1. Read replicas for reporting queries
2. Denormalization for frequently joined data
3. Archiving old orders to separate table
4. Full-text search indexes for product search
5. Materialized views for dashboard data