Database Scaling and Performance

Most database performance problems aren't about scale — they're about a missing index or a sloppy query. This covers how to make a database fast, and then how to scale it when you genuinely outgrow a single machine. The order matters: fix performance first, scale second. If Cartara flagged this in your diff, you likely wrote a query, schema change, or data access pattern that could cause performance problems as your data grows.

The first rule: you probably don't have a scale problem

A well-tuned PostgreSQL database on a modest server handles millions of rows and thousands of users without breaking a sweat. When an app feels slow, the cause is almost always a query issue, not a need for more machines. Reach for the cheap, local fixes below before the expensive, complex ones at the end.

Indexes: the single biggest lever

An index is a lookup structure that lets the database jump straight to matching rows instead of scanning the whole table. Adding the right index can turn a multi-second query into a sub-millisecond one.

• Index the columns you filter, sort, or join on — anything in a WHERE, ORDER BY, or JOIN
• Composite indexes cover multiple columns; order matters. An index on (user_id, created_at) helps queries filtering by user_id but not queries filtering only by created_at
• Every index slows down writes slightly (it must be updated on each insert/update) and uses storage — index deliberately, not everywhere

To find missing indexes, use EXPLAIN ANALYZE to see how the database executes a query. "Sequential scan" on a large table is usually a missing index.

-- Slow: full table scan
SELECT * FROM orders WHERE user_id = '123' ORDER BY created_at DESC;

-- Add index
CREATE INDEX orders_user_id_created_at_idx ON orders (user_id, created_at DESC);

-- Now fast: index scan
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = '123' ORDER BY created_at DESC;

Writing faster queries

Select only what you need. SELECT * pulls every column. Ask for the ones you'll use.

Beware the N+1 problem. This is the most common ORM performance trap: you fetch a list of 100 users, then your code loops and runs one more query per user to get their orders — 101 queries instead of 2.

// N+1 problem: 1 query for users + 1 per user for orders
const users = await db.users.findMany()
for (const user of users) {
  user.orders = await db.orders.findMany({ where: { userId: user.id } })
  // This runs once per user!
}

// Fixed: one query with a join
const users = await db.users.findMany({
  include: { orders: true }  // Prisma eager-loads with a JOIN
})

Paginate large result sets. Never load 50,000 rows to show 20. Use LIMIT/OFFSET or cursor-based pagination.

Push work into the database. Filtering, counting, and aggregating are faster done by the database than by pulling all rows into your app and doing it there.

Connection pooling

Each database connection is expensive to open, and databases cap how many can be open at once. An app that opens a fresh connection per request will exhaust the limit and crash under load — a particularly common failure with serverless functions, since each instance wants its own connection.

A connection pooler (PgBouncer, or the built-in pooler on Supabase/Neon) maintains a set of reusable connections that requests borrow and return. Enable this before launch — it's a one-time setup that prevents a very common production outage.

# In your database URL, use the pooler endpoint
# Direct (bad for serverless):
DATABASE_URL=postgresql://user:pass@db.host:5432/mydb

# Pooled (good for serverless and high-concurrency apps):
DATABASE_URL=postgresql://user:pass@db.host:6543/mydb?pgbouncer=true

Caching

The fastest database query is the one you never make. For data that's read often and changes rarely, store the result in a fast cache (usually Redis) and serve it from there, falling back to the database on a miss.

async function getUserProfile(userId) {
  const cacheKey = `user:${userId}`
  
  // Check cache first
  const cached = await redis.get(cacheKey)
  if (cached) return JSON.parse(cached)
  
  // Cache miss: fetch from database
  const user = await db.users.findUnique({ where: { id: userId } })
  
  // Store in cache for 5 minutes
  await redis.set(cacheKey, JSON.stringify(user), 'EX', 300)
  
  return user
}

The trade-off is cache invalidation — making sure you don't serve stale data after the record changes. This is genuinely tricky, which is why caching should be an optimization you add deliberately, not a default pattern.

What You'll See in Your Code

Cartara often flags SELECT * queries and missing pagination:

// Problematic: fetches all columns and all rows
const products = await db.query('SELECT * FROM products')

// Better: select needed columns, add pagination
const products = await db.query(
  'SELECT id, name, price, category FROM products WHERE active = true ORDER BY created_at DESC LIMIT 20 OFFSET $1',
  [page * 20]
)

Vertical vs horizontal scaling

When you've tuned queries and added caching and still need more capacity:

Vertical scaling (scale up) — give the database server more CPU, RAM, and faster disks. Often just a slider in your provider's dashboard, no code changes. Gets you remarkably far and should be your first move when you need more capacity.

Horizontal scaling (scale out) — spread load across multiple machines. More powerful but much more complex, because data now lives in more than one place.

Read replicas are the easiest horizontal scaling step. The primary handles all writes and replicates to one or more read-only replicas; you route read queries to replicas to spread the load. The catch: replicas may lag slightly behind the primary (replication lag), so a user might not immediately see something they just wrote.

Sharding splits data across separate databases. It removes the single-machine ceiling but adds major complexity — cross-shard queries are hard, and choosing a bad shard key is painful to undo. Most apps never reach this and shouldn't design for it speculatively.

A sane scaling order

1. Add the missing index
2. Fix slow or N+1 queries
3. Enable connection pooling
4. Add caching for hot reads
5. Scale up (bigger machine)
6. Add read replicas
7. Partition large tables
8. Shard — or move to a database built for it

Most teams never get past step 5. Solve the problem you actually have.

Related Concepts

• Data Modeling and Schema Design
• Caching Fundamentals
• Serverless Compute Guide
• From Prototype to Production