Database Design with PostgreSQL for Scalable Web Applications

Your database schema is the most permanent decision in your application. Frontend frameworks change, APIs evolve, but your data model tends to persist. Getting it right from the start saves countless hours of refactoring and migration headaches down the road.

Schema Design with Prisma

Prisma has become the go-to ORM for Node.js and TypeScript applications. Its schema-first approach provides type safety, auto-generated migrations, and a clean query API.

1model User {
2  id        String   @id @default(cuid())
3  email     String   @unique
4  name      String
5  role      Role     @default(MEMBER)
6  orders    Order[]
7  createdAt DateTime @default(now())
8  updatedAt DateTime @updatedAt
9
10  @@index([email])
11  @@index([role])
12}
13
14model Order {
15  id        String      @id @default(cuid())
16  status    OrderStatus @default(PENDING)
17  total     Decimal     @db.Decimal(10, 2)
18  userId    String
19  user      User        @relation(fields: [userId], references: [id])
20  items     OrderItem[]
21  createdAt DateTime    @default(now())
22
23  @@index([userId])
24  @@index([status])
25  @@index([createdAt])
26}
27
28enum Role {
29  ADMIN
30  MANAGER
31  MEMBER
32}
33
34enum OrderStatus {
35  PENDING
36  PROCESSING
37  SHIPPED
38  DELIVERED
39  CANCELLED
40}

Indexing Strategies

Indexes are the single biggest factor in query performance. Add indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY statements. But be strategic — every index slows down write operations.

• Always index foreign keys (userId, orderId, etc.)
• Add composite indexes for queries that filter on multiple columns
• Use partial indexes for columns with skewed data distribution
• Monitor slow queries with pg_stat_statements and add indexes accordingly
• Avoid over-indexing — each index consumes storage and slows writes

Query Optimization

Use EXPLAIN ANALYZE to understand how PostgreSQL executes your queries. Look for sequential scans on large tables, unnecessary joins, and missing indexes. Optimizing a single slow query can improve your entire application's response time.

1-- Find slow queries
2SELECT query, calls, mean_exec_time, total_exec_time
3FROM pg_stat_statements
4ORDER BY mean_exec_time DESC
5LIMIT 10;
6
7-- Analyze a specific query
8EXPLAIN ANALYZE
9SELECT u.name, COUNT(o.id) as order_count
10FROM users u
11LEFT JOIN orders o ON o.user_id = u.id
12WHERE u.role = 'MEMBER'
13GROUP BY u.id
14ORDER BY order_count DESC
15LIMIT 20;

Connection Pooling

PostgreSQL creates a new process for each connection, making connection management critical at scale. Use PgBouncer or Prisma's built-in connection pooling to efficiently manage database connections and prevent connection exhaustion under load.

Conclusion

A reliable PostgreSQL system depends on schema clarity, targeted indexing, and regular query-plan review with EXPLAIN ANALYZE. Treat indexing as an iterative process informed by workload, not a one-time checklist.