cloudnative wiki

AlloyDB for PostgreSQL Architecture & Operations

10 min read

AlloyDB for PostgreSQL Architecture & Operations 🐘⚡

Google Cloud AlloyDB for PostgreSQL is a fully managed, PostgreSQL-compatible relational database service designed for enterprise-grade transactional and analytical workloads (HTAP). AlloyDB decouples database compute from storage using a disaggregated, multi-layered log-processing architecture. It delivers up to 4x faster transactional throughput and up to 100x faster analytical queries than standard community PostgreSQL, while maintaining 100% engine compatibility with PostgreSQL 14/15.

1. Architecture & Disaggregated Engine

AlloyDB decouples compute and storage via an intelligent Log Processing Service (LPS). Unlike traditional monolithic database architectures where write amplification occurs when full database pages and write-ahead logs (WAL) are shipped to disk, AlloyDB only streams WAL records from compute to storage.

                          CLIENT APPLICATIONS / MICROSERVICES
                                           │
                    ┌──────────────────────┴──────────────────────┐
                    │                                             │
             Write Traffic (Port 5432)                   Read Traffic (Port 5432)
                    │                                             │
                    ▼                                             ▼
       ┌────────────────────────┐                   ┌───────────────────────────┐
       │   PRIMARY INSTANCE     │                   │     READ POOL INSTANCE    │
       │  (Single Read-Write)   │                   │ (Autoscaling Read Cluster)│
       │ ┌────────────────────┐ │                   │ ┌───────────────────────┐ │
       │ │  PostgreSQL Engine │ │                   │ │   PostgreSQL Engine   │ │
       │ └──────────┬─────────┘ │                   │ └───────────┬───────────┘ │
       │ ┌──────────▼─────────┐ │                   │ ┌───────────▼───────────┐ │
       │ │ Columnar Engine    │ │                   │ │  Columnar Engine      │ │
       │ │ (In-Memory Cache)  │ │                   │ │  (In-Memory IMCI)     │ │
       │ └──────────┬─────────┘ │                   │ └───────────┬───────────┘ │
       │ ┌──────────▼─────────┐ │                   │ ┌───────────▼───────────┐ │
       │ │ Ultra-Fast Buffer  │ │                   │ │ Ultra-Fast Buffer     │ │
       │ │ Cache (Local NVMe) │ │                   │ │ Cache (Local NVMe)    │ │
       │ └──────────┬─────────┘ │                   │ └───────────┬───────────┘ │
       └────────────┼───────────┘                   └─────────────┼─────────────┘
                    │ WAL Records Only (gRPC)                     │ Block Reads
                    │                                             │
       ═════════════╪═════════════════════════════════════════════╪═════════════════
                    │             HIGH-SPEED GCP BACKPLANE        │
                    ▼                                             ▼
       ┌────────────────────────────────────────────────────────────────────────┐
       │             ALLOYDB INTELLIGENT DISTRIBUTED STORAGE LAYER              │
       │                                                                        │
       │  ┌──────────────────────────────────────────────────────────────────┐  │
       │  │                LOG PROCESSING SERVICE (LPS)                      │  │
       │  │  - Ingests incoming WAL streams asynchronously                   │  │
       │  │  - Materializes modified blocks in-memory continuously           │  │
       │  │  - Removes checkpoint & vacuum write penalties from primary VM   │  │
       │  └─────────────────────────────────┬────────────────────────────────┘  │
       │                                    ▼                                   │
       │  ┌──────────────────────────────────────────────────────────────────┐  │
       │  │               MULTI-ZONE STORAGE REPLICATION                     │  │
       │  │  - Striped block storage automatically scaling up to 128 TiB     │  │
       │  │  - Synchronous quorum replication across 3 Availability Zones    │  │
       │  │  - Instant point-in-time recovery (PITR) with continuous delta   │  │
       │  └──────────────────────────────────────────────────────────────────┘  │
       └────────────────────────────────────────────────────────────────────────┘

