Database Cloud Strategies

BEGINNER

Learn how to implement and manage PostgreSQL in cloud environments using various deployment models including managed services, IaaS, containers, and serverless.

30 min

Tutorial 5 of 8

Learning Objectives

Databases in the Cloud
Database Cloud Deployment Models
Managed PostgreSQL Services
Containerized PostgreSQL Deployments
Serverless PostgreSQL Approaches
Conclusion

Introduction

Learn how to implement and manage PostgreSQL in cloud environments using various deployment models including managed services, IaaS, containers, and serverless.

Databases in the Cloud

Moving databases to cloud environments provides organizations with flexibility, scalability, and often reduced

operational overhead. However, it also introduces new considerations around architecture, data governance,

security, performance, and cost management.

In this lesson, we'll explore various approaches to running PostgreSQL databases in the cloud,

from Infrastructure as a Service (IaaS) deployments to fully managed Database as a Service (DBaaS) offerings.

We'll examine trade-offs between different approaches, best practices for each model, and strategies for

optimizing performance, availability, and cost.

Cloud Strategy Importance

According to Gartner, by 2023, 75% of all databases will be deployed or migrated to a cloud platform.

Choosing the right cloud database strategy is critical for balancing performance, cost, and operational efficiency.

Deployment Models

Managed Services

Containerized

Serverless

Database Cloud Deployment Models

Cloud Deployment Options for PostgreSQL

There are several approaches to running PostgreSQL databases in cloud environments, each with different

trade-offs between control, management overhead, and flexibility.

Infrastructure as a Service (IaaS)

In an IaaS model, you provision virtual machines and install, configure, and manage PostgreSQL yourself.

This approach provides maximum control but also requires the most operational overhead.

Key characteristics:

Full control over the PostgreSQL installation, configuration, and management

Responsibility for OS-level management, security patches, and database upgrades

Control over storage configuration, including RAID arrays or volume managers

Flexibility to use any PostgreSQL version, extensions, or custom configurations

Manual setup of replication, backup strategies, and monitoring

Example implementation:

Provision EC2 instances on AWS, Compute Engine instances on GCP, or VMs on Azure

Install PostgreSQL from packages or compiled from source

Configure replication, backup, and monitoring solutions

Set up connection pooling, load balancing, and security

Platform as a Service (PaaS)

PaaS offerings provide managed runtime environments where you deploy your database while the platform

handles much of the infrastructure management. This reduces operational overhead compared to IaaS

but offers less control.

Key characteristics:

Managed operating system and runtime environment

Platform handles infrastructure provisioning, scaling, and some maintenance tasks

You maintain control over database configuration and management

Automatic patching of underlying infrastructure

Integration with platform monitoring, logging, and security services

Example implementation:

Deploy PostgreSQL to Google App Engine flexible environment

Run PostgreSQL on AWS Elastic Beanstalk

Use Heroku PostgreSQL addons

Database as a Service (DBaaS)

DBaaS offerings provide fully managed PostgreSQL databases where the provider handles provisioning,

high availability, backups, patching, and most administrative tasks. This significantly reduces operational

overhead but provides less control over the underlying infrastructure.

Key characteristics:

Fully managed database infrastructure

Automated backups, patches, and version upgrades

Built-in high availability and replication

Automated scaling (vertical or horizontal)

Integrated monitoring, logging, and alerting

Limited access to underlying OS or infrastructure

Example services:

Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL

Azure Database for PostgreSQL

Google Cloud SQL for PostgreSQL

Digital Ocean Managed PostgreSQL

Aiven for PostgreSQL

Self-managed Kubernetes

Deploying PostgreSQL on Kubernetes gives you the benefits of container orchestration while allowing

more control than a fully managed DBaaS. This approach is growing in popularity as Kubernetes operators

for PostgreSQL mature.

Key characteristics:

Container-based deployment with Kubernetes orchestration

Database-specific operators handle PostgreSQL management

Declarative configuration and GitOps-friendly approach

Platform agnostic (works across cloud providers)

Flexible scaling and self-healing capabilities

Requires Kubernetes expertise

Implementation options:

Zalando Postgres Operator

CloudNativePG (formerly known as EnterpriseDB PostgreSQL Operator)

Crunchy Data PostgreSQL Operator

Stackgres Operator

Serverless Database

Serverless databases automatically scale compute and storage resources based on workload demands, with

no provisioning required. You pay only for the resources consumed, making them ideal for variable workloads.

Key characteristics:

Automatic scaling of compute and storage resources

Pay-per-use pricing model

No need to provision or manage servers

Ability to scale to zero when not in use

Typically comes with built-in high availability

May have limitations compared to traditional PostgreSQL

Example services:

Amazon Aurora Serverless for PostgreSQL

Azure Database for PostgreSQL Serverless

CockroachDB Serverless (PostgreSQL-compatible)

Neon (PostgreSQL-specific serverless platform)

Choosing the Right Deployment Model

Selecting the appropriate cloud database deployment model depends on several factors. Here's a comparison

to help you make the right choice for your organization's needs.

