Scale Postgres Before Reaching for NoSQL

You Probably Don't Need Another Database

We've audited dozens of systems where the team added Redis "for performance" or MongoDB "for flexibility." In most cases, the real problem was unoptimized Postgres queries and missing indexes.

The Optimization Ladder

Before adding complexity to your stack, climb this ladder:

1. Fix Your Queries

The single biggest win. Use EXPLAIN ANALYZE on every slow query:

EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 50;

Look for sequential scans on large tables, nested loops where hash joins would be better, and sorts that could be eliminated with proper indexing.

2. Add the Right Indexes

Not just any indexes — the right ones:

-- Composite index matching your query pattern
CREATE INDEX CONCURRENTLY idx_orders_created_customer
ON orders (created_at DESC, customer_id)
WHERE created_at > NOW() - INTERVAL '90 days';
 
-- Partial index for hot queries
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'pending';

3. Connection Pooling

If you're opening a new connection per request, you're leaving massive performance on the table. Use PgBouncer or Supavisor:

# pgbouncer.ini
[databases]
myapp = host=localhost dbname=myapp
 
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

4. Read Replicas

Postgres streaming replication is rock-solid. Route read-heavy queries to replicas:

const readPool = new Pool({ host: 'replica.db.internal' });
const writePool = new Pool({ host: 'primary.db.internal' });
 
async function getOrders(customerId: string) {
  // Read from replica — slightly stale is fine here
  return readPool.query(
    'SELECT * FROM orders WHERE customer_id = $1',
    [customerId]
  );
}

5. Materialized Views

For complex aggregations that don't need real-time data:

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  date_trunc('month', created_at) AS month,
  SUM(total) AS revenue,
  COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY 1;
 
-- Refresh on a schedule
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

When You Actually Need Something Else

Postgres isn't the answer to everything. Reach for specialized tools when:

The Cost of Complexity

Every new database in your stack means:

• Another backup strategy
• Another monitoring dashboard
• Another failure mode at 3 AM
• Another thing new engineers need to learn
• Another data consistency boundary to manage

Make Postgres sweat before you add complexity. You'll be surprised how far it takes you.