Part 044 — PostgreSQL on Azure

1. Why this part matters

Azure Database for PostgreSQL changes the operational model of PostgreSQL, but not the engineering responsibility for data correctness.

A managed PostgreSQL service can help with:

• provisioning.
• backups.
• high availability options.
• patching workflows.
• monitoring integration.
• storage management.
• cloud networking integration.
• identity/secret integration.

It does not automatically solve:

• bad schema design.
• missing constraints.
• unsafe migrations.
• N+1 query patterns.
• broken transaction boundaries.
• incorrect retry behavior.
• event duplication.
• replica lag assumptions.
• data privacy leakage.
• poor observability correlation.

In Java/JAX-RS systems, Azure PostgreSQL is still part of this request path:

HTTP request
  -> JAX-RS resource
  -> service method
  -> transaction boundary
  -> MyBatis mapper / JDBC
  -> Azure Database for PostgreSQL endpoint
  -> PostgreSQL query planner / locks / WAL / storage

Cloud-managed PostgreSQL reduces platform work. It does not remove database design work.

2. Azure PostgreSQL deployment options

Common Azure-related options:

Flexible Server is usually the main managed service to understand.

Do not evaluate Azure PostgreSQL only from the portal screen. Evaluate it as part of the whole backend system:

• Java connection pool.
• MyBatis query patterns.
• transaction boundary.
• migration runner.
• network path.
• identity and secret handling.
• backup/restore process.
• HA/failover behavior.
• observability and alerting.
• CDC/event integration.
• operational ownership.

Internal verification checklist

Verify:

• Is Azure PostgreSQL used for any CSG/team environment?
• Is it Flexible Server, VM self-managed, AKS, or customer-managed?
• Which PostgreSQL version is used?
• Are dev/test/prod on the same engine family?
• Are AWS/Azure/on-prem deployment modes expected to be portable?
• Who owns server parameter changes?
• Who owns HA, backup, restore, and upgrades?
• Who approves extensions and security configuration?

3. Flexible Server mental model

Azure Database for PostgreSQL Flexible Server is a managed PostgreSQL server.

At the application layer, it still looks like PostgreSQL:

Java service
  -> HikariCP
  -> pgJDBC
  -> Azure PostgreSQL endpoint
  -> PostgreSQL backend/session

At the operations layer, Azure wraps PostgreSQL with:

• server lifecycle management.
• compute/storage tiers.
• server parameters.
• backup retention.
• high availability configuration.
• read replica support.
• networking controls.
• monitoring/logging integration.
• maintenance windows.
• identity/security integrations.

The important mental split:

Azure owns parts of the managed service runtime.
Your team owns the correctness of database use.

Correctness includes:

• schema constraints.
• transaction isolation choice.
• locking strategy.
• query plan stability.
• migration compatibility.
• application retry semantics.
• data retention.
• outbox/CDC semantics.
• audit and privacy controls.

4. Server parameters

Azure exposes PostgreSQL configuration through server parameters.

These parameters shape runtime behavior.

Common areas:

• connections.
• memory.
• WAL.
• logging.
• autovacuum.
• query planning.
• extensions.
• timeouts.
• logical replication.

Examples to evaluate:

• max_connections.
• work_mem.
• maintenance_work_mem.
• shared_buffers or service-managed equivalent behaviour.
• statement_timeout.
• idle_in_transaction_session_timeout.
• log_min_duration_statement.
• log_lock_waits.
• deadlock_timeout.
• track_io_timing.
• shared_preload_libraries where applicable.
• logical replication-related parameters if CDC is used.

Treat server parameter changes as production changes.

Dangerous pattern

API latency increased
  -> increase max_connections
  -> more backend processes allowed
  -> DB CPU/memory contention worsens
  -> latency increases further

Connection bottleneck should be diagnosed across:

request rate
  -> app thread pool
  -> Hikari pool wait
  -> PostgreSQL active sessions
  -> CPU/IO/lock waits

Increasing max_connections is not a universal fix.

Internal verification checklist

• Which server parameters differ across environments?
• Are parameters managed by Terraform/Bicep/ARM/GitOps or manual portal changes?
• Which changes require restart?
• Is there a review process for server parameter changes?
• Are timeouts configured globally, per role, or in application pool?
• Are logging parameters sufficient for slow query diagnosis?

5. Networking model

Azure PostgreSQL networking commonly involves:

