Part 043 — PostgreSQL on AWS

1. Why this part matters

AWS changes the operational boundary of PostgreSQL.

With self-managed PostgreSQL, the team owns almost everything:

• OS.
• filesystem.
• package lifecycle.
• PostgreSQL binaries.
• HA tooling.
• WAL archiving.
• backup execution.
• patching.
• monitoring stack.
• failover automation.

With Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-compatible, AWS takes over many infrastructure responsibilities, but not the application data correctness responsibilities.

The database is managed.

The data model is still yours.

The query patterns are still yours.

The migration risk is still yours.

The connection pool behavior is still yours.

The slow SQL is still yours.

The transaction boundary in Java/JAX-RS is still yours.

The dangerous belief is:

Managed database means the database cannot become a production incident.

A managed PostgreSQL service reduces a class of operational burden. It does not remove the need for database engineering discipline.

In a CSG-like Quote & Order platform, AWS PostgreSQL configuration can affect:

• quote creation latency.
• order submission throughput.
• catalog lookup latency.
• approval workflow correctness.
• Kafka/CDC publishing delay.
• migration rollout safety.
• failover behavior visible to Java services.
• backup/restore ability after data corruption.
• audit/compliance evidence.

This part explains how to reason about PostgreSQL on AWS as a senior backend engineer.

2. AWS PostgreSQL deployment options

The common AWS options are:

For most cloud-native enterprise services, RDS or Aurora is usually the first evaluation path.

Do not choose based only on feature list. Choose based on:

• required PostgreSQL version.
• extension compatibility.
• performance profile.
• write/read ratio.
• failover expectation.
• RPO/RTO.
• backup/restore process.
• observability needs.
• cost model.
• operational ownership.
• cloud portability requirements.
• internal platform standard.

Internal verification checklist

Verify with the CSG/team/platform context:

• Is PostgreSQL on AWS used directly by the service?
• Is the engine RDS PostgreSQL, Aurora PostgreSQL-compatible, EC2 self-managed, or external?
• Is the environment dev/test/staging/prod using the same engine family?
• Are there differences between SaaS cloud, on-prem, and hybrid customers?
• Who owns parameter changes: backend team, DBA, SRE, platform, or customer operations?
• Who owns backups and restore drills?
• Who approves extensions?
• Who executes emergency failover?
• Who reviews major/minor version upgrades?

3. Amazon RDS for PostgreSQL mental model

Amazon RDS for PostgreSQL is managed PostgreSQL with an AWS control plane around it.

You still connect using normal PostgreSQL protocol:

Java/JAX-RS service
  -> HikariCP / DataSource
  -> pgJDBC
  -> RDS endpoint
  -> PostgreSQL backend process
  -> tables/indexes/WAL/storage

AWS manages many infrastructure activities:

• provisioning.
• backups.
• patching workflows.
• monitoring integration.
• storage allocation.
• Multi-AZ standby management if enabled.
• snapshots.
• parameter groups.
• read replica creation.

But the application still controls:

• schema design.
• constraints.
• indexes.
• SQL quality.
• transaction scope.
• connection pool sizing.
• migration safety.
• data lifecycle.
• outbox/CDC semantics.
• query timeouts.
• idempotency.
• operational runbooks.

RDS is not an ORM, not a data modelling tool, and not a query tuning substitute.

4. Aurora PostgreSQL-compatible mental model

Aurora PostgreSQL-compatible speaks PostgreSQL-compatible APIs, but the storage architecture is AWS-specific.

At a high level:

Aurora writer instance
  -> distributed Aurora storage layer
  -> reader instances can attach to same cluster storage

This differs from standard PostgreSQL physical storage replication.

For a backend engineer, the key rule is:

Treat Aurora as PostgreSQL-compatible, not as identical to every self-managed PostgreSQL operational assumption.

Evaluate Aurora carefully for:

• supported PostgreSQL versions.
• supported extensions.
• parameter behavior.
• failover time expectations.
• read replica lag behavior.
• performance characteristics.
• query plan differences.
• CDC/logical replication support and constraints.
• backup/restore behaviour.
• cost model.

Aurora can be excellent for certain enterprise workloads, but compatibility should be validated rather than assumed.

Internal verification checklist

