PostgreSQL RDS and Aurora

2025-08-19

Deployment

HA/DR

Infra Deployment

Terraform vs Pulumi

• Desired State Configurate
• Code-State File-Infra
• Apply

Tech stack

• Mature and widely adopted and extensive provider support.

  • CMK support for Geo-backup enabled Azure PostgreSQL flexible server

DB Deployment

Prisma vs EF

• Code First
• DSC
• Code-Migrations-DB
• Apply

Issues

• table/column rename

  • EF: Need specific seting, otherwise recreate table may happen

  • Prisma renaming issue

• Column order

  • EF recreate table

  • Prisma columns order issue

Code First vs DB First

GitLab converts the existing Ruby schema from schema.rb to structure.sql

Optimization

Fine-tuning

• Identity
• Explain
• Interpret and analysis
  • estimated vs acutal rows
  • most expensive cost
• Tuning and verify

Stats

• Table Stats
  • Partition Stats
• Column Stats
  • Data distribution
  • Correlation with physical ordering
  • Correlation between columns
• System Stats
  • Seq_page_cost/Random_page_cost = 1/4
  • Scattered Read/Sequential Read = ((10ms+8*2ms)/8) / (10ms+2ms) = 0.27 Jonathan Lewis

Bind value type

• Character columns 32, 128, 2000, or 4000 bytes

• EF string properties to nvarchar(max)

Bind variables

• IN() list queries are not parameterized
• Prevent Prisma from reaching bind parameters limit

DB time model/Wait event

• Snapshot
• Acitve Session History
• Query Plan Managment
  • Upgrade/AB test/SaaS rollout
  • Optimal vs Stability

Long compilation time issue

Monitor

• Performance Insights
• Cloudwatch
• APM

Cloud-native RDS

• AWS log based storage

• Azure SQL DB Hyperscale

Data Engineer