Storage-Compute Decoupling Mechanics

  1. WAL-Only Streaming: The primary compute node does not write dirty 8KB data blocks to remote storage. It persists WAL log fragments across redundant storage nodes via low-latency gRPC streams. A transaction commits as soon as the WAL records reach the durable multi-zone quorum.
  2. Log Processing Service (LPS): A distributed fleet of micro-storage processes continuously applies WAL records to base page snapshots in the background. The primary instance is freed from checkpoint overhead, background write operations, and page flushing contention.
  3. Ultra-Fast Buffer Cache: Compute nodes leverage local high-performance NVMe caching as a tier between memory (RAM) and remote storage. If data misses the in-memory shared buffers, it fetches from the local NVMe cache at microsecond latency rather than querying remote network storage.
  4. Columnar Engine (IMCI): An in-memory columnar store automatically identifies analytical query patterns, transforms relevant row-store tables/columns into columnar format in RAM, and vectorizes execution with SIMD CPU instructions.

2. Core Concepts & Subsystems

Cluster Topology

  • Cluster: The top-level administrative boundary encompassing shared storage, network configuration (VPC Peering or Private Service Connect), encryption keys (Google-managed or CMEK), and database configurations.
  • Primary Instance: The singular read-write node responsible for schema migrations, DDL, write transactions, and streaming WAL to the storage layer. If the primary instance fails, a standby compute node replaces it in under 60 seconds without data loss because storage state is independent.
  • Read Pools: Autoscaling, stateless replica groups that serve read-only queries. Unlike standard PostgreSQL streaming replicas that replicate WAL over logical connections, read pool nodes read directly from the shared multi-zone storage layer, eliminating replica lag under heavy write workloads.

Built-in Columnar Engine

AlloyDB embeds an analytical columnar execution engine directly inside PostgreSQL:

  • Automatic Columnar Caching: When enabled (alloydb.enable_columnar_engine = 'on'), a machine-learning background daemon analyzes query history, identifies columns involved in aggregations/scans, and allocates up to a configured percentage of RAM (alloydb.columnar_engine_size) to hold columnar representations.
  • Vectorized Execution: Employs Single Instruction, Multiple Data (SIMD) instruction sets on modern Intel/AMD processors to perform parallel filtering, hashing, and aggregation directly in cache.
  • Dynamic Summarization: Maintains min/max block metadata, zone maps, and dictionary encodings to prune non-matching data partitions before scanning memory.

Cross-Region Replication & Disaster Recovery

  • Secondary Cluster: A complete cluster provisioned in a different GCP region that continuously receives asynchronous storage delta streams from the primary cluster’s storage layer.
  • Promote without Rebuild: If a regional catastrophe strikes the primary cluster, the secondary cluster can be promoted to independent read-write status in minutes with zero snapshot reconstruction overhead.

3. Production Deployment & Management CLI (gcloud)

1. Configure Private Services Access (PSA) Peering

AlloyDB requires VPC private access via Service Networking.

# Reserve an IP range for Google Managed Services
gcloud compute addresses create alloydb-peering-range \
    --global \
    --purpose=VPC_PEERING \
    --prefix-length=20 \
    --description="Peering range for AlloyDB cluster" \
    --network=production-vpc \
    --project=core-infrastructure-prod
 
# Connect VPC to the Service Networking service
gcloud services vpc-peerings connect \
    --service=servicenetworking.googleapis.com \
    --ranges=alloydb-peering-range \
    --network=production-vpc \
    --project=core-infrastructure-prod

2. Create an AlloyDB Cluster with Automated Backups and CMEK

gcloud alloydb clusters create prod-db-cluster \
    --region=us-central1 \
    --network=projects/core-infrastructure-prod/global/networks/production-vpc \
    --kms-key-name=projects/core-infrastructure-prod/locations/us-central1/keyRings/prod-ring/cryptoKeys/alloydb-key \
    --automated-backup-days-of-week=MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY,SUNDAY \
    --automated-backup-start-time=02:00 \
    --automated-backup-retention-count=30 \
    --automated-backup-window=4h \
    --continuous-backup-recovery-window-days=14 \
    --project=core-infrastructure-prod

3. Deploy Highly Available Primary Instance with Columnar Engine