Kubernetes

Serverless

Low-Medium

Operational Overhead

Medium-High

Scalability

Semi-automated

Cost Efficiency

Medium-High

High for variable workloads

Feature Flexibility

Low-Medium

Startup Speed

Legacy systems, specific requirements, custom configurations

Most production workloads, reducing operational burden

Multi-cloud, GitOps workflows, standardized deployments

Variable workloads, development environments, cost optimization

Selection Criteria

Consider these factors when choosing your cloud database deployment model:

Team expertise

: Do you have DBA specialists or is your team more DevOps focused?

Operational requirements

: How much time can you dedicate to database management?

Performance needs

: What are your latency, throughput, and consistency requirements?

Scalability patterns

: Is your workload predictable or highly variable?

Cost constraints

: What's your budget and how important is cost predictability?

Vendor lock-in concerns

: How important is cloud provider independence?

Compliance and security

: Do you have specific requirements that limit your options?

Hybrid Approaches

Many organizations adopt a hybrid approach, using different deployment models for different workloads.

For example, using a managed DBaaS for production while using containerized PostgreSQL for development

and testing environments, or using serverless databases for variable workloads and provisioned instances

for steady-state workloads.

Bash

1# Example: Setup PostgreSQL on an EC2 instance with Amazon Linux 2
2sudo amazon-linux-extras install postgresql13
3sudo systemctl enable postgresql
4sudo systemctl start postgresql
5
6# Configure for external connections
7sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses TO '*';"
8sudo vi /var/lib/pgsql/data/pg_hba.conf
9# Add: host all all 0.0.0.0/0 md5
10
11# Restart PostgreSQL
12sudo systemctl restart postgresql

YAML

1# Example: app.yaml for PostgreSQL on Google App Engine flexible environment
2runtime: custom
3env: flex
4
5resources:
6  cpu: 2
7  memory_gb: 4
8  disk_size_gb: 100
9
10beta_settings:
11  cloud_sql_instances: your-project:your-region:your-instance=tcp:5432

Bash

1# Example: Creating an RDS PostgreSQL instance with AWS CLI
2aws rds create-db-instance \\
3  --db-instance-identifier my-postgres-instance \\
4  --db-instance-class db.m5.large \\
5  --engine postgres \\
6  --allocated-storage 100 \\
7  --master-username admin \\
8  --master-user-password your_password \\
9  --backup-retention-period 7 \\
10  --multi-az \\
11  --engine-version 13.7

YAML

1# Example: Zalando Postgres Operator manifest
2apiVersion: "acid.zalan.do/v1"
3kind: postgresql
4metadata:
5  name: acid-postgres-cluster
6spec:
7  teamId: "database-team"
8  volume:
9    size: 100Gi
10  numberOfInstances: 3
11  postgresql:
12    version: "13"
13    parameters:
14      shared_buffers: "1GB"
15      max_connections: "200"
16  users:
17    appuser:
18    - superuser
19    - createdb
20  databases:
21    appdb: appuser
22  allowedSourceRanges:
23  - 0.0.0.0/0

Bash

1# Example: Creating an Aurora Serverless v2 PostgreSQL cluster with AWS CLI
2aws rds create-db-cluster \\
3  --db-cluster-identifier my-serverless-postgres \\
4  --engine aurora-postgresql \\
5  --engine-version 13.6 \\
6  --engine-mode serverless \\
7  --master-username admin \\
8  --master-user-password your_password \\
9  --scaling-configuration MinCapacity=0.5,MaxCapacity=16,AutoPause=true,SecondsUntilAutoPause=3600,TimeoutAction=ForceApplyCapacityChange

Managed PostgreSQL Services

Major Cloud Provider Offerings

All major cloud providers offer managed PostgreSQL services that handle the operational aspects of running databases,

including high availability, backups, patching, and monitoring. Here's a comparison of the primary offerings.

Amazon RDS for PostgreSQL & Aurora

Amazon Web Services offers two main managed PostgreSQL services: RDS for PostgreSQL, which is a traditional managed

PostgreSQL service, and Aurora PostgreSQL, which is a custom implementation with enhanced performance and features.

RDS for PostgreSQL

Standard PostgreSQL implementation

Multi-AZ deployment for high availability

Automated backups and point-in-time recovery

Supports major PostgreSQL versions

Storage autoscaling

Read replicas for read scaling

Aurora PostgreSQL

Custom storage layer for better performance

6-way replication across 3 AZs

Faster recovery from failures

Storage auto-scaling to 128TB

Up to 15 read replicas with minimal lag

Global database option for multi-region deployments

Serverless option for variable workloads

Unique features:

IAM authentication

: Use AWS IAM roles for database authentication

Performance Insights

: Advanced performance monitoring and analysis

Aurora Serverless

: Auto-scaling PostgreSQL with pay-per-use pricing

: Connection pooling service that handles connection management

: Quickly revert the database to a previous point in time

Azure Database for PostgreSQL

Microsoft Azure offers several deployment options for managed PostgreSQL databases with different levels of

