Configuring connection validation queries for AWS RDS Proxy

This guide is part of Connection Acquisition Timeout Strategies. AWS RDS Proxy relies on connection health checks and lightweight validation to detect and discard stale database connections before routing client traffic. Misconfigured validation parameters cause Connection is not valid errors, increased latency, and unexpected pool exhaustion. This guide provides exact remediation steps, CLI configurations, and validation commands to stabilize your connection lifecycle.

Key Objectives:

  • Identify stale connection symptoms via RDS Proxy CloudWatch metrics
  • Configure pool settings and init_query for lightweight validation
  • Align validation intervals with RDS Proxy idle timeout thresholds
  • Verify configuration using AWS CLI and live connection tests

Understanding RDS Proxy Validation Mechanics

RDS Proxy intercepts client requests and validates backend database connections before routing traffic. Default behavior relies on TCP keepalives at the transport layer. TCP keepalives only verify network reachability, not logical database state.

Explicit SQL validation becomes important when handling logical state changes, read replica promotions, or cluster failovers. RDS Proxy does not expose a dedicated --validation-query CLI flag. Instead, validation behavior is controlled through connection pool configuration parameters (MaxConnectionsPercent, MaxIdleConnectionsPercent, ConnectionBorrowTimeout) and optionally through InitQuery, which executes a SQL statement when each new backend connection is established.

When designing pool behavior, understanding the underlying Pool Architecture & Algorithm Fundamentals helps align validation frequency with connection acquisition patterns. Proper alignment prevents thread starvation during high-concurrency spikes.

Validation points across the proxy path The application pool validates its connection to the proxy, while RDS Proxy independently health-checks its own connections to the database. A client-side validation query proves only the first hop is alive. Application pool isValid() / SELECT 1 on borrow after idle proves hop 1 only hop 1 RDS Proxy client sessions (many) borrow is instant, always "alive" server sessions (few) proxy runs its own health checks hop 2 RDS / Aurora failover swaps the writer without the client noticing proxy absorbs the change A client validation query always succeeds against the proxy, even mid-failover — so it cannot detect a broken hop 2 Keep client validation cheap and idle-only; let the proxy own database liveness
Validation on the client side proves only that the proxy is reachable. Database liveness across the second hop is the proxy's responsibility, which is why heavy client-side probes buy nothing here.

Diagnosing Stale Connection Errors

Stale connections manifest as abrupt query failures and connection pool exhaustion. Monitor CloudWatch metrics to isolate validation bottlenecks. Track DatabaseConnectionsCurrentlyBorrowed and ClientConnectionsCurrentlyBorrowed for divergence.

Application logs will surface explicit errors. Look for ERROR: connection is not valid or FATAL: terminating connection due to administrator command. Correlate these spikes with RDS failover events or transient network partitions.

Metric / Log Pattern Threshold / Indicator Action Required
DatabaseConnectionsCurrentlyBorrowed Sustained > 85% of MaxConnectionsPercent Increase pool size or reduce validation frequency
ClientConnectionsCurrentlyBorrowed Rapid drop to 0 after failover Verify application pool pool_recycle is set below proxy idle timeout
App Log: connection is not valid > 5 errors/minute Check proxy target group health; verify pool_pre_ping or equivalent

High acquisition latency during validation cycles often requires tuning Connection Acquisition Timeout Strategies to prevent client-side timeouts.

Configuring RDS Proxy Connection Pool Parameters via AWS CLI

Apply connection pool configuration directly through the AWS CLI using modify-db-proxy-target-group. This controls how RDS Proxy manages backend connections.

aws rds modify-db-proxy-target-group \
  --db-proxy-name my-proxy \
  --target-group-name default \
  --connection-pool-config '{
    "MaxConnectionsPercent": 80,
    "MaxIdleConnectionsPercent": 50,
    "ConnectionBorrowTimeout": 120,
    "SessionPinningFilters": ["EXCLUDE_VARIABLE_SETS"],
    "InitQuery": "SET TIME ZONE UTC"
  }'