If Aurora is used, verify:

• exact Aurora PostgreSQL major/minor version.
• extension support.
• parameter group differences.
• logical replication/CDC compatibility.
• failover behaviour observed in staging.
• whether application uses cluster endpoint, writer endpoint, or reader endpoint.
• how Java services react to writer failover.
• whether read-after-write expectations accidentally use reader endpoints.

5. Endpoint strategy

AWS database endpoint choice matters.

Common endpoint categories:

A dangerous mistake is to send correctness-sensitive reads to a lagging reader.

Example:

POST /orders
  -> insert order on writer
  -> publish command response
  -> GET /orders/{id} routed to reader
  -> reader lag means order appears missing

This creates apparent data loss even though the write committed.

For Java/JAX-RS systems, endpoint choice should be encoded intentionally:

• write DataSource.
• read-only DataSource if lag is acceptable.
• transaction routing rules.
• explicit documentation for consistency expectations.
• fallback strategy when replica lag is high.

PR review questions

Ask:

• Does this query require read-your-writes?
• Can this API tolerate replica lag?
• Is the read endpoint documented?
• Is the connection pool separated for writer and reader?
• Are retries safe after failover?
• Does the API return stale state during failover?

6. VPC, subnet group, and network boundary

AWS PostgreSQL connectivity is shaped by networking.

Core pieces:

• VPC.
• subnet group.
• private subnets.
• security groups.
• route tables.
• NAT, if needed.
• DNS resolution.
• VPC peering or Transit Gateway, if hybrid.
• private connectivity from EKS/ECS/EC2.

A typical production posture:

Internet
  -> load balancer
  -> Java service in private subnet / EKS / ECS
  -> PostgreSQL endpoint in private subnets

Database should usually not be publicly accessible.

Security group rules should be narrow:

allow TCP 5432 from application security group
not: allow TCP 5432 from 0.0.0.0/0

Failure modes

Internal verification checklist

• Is the DB public or private?
• Which subnets host the DB?
• Which security groups can connect?
• Are migration runners inside the same network boundary?
• Does EKS/ECS have private DNS resolution to the DB endpoint?
• Is cross-region or hybrid latency expected?
• Is there a bastion or SSM Session Manager path for emergency access?

7. Security groups and least network access

Security group design should follow service identity boundaries.

Prefer:

DB security group:
  inbound 5432 from quote-service SG
  inbound 5432 from migration-runner SG
  inbound 5432 from approved DBA access path

Avoid:

inbound 5432 from entire VPC CIDR
inbound 5432 from office IP ranges without review
inbound 5432 from public internet

Network access is not authorization.

Even if a service can reach PostgreSQL, it still needs a database role with minimum privileges.

Use both:

• network least privilege.
• database least privilege.

Production review checklist

• Does every service have a distinct database identity?
• Is the migration identity separate from runtime identity?
• Is DBA access audited?
• Is read-only access separated?
• Are security group rules reviewed as part of architecture changes?

8. Parameter groups

RDS/Aurora parameters are managed through parameter groups.

Examples of PostgreSQL-relevant parameters:

• max_connections.
• shared_buffers.
• work_mem.
• maintenance_work_mem.
• effective_cache_size.
• statement_timeout if configured globally.
• idle_in_transaction_session_timeout.
• log_min_duration_statement.
• log_lock_waits.
• deadlock_timeout.
• autovacuum settings.
• track_io_timing.
• logical replication settings if CDC is used.

Some parameters require reboot.

Some can be applied dynamically.

Some are constrained by AWS engine type and instance class.

Parameter changes should be treated as production changes, not console tweaks.

Bad parameter change pattern

Incident: query is slow
  -> increase work_mem globally
  -> many concurrent sessions allocate more memory
  -> memory pressure rises
  -> DB becomes less stable

Better pattern

Incident: query is slow
  -> capture EXPLAIN ANALYZE and wait events
  -> identify sort/hash spill
  -> tune query/index first
  -> consider session-level work_mem for controlled job
  -> test under concurrency
  -> document parameter change if still needed

Internal verification checklist

