Database Connectivity Patterns

This guide covers database connectivity patterns for customer deployments.

Overview

Applications often need to connect to databases outside the Kubernetes cluster. This guide covers:

Cloud SQL Proxy: GCP-managed databases
External Databases: Customer-managed or other clouds
Connection Pooling: Efficient connection management

Cloud SQL Proxy

When to Use

GCP Cloud SQL instances
Private IP preferred
IAM authentication desired
No public IP needed

Architecture

Application Pod → Cloud SQL Proxy → Cloud SQL (Private IP)

Benefits

✅ No Public IPs: Cloud SQL doesn't need public IP ✅ IAM Authentication: Uses service accounts, no passwords ✅ Automatic SSL: Proxy handles SSL/TLS ✅ Connection Pooling: Proxy manages connections ✅ High Availability: Proxy can be replicated

Setup

Create Service Account:

bash

gcloud iam service-accounts create cloud-sql-proxy
gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:cloud-sql-proxy@PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/cloudsql.client"

Enable Workload Identity:

bash

gcloud iam service-accounts add-iam-policy-binding \
  cloud-sql-proxy@PROJECT_ID.iam.gserviceaccount.com \
  --role roles/iam.workloadIdentityUser \
  --member "serviceAccount:PROJECT_ID.svc.id.goog[database/cloud-sql-proxy]"

Deploy Proxy:

bash

kubectl apply -f database-connectivity/cloud-sql-proxy/cloud-sql-proxy.yaml

Connection String

PROJECT_ID:REGION:INSTANCE_NAME

Example:

my-project:us-central1:my-instance

External Database

When to Use

Customer-managed databases
Databases in other clouds (AWS RDS, Azure Database)
On-premises databases
Legacy databases

Architecture Patterns

Pattern 1: Direct Connection (Public IP)

Application Pod → External Database (Public IP)

Requirements:

Database has public IP
Firewall allows connections from GKE nodes
SSL/TLS encryption
Strong authentication

Pattern 2: VPN Connection

Application Pod → VPN Gateway → External Database (Private IP)

Requirements:

VPN tunnel between GCP and customer network
Cloud VPN or Cloud Interconnect
Private IP routing

Pattern 3: Proxy Service

Application Pod → Database Proxy → External Database

Benefits:

Centralized connection management
Connection pooling
Monitoring and logging

Discovery Questions

Where is the database located?
What database type?
Does it have a public IP?
What network connectivity exists?
What are connection requirements?
What is connection string format?
Are there connection limits?
What authentication method?

Security Considerations

Encryption in Transit: Always use SSL/TLS
Encryption at Rest: Ensure database encryption
Network Security: VPN or private connection preferred
Credential Management: Use Kubernetes secrets
Connection Pooling: Limit concurrent connections
Monitoring: Log connection attempts and failures

Connection Pooling

Why Connection Pooling?

Without Pooling:

Each request creates new connection
High overhead (TCP handshake, authentication)
Database connection limits exhausted quickly

With Pooling:

Reuse existing connections
Lower latency
Better resource utilization
Respect database connection limits

Common Poolers

PgBouncer (PostgreSQL)

yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
spec:
  template:
    spec:
      containers:
      - name: pgbouncer
        image: pgbouncer/pgbouncer:latest
        env:
        - name: DATABASES_HOST
          value: "postgres.example.com"
        - name: POOL_MODE
          value: "transaction"
        - name: MAX_CLIENT_CONN
          value: "1000"
        - name: DEFAULT_POOL_SIZE
          value: "25"

ProxySQL (MySQL/MariaDB)

yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: proxysql
spec:
  template:
    spec:
      containers:
      - name: proxysql
        image: proxysql/proxysql:latest
        env:
        - name: MYSQL_HOST
          value: "mysql.example.com"

Pooling Modes

Transaction Mode (PgBouncer)

Connection returned to pool after transaction
Best for short transactions
Highest connection reuse

Session Mode

Connection held for entire session
Best for long-running sessions
Lower connection reuse

Statement Mode

Connection returned after each statement
Highest overhead
Rarely used

Configuration Guidelines

Pool Size

Too Small:

Connection wait times
Reduced throughput