gcloud alloydb instances create prod-primary \
    --cluster=prod-db-cluster \
    --region=us-central1 \
    --instance-type=PRIMARY \
    --cpu-count=16 \
    --availability-type=REGIONAL \
    --database-flags=alloydb.enable_columnar_engine=on,alloydb.columnar_engine_size=25,shared_preload_libraries="alloydb_columnar" \
    --project=core-infrastructure-prod

4. Create an Autoscaling Read Pool

gcloud alloydb instances create prod-read-pool-01 \
    --cluster=prod-db-cluster \
    --region=us-central1 \
    --instance-type=READ_POOL \
    --cpu-count=8 \
    --read-pool-node-count=3 \
    --project=core-infrastructure-prod

5. Verify Cluster Status & Storage Consumption

# Inspect cluster details and storage capacity
gcloud alloydb clusters describe prod-db-cluster \
    --region=us-central1 \
    --project=core-infrastructure-prod \
    --format="yaml(state,clusterType,continuousBackupInfo,encryptionConfig)"
 
# List all instances in the cluster with private IP endpoints
gcloud alloydb instances list \
    --cluster=prod-db-cluster \
    --region=us-central1 \
    --project=core-infrastructure-prod \
    --format="table(name,instanceType,state,ipAddress,nodeCount)"

6. Set Up AlloyDB Auth Proxy for Secure Local/Kubernetes Connections

# Download and execute the AlloyDB Auth Proxy
curl -o alloydb-auth-proxy https://storage.googleapis.com/alloydb-auth-proxy/v1.7.0/alloydb-auth-proxy.linux.amd64
chmod +x alloydb-auth-proxy
 
# Run proxy binding locally to port 5432 with IAM authentication
./alloydb-auth-proxy \
    projects/core-infrastructure-prod/locations/us-central1/clusters/prod-db-cluster/instances/prod-primary \
    --address 0.0.0.0 \
    --port 5432 \
    --auto-iam-authn

4. Quotas, Performance, and Configuration Limits

DimensionDefault / Maximum LimitNotes / Operational Strategy
Max Storage Capacity128 TiB per clusterElastic auto-growth in 10 GB increments; no manual disk resizing
Max Compute Shape128 vCPUs / 864 GiB RAMMemory-optimized N2 machine series underneath
Read Pool NodesUp to 20 nodes per read poolMultiple read pools can be provisioned per cluster
Read Pools per Cluster20 Read PoolsSegment analytical read traffic from OLTP reporting
Continuous Backup Window1 to 35 daysEnables point-in-time recovery down to the exact second
Recovery Point Objective (RPO)0 seconds within regionMulti-zone synchronous quorum log replication
Recovery Time Objective (RTO)< 60 secondsAutomatic failover to healthy standby node without cold restart
PostgreSQL Extensions60+ supportedIncludes pgvector, PostGIS, pg_stat_statements, hypopg

5. Official References & Documentation

6. Realistic Pricing Scenarios

AlloyDB pricing is based on:

  1. Compute (vCPU + RAM): Billed per vCPU hour. E.g., ~0.046 per vCPU-hr (Read Pool / Non-HA) in us-central1.
  2. Storage: $0.17 per GB-month of actual data stored (includes multi-zone replication automatically).
  3. Backup Storage: $0.08 per GB-month for backup storage exceeding the continuous recovery window.
  4. Network Egress: Standard GCP egress rates; internal intra-region traffic is free.

Scenario A: High-Concurrency Tier-1 Financial Ledger (Production OLTP)

  • Architecture:
    • Primary Instance: 16 vCPUs, 128 GiB RAM (Regional HA, active-standby pair managed by platform) = 16 vCPUs running continuously (1.104/hr).
    • Storage: 4,000 GB (4 TiB) transactional data replicated across 3 AZs.
    • Read Pool: 1 Read Pool with two 8-vCPU nodes (16 vCPUs non-HA = 0.736/hr).
    • Backups: 14 days PITR continuous backup (~6,000 GB incremental log storage, 4,000 GB baseline free = 2,000 GB billable at $0.08/GB).
  • Monthly Cost Calculation:
    • Primary Compute: 16 vCPUs × 805.92**
    • Read Pool Compute: 16 vCPUs × 537.28**
    • Managed Storage: 4,000 GB × 680.00**
    • Backup Storage: 2,000 GB × 160.00**
  • Total Monthly Cost: $2,183.20 / month