• Which parameter group is attached to prod?
• Are parameter groups managed by Terraform/GitOps or console?
• Which parameters differ across dev/staging/prod?
• Are parameter changes peer-reviewed?
• Which parameters require reboot?
• Is there a rollback plan for parameter changes?

9. Option and extension support

PostgreSQL extensions are not just libraries. They become production dependencies.

Common extensions in enterprise PostgreSQL systems:

• pg_stat_statements.
• pgcrypto.
• uuid-ossp.
• citext.
• pg_trgm.
• btree_gin.
• btree_gist.
• unaccent.
• PostGIS, if geospatial.
• vector extensions, if approved and supported.

On managed AWS PostgreSQL, extension availability depends on:

• engine family.
• engine version.
• RDS vs Aurora.
• AWS region/support matrix.
• parameter settings.
• superuser limitations.

Never design a schema assuming an extension exists until validated in the target environment.

Migration concern

This migration is not portable unless extension is supported:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_customer_name_trgm
ON customer
USING gin (name gin_trgm_ops);

Before merging:

• verify extension exists in dev/staging/prod.
• verify permission to create extension.
• verify restore into DR environment.
• verify on-prem/hybrid customer support if product is deployable outside AWS.

10. RDS Proxy

RDS Proxy is a managed database proxy for supported RDS engines.

It can help with:

• connection pooling.
• reducing connection storms.
• smoothing failover behavior.
• improving application resilience to transient database unavailability.
• centralizing authentication via Secrets Manager/IAM integration in supported patterns.

It does not fix:

• bad SQL.
• oversized transactions.
• missing indexes.
• unbounded connection pools.
• incorrect retry semantics.
• application-level data correctness bugs.

A simplified model:

Java pods
  -> HikariCP pools
  -> RDS Proxy
  -> RDS PostgreSQL connections

Important: adding RDS Proxy does not mean application-side pool size can be ignored.

If every pod still opens an excessive pool, the proxy may reduce backend connection pressure but the application can still suffer:

• request queueing.
• pool timeout.
• transaction pinning.
• failover blips.
• unexpected latency.

Transaction pinning mental model

Some operations can force a proxy session to stay pinned to a backend connection for the duration of a session/transaction.

Examples to evaluate carefully:

• session state.
• temporary tables.
• prepared statement behavior.
• advisory locks.
• SET commands.
• transaction-scoped behavior.

The exact support and caveats should be verified against current AWS documentation and the specific driver/proxy configuration.

Internal verification checklist

• Is RDS Proxy used?
• Which services connect through it?
• Are application pools still bounded?
• Are there transaction/session pinning concerns?
• Are failover tests run through RDS Proxy?
• Are proxy metrics monitored?
• Is Secrets Manager/IAM auth integrated?

11. IAM authentication and Secrets Manager

AWS gives two broad patterns for database credentials:

1. static database password stored in a secret manager.
2. IAM database authentication, where supported and approved.

Static password pattern:

Java service
  -> reads secret from AWS Secrets Manager or injected environment
  -> opens PostgreSQL connection with username/password

IAM authentication pattern:

Java service role
  -> generates auth token
  -> connects to database using IAM auth flow

Each pattern has trade-offs.

For Java connection pools, credential rotation must be tested.

A common issue:

Secret rotated
  -> old pooled connections remain valid briefly
  -> new connections fail because app did not reload secret
  -> pool gradually degrades

Internal verification checklist

• Where are database credentials stored?
• How are secrets injected into Java services?
• Is rotation automated?
• Does HikariCP handle refreshed credentials correctly?
• Are migration credentials separate?
• Are secrets present in logs, env dumps, CI logs, or Helm values?

12. CloudWatch metrics and logs

RDS emits metrics and logs to AWS observability systems.

Important metric groups:

PostgreSQL logs can include:

• slow queries.
• connection events if configured.
• disconnections.
• lock waits if configured.
• deadlocks.
• autovacuum logs if configured.
• checkpoints if configured.

CloudWatch metrics alone are not enough. Combine them with PostgreSQL internal views:

select * from pg_stat_activity;
select * from pg_locks;
select * from pg_stat_statements;
select * from pg_stat_database;

Java/JAX-RS correlation

Production diagnosis should connect:

HTTP endpoint latency
  -> Java thread pool saturation
  -> HikariCP wait time
  -> SQL execution time
  -> PostgreSQL wait event
  -> RDS CPU/IO/lock/connection metric

If these layers are not correlated, teams guess.

13. Performance Insights and Enhanced Monitoring

Performance Insights helps visualize database load and identify top SQL/wait dimensions in AWS-managed databases.

Use it to answer:

• Is the DB CPU-bound, IO-bound, or lock-bound?
• Which SQL statements dominate database load?
• Which waits dominate during incident windows?
• Did load change after deployment?
• Did a migration change query behavior?
• Is a read replica overloaded?

Enhanced Monitoring provides OS-level visibility, useful when available.

Do not rely only on average CPU.

A PostgreSQL database can be unhealthy with moderate CPU if it is blocked on:

• locks.
• IO waits.
• connection saturation.
• WAL flush.
• temp file spill.
• replication lag.

Internal verification checklist

• Is Performance Insights enabled?
• What retention is configured?
• Can backend engineers access it?
• Are top SQL fingerprints correlated with application endpoints?
• Is Enhanced Monitoring enabled for prod?
• Are CloudWatch alarms tied to runbooks?

14. Snapshots, automated backups, and PITR

AWS managed PostgreSQL can provide automated backups and snapshots.

Important distinction:

Backups are only useful if restore works.

A senior engineer should ask:

When was the last successful restore drill?

Not only:

Is backup enabled?

Data corruption scenario

If a bad application release updates prices incorrectly:

15:00 deploy bad release
15:05 price update job starts corrupting data
15:30 customer impact detected
15:35 rollback app

Backup alone does not solve this cleanly.

You need:

• exact incident timeline.
• point-in-time restore target.
• ability to compare restored DB to current DB.
• data repair plan.
• audit/event replay decision.
• downstream Kafka/event correction strategy.

Internal verification checklist

• Is automated backup enabled?
• What is backup retention?
• Is PITR enabled/available?
• Are snapshots encrypted?
• Are snapshots copied cross-region if required?
• When was the last restore drill?
• Who can initiate restore?
• How is restored DB isolated from production services?

15. Multi-AZ and failover

Multi-AZ is for availability, not read scaling in the single-standby model.

A common misunderstanding:

Multi-AZ means my application can read from the standby.

For standard RDS Multi-AZ standby, the standby is not a normal read endpoint.

Read replicas are separate scaling constructs.

During failover, applications may observe:

• connection drops.
• transaction aborts.
• DNS endpoint changes.
• temporary write unavailability.
• in-flight request failure.
• increased latency.

Application must be prepared.

Java/JAX-RS failover behavior

A failover-safe service should:

• use bounded connection timeout.
• use bounded socket timeout.
• use statement timeout.
• not hold long transactions.
• retry only safe operations.
• use idempotency keys for external commands.
• translate transient DB errors appropriately.
• avoid retrying non-idempotent writes blindly.

Retry classification

16. Read replicas

Read replicas can help offload read-heavy workloads.

They are not a substitute for good indexing or query design.

They introduce replication lag.

Use read replicas for:

• dashboards.
• reporting.
• read-only back-office workflows.
• non-critical list/search pages that tolerate staleness.
• expensive analytical reads separated from writer.

Be careful with:

• approval decisions requiring latest state.
• order state immediately after submission.
• catalog updates that must be visible immediately.
• idempotency key lookup after write.
• optimistic locking reads.

Lag-aware design

A read path should declare one of these:

fresh: must read from writer
bounded-stale: replica allowed if lag below threshold
stale-ok: replica allowed

Internal verification checklist

• Are read replicas used?
• Which endpoints use them?
• Is lag monitored?
• Is there fallback to writer when lag is high?
• Are read replicas used for migrations/backfills/reporting?
• Is replica query load causing lag?

17. Storage autoscaling and disk pressure

RDS storage autoscaling can reduce risk of running out of allocated storage.

But it is not a substitute for storage governance.

Disk pressure can come from:

• table growth.
• index growth.
• bloat.
• WAL retention.
• replication slot lag.
• temp files from sort/hash spill.
• large migrations.
• bulk load.
• failed cleanup jobs.
• unbounded audit/event tables.

Storage growth is often a data lifecycle problem.

Dangerous pattern