InitQuery runs once per new backend connection; it can initialize session variables but is not a per-checkout validation query. For per-checkout health checks, configure pool_pre_ping=True (SQLAlchemy) or equivalent in your application driver. Always pair proxy configuration with IdleClientTimeout to recycle unused connections proactively.

Validating Configuration & Running Live Tests

Verify applied settings using describe-db-proxy-target-groups. Confirm pool configuration fields match your intended values.

aws rds describe-db-proxy-target-groups \
  --db-proxy-name my-proxy \
  --query "TargetGroups[].ConnectionPoolConfig"

Execute live routing tests to confirm connection establishment. Use IAM auth tokens to simulate production traffic patterns.

PGPASSWORD=$TOKEN psql \
  -h my-proxy.proxy-abc123.us-east-1.rds.amazonaws.com \
  -U admin \
  -d appdb \
  -c "SELECT 1 AS validation_check;"

Monitor query execution latency. If validation overhead exceeds 50ms, investigate backend resource contention or network path degradation.

The Cost Model: What Each Validation Strategy Actually Buys

Validation is not free, and the three available strategies sit at very different points on the cost/coverage curve. Choosing between them is a matter of arithmetic once you know your borrow rate.

Validating on every borrow adds one round trip to every single request. At 2,000 requests per second and a 0.4 ms round trip to the proxy inside the same VPC, that is 800 ms of aggregate latency per second — most of a full core spent proving connections are alive that almost always are. Across a fleet, it also multiplies the query count the proxy sees, which counts against its own connection-borrow accounting.

Validating only after an idle period — HikariCP does this automatically, and most drivers expose an equivalent — reduces the probe rate by two or three orders of magnitude while catching the case that matters: a connection that has been sitting long enough for something in the path to have reaped it. A connection borrowed and returned continuously is never in danger of having been silently closed; a connection that has been idle for ten minutes very much is.

Age-based rotation with no probe at all eliminates the round trip entirely and relies on maxLifetime being shorter than every reaper in the path. This works, and works well, but it fails closed rather than open: if a network component you did not know about reaps at four minutes and your lifetime is twenty-five, you will discover it as intermittent errors rather than as a silent replacement.

Strategy Cost Per Borrow Catches Reaped Socket Catches Failed Backend When It Fits
Probe every borrow 1 round trip, always Yes No — the proxy answers Never, behind a proxy
Probe after idle window ~0 at steady load Yes No — the proxy answers Default choice
Age rotation only Zero Only if lifetime is shorter than the reaper No Well-characterised networks
Probe + short lifetime ~0 at steady load Yes, twice over No Paths with unknown reapers

The column that surprises people is “catches failed backend”. Behind RDS Proxy, no client-side validation strategy detects a database problem, because the proxy answers the probe from its own client-facing session. The proxy is designed to absorb failover precisely so the client does not have to — which means client validation should be tuned for cost, not for coverage it cannot provide.

Validation overhead by strategy and borrow rate Probing on every borrow scales its cost linearly with request rate, while idle-only probing stays flat near zero because a continuously reused connection is rarely idle long enough to be probed. 0 400 800 probe ms / second 100 500 1000 1500 2000 borrows per second probe every borrow probe after idle window age rotation, no probe
Probing on every borrow scales its cost with traffic; idle-window probing stays flat because a busy connection is never idle long enough to qualify.

Tuning Validation Intervals & Timeout Alignment

Ensure IdleClientTimeout strictly exceeds your application pool’s idle_timeout / idleTimeoutMillis to prevent premature recycling on the proxy side.

Parameter Safe Range Production Recommendation
IdleClientTimeout 300s – 1800s 900s (15m) for standard API services
ConnectionBorrowTimeout 30s – 300s 120s to absorb transient validation spikes
MaxIdleConnectionsPercent 20% – 70% 50% to balance memory footprint and reuse
App pool_recycle 75% of IdleClientTimeout Recycle before proxy drops idle connections

Disable aggressive per-checkout validation during high-throughput batch jobs. Excessive checks during bulk inserts or data migrations consume unnecessary backend IOPS. Adjust pool parameters dynamically via CLI or infrastructure templates during maintenance windows.

Configuration Reference