• public access with firewall rules.
• private access using virtual network integration.
• private endpoint / Azure Private Link patterns.
• DNS zone configuration.
• subnet delegation in some configurations.
• network security groups.
• route tables.
• peering/hub-spoke topology.
• hybrid connectivity via VPN/ExpressRoute.

Production systems generally prefer private connectivity.

The target model is usually:

Internet
  -> application gateway / load balancer
  -> Java service in private network / AKS
  -> Azure PostgreSQL private endpoint or VNet-integrated endpoint

Avoid exposing PostgreSQL broadly through public network rules.

Failure modes

Internal verification checklist

• Is the server public, private, or both?
• Are firewall rules tightly scoped?
• Is private endpoint/private DNS configured correctly?
• Can AKS/App Service/VM workloads resolve the private endpoint?
• Can CI/CD migration runner reach the DB?
• Is hybrid connectivity involved?
• Are DNS and route changes reviewed with platform/SRE?

6. Firewall rules and private endpoints

Firewall rules are easy to create and easy to over-broaden.

Bad pattern:

Allow public access from broad corporate ranges or all Azure services without service-level review.

Better pattern:

Application workload connects through private network path.
Migration runner has explicit, audited access.
DBA access uses controlled operational path.

Private endpoint introduces DNS responsibility.

A common private endpoint issue:

The endpoint exists.
The app still resolves public DNS.
The connection fails or bypasses expected path.

Always test from the same runtime location as the Java service.

Verification command examples

From an approved diagnostic pod/host:

nslookup <server-name>.postgres.database.azure.com

psql "host=<host> dbname=<db> user=<user> sslmode=require"

Do not run ad-hoc diagnostics from unapproved environments in production.

7. Managed identity and authentication

Azure environments may use:

• PostgreSQL native username/password.
• Microsoft Entra ID authentication where configured/supported.
• managed identity integration patterns.
• secret storage through Azure Key Vault.

The design should be explicit.

For Java/HikariCP, authentication affects connection lifecycle.

If credentials rotate, the service must be able to create new connections with new credentials without requiring unsafe manual restarts.

Internal verification checklist

• What authentication method is used?
• Where are credentials stored?
• How are secrets injected into Java services?
• Is Key Vault used?
• Is secret rotation tested?
• Are migration credentials separate from runtime credentials?
• Are read-only/support credentials separate?
• Are credentials visible in logs, Helm values, pipeline variables, or crash dumps?

8. Azure Key Vault integration

Key Vault is commonly used for:

• database passwords.
• client certificates.
• connection strings.
• encryption keys.
• application-level data encryption keys.

But Key Vault only helps if the runtime integration is safe.

Design questions:

• Does the app read secrets at startup only?
• Can the app refresh secrets?
• What happens during secret rotation?
• Are old DB connections drained?
• Is Key Vault availability part of startup reliability?
• Are secret versions pinned or latest?
• Are access policies/RBAC least privilege?

Bad failure mode

Secret rotated
  -> Java pods still hold old password
  -> existing pooled connections survive
  -> new connections fail
  -> partial degradation appears as random DB failures

Safer operational pattern

1. Add/prepare new credential
2. Verify app can read new secret
3. Roll services or refresh pools in controlled order
4. Revoke old credential
5. Monitor connection failures

The exact process depends on internal platform standards.

9. High availability

Azure PostgreSQL HA options are service-specific and region/tier dependent.

The core idea is to avoid a single database node becoming a single point of failure.

HA can involve:

• primary server.
• standby replica.
• synchronous commit behavior depending on service model.
• zone-redundant or same-zone options depending on region/config.
• automatic failover.
• maintenance-driven failover.

For application engineers, HA mainly means:

Connections can break even when the database service is working as designed.

Your Java service must tolerate:

• dropped connections.
• in-flight transaction failure.
• transient connection acquisition failure.
• DNS endpoint movement.
• brief write unavailability.
• commit outcome uncertainty.

Application failover checklist

• Are connection timeouts bounded?
• Are socket/read timeouts bounded?
• Are SQL statements bounded by statement timeout?
• Are long transactions avoided?
• Are retries limited and jittered?
• Are non-idempotent commands protected by idempotency key?
• Is transaction outcome verified before retrying order/quote commands?
• Are outbox publishers resumable?

10. Read replicas

Read replicas can offload read workloads from the primary.

They are useful for:

