📖 技能介紹

Database Query Best Practices - Prevent Connection Pool Exhaustion

CRITICAL SKILL - READ THIS BEFORE QUERYING RAILWAY DATABASE

The Problem

When querying Railway production database from local development environment, you may encounter:

psycopg2.OperationalError: sorry, too many clients already

This happens when:

Multiple local dev servers are running (npm run dev, npm run develop)
Each dev server holds database connections open
Connection pool limit is reached (300 total connections)
No new connections can be established

ALWAYS Use This Approach

1. Check Connection Pool Status FIRST

Before ANY database query, check active connections:

python3 << 'EOF'
import psycopg2

DATABASE_URL = "postgresql://postgres:password@host:port/railway"

try:
    conn = psycopg2.connect(DATABASE_URL, connect_timeout=10)
    cur = conn.cursor()

    # Check active connections
    cur.execute("""
        SELECT count(*)
        FROM pg_stat_activity
        WHERE datname = 'railway'
    """)
    active = cur.fetchone()[0]

    # Check pool limit
    cur.execute("SHOW max_connections")
    max_conn = cur.fetchone()[0]

    print(f"Active connections: {active}/{max_conn}")

    if active > int(max_conn) * 0.9:
        print(f"WARNING: Connection pool at {(active/int(max_conn))*100:.1f}% capacity")
        print("Consider closing dev servers before querying")
    else:
        print("Connection pool healthy - safe to query")

    cur.close()
    conn.close()

except Exception as e:
    print(f"Cannot connect: {e}")
    print("\nSOLUTION:")
    print("1. Kill local dev servers: pkill -9 -f 'npm run dev'")
    print("2. Wait 30 seconds for connections to close")
    print("3. Try again")
EOF

2. Always Use Context Managers

NEVER do this:

conn = psycopg2.connect(DATABASE_URL)
cur = conn.cursor()
cur.execute("SELECT * FROM users")
# Forgot to close! Connection leak!

ALWAYS do this:

import psycopg2

DATABASE_URL = "postgresql://..."

try:
    conn = psycopg2.connect(DATABASE_URL, connect_timeout=30)
    cur = conn.cursor()

    # Do your queries
    cur.execute("SELECT * FROM users")
    results = cur.fetchall()

    # Process results...

finally:
    # ALWAYS close in finally block
    if cur:
        cur.close()
    if conn:
        conn.close()

3. Use Short-Lived Connections

For one-off queries, open connection, query, close immediately:

def get_user_count():
    """Get user count - connection opened and closed in function"""
    conn = None
    try:
        conn = psycopg2.connect(DATABASE_URL, connect_timeout=30)
        cur = conn.cursor()

        cur.execute("SELECT COUNT(*) FROM users")
        count = cur.fetchone()[0]

        cur.close()
        return count

    finally:
        if conn:
            conn.close()  # Connection immediately released

# Good: Connection closed after function returns
user_count = get_user_count()

4. Kill Dev Servers Before Queries

If you need to run database queries, stop dev servers first:

# Stop all dev servers
pkill -9 -f "npm run dev"
pkill -9 -f "npm run develop"

# Wait for connections to close
sleep 10

# Now safe to query
python3 scripts/your_query_script.py

# Restart dev servers after
cd src/backend && npm run dev &
cd AINative-website && npm run dev &

5. Use Railway CLI for Quick Queries (RECOMMENDED)

Instead of Python scripts, use Railway CLI when possible:

# Login to Railway
railway login

# Link to project
railway link

# Run SQL query directly
railway run psql -c "SELECT COUNT(*) FROM users WHERE created_at >= NOW() - INTERVAL '30 days'"

This uses Railway's managed connections and doesn't consume local pool.

Emergency: Connection Pool Full

If you encounter "too many clients already":

Option 1: Kill Local Dev Servers

pkill -9 -f "npm run dev"
pkill -9 -f "npm run develop"
pkill -9 node
sleep 30  # Wait for DB connections to close

Option 2: Check Active Connections on Railway

railway run psql -c "
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start
FROM pg_stat_activity
WHERE datname = 'railway'
ORDER BY query_start DESC
LIMIT 20"

Option 3: Terminate Idle Connections (LAST RESORT)

railway run psql -c "
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'railway'
  AND state = 'idle'
  AND query_start < NOW() - INTERVAL '10 minutes'"

Pre-Query Checklist

Before running ANY database query, verify:

[ ] Are local dev servers running? → Stop them first
[ ] Is connection pool healthy? → Run connection check
訪問小蔥技能站7w4.net，解鎖更多實用的AI技能外掛。
[ ] Using try/finally to close connections? → Yes
[ ] Using short-lived connections? → Yes
[ ] Can I use Railway CLI instead? → Prefer this