VERCEL_FIX.md

Vercel Deployment Fix - Connection Pool Exhaustion

Problem

The Vercel deployment was failing with error:

remaining connection slots are reserved for roles with the SUPERUSER attribute

This occurred because:

1. Table creation at import time - Base.metadata.create_all() was running every time a serverless function started
2. Connection pooling misconfiguration - Not using NullPool properly for serverless
3. Connection leaks - Connections weren't being disposed properly

Changes Made

1. Fixed api/main.py

• ✅ Removed Base.metadata.create_all(bind=engine) from module-level code
• ✅ Added shutdown event to dispose of connections properly
• ✅ Enhanced health check endpoint to test database connectivity
• ⚠️ Important: Tables must exist before deployment (use migrations)

2. Fixed app/database/database.py

• ✅ Explicitly imported and used NullPool for serverless environments
• ✅ Added detailed comments about serverless connection management
• ✅ Proper configuration for connection timeout and app identification

What This Means

For Development (Local)

• Tables are created via Alembic migrations: alembic upgrade head
• No behavior change for local development

For Production (Vercel)

• CRITICAL: Database tables MUST exist before deployment
• Each serverless function creates a fresh connection
• Connections are disposed after each request
• No connection pool = no connection exhaustion

Deployment Checklist

Before deploying to Vercel:

1. Ensure Database is Migrated

   # Run migrations on your production database
   alembic upgrade head

2. Verify Environment Variables Make sure Vercel has:

   • DATABASE_URL - Your Aiven PostgreSQL connection string
   • ENVIRONMENT - Set to "production"
   • Any other required environment variables

3. Test Locally First

   # Activate virtual environment
   source .venv/bin/activate
   
   # Test with production-like settings
   export DATABASE_URL="your-aiven-database-url"
   python api/main.py
   
   # Visit http://localhost:8000/health
   # Should show: {"database": "connected"}

4. Deploy to Vercel

   # Commit changes
   git add .
   git commit -m "Fix: Remove table creation at startup for Vercel deployment"
   git push
   
   # Vercel will auto-deploy if connected to GitHub

5. Verify Deployment

   • Check Vercel logs for any errors
   • Visit https://your-app.vercel.app/health
   • Should return database status: "connected"
   • Test GraphQL endpoint: https://your-app.vercel.app/graphql

Database Connection Management

Aiven Connection Limits

Check your Aiven plan's connection limits:

• Hobbyist: 25 connections
• Startup: 100 connections
• Business: 200+ connections

Monitoring Connections

Connect to your Aiven database and run:

-- Check current connections
SELECT count(*) FROM pg_stat_activity;

-- Check connection limit
SELECT setting FROM pg_settings WHERE name = 'max_connections';

-- See active connections by application
SELECT application_name, count(*) 
FROM pg_stat_activity 
GROUP BY application_name;

Troubleshooting

If deployment still fails

1. Check Vercel Logs

   vercel logs

2. Verify Database Accessibility

   • Ensure Aiven database allows connections from Vercel IPs
   • Check database SSL requirements
   • Verify connection string format

3. Test Connection Locally

   from sqlalchemy import create_engine, text
   from sqlalchemy.pool import NullPool
   
   engine = create_engine(
       "your-database-url",
       poolclass=NullPool
   )
   
   with engine.connect() as conn:
       result = conn.execute(text("SELECT 1"))
       print("Connection successful!")

4. Check for Lingering Connections

   • Old deployments might still hold connections
   • Wait 5-10 minutes for connections to time out
   • Or manually kill connections in Aiven console

Additional Recommendations

For Better Scalability

1. Consider Connection Pooling Service

   • Use PgBouncer or Aiven's connection pooling
   • Reduces database connection load
   • Better for high-traffic applications

2. Implement Query Timeout

   connect_args = {
       'connect_timeout': 10,
       'command_timeout': 30,  # Query timeout
   }

3. Add Request Timeout to Vercel

   • Configure in vercel.json if needed
   • Prevents long-running connections

For Monitoring

1. Add Application Monitoring

   • Sentry for error tracking
   • DataDog or New Relic for APM

2. Database Monitoring

   • Enable Aiven metrics
   • Set up alerts for connection count
   • Monitor query performance

Files Changed

• ✅ api/main.py - Removed table creation, added connection disposal
• ✅ app/database/database.py - Explicit NullPool configuration
• 📝 VERCEL_FIX.md - This documentation

Next Steps

1. Run migrations on production database
2. Commit and push these changes
3. Deploy to Vercel
4. Monitor logs and health endpoint
5. Test GraphQL functionality

---

Date: October 13, 2025
Issue: Connection pool exhaustion on Vercel
Status: ✅ Fixed - Ready for deployment