• reporting.
• dashboard queries.
• exports.
• back-office search/list pages that tolerate staleness.
• analytical reads that should not compete with writes.

They are risky for correctness-sensitive reads.

Replication is usually asynchronous for read replica scenarios, so lag exists.

Staleness categories

Classify read paths:

Example dangerous path:

POST /quotes/{id}/submit
  -> write submitted state on primary
  -> immediate GET /quotes/{id}
  -> read from lagging replica
  -> UI shows old state

This is not a UI bug only. It is a consistency contract bug.

Internal verification checklist

• Are read replicas used?
• Which APIs use replica reads?
• Is replica lag monitored?
• Is there fallback to primary?
• Are read-after-write flows tested?
• Are reporting jobs isolated from transactional workload?

11. Backup, restore, and business continuity

Managed backup is not the same as validated recovery.

Azure PostgreSQL can provide automated backup and restore capabilities depending on configuration and service tier.

Important concepts:

• backup retention.
• point-in-time restore.
• geo-redundant backup if required.
• restore to a new server.
• RPO.
• RTO.
• restore drill.
• backup encryption.
• deleted server restore if supported/configured.

A backup strategy should answer:

What exact incident can this recover from?
How much data can we lose?
How long can we be down?
Who performs restore?
How often do we prove it?

Application-level corruption scenario

For a bad migration or bad application update:

Incorrect SQL updates active quote prices
  -> backups exist
  -> restoring whole DB would lose valid concurrent work
  -> team needs isolated restore + data diff + repair script

So the runbook needs:

• timeline.
• isolated restore environment.
• comparison queries.
• repair strategy.
• event/outbox correction.
• customer impact analysis.

Backups are infrastructure. Recovery is a workflow.

Internal verification checklist

• What is backup retention?
• Is PITR available and tested?
• Is geo-redundant backup enabled if required?
• When was the last restore drill?
• Who can restore?
• Where is restored DB created?
• How are restored credentials handled?
• How is data diff/repair performed after logical corruption?

12. Azure Monitor

Azure Monitor is the main entry point for Azure resource metrics/logs.

For PostgreSQL, useful categories include:

• CPU.
• memory.
• storage.
• IOPS.
• connections.
• active sessions.
• failed connections.
• replication lag.
• backup storage.
• server logs.
• query-related telemetry where configured.

But cloud metrics must be correlated with PostgreSQL internal views.

Use both:

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

Application correlation:

Azure Monitor metric spike
  + Java endpoint latency
  + Hikari pool wait
  + pg_stat_activity wait event
  + top SQL fingerprint
  = useful diagnosis

Without correlation, dashboards become decorative.

Internal verification checklist

• Are Azure Monitor metrics enabled and retained?
• Are PostgreSQL logs collected?
• Is slow query logging configured?
• Is pg_stat_statements enabled?
• Are alerts connected to runbooks?
• Can backend engineers access dashboards during incidents?

13. Query Store

Azure Database for PostgreSQL may provide Query Store capability depending on service and configuration.

The point of a query store is to track query performance over time.

Use it to answer:

• Which query regressed after deployment?
• Did average latency or p95 change?
• Did plan behavior change?
• Which queries consume most resources?
• Did a migration or stats change affect planner choices?

Do not use Query Store as a replacement for understanding EXPLAIN.

Use it as a historical signal.

For a bad query, still inspect:

explain (analyze, buffers)
select ...;

Internal verification checklist

• Is Query Store enabled?
• What retention is configured?
• Who can access it?
• Are query IDs/fingerprints mapped back to MyBatis mapper IDs?
• Are regressions reviewed after deployments?
• Is it included in performance RCA?

14. Storage scaling and disk lifecycle

Storage pressure is often caused by data lifecycle problems, not only storage configuration.

Sources of growth:

• quote/order history.
• audit trail.
• state transition history.
• outbox/inbox events.
• CDC/WAL retention.
• failed replication consumers.
• JSONB/catalog snapshots.
• indexes.
• bloat.
• temp files.
• large backfills.
• materialized views.

Storage scaling can buy time. It does not define retention.

A production-grade PostgreSQL system needs:

• table growth dashboard.
• index growth dashboard.
• bloat monitoring.
• retention policy.
• archival process.
• outbox cleanup.
• audit retention decision.
• backfill storage estimate.

Internal verification checklist