Outbox consumer is down
  -> replication slot or outbox table grows
  -> WAL/table storage grows
  -> storage autoscaling buys time
  -> no one fixes root cause
  -> cost and risk increase

Internal verification checklist

• Is storage autoscaling enabled?
• What are storage limits?
• Which tables grow fastest?
• Is WAL growth monitored?
• Are replication slots monitored?
• Are temp files monitored?
• Is retention enforced on audit/outbox/history tables?

18. Maintenance window and patching

Managed database does not mean invisible maintenance.

Maintenance can affect:

• minor version patching.
• instance restart.
• failover.
• parameter application.
• certificate rotation.
• storage maintenance.
• OS-level host maintenance.

Backend services should assume database restarts or failovers can happen.

Production readiness includes:

• short transactions.
• bounded connection acquisition.
• retry with jitter for safe operations.
• idempotency for commands.
• outbox for event publication.
• readiness probes that reflect DB dependency carefully.
• runbooks for connection storm after restart.

Internal verification checklist

• What is the maintenance window?
• Who receives maintenance notifications?
• Are maintenance events tested in staging?
• Is app behavior during DB restart tested?
• Are clients using current TLS certificates?
• Are minor version upgrades automatic or manual?

19. Version upgrades

PostgreSQL version upgrade is not just a platform task.

It can affect:

• query planner behavior.
• reserved keywords.
• extension compatibility.
• JDBC driver compatibility.
• function/procedure behavior.
• collation behavior.
• logical replication compatibility.
• performance plans.
• migration scripts.

Upgrade readiness should include:

1. inventory current version.
2. inventory extensions.
3. test restore/clone on target version.
4. run application integration tests.
5. run migration tests.
6. compare top query plans.
7. run load test for critical paths.
8. validate CDC/outbox pipeline.
9. validate backup/restore after upgrade.
10. document rollback or roll-forward path.

Query plan regression check

Capture before/after for top SQL:

explain (analyze, buffers)
select ...;

Also compare pg_stat_statements baseline after staging traffic replay.

20. AWS PostgreSQL and CDC/Kafka

CDC on AWS PostgreSQL typically involves logical decoding or service-specific integration.

Key concerns:

• wal_level and logical replication settings.
• replication slots.
• WAL retention.
• Debezium connector permissions.
• publication/table selection.
• schema evolution.
• failover behavior.
• replica/slot compatibility.
• large transaction behavior.
• event ordering and idempotency.

A CDC pipeline can silently endanger storage if it stops consuming.

Debezium down
  -> replication slot retained
  -> WAL cannot be recycled
  -> storage grows
  -> DB disk pressure incident

Internal verification checklist

• Is CDC used on AWS PostgreSQL?
• Is Debezium connected to RDS/Aurora directly?
• Which tables are published?
• Are replication slots monitored?
• What happens on failover?
• Are schema changes coordinated with event consumers?
• Is there a backfill/replay strategy?

21. AWS PostgreSQL and Kubernetes/EKS services

When Java services run in EKS and PostgreSQL is RDS/Aurora, pay attention to:

• total connection pool across replicas.
• DNS behavior during failover.
• pod restart storm after DB unavailability.
• secret injection/rotation.
• network policy/security group integration.
• migration Job execution.
• readiness/liveness behavior.
• sidecar/proxy usage.

Connection multiplication example:

30 pods * Hikari maximumPoolSize 20 = 600 possible app connections

If the database max_connections is 500, this is already unsafe before admin sessions, migration jobs, Debezium, BI tools, and replicas are counted.

Safer pool budget

usable_db_connections = max_connections - reserved_admin - replication - migration - observability
per_pod_pool = floor(usable_db_connections / max_expected_pods)

Then validate under load.

22. Migration execution on AWS

Database migrations may be executed from:

• application startup.
• CI/CD runner.
• Kubernetes Job.
• ECS task.
• DBA-managed workflow.
• GitOps pipeline.
• manual break-glass script.

Production-safe migration execution requires:

• network access to DB.
• correct credentials.
• lock timeout.
• statement timeout.
• dry-run/generated SQL review.
• preflight checks.
• rollback/roll-forward plan.
• observability during migration.
• single-writer migration lock.
• approval path.