Scenario B: High-Throughput E-Commerce & Real-Time Analytics (HTAP)

  • Architecture:
    • Primary Instance: 32 vCPUs, 256 GiB RAM (Regional HA = 32 vCPUs × 2.208/hr).
    • Storage: 12,000 GB (12 TiB) product catalog, telemetry, and transactional logs.
    • Analytics Read Pool: 4 nodes of 16 vCPUs each (64 vCPUs non-HA = 64 × 2.944/hr) with Columnar Engine allocating 30% RAM.
    • Backups: 30 days continuous recovery (~18,000 GB total, 12,000 GB included = 6,000 GB billable at $0.08/GB).
  • Monthly Cost Calculation:
    • Primary Compute: 32 vCPUs × 1,611.84**
    • Analytics Read Pool: 64 vCPUs × 2,149.12**
    • Managed Storage: 12,000 GB × 2,040.00**
    • Backup Storage: 6,000 GB × 480.00**
  • Total Monthly Cost: $6,280.96 / month

7. Battle-Tested Nuggets & Production Gotchas

  1. Autovacuum Freezing Does Not Degrade Disk I/O: In standard PostgreSQL, table bloat and transaction ID wraparound (autovacuum_freeze_max_age) cause massive random disk write spikes that freeze the database. In AlloyDB, autovacuum cleanup runs as in-memory index updates and WAL streams; the actual physical page defragmentation and freeze compaction occur asynchronously inside the Log Processing Service (LPS), completely isolating client query latencies from vacuum storms.
  2. Columnar Engine Query Routing Pitfall: The columnar engine only accelerates queries routed to instances where it is actively initialized. If you configure alloydb.enable_columnar_engine = 'on' on read pool nodes, ensure your analytics BI tools (e.g., Looker, Tableau) connect directly to the Read Pool IP address, not the primary instance IP. Running heavy GROUP BY aggregations on the primary instance burns expensive HA CPU cores and risks starving OLTP write transactions.
  3. Read Pool Load Balancing is L4 Client-Side / DNS-based: An AlloyDB Read Pool provides a single private IP address that internally resolves via round-robin or an internal L4 proxy to pool nodes. However, long-lived client connection pools (e.g., HikariCP, PgBouncer) maintain open TCP sockets to specific nodes. If a read pool autoscales from 2 to 6 nodes under peak load, existing connections will not rebalance automatically. You must configure connection lifetime parameters (maxLifetime in HikariCP, server_lifetime in PgBouncer) to ensure connections churn and distribute evenly across scaled nodes.
  4. Primary Instance Resizing Causes a 15-30 Second Cutover: Modifying the CPU shape of an AlloyDB primary instance (e.g., from 8 vCPUs to 16 vCPUs) uses rolling infrastructure replacement. For Regional HA clusters, the standby instance is updated first, a fast failover is executed (< 30 seconds), and the old primary is updated. While downtime is minimal, in-flight non-idempotent write transactions will receive an immediate connection reset by peer error and must be retried with application-level exponential backoff.
  5. Private IP Allocation Size Must Account for Future Read Pools: AlloyDB instances allocate IP addresses from the reserved Service Networking range. Each primary instance, standby instance, and read pool node consumes an internal IP. If you allocate a small /24 subnet for service networking and deploy multiple AlloyDB clusters, Cloud SQL, and Memorystore instances, you will encounter IP_SPACE_EXHAUSTED errors when attempting to scale up read pool node counts during traffic spikes. Always allocate at least a /20 or /19 CIDR block for production VPC peering.
  6. Vector Search Acceleration (pgvector + ScaNN index): AlloyDB includes specialized optimizations for pgvector that integrate Google’s proprietary ScaNN (Scalable Nearest Neighbors) vector search algorithm. By utilizing CREATE INDEX ... USING scann, query latencies on millions of 768-dimensional or 1536-dimensional embedding vectors achieve up to 10x higher QPS and significantly lower P99 latencies compared to standard HNSW or IVFFlat indexes in community PostgreSQL.

Across the wiki

Graph View

Backlinks