• Is storage auto-growth/scaling configured?
• What is max storage?
• Which tables grow fastest?
• Are audit/outbox/history tables retained forever?
• Are temp files monitored?
• Is bloat measured?
• Is there an archival policy?

15. Maintenance window and patching

Azure-managed PostgreSQL still has maintenance events.

Maintenance can involve:

• PostgreSQL minor version updates.
• underlying host maintenance.
• restart.
• failover.
• certificate updates.
• storage or platform maintenance.

Backend services should treat planned maintenance like controlled chaos testing.

They should survive:

• broken connections.
• transaction failures.
• temporary connection refusal.
• failover latency.
• new backend sessions.

Service readiness checklist

• Hikari connection timeout is bounded.
• SQL statements have timeouts.
• long transactions are avoided.
• retries are safe and limited.
• idempotency key protects command APIs.
• outbox/inbox workers resume.
• migration jobs are not running during maintenance unless approved.
• customer-facing teams know expected impact.

16. Version upgrades

PostgreSQL version upgrades can change more than version number.

Possible impacts:

• query planner changes.
• extension compatibility.
• collation behavior.
• reserved keywords.
• function/procedure behavior.
• JDBC compatibility.
• logical replication behavior.
• migration scripts.
• monitoring views.
• cloud service parameter defaults.

Upgrade process should include:

1. Inventory current server version
2. Inventory extensions
3. Restore/clone production-like data to target version
4. Run migrations
5. Run integration tests
6. Compare top query plans
7. Run load tests for critical APIs
8. Validate CDC/outbox
9. Validate backup/restore after upgrade
10. Document cutover and rollback/roll-forward plan

MyBatis-specific check

Because MyBatis exposes SQL directly, upgrade testing should include mapper-level query coverage:

• dynamic SQL branches.
• pagination queries.
• JSONB operators.
• FTS queries.
• stored function/procedure calls.
• lock queries.
• bulk operations.

17. Azure PostgreSQL and CDC/Kafka

If CDC is used, PostgreSQL logical decoding and replication slots become operational concerns.

CDC pipeline shape:

PostgreSQL WAL
  -> logical decoding / replication slot
  -> Debezium or equivalent connector
  -> Kafka topic
  -> downstream consumers

Main risks:

• replication slot retains WAL if connector is down.
• schema changes break connector or consumers.
• large transactions produce delayed events.
• failover behaviour must be understood.
• publication/table selection may miss required data.
• duplicate events are normal under retries.
• out-of-order processing must be handled.

Event consistency rule

Do not depend on CDC alone to provide business exactly-once semantics.

Use:

• transactional outbox where appropriate.
• idempotent consumers.
• event keys.
• schema governance.
• replay strategy.
• reconciliation jobs.

Internal verification checklist

• Is logical replication enabled?
• Which connector is used?
• Are replication slots monitored?
• What happens if connector stops for hours?
• Are WAL/storage alerts connected to CDC runbook?
• Are schema migrations coordinated with Kafka consumers?
• Are duplicate/replay events tested?

18. Azure PostgreSQL with AKS-based Java services

When Java services run in AKS and PostgreSQL runs as Azure managed service, key integration concerns are:

• private DNS resolution from pods.
• network policy and subnet route.
• total Hikari pool across replicas.
• secret injection from Key Vault.
• pod restart storm after DB failover.
• migration job network access.
• readiness probe behavior.
• service mesh/proxy timeout interaction.
• observability correlation across AKS and Azure PostgreSQL.

Connection multiplication example:

20 pods * maximumPoolSize 25 = 500 possible application connections

This number must fit within:

PostgreSQL max_connections
  - admin reserve
  - migration jobs
  - monitoring sessions
  - CDC sessions
  - BI/reporting sessions
  - other services

A managed DB does not protect you from over-connecting.

19. Migration execution on Azure

Migration execution can come from:

• application startup.
• CI/CD pipeline.
• AKS Job.
• Azure DevOps pipeline.
• GitHub Actions runner with private network access.
• DBA-controlled process.
• manual break-glass path.

Production-safe migration needs:

• controlled single execution.
• private network access.
• migration credential.
• lock timeout.
• statement timeout.
• generated SQL review.
• preflight checks.
• impact estimate.
• rollback/roll-forward strategy.
• monitoring during execution.
• post-migration validation.

Azure-specific checks