Avoid letting many pods run migrations concurrently on startup unless explicitly controlled by the migration tool and deployment design.

AWS-specific checks

• Can CI reach private RDS?
• Is migration runner in VPC?
• Is Secrets Manager access granted only to migration job?
• Are security group rules temporary or permanent?
• Are migration logs retained?
• Are RDS events monitored during migration?

23. Common AWS PostgreSQL failure modes

24. Production runbook: RDS/Aurora incident triage

A practical incident sequence:

1. Identify customer impact
2. Identify affected service/API
3. Check app metrics: latency, errors, pool wait
4. Check DB metrics: CPU, connections, IO, storage, lag
5. Check top SQL/wait events
6. Check locks and long transactions
7. Check recent deployments/migrations
8. Check RDS events/maintenance/failover
9. Decide mitigation with lowest blast radius
10. Document timeline and corrective actions

Mitigation options, ordered roughly from safer to riskier:

• reduce traffic to bad endpoint.
• disable expensive feature flag.
• pause non-critical batch/reporting job.
• kill clearly harmful query.
• add temporary statement timeout.
• fail over only if DB/node issue requires it.
• create emergency index only with careful locking strategy.
• restore/PITR only for severe corruption/data loss scenarios.

25. Architecture review checklist for PostgreSQL on AWS

Use this checklist for PRs, ADRs, platform reviews, or service readiness.

Engine and topology

• RDS PostgreSQL vs Aurora vs self-managed is explicit.
• PostgreSQL version is documented.
• extension support is verified.
• Multi-AZ/HA topology is understood.
• read replica strategy is documented.

Connectivity

• DB is private unless a strong exception exists.
• security groups are narrow.
• service-to-DB path is documented.
• migration runner connectivity is defined.
• DNS/failover behavior is tested.

Application integration

• HikariCP pool size accounts for replica count.
• statement/query timeouts exist.
• transient failure handling is safe.
• idempotency exists for non-idempotent commands.
• read/write routing handles replica lag.

Operations

• backups and PITR are enabled if required.
• restore drill has been performed.
• Performance Insights or equivalent is enabled.
• slow query logs are available.
• alerts have runbooks.
• maintenance window is known.

CDC and integration

• replication slots are monitored.
• Debezium/failover behavior is tested.
• WAL retention risk is understood.
• event schema evolution is coordinated.
• replay/reconciliation strategy exists.

Security

• runtime and migration roles are separate.
• Secrets Manager/IAM auth approach is documented.
• secret rotation is tested.
• TLS requirements are documented.
• audit/compliance requirements are covered.

26. Senior engineer heuristics

Use these heuristics:

Managed PostgreSQL reduces infrastructure burden, not correctness burden.

Multi-AZ is not a read scaling design by itself.

Read replicas are stale until proven fresh enough for the use case.

RDS Proxy helps connection management; it does not excuse bad pool sizing.

Backups are not valid until restore has been tested.

Parameter changes are production code changes.

CDC replication slots are storage risk if consumers stop.

Cloud dashboards must be correlated with PostgreSQL internal views and application metrics.

27. Internal verification checklist

For CSG/team context, verify:

• actual AWS PostgreSQL engine and version.
• RDS vs Aurora vs self-managed vs customer-managed deployment.
• parameter group ownership.
• extension allowlist.
• VPC/subnet/security group model.
• whether RDS Proxy is used.
• secret management and rotation.
• connection pool sizing standard.
• Multi-AZ and read replica topology.
• PITR/backup retention and restore drills.
• Performance Insights/CloudWatch/dashboard access.
• slow query log availability.
• maintenance window policy.
• version upgrade process.
• CDC/Debezium/replication slot configuration.
• migration execution model.
• incident escalation path to DBA/SRE/platform.

28. What to remember

PostgreSQL on AWS is a shared responsibility system.

AWS may manage the database infrastructure.

The engineering team still owns:

• schema correctness.
• transaction design.
• query performance.
• connection behavior.
• migration safety.
• data lifecycle.
• event consistency.
• privacy/security posture.
• restore readiness.
• operational debugging.

The senior backend engineer's job is to make sure cloud-managed convenience does not hide database risk until production traffic exposes it.