Skip to main content

Database Configurations

Overview

This document details the database infrastructure for the SMART360 platform, consisting of a PostgreSQL RDS instance serving multiple microservice databases and a PgBouncer connection pooling proxy for optimized database connections.

RDS Configuration

Instance Specifications

  • Engine: PostgreSQL 15
  • Instance Class: db.t3.large
  • Deployment: Single-AZ deployment
  • Storage Type: General Purpose SSD (gp3)
  • Region: us-east-1
  • Read Replicas: None configured

Database Architecture

Multi-Database Setup

The RDS instance hosts 9 separate databases, each serving specific microservice domains:

Database Name

Microservice

auth360v1

Auth service

ax360v1

AX service

bx360v1

BX service

cx360v1

CX service

mx360v1

MX service

onboarding360v1

Onboarding service

wx360v1

WX service

report360v1

Reporting service

notification360v1

Notification service

Schema Organization

  • Database Isolation: Each microservice maintains its own dedicated database
  • Schema Separation: Databases utilize separate schemas for logical data organization
  • Data Sovereignty: Microservices have exclusive access to their respective databases

Database Access Management

User Configuration

  • Master User: authuser
  • Access Pattern: Single master user with administrative privileges
  • Connection Strategy: All microservices connect through the master user via PgBouncer
  • Security Model: Database-level isolation provides security boundaries between microservices

Backup and Recovery

Automated Backup Configuration

  • Backup Status: Enabled
  • Retention Period: 1 Day
  • Backup Window: 21:30-22:00 UTC (GMT)
  • Backup Target: AWS Cloud (US East N. Virginia)
  • Backup Type: Automated snapshots

Recovery Capabilities

  • Point-in-time recovery available within the 1-day retention window
  • Automated daily snapshots for disaster recovery scenarios

PgBouncer Configuration

Deployment Architecture

  • Location: Dedicated EC2 instance in the same VPC as RDS
  • Purpose: Database connection pooling and optimization
  • Connection Flow: ECS Services → PgBouncer → RDS Instance

Connection Pooling Strategy

Pool Management

PgBouncer serves as an intermediary layer providing:

  • Connection Reuse: Reduces connection overhead to the RDS instance
  • Resource Optimization: Manages database connections efficiently across multiple microservices
  • Performance Enhancement: Minimizes connection establishment latency

Database Routing

  • Multi-Database Support: PgBouncer configured to route connections to all 9 databases
  • Service Isolation: Each microservice connects to its designated database through PgBouncer
  • Authentication: Centralized authentication handling through the master user

Network Architecture

Connection Path

ECS Services (cx-web, wx-web, etc.) 
    ↓
PgBouncer (EC2 Instance - Same VPC)
    ↓
RDS PostgreSQL Instance (Same VPC)

Advantages of PgBouncer Implementation

  • Reduced Connection Overhead: Minimizes direct connections to RDS
  • Improved Performance: Connection pooling reduces latency
  • Resource Efficiency: Optimizes database connection utilization
  • Scalability: Supports multiple microservices with efficient connection management

Database Integration Pattern

Microservice Database Mapping

Each microservice in the ECS cluster connects to its corresponding database:

  • CX Services (cx-web, cx-celery, cx-celery-beat) → cx360v1
  • WX Services (wx-web, wx-celery, wx-celery-beat) → wx360v1
  • Additional Services → Respective databases as per service requirements

Connection Management

  • Authentication: All services authenticate using the authuser master credentials
  • Connection Pooling: PgBouncer manages connection pools for optimal resource utilization
  • Database Isolation: Logical separation ensures microservice data independence

Operational Considerations

Monitoring and Maintenance

  • Backup Verification: Daily automated snapshots ensure data protection
  • Connection Monitoring: PgBouncer metrics provide insights into connection usage patterns
  • Performance Optimization: Connection pooling reduces database load and improves response times

Scalability Factors

  • Horizontal Scaling: Additional microservices can be accommodated through PgBouncer
  • Database Growth: Current setup supports expansion of existing databases and addition of new ones
  • Connection Scaling: PgBouncer configuration can be tuned based on service scaling requirements

Security Model

Access Control

  • VPC Isolation: RDS and PgBouncer deployed within the same VPC for network security
  • Database-Level Security: Separate databases provide isolation between microservices
  • Centralized Authentication: Master user approach simplifies credential management while maintaining security

Data Protection

  • Backup Security: Automated snapshots with tag copying for compliance tracking
  • Network Security: VPC-level isolation protects database communications
  • Connection Security: PgBouncer provides an additional security layer between services and database
Back to top