• Can the runner resolve private endpoint DNS?
• Does the runner have Key Vault access?
• Is migration identity separate from runtime identity?
• Are firewall rules temporary and audited?
• Are migration logs retained?
• Is the pipeline blocked during maintenance/failover windows?

20. Common Azure PostgreSQL failure modes

21. Production incident triage flow

Use a structured flow:

1. Identify impacted APIs/customers
2. Check recent deployments and migrations
3. Check Java metrics: latency, errors, pool wait
4. Check Azure PostgreSQL metrics: CPU, memory, IO, connections, storage
5. Check PostgreSQL internals: activity, locks, top SQL, wait events
6. Check Azure service events: failover, maintenance, restart
7. Check network/DNS if connection failures dominate
8. Check Key Vault/auth if login failures dominate
9. Choose minimal-blast-radius mitigation
10. Document timeline and follow-up actions

Mitigation examples:

• disable expensive reporting endpoint.
• reduce batch job concurrency.
• pause CDC-consuming backfill only if safe.
• kill clearly harmful query.
• roll back application release.
• apply emergency index only with locking strategy.
• route stale-tolerant reads to replica.
• scale compute only if resource bottleneck is proven.
• restore/PITR only when corruption/recovery plan demands it.

22. Architecture review checklist for PostgreSQL on Azure

Engine and topology

• Flexible Server vs VM vs AKS vs external is explicit.
• PostgreSQL version is documented.
• HA setting is documented.
• read replica usage is documented.
• extension support is verified.

Networking

• private connectivity strategy is defined.
• firewall rules are narrow.
• private DNS resolution is tested from runtime.
• migration runner network path is defined.
• hybrid/VPN/ExpressRoute assumptions are documented.

Application integration

• Hikari pool size accounts for pod count.
• timeouts are bounded.
• retry policy separates safe vs unsafe operations.
• idempotency key exists for command APIs.
• read replica usage respects consistency requirements.

Security and identity

• Key Vault/identity pattern is documented.
• secret rotation is tested.
• runtime and migration identities are separate.
• read-only/support accounts are separate.
• audit access is available.

Operations

• backup retention and PITR are verified.
• restore drill has been performed.
• Azure Monitor dashboard exists.
• Query Store or equivalent query telemetry is enabled if approved.
• slow query logs are available.
• alerts link to runbooks.
• maintenance window is known.

CDC/event integration

• logical replication settings are verified.
• replication slots are monitored.
• schema migration and consumer compatibility process exists.
• duplicate/replay handling is tested.
• reconciliation exists for downstream event consumers.

23. Senior engineer heuristics

Use these heuristics when reviewing Azure PostgreSQL designs:

Private endpoint without correct DNS is not working private connectivity.

Flexible Server manages infrastructure; it does not manage your transaction boundary.

Read replica means stale unless the lag contract says otherwise.

Key Vault secret rotation must be tested with real connection pools.

Server parameters are production code changes.

Backup is a feature. Restore is an operational capability.

Azure Monitor tells you resource symptoms; PostgreSQL views tell you database causes.

CDC slots are operational liabilities when consumers stop.

24. Internal verification checklist

For CSG/team context, verify:

• actual Azure PostgreSQL engine and version.
• Flexible Server vs VM vs AKS vs customer-managed deployment.
• HA configuration.
• read replica configuration.
• server parameter ownership.
• extension allowlist.
• private endpoint/VNet/firewall/DNS design.
• Key Vault and identity integration.
• secret rotation runbook.
• connection pool sizing standard.
• backup retention and PITR.
• restore drill history.
• Azure Monitor/Query Store availability.
• slow query log access.
• maintenance window and upgrade process.
• CDC/Debezium/logical replication setup.
• migration execution model.
• incident escalation path to DBA/SRE/platform.

25. What to remember

Azure PostgreSQL is still PostgreSQL from the perspective of correctness and query behavior.

The cloud platform changes how you operate it, connect to it, monitor it, patch it, back it up, and secure it.

It does not remove the need to reason about:

• data ownership.
• schema evolution.
• locking.
• isolation.
• indexing.
• query planning.
• migration safety.
• event consistency.
• privacy.
• observability.
• incident response.

The senior backend engineer's job is to understand both sides:

PostgreSQL engine behavior
  + Azure managed service behavior
  + Java/JAX-RS runtime behavior
  + MyBatis/JDBC integration behavior
  + production failure behavior

That combined model is what prevents cloud database configuration from becoming a hidden production risk.