Rails → RDS Proxy Migration Guide¶

Overview¶

RDS Proxy provides connection pooling between application and Aurora, reducing connection overhead and improving failover handling. documentloader already uses RDS Proxy directly. This guide covers migrating Rails to use RDS Proxy, addressing the connection pinning challenges specific to our multi-tenant PerCaseModel architecture.

Current State¶

Component	RDS Proxy?	Connection Path
documentloader (Lambda)	Yes	Lambda → RDS Proxy → Aurora
Rails (Puma/Sidekiq)	No	Rails → Aurora directly via writer/reader hostnames
Rails version	N/A	7.0.8.7, Ruby 3.1.4, mysql2 gem 0.5.6

The Connection Pinning Problem¶

RDS Proxy multiplexes connections — multiple application sessions share fewer database connections. But certain operations pin a connection to a single session, disabling multiplexing. If most connections are pinned, RDS Proxy becomes a passthrough with extra latency.

Pinning Triggers Relevant to Rails¶

Trigger	Rails Impact	Severity
Prepared statements	ActiveRecord uses by default	HIGH
SET statements (timezone, encoding, sql_mode)	Rails issues on connection init	HIGH
Schema switching (`USE {database}`)	PerCaseModel switches per request	CRITICAL
Queries > 16 KB	Large complex queries	MEDIUM
Table locks (`SELECT...FOR UPDATE`)	Bates numbering, dedup	LOW (specific paths only)
Temporary tables	Some report queries	LOW

The PerCaseModel Challenge¶

Our multi-tenant architecture uses PerCaseModel.set_case(case_id) which establishes a connection to the case-specific schema ({RDS_DBNAME}_case_{case_id}). This likely issues USE or establish_connection with a different database, which pins the connection for the entire session.

This is the primary architectural challenge — not a gem version issue.

Migration Steps¶

Step 1: Disable Prepared Statements¶

# config/database.yml
production:
  adapter: mysql2
  prepared_statements: false   # ← ADD THIS
  host: <%= ENV['DB_HOST'] %>  # Will become RDS Proxy endpoint
  reader_host: <%= ENV['DB_READER_HOST'] %>
  database: nextpoint_production
  reconnect: true
  encoding: utf8
  strict: false

Impact: Slight increase in query parse time. Negligible for most workloads.

Step 2: Move Connection Init SET Statements to RDS Proxy¶

Rails issues SET statements when establishing connections (timezone, encoding, sql_mode). Move these to the RDS Proxy initialization query so they don't cause per-connection pinning.

In RDS Proxy configuration (AWS Console or CDK):

-- Initialization query (set in RDS Proxy config)
SET NAMES utf8, time_zone = '+00:00', sql_mode = 'STRICT_TRANS_TABLES'

In Rails, remove equivalent initializers that set these values per connection.

Step 3: Apply Session Pinning Filters¶

For MySQL, RDS Proxy supports session pinning filters that exempt specific operations from causing pinning. Configure these in the proxy:

SessionPinningFilters: EXCLUDE_VARIABLE_SETS

This tells the proxy that SET statements for session variables are safe to multiplex (because the initialization query ensures consistent state).

Step 4: Audit PerCaseModel Schema Switching¶

This is the hardest step. Options:

Option A: Accept Pinning for PerCaseModel Connections - PerCaseModel connections get pinned (unavoidable with USE {database}) - Core model connections (accounts, users, npcases) get multiplexed - Benefit: Partial improvement — core connections pool better - Effort: Low — just point host to proxy endpoint

Option B: Connection-Per-Schema with Proxy - Configure separate RDS Proxy target groups per high-traffic schemas - Not practical for 20K+ schemas

Option C: Schema Prefix Instead of USE - Instead of USE nextpoint_case_123, qualify table names: nextpoint_case_123.exhibits - Avoids the USE statement that causes pinning - Requires significant ActiveRecord adapter changes

Recommendation: Option A — accept that PerCaseModel connections will be pinned, but gain multiplexing benefits for core database connections (accounts, users, batches, jobs). This is the pragmatic path.

Step 5: Update Connection Configuration¶

# config/database.yml (production)
production:
  adapter: mysql2
  prepared_statements: false
  host: <%= ENV['RDS_PROXY_WRITER_ENDPOINT'] %>
  reader_host: <%= ENV['RDS_PROXY_READER_ENDPOINT'] %>
  database: nextpoint_production
  reconnect: true
  encoding: utf8
  strict: false
  pool: 25  # Can be higher with proxy managing actual DB connections

Environment variables (set via SSM Parameter Store):

RDS_PROXY_WRITER_ENDPOINT=nextpoint-proxy.proxy-xxxx.us-east-1.rds.amazonaws.com
RDS_PROXY_READER_ENDPOINT=nextpoint-proxy-reader.proxy-xxxx.us-east-1.rds.amazonaws.com

Step 6: CDK Infrastructure¶

const proxy = new rds.DatabaseProxy(this, 'RailsProxy', {
  proxyTarget: rds.ProxyTarget.fromCluster(auroraCluster),
  secrets: [auroraCluster.secret!],
  vpc,
  vpcSubnets: { subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS },
  requireTLS: true,
  sessionPinningFilters: [rds.SessionPinningFilter.EXCLUDE_VARIABLE_SETS],
  initQuery: "SET NAMES utf8, time_zone = '+00:00'",
  maxConnectionsPercent: 90,
  maxIdleConnectionsPercent: 50,
});

Step 7: Monitor After Deployment¶

CloudWatch metrics to watch:

Metric	Target	Alert If
`DatabaseConnectionsCurrentlySessionPinned`	< 50% of total	> 80% (proxy not helping)
`ClientConnectionsReceived`	Increasing	N/A (more clients using proxy)
`DatabaseConnections`	Decreasing vs pre-proxy	Higher than pre-proxy (proxy adding overhead)
`AvailabilityPercentage`	> 99.9%	< 99.5%
`QueryRequests`	Baseline	Sudden drop (proxy rejecting)

Rollback Plan¶

RDS Proxy endpoint is separate from Aurora endpoint. Rollback = point DB_HOST back to Aurora writer endpoint. No data migration, no schema changes.

# Rollback (SSM Parameter Store update)
RDS_PROXY_WRITER_ENDPOINT → original Aurora writer endpoint
RDS_PROXY_READER_ENDPOINT → original Aurora reader endpoint

Expected Impact¶

With Option A (accept PerCaseModel pinning): - Core database connections (non-case-specific): multiplexed, ~60% fewer actual DB connections - PerCaseModel connections: pinned, no multiplexing benefit, but proxy handles failover - Overall: moderate improvement in connection management, better failover handling - Sidekiq benefit: higher thread count possible without exhausting max_connections

Full benefit requires: Reducing reliance on PerCaseModel schema switching — which is a longer-term architectural change aligned with the npcase database optimization research.

References¶

Ask the Architecture ×

Ask questions about Nextpoint architecture, patterns, rules, or any module. Powered by Claude Opus 4.6.