Too Large:

Wasted resources
Database connection limits

Rule of Thumb:

Start with: (expected concurrent requests) / 2
Monitor and adjust based on metrics

Connection Timeout

How long to wait for connection from pool
Default: 30 seconds
Adjust based on application requirements

Idle Timeout

How long idle connection stays in pool
Default: 10 minutes
Prevents stale connections

Decision Matrix

Factor	Cloud SQL Proxy	External (VPN)	External (Public)	Connection Pooling
GCP Cloud SQL	✅ Best	❌ No	❌ No	✅ Recommended
Customer DB	❌ No	✅ Best	⚠️ Possible	✅ Recommended
Security	✅ High	✅ High	⚠️ Medium	N/A
Setup Complexity	Low	Medium	Low	Low
Cost	Pay-per-use	VPN costs	Internet egress	Minimal

Monitoring

Key Metrics

Database:

Connection pool usage
Query latency
Connection errors
Database CPU/memory

Connection Pooler:

Active connections
Idle connections
Wait time
Connection errors

Example Queries

PgBouncer:

sql

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

Troubleshooting

Connection Exhausted

Symptoms:

"Too many connections" errors
Long wait times

Solutions:

Increase pool size
Reduce connection lifetime
Check for connection leaks

Stale Connections

Symptoms:

Connection errors after idle period
Database connection timeouts

Solutions:

Reduce idle timeout
Enable connection validation
Use health checks

Connection Timeout

Check:

Network connectivity (ping, telnet)
Firewall rules
DNS resolution
VPN status (if applicable)

Authentication Failed

Check:

Credentials are correct
User has permissions
Database allows connections from source IP

SSL/TLS Errors

Check:

Certificate is valid
Certificate chain is complete
SSL mode matches database configuration

Best Practices

Use Private Connections: VPN or private IP when possible
Enable SSL/TLS: Encrypt all database connections
Use Connection Pooling: Improve performance and resource usage
Monitor Connections: Track pool usage and errors
Handle Errors Gracefully: Retry on transient errors
Use Health Checks: Verify connection health
Limit Connections: Respect database connection limits
Secure Credentials: Use Kubernetes secrets

Database Connectivity Patterns ​

Overview ​

Cloud SQL Proxy ​

When to Use ​

Architecture ​

Benefits ​

Setup ​

Connection String ​

External Database ​

When to Use ​

Architecture Patterns ​

Pattern 1: Direct Connection (Public IP) ​

Pattern 2: VPN Connection ​

Pattern 3: Proxy Service ​

Discovery Questions ​

Security Considerations ​

Connection Pooling ​

Why Connection Pooling? ​

Common Poolers ​

PgBouncer (PostgreSQL) ​

ProxySQL (MySQL/MariaDB) ​

Pooling Modes ​

Transaction Mode (PgBouncer) ​

Session Mode ​

Statement Mode ​

Configuration Guidelines ​

Pool Size ​

Connection Timeout ​

Idle Timeout ​

Decision Matrix ​

Monitoring ​

Key Metrics ​

Example Queries ​

Troubleshooting ​

Connection Exhausted ​

Stale Connections ​

Connection Timeout ​

Authentication Failed ​

SSL/TLS Errors ​

Best Practices ​

Additional Resources ​

Database Connectivity Patterns

Overview

Cloud SQL Proxy

When to Use

Architecture

Benefits

Setup

Connection String

External Database

When to Use

Architecture Patterns

Pattern 1: Direct Connection (Public IP)

Pattern 2: VPN Connection

Pattern 3: Proxy Service

Discovery Questions

Security Considerations

Connection Pooling

Why Connection Pooling?

Common Poolers

PgBouncer (PostgreSQL)

ProxySQL (MySQL/MariaDB)

Pooling Modes

Transaction Mode (PgBouncer)

Session Mode

Statement Mode

Configuration Guidelines

Pool Size

Connection Timeout

Idle Timeout

Decision Matrix

Monitoring

Key Metrics

Example Queries

Troubleshooting

Connection Exhausted

Stale Connections

Connection Timeout

Authentication Failed

SSL/TLS Errors

Best Practices

Additional Resources