control, scale, and pricing models.

Single Server

Traditional managed PostgreSQL

Vertical scaling up to 64 vCores

Automated backups

High availability within a region

Read replicas for read scaling

Flexible Server

More control over configurations

Zone redundant high availability

Stop/start capability to save costs

Burstable compute tiers

Planned maintenance scheduling

Hyperscale (Citus)

Horizontal scaling via sharding

Scale to hundreds of nodes

Handle TB to PB of data

High concurrency support

Based on Citus extension

Unique features:

Azure Active Directory authentication

: Integrate with Microsoft's identity service

Intelligent Performance

: Automatic query tuning and performance recommendations

Hyperscale (Citus)

: Distributed PostgreSQL for high-scale workloads

Azure Private Link

: Private network access to PostgreSQL services

Advanced Threat Protection

: Security monitoring and threat detection

Google Cloud SQL for PostgreSQL

Google Cloud Platform offers Cloud SQL for PostgreSQL, a fully managed database service that

automates backups, replication, patches, and updates.

Key features:

High availability configuration

: Automated failover to standby in different zone

Automated backups

: Point-in-time recovery and scheduled backups

Read replicas

: Cross-region read replicas for read scaling

Integration with GCP services

: Connect to Compute Engine, GKE, App Engine

Maintenance windows

: Control when maintenance occurs

: Integration with Google Cloud's identity and access management

Automatic storage increase

: Storage grows automatically as needed

Private IP connectivity

: Via VPC Service Controls and Private Service Connect

Machine types:

Shared-core machines

: Cost-effective for development and low-traffic applications

Standard machines

: General-purpose workloads with balanced CPU and memory

High-memory machines

: Memory-intensive workloads like in-memory analytics

High-CPU machines

: Compute-intensive workloads

Containerized PostgreSQL Deployments

Containerization Approaches

Running PostgreSQL in containers provides flexibility and consistency across environments, from development to

production. There are several approaches to containerizing PostgreSQL, each with different trade-offs.

Basic Docker Deployment

The simplest approach is running PostgreSQL in a Docker container using the official PostgreSQL image.

This is ideal for development, testing, or simple production deployments with moderate requirements.

Key considerations:

Use named volumes for data persistence

Configure appropriate resource limits

Ensure proper configuration via environment variables or custom config files

Set up regular backups for data protection

Consider using container orchestration for high availability

