Rails 5连接RDS Postgres报错：剩余连接槽仅保留给超级用户

Hey there, let's tackle this Postgres connection exhaustion issue you're facing. That error message means all regular connection slots are maxed out, leaving only the reserved ones for superusers—so we need to fix both the immediate problem and the root causes.

1. First: Diagnose the Connection Bottleneck #

First, let's pinpoint exactly which connections are hogging slots. Run this query in your Postgres instance (via psql, pgAdmin, or RDS Query Editor):

SELECT 
  pid, 
  usename, 
  datname, 
  state, 
  query_start, 
  query 
FROM pg_stat_activity 
WHERE state IN ('idle in transaction', 'idle') 
ORDER BY query_start;

The idle in transaction connections are your biggest culprit here—they hold onto connection slots even though they're not actively doing work. Unlike regular idle connections (which usually get returned to the pool), these are stuck in an open transaction.

2. Immediate Fix: Clear Stuck Idle Connections #

To free up slots right away, terminate connections that have been idle in transaction for a reasonable window (e.g., 5 minutes—adjust based on your app's typical transaction length):

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle in transaction' 
AND query_start < NOW() - INTERVAL '5 minutes';

⚠️ Heads up: Be cautious here—only kill connections you're sure are stuck. Avoid terminating active transactions that are still processing work.

3. Root Cause 1: Fix Rails Connection Pool Misconfiguration #

Rails uses a connection pool to manage database connections, and misconfiguring this is a top cause of exhaustion.

Check Your database.yml #

Open config/database.yml and look at the pool setting. This defines how many connections each Rails process (e.g., Puma worker, Sidekiq process) can hold.

For RDS Postgres, 5% of your max_connections (minimum 5) are reserved for superusers. With your max set to 801, that's ~40 reserved slots, leaving ~761 for your app. So:

• If using Puma: number_of_workers * pool_size <= 761
• If using Sidekiq: sidekiq_concurrency <= pool_size (match the pool to Sidekiq's concurrency to avoid leaks)

Example database.yml snippet:

production:
  adapter: postgresql
  database: your_app_db
  username: your_db_user
  password: your_db_pass
  host: your-rds-endpoint.rds.amazonaws.com
  pool: 20  # Adjust based on your worker count and concurrency needs

Ensure Connections Are Released #

Rails automatically releases connections back to the pool at the end of web requests, but background jobs (like Sidekiq) need extra care. Make sure your jobs don't leave open transactions—use Rails' built-in transaction blocks, which auto-rollback on exceptions:

def perform(user_id)
  User.transaction do
    user = User.find(user_id)
    user.update!(status: "processed")
    # Any exceptions here will trigger an auto-rollback
  end
end

For Sidekiq, you can also explicitly ensure connections are returned to the pool:

Sidekiq::Worker.define_singleton_method(:perform_async) do |*args|
  ActiveRecord::Base.connection_pool.with_connection do
    perform(*args)
  end
end

4. Root Cause 2: Fix Unclosed Transactions in Rails Code #

Most idle in transaction connections come from code that opens a transaction but never commits/rolls it back. Common pitfalls to fix:

• Stop using manual begin/commit calls—always use ActiveRecord::Base.transaction instead (it handles rollbacks automatically)
• Avoid lazy loading associations outside of a transaction scope (this can unintentionally re-open a transaction)
• Ensure exceptions inside transactions are properly handled (Rails' transaction blocks do this by default, so lean into them)

Bad code that can leave stuck transactions:

# ❌ Avoid this
user = User.find(1)
user.begin_transaction
user.update!(name: "Updated Name")
# If an error occurs here, the transaction stays open indefinitely

Good code that avoids leaks:

# ✅ Better: Use Rails' transaction wrapper
User.transaction do
  user = User.find(1)
  user.update!(name: "Updated Name")
end
# Auto-rolls back on any exception, no stuck connections

5. Scale RDS or Add a Connection Pooler (PgBouncer) #

If your app's concurrency is genuinely outgrowing your current RDS instance's limits:

• Upgrade your RDS instance: Larger instance types have higher max_connections limits (check AWS docs for your instance class to confirm).
• Add PgBouncer: This lightweight connection pooler sits between your Rails app and RDS. It reuses a small number of actual RDS connections for many app connections, drastically reducing the total open connections to your database. Perfect for high-concurrency apps with short-lived requests.

6. Prevent Future Issues #

• Monitor connections: Use AWS CloudWatch to track the DatabaseConnections metric for your RDS instance. Set up an alarm when connections hit 80% of your max limit.
• Log transaction activity: Add simple logging to your Rails app to track when transactions start and end. This helps you trace which parts of your code are causing stuck transactions.
• Regularly audit connections: Schedule periodic runs of the pg_stat_activity query to catch idle transactions early before they cause exhaustion.

内容的提问来源于stack exchange，提问作者Afzal Lakdawala