PostgreSQL Performance Patterns for Fullstack Apps

Database Performance is Frontend Performance

Slow queries make slow apps. Users don't care if the bottleneck is in the database or the frontend — they just see a loading spinner. After scaling PostgreSQL to handle millions of queries per day, here's what actually matters.

Indexing That Works

The Three Index Types You Need

1. **B-tree indexes** for equality and range queries — your bread and butter

2. **GIN indexes** for full-text search and array operations

3. **Partial indexes** for queries with common WHERE clauses to reduce index size

Composite Index Column Order Matters

-- Good: Most selective column first

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- The reverse is much less useful for user_id queries

The leftmost columns in a composite index are the ones that can be used independently. Plan column order based on your most common query patterns.

Connection Pooling

Connection management is where most apps fail under load. Key rules:

· Use **PgBouncer** in transaction mode for web apps

· Set max connections to (CPU cores * 2) + spare for maintenance

· Monitor **idle in transaction** connections — they're usually a bug

Query Patterns I Use Daily

Pagination with Keyset

-- Instead of OFFSET (gets slow on deep pages)

SELECT * FROM posts

WHERE id > :last_id

ORDER BY id

LIMIT 20;

Keyset pagination is O(log n) while OFFSET-based pagination is O(n). The difference becomes dramatic past page 10.

Materialized Views for Dashboards

If you're computing aggregations on every request, use materialized views refreshed on a schedule. A dashboard that takes 5 seconds to load because of real-time aggregation is a poor experience.

Avoiding N+1 in ORMs

ORMs make N+1 queries easy to write by accident. Always use eager loading and check your query logs. Prisma's `include`, Drizzle's `with`, or raw JOINs — whatever tool you use, understand the query it generates.

Monitoring That Saved Me

Set up alerts for:

· Queries running longer than 500ms

· Connection pool reaching 80% capacity

· Cache hit ratio dropping below 95%

· Dead tuples exceeding 20% of live tuples

These four metrics catch 90% of performance issues before users notice.