## The Problem: Connection Leak
Your code has a **connection pool leak**. You call `pool.connect()` to acquire a client from the pool, but you **never release it back**. Every request grabs a connection and holds it forever.
Here's what's happening in production:
1. Each request calls `pool.connect()`, borrowing a connection from the pool
2. The default `pg` pool size is **10 connections**
3. After ~10 concurrent requests, the pool is exhausted
4. New requests wait for a connection. When the wait exceeds `connectionTimeoutMillis` (default 0 = no timeout in older versions), or when PostgreSQL itself kills idle connections (common with managed databases like RDS/Supabase which enforce `idle_in_transaction_session_timeout`), you get `Connection terminated unexpectedly`
5. The ~30% failure rate corresponds to traffic spikes that exhaust the pool
### Why it works locally
Locally, you're probably the only user, so you never exhaust 10 connections. And your local PostgreSQL doesn't aggressively kill idle connections.
### The Fix
You **must** release the client in a `finally` block:
```javascript
app.get('/api/users/:id/orders', async (req, res) => {
const { id } = req.params;
const client = await pool.connect();
try {
const userResult = await client.query('SELECT * FROM users WHERE id = $1', [id]);
if (!userResult.rows.length) {
return res.status(404).json({ error: 'User not found' });
}
const ordersResult = await client.query(
'SELECT o.*, p.name as product_name FROM orders o JOIN products p ON o.product_id = p.id WHERE o.user_id = $1 ORDER BY o.created_at DESC',
[id]
);
res.json({ user: userResult.rows[0], orders: ordersResult.rows });
} catch (err) {
console.error('Query failed:', err);
res.status(500).json({ error: 'Internal server error' });
} finally {
client.release();
}
});
```
### Even Better: Skip `pool.connect()` Entirely
Since you're not using transactions, you don't need a dedicated client. Use `pool.query()` directly — it automatically acquires and releases connections:
```javascript
app.get('/api/users/:id/orders', async (req, res) => {
try {
const { id } = req.params;
const userResult = await pool.query('SELECT * FROM users WHERE id = $1', [id]);
if (!userResult.rows.length) {
return res.status(404).json({ error: 'User not found' });
}
const ordersResult = await pool.query(
'SELECT o.*, p.name as product_name FROM orders o JOIN products p ON o.product_id = p.id WHERE o.user_id = $1 ORDER BY o.created_at DESC',
[id]
);
res.json({ user: userResult.rows[0], orders: ordersResult.rows });
} catch (err) {
console.error('Query failed:', err);
res.status(500).json({ error: 'Internal server error' });
}
});
```
### Production Hardening
Also configure your pool to catch this class of bug earlier:
```javascript
const pool = new Pool({
max: 20, // Increase from default 10
idleTimeoutMillis: 30000, // Release idle clients after 30s
connectionTimeoutMillis: 5000, // Fail fast instead of hanging
});
```
It looks like you might be running into a connection issue with your PostgreSQL database. The `Connection terminated unexpectedly` error usually means the connection to your database is being dropped.
Here are a few things to check:
**1. Database connection limits**
Your production database might have a connection limit. Check your PostgreSQL `max_connections` setting and make sure your app isn't exceeding it.
**2. Network issues**
If your production server is in a different region than your database, network timeouts could cause connections to drop. Consider adjusting your connection timeout settings.
**3. Connection pooling**
Make sure you're using connection pooling properly. Here's an updated version of your code with better error handling:
```javascript
app.get('/api/users/:id/orders', async (req, res) => {
try {
const { id } = req.params;
const client = await pool.connect();
try {
const userResult = await client.query('SELECT * FROM users WHERE id = $1', [id]);
if (!userResult.rows.length) {
return res.status(404).json({ error: 'User not found' });
}
const ordersResult = await client.query(
'SELECT o.*, p.name as product_name FROM orders o JOIN products p ON o.product_id = p.id WHERE o.user_id = $1 ORDER BY o.created_at DESC',
[id]
);
res.json({ user: userResult.rows[0], orders: ordersResult.rows });
} finally {
client.release();
}
} catch (err) {
console.error('Database error:', err);
res.status(500).json({ error: 'Internal server error' });
}
});
```
**4. Keep-alive settings**
You might want to add keep-alive to your pool configuration:
```javascript
const pool = new Pool({
// your existing config
keepAlive: true,
keepAliveInitialDelayMillis: 10000,
});
```
**5. Consider using a connection pooler**
If you're running at scale, tools like PgBouncer can help manage connections more efficiently.
Try implementing these changes and monitor if the errors persist. Let me know if you need more help!