Infrastructure-as-code ensures consistent validation and pool sizing across environments. The following Terraform block enforces MySQL-compatible proxy configuration with strict idle recycling.

resource "aws_db_proxy" "main" {
  name                   = "app-proxy"
  engine_family          = "MYSQL"
  idle_client_timeout    = 1800
  require_tls            = true
  role_arn               = aws_iam_role.rds_proxy.arn
  vpc_subnet_ids         = aws_subnet.private[*].id

  auth {
    auth_scheme = "SECRETS"
    secret_arn  = aws_secretsmanager_secret.db_creds.arn
    iam_auth    = "DISABLED"
  }
}

resource "aws_db_proxy_default_target_group" "main" {
  db_proxy_name = aws_db_proxy.main.name

  connection_pool_config {
    connection_borrow_timeout    = 120
    max_connections_percent      = 90
    max_idle_connections_percent = 50
    session_pinning_filters      = ["EXCLUDE_VARIABLE_SETS"]
  }
}

Deploy this configuration alongside automated drift detection. Validate pool metrics post-deployment to confirm parameter inheritance.

Choosing a validation configuration Branch first on whether pinning is occurring, then on whether the network path contains an unknown idle reaper, arriving at age rotation, idle-window probing, or both. Stale-connection errors? reset by peer / connection closed no age rotation only maxLifetime under the shortest reaper, no probe on the borrow path at all yes Reaper interval known? NAT, NLB, proxy IdleClientTimeout yes lower maxLifetime set it 60 s below the shortest known reaper; re-measure over a full cycle no idle-window probe validate connections idle longer than 30 s, plus age rotation never: probe every borrow pays a round trip on every request for coverage the proxy already gives
Two questions settle the configuration: whether stale-connection errors are actually occurring, and whether every idle reaper in the path is known.

Common Mistakes

  • Using heavy queries as InitQuery: Resource-intensive SQL in InitQuery increases new-connection latency. Keep it to lightweight session initialization such as SET search_path or SET TIME ZONE.
  • Setting proxy IdleClientTimeout shorter than app pool idleTimeoutMillis: The proxy drops connections that the application pool still considers live, causing ECONNRESET errors.
  • Ignoring IAM auth token rotation: RDS Proxy authentication can fail if the IAM token expires mid-cycle. Use short-lived tokens and rotate them before expiry.

FAQ

Does RDS Proxy support per-checkout SQL validation queries?
Not natively. RDS Proxy performs TCP-level health checks and uses InitQuery for session initialization on new backend connections. Per-checkout SQL validation (equivalent to pool_pre_ping) must be implemented in the application driver.
How do I verify RDS Proxy pool configuration is applied correctly?
Run aws rds describe-db-proxy-target-groups --db-proxy-name <name> --query "TargetGroups[].ConnectionPoolConfig" and confirm MaxConnectionsPercent, MaxIdleConnectionsPercent, and ConnectionBorrowTimeout match your intended values.
What happens if the proxy cannot borrow a backend connection within ConnectionBorrowTimeout?
The proxy returns a connection error to the client. The client pool will typically surface this as a connection timeout or acquisition failure. Increase ConnectionBorrowTimeout or reduce MaxConnectionsPercent to free up headroom.
Should pool_pre_ping or connectionTestQuery stay enabled once the proxy is in place?
Turn per-borrow probing off and rely on idle-window validation plus age rotation. The probe only ever proves the proxy is reachable, and the proxy answers it from its own client-facing session — so the coverage you are paying a round trip for does not exist. Keep the probe only if the path between application and proxy crosses a component with an unknown idle timeout.
Does InitQuery count as validation?
No. InitQuery runs once when the proxy opens a backend connection, to set session parameters such as SET search_path. It is not executed per client borrow and does nothing to detect a connection that has gone stale between borrows. Treat it as initialisation, not as a health check.
Why do stale-connection errors cluster at low traffic rather than at peak?
Because a connection has to be idle to be reaped. At peak, every connection is borrowed and returned continuously and never sits long enough for a NAT gateway or the proxy’s own IdleClientTimeout to close it. Overnight and at weekends, connections idle for exactly that long — which is why this failure characteristically appears in the first requests of the morning.