Sample PostgreSQL configuration file (

postgresql.conf

Kubernetes StatefulSets

For production deployments, Kubernetes StatefulSets provide a foundation for running PostgreSQL with

stable network identities and persistent storage. This approach is suitable for teams familiar with

Kubernetes who want more control than managed services provide.

Key considerations:

Use Persistent Volume Claims (PVCs) with appropriate storage classes

Configure appropriate resource requests and limits

Implement readiness and liveness probes

Use ConfigMaps and Secrets for configuration

Set up a backup strategy (e.g., with Velero or custom operators)

Implement monitoring with Prometheus and Grafana

Consider node affinity rules for performance

PostgreSQL Operators

PostgreSQL operators extend Kubernetes with custom resources and controllers specifically designed

for managing PostgreSQL clusters. They automate many tasks including deployment, scaling, high availability,

backups, and monitoring.

Zalando Postgres Operator

Created by Zalando, a large e-commerce company

Manages PostgreSQL clusters deployed as StatefulSets

Handles high availability via Patroni

Supports automatic failover

Manages database users and roles

Creates and restores backups

CloudNativePG Operator

CNCF Sandbox project (formerly EDB operator)

Declarative configuration and management

Built-in high availability and failover

Supports rolling updates

Backup and recovery management

Monitoring integration

Crunchy Data PostgreSQL Operator

From Crunchy Data, a PostgreSQL company

Comprehensive PostgreSQL-as-a-Service

Complex deployment topologies

Automated backup and disaster recovery

Monitoring with pgMonitor

Advanced security features

Stackgres Operator

Focus on simplicity and developer experience

WebUI for cluster management

Integrated connection pooling

Automated backups and point-in-time recovery

Bundled set of popular extensions

Graphical query analysis

Example of using Zalando PostgreSQL operator:

Replication and High Availability

Setting up replication and high availability in containerized PostgreSQL deployments requires additional

tools and configurations. Several approaches are commonly used in production environments.

Template for high-availability PostgreSQL

Leader election with DCS (etcd, Consul, ZooKeeper)

Automated failover and recovery

Dynamic configuration management

REST API for status and control

Used by many PostgreSQL operators

Open-source replication manager

Monitors replication status

Manages failover between standby servers

Automated standby clone creation

Integration with various monitoring systems

Suitable for simpler HA setups

Example of Patroni configuration in containers:

Containerized HA Considerations

When implementing high availability in containerized PostgreSQL:

Ensure persistent storage for all PostgreSQL instances

Implement proper resource limits and requests

Configure anti-affinity to distribute replicas across nodes

Implement connection pooling (PgBouncer, Pgpool-II)

Set up service discovery for dynamic endpoints

Establish regular backup procedures

Implement monitoring and alerting for replication lag

Containerized PostgreSQL Best Practices

Running PostgreSQL in containers requires special attention to several aspects to ensure data safety,

performance, and reliability in production environments.

Storage Considerations

Storage is one of the most critical aspects of containerized PostgreSQL deployments:

Use persistent volumes with appropriate storage classes

Select storage with adequate IOPS for your workload

Avoid using host-path volumes in production

Consider using local SSDs for high-performance workloads

Implement storage monitoring and alerting

Ensure volume backups are part of your backup strategy

Test storage performance before production deployment

Resource Management

Proper resource allocation is essential for PostgreSQL performance in containers:

Set appropriate CPU and memory requests and limits

Align PostgreSQL configuration with container resources

Consider using dedicated nodes for database workloads

Implement node anti-affinity for replicas

Use Pod Disruption Budgets to ensure availability

Monitor resource usage and adjust as needed

Be cautious with memory limits (OOM kills can corrupt databases)

Configuration Management

Managing PostgreSQL configuration in containerized environments:

Use ConfigMaps for PostgreSQL configuration files

Leverage Secrets for sensitive configuration

Implement proper ownership and permissions for config files

Consider using tools like Confd or Consul Template for dynamic configuration

Implement configuration validation before applying changes

Use GitOps practices to manage configuration as code

Monitoring and Logging

Effective monitoring is crucial for containerized PostgreSQL:

Implement Prometheus metrics with postgres_exporter

Set up Grafana dashboards for visualization

Configure alerts for key metrics (replication lag, disk space, etc.)

Centralize logs with Elasticsearch, Loki, or similar

Enable appropriate PostgreSQL logging

Implement health checks and readiness probes

Monitor both PostgreSQL and container metrics

Container-Specific PostgreSQL Optimizations

shared_buffers

to about 25-30% of container memory

max_connections

based on your application needs and memory

Use connection pooling (PgBouncer) to manage connection limits

Consider setting

synchronous_commit = off

for performance when appropriate

based on query complexity and available memory

effective_cache_size

to about 75% of container memory

volumes for temporary tablespaces

YAML

1# Example: docker-compose.yml for PostgreSQL
2version: '3.8'
3
4services:
5  postgres:
6    image: postgres:14
7    restart: always
8    environment:
9      POSTGRES_USER: appuser
10      POSTGRES_PASSWORD: apppassword
11      POSTGRES_DB: appdb
12      PGDATA: /var/lib/postgresql/data/pgdata
13    ports:
14      - "5432:5432"
15    volumes:
16      - postgres_data:/var/lib/postgresql/data
17      - ./init-scripts:/docker-entrypoint-initdb.d
18      - ./postgresql.conf:/etc/postgresql/postgresql.conf
19    command: postgres -c 'config_file=/etc/postgresql/postgresql.conf'
20    healthcheck:
21      test: ["CMD-SHELL", "pg_isready -U appuser -d appdb"]
22      interval: 10s
23      timeout: 5s
24      retries: 5
25
26volumes:
27  postgres_data:

INI

1# PostgreSQL configuration
2listen_addresses = '*'
3max_connections = 100
4shared_buffers = 256MB
5effective_cache_size = 768MB
6work_mem = 8MB
7maintenance_work_mem = 64MB
8random_page_cost = 1.1
9effective_io_concurrency = 200
10wal_buffers = 16MB
11min_wal_size = 2GB
12max_wal_size = 8GB
13checkpoint_completion_target = 0.9
14default_statistics_target = 100
15log_min_duration_statement = 500ms
16log_checkpoints = on
17log_connections = on
18log_disconnections = on

YAML

1# Example: PostgreSQL StatefulSet
2apiVersion: apps/v1
3kind: StatefulSet
4metadata:
5  name: postgresql
6spec:
7  serviceName: postgresql
8  replicas: 1
9  selector:
10    matchLabels:
11      app: postgresql
12  template:
13    metadata:
14      labels:
15        app: postgresql
16    spec:
17      containers:
18      - name: postgresql
19        image: postgres:14
20        ports:
21        - containerPort: 5432
22          name: postgresql
23        env:
24        - name: POSTGRES_USER
25          valueFrom:
26            secretKeyRef:
27              name: postgresql-secrets
28              key: username
29        - name: POSTGRES_PASSWORD
30          valueFrom:
31            secretKeyRef:
32              name: postgresql-secrets
33              key: password
34        - name: POSTGRES_DB
35          value: appdb
36        - name: PGDATA
37          value: /var/lib/postgresql/data/pgdata
38        resources:
39          requests:
40            cpu: 500m
41            memory: 1Gi
42          limits:
43            cpu: 2
44            memory: 4Gi
45        volumeMounts:
46        - name: postgresql-data
47          mountPath: /var/lib/postgresql/data
48        - name: postgresql-config
49          mountPath: /etc/postgresql
50        readinessProbe:
51          exec:
52            command:
53            - pg_isready
54            - -U
55            - $(POSTGRES_USER)
56            - -d
57            - $(POSTGRES_DB)
58          initialDelaySeconds: 10
59          timeoutSeconds: 5
60        livenessProbe:
61          exec:
62            command:
63            - pg_isready
64            - -U
65            - $(POSTGRES_USER)
66            - -d
67            - $(POSTGRES_DB)
68          initialDelaySeconds: 30
69          timeoutSeconds: 5
70      volumes:
71      - name: postgresql-config
72        configMap:
73          name: postgresql-config
74  volumeClaimTemplates:
75  - metadata:
76      name: postgresql-data
77    spec:
78      accessModes: [ "ReadWriteOnce" ]
79      storageClassName: "ssd"
80      resources:
81        requests:
82          storage: 100Gi
83---
84# Service for PostgreSQL
85apiVersion: v1
86kind: Service
87metadata:
88  name: postgresql
89spec:
90  ports:
91  - port: 5432
92    targetPort: 5432
93    name: postgresql
94  selector:
95    app: postgresql
96  type: ClusterIP

YAML

1# Example: PostgreSQL cluster with Zalando operator
2apiVersion: acid.zalan.do/v1
3kind: postgresql
4metadata:
5  name: acid-postgres-cluster
6spec:
7  teamId: "database-team"
8  volume:
9    size: 100Gi
10    storageClass: "ssd"
11  numberOfInstances: 3
12  postgresql:
13    version: "14"
14    parameters:
15      shared_buffers: "1GB"
16      max_connections: "200"
17      work_mem: "16MB"
18  resources:
19    requests:
20      cpu: 1
21      memory: 2Gi
22    limits:
23      cpu: 4
24      memory: 8Gi
25  users:
26    appuser:
27    - superuser
28    - createdb
29    readonly:
30    - readonly
31  databases:
32    appdb: appuser
33  preparedDatabases:
34    analytics:
35      extensions:
36        - pg_stat_statements
37        - pg_partman
38  patroni:
39    initdb:
40      encoding: "UTF8"
41      locale: "en_US.UTF-8"
42      data-checksums: "true"
43  additionalVolumes:
44    - name: scripts
45      mountPath: /scripts
46      volumeSource:
47        configMap:
48          name: postgres-scripts
49  maintenanceWindows:
50    - "Mon:01:00-06:00"
51    - "Sat:00:00-04:00"

YAML

1# Example: Patroni configuration with etcd for DCS
2bootstrap:
3  dcs:
4    ttl: 30
5    loop_wait: 10
6    retry_timeout: 10
7    maximum_lag_on_failover: 1048576
8    postgresql:
9      use_pg_rewind: true
10      parameters:
11        wal_level: replica
12        hot_standby: "on"
13        wal_keep_segments: 100
14        max_wal_senders: 10
15        max_replication_slots: 10
16        wal_log_hints: "on"
17  initdb:
18  - encoding: UTF8
19  - data-checksums
20  pg_hba:
21  - host replication replicator 0.0.0.0/0 md5
22  - host all all 0.0.0.0/0 md5
23
24postgresql:
25  listen: 0.0.0.0:5432
26  connect_address: \${HOSTNAME}:5432
27  data_dir: /data/postgresql0
28  bin_dir: /usr/lib/postgresql/14/bin
29  pgpass: /tmp/.pgpass
30  authentication:
31    replication:
32      username: replicator
33      password: rep-pass
34    superuser:
35      username: postgres
36      password: admin-pass
37
38etcd:
39  host: etcd:2379
40
41tags:
42  nofailover: false
43  noloadbalance: false
44  clonefrom: false
45  nosync: false

YAML

1# Example: StorageClass for PostgreSQL in Kubernetes
2apiVersion: storage.k8s.io/v1
3kind: StorageClass
4metadata:
5  name: postgres-storage
6provisioner: kubernetes.io/gce-pd
7parameters:
8  type: pd-ssd
9  fstype: ext4
10  replication-type: none
11reclaimPolicy: Retain
12allowVolumeExpansion: true

YAML

1# Example: Resource configuration aligned with PostgreSQL settings
2resources:
3  requests:
4    cpu: 2
5    memory: 4Gi
6  limits:
7    cpu: 4
8    memory: 8Gi
9
10# Corresponding PostgreSQL settings:
11# shared_buffers = 2GB (25% of memory)
12# work_mem = 64MB
13# maintenance_work_mem = 512MB
14# effective_cache_size = 6GB (75% of memory)
15
16# Node affinity for database workloads
17affinity:
18  nodeAffinity:
19    requiredDuringSchedulingIgnoredDuringExecution:
20      nodeSelectorTerms:
21      - matchExpressions:
22        - key: node-role.kubernetes.io/database
23          operator: Exists

YAML

1# Example: ConfigMap for PostgreSQL configuration
2apiVersion: v1
3kind: ConfigMap
4metadata:
5  name: postgres-config
6data:
7  postgresql.conf: |
8    listen_addresses = '*'
9    max_connections = 100
10    shared_buffers = 2GB
11    work_mem = 64MB
12    maintenance_work_mem = 512MB
13    effective_cache_size = 6GB
14    synchronous_commit = off
15    wal_buffers = 16MB
16    checkpoint_completion_target = 0.9
17    random_page_cost = 1.1
18    log_statement = 'ddl'
19    log_min_duration_statement = 1000
20  pg_hba.conf: |
21    local   all             all                                     trust
22    host    all             all             127.0.0.1/32            trust
23    host    all             all             ::1/128                 trust
24    host    all             all             0.0.0.0/0               md5
25    host    replication     replicator      0.0.0.0/0               md5

YAML

1# Example: PostgreSQL Prometheus exporter sidecar
2- name: postgres-exporter
3  image: prometheuscommunity/postgres-exporter:v0.10.0
4  ports:
5  - containerPort: 9187
6    name: metrics
7  env:
8  - name: DATA_SOURCE_NAME
9    value: "postgresql://postgres:password@localhost:5432/postgres?sslmode=disable"
10  resources:
11    requests:
12      cpu: 100m
13      memory: 128Mi
14    limits:
15      cpu: 200m
16      memory: 256Mi
17  livenessProbe:
18    httpGet:
19      path: /
20      port: 9187
21    initialDelaySeconds: 30
22    timeoutSeconds: 5

Serverless PostgreSQL Approaches

Serverless Database Options

Serverless PostgreSQL options have evolved significantly, offering auto-scaling, pay-per-use billing,

and reduced operational overhead. These solutions are particularly well-suited for variable workloads

and applications where cost optimization is important.

Amazon Aurora Serverless

Amazon Aurora Serverless is an on-demand, auto-scaling configuration for Amazon Aurora that

automatically adjusts database capacity based on application needs. It's designed for workloads

with variable, intermittent, or unpredictable demands.

Aurora Serverless v2

Fine-grained scaling (0.5-128 ACUs)

Scales in less than a second

Compatible with Aurora features

Multi-AZ support

Global database support

Support for read replicas

Key Use Cases

Variable workloads with traffic spikes

Development and test environments

SaaS applications with multi-tenant databases

Seasonal or cyclical workloads

New applications with unknown demand

Infrequently used applications

Configuration options:

Minimum and maximum capacity

: Set bounds for scaling (0.5-128 ACUs)

Auto pause

: Database pauses after specified inactivity period (v1 only)

Scaling timeout action

: Configure what happens when scaling can't be completed

Scaling metrics

: CPU utilization, connections, and available memory

Azure PostgreSQL Serverless

Azure Database for PostgreSQL offers serverless compute through its Flexible Server deployment option.

This model scales compute based on workload demand and can automatically pause during periods of inactivity.

Key features:

Burstable tier

: B-series compute that can burst when needed

Auto-stop/start

: Pause during inactive periods to save costs

Vertical scaling

: Adjust compute size up or down based on workload

Scheduled scaling

: Scale based on predictable patterns

Zone redundancy

: High availability across availability zones

Configuration options:

Compute tier

: Burstable, General Purpose, or Memory Optimized

vCores and memory

: Select appropriate compute size

Auto-stop timeout

: Period of inactivity before database pauses

: Provisioned or burstable IOPS

High availability

: Enable or disable zone redundancy

Specialized Serverless PostgreSQL Providers

Several specialized providers offer serverless PostgreSQL solutions designed with unique architectures

that separate compute and storage to enable true serverless scalability and pricing models.

Serverless PostgreSQL with storage branching

True scale to zero with compute isolation

Branching for development, testing, and CI/CD

Autoscaling of compute resources

Time-travel backups

Built on a custom storage engine

CockroachDB Serverless

PostgreSQL-compatible distributed SQL

Automatic scaling of storage and compute

Multi-region by default

Strong consistency guarantees

Pay-per-use billing model

Built on CockroachDB's distributed architecture

PostgreSQL database with serverless APIs

Built-in authentication and authorization

Auto-generated REST and GraphQL APIs

Real-time subscriptions

Free tier for small projects

Pro plan with higher limits and more features

PlanetScale (MySQL-compatible)

While MySQL-based, offers similar serverless model

Database branching and non-blocking schema changes

Scale to zero capability

Auto-scaling compute and storage

Based on Vitess technology

Serverless pricing model

Architecture differences:

These specialized providers often implement innovative architectural approaches:

Storage and compute separation

: Enable independent scaling

Custom storage engines

: Optimized for cloud-native workloads

Distributed architectures

: For global scale and resilience

Branching capability

: Storage-efficient database cloning

Integrated APIs

: Simplified developer experience

Compatibility Considerations

While these services are PostgreSQL-compatible, they may have differences in supported features,

extensions, and behaviors. Always check compatibility documentation and test your application

thoroughly before committing to a specialized serverless provider.

Serverless Database Best Practices

Serverless PostgreSQL databases have unique characteristics that require specific approaches to

optimize performance, cost, and reliability.

Cost Optimization

Serverless databases charge based on usage, so optimizing usage patterns is critical:

Set appropriate min/max capacity limits for your workload

Enable auto-pause for non-critical or development databases

Implement connection pooling to reduce idle connections

Optimize queries to reduce compute time

Batch operations to reduce the number of database scale-up events

Monitor and analyze usage patterns to identify optimization opportunities

Use read replicas strategically for read-heavy workloads

Performance Management

Serverless databases introduce unique performance considerations:

Account for cold start latency when database scales up

Use appropriate connection pooling to maintain warm connections

Implement retry logic for operations during scaling events

Consider pre-warming for critical workloads

Optimize query patterns to reduce database load

Monitor and alert on scaling events that impact performance

Consider minimum capacity settings for latency-sensitive applications

Reliability and Availability

Ensuring reliability in serverless database environments:

Enable multi-availability zone deployments where available

Implement application-level circuit breakers

Design for graceful degradation during scaling events

Set up automated backups and verify restoration processes

Implement database health checks in your application

Use read replicas for high availability of read operations

Test failover scenarios and recovery procedures

Monitor for scaling failures and capacity constraints

Development Workflows

Adapting development practices for serverless databases:

Use database branching capabilities for development and testing

Implement infrastructure as code for database provisioning

Create separate development, testing, and production database instances

Automate schema migrations with appropriate tooling

Implement database versioning alongside application code

Use feature flags for database-dependent features

Create realistic test data that represents production patterns

Serverless Database Limitations

Be aware of these common limitations in serverless PostgreSQL offerings:

Limited PostgreSQL extensions

compared to self-managed instances

Connection limits

that may require careful connection pooling

Cold start latency

when scaling from zero or minimal capacity

Maximum scaling capacity

lower than provisioned options

Limited access to PostgreSQL configuration parameters

Unpredictable costs

with highly variable workloads

Potential performance variability

during scaling events

Bash

1# Example: Creating Aurora Serverless v2 PostgreSQL cluster with AWS CLI
2aws rds create-db-cluster \\
3    --db-cluster-identifier serverless-pg-cluster \\
4    --engine aurora-postgresql \\
5    --engine-version 13.7 \\
6    --master-username admin \\
7    --master-user-password password123 \\
8    --db-subnet-group-name default \\
9    --vpc-security-group-ids sg-0123456789 \\
10    --serverless-v2-scaling-configuration MinCapacity=0.5,MaxCapacity=16
11
12# Create serverless writer instance
13aws rds create-db-instance \\
14    --db-cluster-identifier serverless-pg-cluster \\
15    --db-instance-identifier serverless-pg-instance \\
16    --engine aurora-postgresql \\
17    --db-instance-class db.serverless

Bash

1# Example: Creating Azure PostgreSQL Flexible Server with serverless features
2az postgres flexible-server create \\
3    --name my-serverless-pg \\
4    --resource-group my-resource-group \\
5    --location eastus \\
6    --admin-user adminuser \\
7    --admin-password password123 \\
8    --sku-name Standard_B1ms \\
9    --tier Burstable \\
10    --storage-size 32 \\
11    --version 14 \\
12    --auto-stop 1440 \\
13    --high-availability Disabled
14
15# Create a database
16az postgres flexible-server db create \\
17    --resource-group my-resource-group \\
18    --server-name my-serverless-pg \\
19    --database-name mydb

JavaScript

1// Example: Optimizing connection pooling with Node.js
2const { Pool } = require('pg');
3
4const pool = new Pool({
5  host: 'your-serverless-postgres.aws.com',
6  user: 'admin',
7  password: 'password',
8  database: 'mydb',
9  max: 20, // Limit max connections
10  idleTimeoutMillis: 30000, // Close idle connections after 30s
11  connectionTimeoutMillis: 2000 // Return error after 2s connection timeout
12});
13
14// Reuse connections from the pool
15async function query(text, params) {
16  const client = await pool.connect();
17  try {
18    return await client.query(text, params);
19  } finally {
20    client.release(); // Always release client back to the pool
21  }
22}

Python

1# Example: Implementing retry logic for serverless databases
2import psycopg2
3import time
4from psycopg2 import pool
5
6# Create a connection pool
7connection_pool = pool.SimpleConnectionPool(
8    minconn=1,
9    maxconn=10,
10    user="admin",
11    password="password",
12    host="your-serverless-postgres.aws.com",
13    database="mydb"
14)
15
16def execute_query_with_retry(query, params=None, max_retries=3, backoff_factor=0.5):
17    """Execute a query with exponential backoff retry logic."""
18    retries = 0
19    last_exception = None
20
21    while retries < max_retries:
22        conn = None
23        try:
24            conn = connection_pool.getconn()
25            cursor = conn.cursor()
26            cursor.execute(query, params)
27            result = cursor.fetchall()
28            conn.commit()
29            connection_pool.putconn(conn)
30            return result
31        except (psycopg2.OperationalError, psycopg2.InterfaceError) as e:
32            last_exception = e
33            if conn:
34                connection_pool.putconn(conn, close=True)  # Force close this connection
35
36            wait_time = backoff_factor * (2 ** retries)
37            print(f"Database operation failed, retrying in {wait_time}s: {str(e)}")
38            time.sleep(wait_time)
39            retries += 1
40
41    # If we get here, we've exhausted our retries
42    raise last_exception

TypeScript

1// Example: Implementing a circuit breaker for database operations
2import { CircuitBreaker } from 'opossum';
3
4class DatabaseService {
5  private pool;
6  private circuitBreaker;
7
8  constructor() {
9    // Initialize database connection pool
10    this.pool = createConnectionPool();
11
12    // Create circuit breaker
13    this.circuitBreaker = new CircuitBreaker(this.executeQuery, {
14      timeout: 5000, // Time in ms before a request is considered failed
15      resetTimeout: 30000, // Time in ms before trying again after circuit opens
16      errorThresholdPercentage: 50, // % of failures before opening circuit
17      rollingCountTimeout: 10000, // Time window for error rate calculation
18      rollingCountBuckets: 10 // Number of buckets for rolling window
19    });
20
21    // Circuit breaker events
22    this.circuitBreaker.on('open', () => {
23      console.log('Circuit breaker opened - database appears unhealthy');
24      // Alert monitoring system
25    });
26
27    this.circuitBreaker.on('close', () => {
28      console.log('Circuit breaker closed - database appears healthy again');
29    });
30  }
31
32  async query(sql, params) {
33    try {
34      // Use circuit breaker to execute query
35      return await this.circuitBreaker.fire(sql, params);
36    } catch (error) {
37      // Handle circuit breaker failure (open circuit or operation failure)
38      console.error('Database operation failed:', error);
39
40      // Fall back to cache, alternative data source, or graceful degradation
41      return this.fallbackBehavior(sql, params);
42    }
43  }
44
45  private async executeQuery(sql, params) {
46    const client = await this.pool.connect();
47    try {
48      const result = await client.query(sql, params);
49      return result.rows;
50    } finally {
51      client.release();
52    }
53  }
54
55  private fallbackBehavior(sql, params) {
56    // Implement appropriate fallback strategy
57    // - Return cached data
58    // - Return empty result with appropriate user feedback
59    // - Query a read replica or alternative data source
60  }
61}

YAML

1# Example: Terraform configuration for Aurora Serverless v2
2resource "aws_rds_cluster" "aurora_serverless" {
3  cluster_identifier      = "aurora-serverless-pg"
4  engine                  = "aurora-postgresql"
5  engine_mode             = "provisioned"
6  engine_version          = "13.7"
7  database_name           = "appdb"
8  master_username         = "admin"
9  master_password         = var.db_password
10  backup_retention_period = 7
11  preferred_backup_window = "03:00-05:00"
12  db_subnet_group_name    = aws_db_subnet_group.aurora.name
13  vpc_security_group_ids  = [aws_security_group.aurora.id]
14  skip_final_snapshot     = true
15
16  serverlessv2_scaling_configuration {
17    min_capacity = 0.5
18    max_capacity = 8
19  }
20
21  tags = {
22    Environment = "development"
23  }
24}
25
26resource "aws_rds_cluster_instance" "aurora_serverless_instance" {
27  identifier          = "aurora-serverless-instance"
28  cluster_identifier  = aws_rds_cluster.aurora_serverless.id
29  instance_class      = "db.serverless"
30  engine              = "aurora-postgresql"
31  engine_version      = "13.7"
32  publicly_accessible = false
33}

Conclusion

Deploying PostgreSQL in the cloud offers numerous advantages in terms of scalability, availability, and

operational efficiency. The choice of deployment model—whether managed services, containerized solutions, or

serverless options—depends on your specific requirements, team expertise, and business constraints.

Regardless of the approach you choose, implementing best practices for security, performance, high availability,

and monitoring is essential for a successful cloud database deployment. Consider starting with managed services

for critical production workloads while exploring containerized or serverless options for development, testing,

or variable workloads.

Additional Resources

PostgreSQL Documentation

Official PostgreSQL documentation with configuration guides and best practices

Cloud Provider Guides

AWS, Azure, and GCP documentation for their PostgreSQL offerings

PostgreSQL Kubernetes Operators

Documentation for Zalando, CloudNativePG, and other PostgreSQL operators

Database Reliability Engineering

Concepts and practices for reliable database operations in cloud environments

Ready to Practice?

Head to the interactive lab to practice implementing cloud-based PostgreSQL deployments with hands-on exercises.

Open Interactive Lab

Key Takeaways

Databases in the Cloud
Database Cloud Deployment Models
Managed PostgreSQL Services
Containerized PostgreSQL Deployments
Serverless PostgreSQL Approaches

What's Next?

Your progress is saved in this browser, on this device only.

Previous: Database DevOps CI/CD

Next: Database Disaster Recovery

Databases

Database Cloud Strategies

Learning Objectives

Introduction

Databases in the Cloud

Database Cloud Deployment Models

Managed PostgreSQL Services

Containerized PostgreSQL Deployments

Serverless PostgreSQL Approaches

Conclusion

Key Takeaways

What's Next?

Table of Contents