Managed Postgres Basics

Managed PostgreSQL (RDS, Cloud SQL, Aurora, Neon, Supabase) shifts patching, backups, and failover mechanics to the provider. You still own schema design, query performance, connection math, extensions allowlist, and access control. Treat managed as less ops toil, not less database engineering.

Recipe

Shared responsibility (typical):
  Provider: hypervisor, disk, automated backups, minor version patches, HA failover mechanism
  You:      queries, indexes, migrations, roles, connection pools, extensions, cost, RPO/RTO validation

-- Your job on day one regardless of provider
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT version();
SHOW max_connections;

When to reach for this:

Greenfield choosing managed vs self-hosted Patroni
Onboarding engineers who assume RDS tunes slow queries automatically
Compliance packet documenting responsibility split

Working Example

RDS PostgreSQL 18.4 provisioning checklist from application team view.

# Terraform mindset (conceptual)
Engine: postgres
EngineVersion: "18.4"
InstanceClass: db.r7g.large
MultiAZ: true
StorageEncrypted: true
BackupRetentionPeriod: 14
DeletionProtection: true

-- After endpoint available: app responsibilities
CREATE ROLE app_api LOGIN PASSWORD 'from-secrets-manager';
GRANT CONNECT ON DATABASE orders TO app_api;
-- indexes, RLS, pg_stat_statements review weekly
 
-- Parameter group (your ticket to DBA/platform):
-- log_min_duration_statement = 500
-- shared_preload_libraries = pg_stat_statements

# Connection via TLS
psql "host=prod.xxxx.us-east-1.rds.amazonaws.com sslmode=verify-full sslrootcert=rds-ca.pem"

What this demonstrates:

Provider creates instance, storage encryption, Multi-AZ substrate
Team creates roles, schema, indexes, and monitors queries
TLS and CA bundle are client-side requirements
Parameter groups are how you tune logging and extensions

Deep Dive

Responsibility Matrix

Area	Managed provider	Your team
OS/kernel patches	Yes	No
Postgres minor upgrades	Scheduled window	Test app compatibility
Major upgrades	Assisted path	Migration testing, extension checks
Base backups	Automated	Restore drills
Failover	Mechanism	App reconnect, pooler config
Slow queries	Not auto-fixed	EXPLAIN, indexes, stats
Extensions	Allowlist	Request + test
Network	VPC placement	Security groups, private link
Cost	Instance hours	Right-sizing, storage growth

What Managed Does Not Fix

N+1 queries from ORM
Missing indexes on 50M row tables
max_connections exhaustion without PgBouncer
Logical replication slot bloat from misconfigured consumers
Data model mistakes

Connection Architecture

App -> PgBouncer (recommended) -> managed endpoint -> primary (+ standby)

Use provider read endpoints for replicas with lag-aware routing.

Gotchas

Assuming Multi-AZ fixes application bugs - failover still loses in-flight transactions without retry. Fix: idempotent writes and pooler reconnect.
Using default parameter group - max_connections, logging, and work_mem often wrong for workload. Fix: custom parameter group per environment.
Public accessibility enabled - internet-exposed Postgres. Fix: private subnet + VPN/bastion only.
Superuser-like master user in app - RDS postgres or admin in DSN. Fix: app-specific roles with grants.
Ignoring storage autoscaling ceiling - disk max still hit. Fix: forecast growth; alert at 80%.
Extension drift staging vs prod - deploy fails on missing vector. Fix: extension manifest in migrations repo.

Alternatives

Alternative	Use When	Don't Use When
Self-hosted Patroni	Need exotic extensions, kernel tuning	Small team without DBA bench
Kubernetes operators (CloudNativePG)	K8s-native, GitOps	Want fully hands-off patching
Serverless (Neon)	Branching dev, variable load	Predictable heavy OLTP cheaper on RDS
Aurora	AWS scale + fast failover	Multi-cloud requirement

FAQs

Is managed cheaper than VMs?

Often yes at small/medium scale when labor included. Large steady workloads may favor reserved instances or self-hosted.

Who patches Postgres minor?

Provider schedules; you choose maintenance window and test extensions.

Can I SSH to instance?

Generally no on RDS/Cloud SQL. Use logs, metrics, and SQL only.

pg_hba control?

Limited - network + security groups + IAM auth replace much of hba on cloud.

Backup enough for DR?

Provider backups help; you still run restore drill and document RPO/RTO.

Read replicas managed?

Yes as button-click; you configure routing and lag SLO.

Compliance BAA/HIPAA?

Sign provider agreement; you still configure encryption, access, audit.

Vendor lock-in?

Logical migration via pg_dump/replication possible but costly; abstract pooler and driver.

Dev/staging parity?

Same major version and extension set; smaller instance class is fine.

When leave managed?

Rare extensions, custom WAL archiving, or cost at very large steady scale.

AWS RDS & Aurora Postgres - AWS specifics
Google Cloud SQL & AlloyDB - GCP specifics
Neon & Supabase - serverless patterns
Cloud Selection ADR - decision matrix
Managed Postgres Best Practices - operational checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Recipe

Shared responsibility (typical):
  Provider: hypervisor, disk, automated backups, minor version patches, HA failover mechanism
  You:      queries, indexes, migrations, roles, connection pools, extensions, cost, RPO/RTO validation

-- Your job on day one regardless of provider
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT version();
SHOW max_connections;

When to reach for this:

Greenfield choosing managed vs self-hosted Patroni
Onboarding engineers who assume RDS tunes slow queries automatically
Compliance packet documenting responsibility split

Working Example

RDS PostgreSQL 18.4 provisioning checklist from application team view.

# Terraform mindset (conceptual)
Engine: postgres
EngineVersion: "18.4"
InstanceClass: db.r7g.large
MultiAZ: true
StorageEncrypted: true
BackupRetentionPeriod: 14
DeletionProtection: true

-- After endpoint available: app responsibilities
CREATE ROLE app_api LOGIN PASSWORD 'from-secrets-manager';
GRANT CONNECT ON DATABASE orders TO app_api;
-- indexes, RLS, pg_stat_statements review weekly
 
-- Parameter group (your ticket to DBA/platform):
-- log_min_duration_statement = 500
-- shared_preload_libraries = pg_stat_statements

# Connection via TLS
psql "host=prod.xxxx.us-east-1.rds.amazonaws.com sslmode=verify-full sslrootcert=rds-ca.pem"

What this demonstrates:

Provider creates instance, storage encryption, Multi-AZ substrate
Team creates roles, schema, indexes, and monitors queries
TLS and CA bundle are client-side requirements
Parameter groups are how you tune logging and extensions

Deep Dive

Responsibility Matrix

Area	Managed provider	Your team
OS/kernel patches	Yes	No
Postgres minor upgrades	Scheduled window	Test app compatibility
Major upgrades	Assisted path	Migration testing, extension checks
Base backups	Automated	Restore drills
Failover	Mechanism	App reconnect, pooler config
Slow queries	Not auto-fixed	EXPLAIN, indexes, stats
Extensions	Allowlist	Request + test
Network	VPC placement	Security groups, private link
Cost	Instance hours	Right-sizing, storage growth

What Managed Does Not Fix

N+1 queries from ORM
Missing indexes on 50M row tables
max_connections exhaustion without PgBouncer
Logical replication slot bloat from misconfigured consumers
Data model mistakes

Connection Architecture

App -> PgBouncer (recommended) -> managed endpoint -> primary (+ standby)

Use provider read endpoints for replicas with lag-aware routing.

Gotchas

Assuming Multi-AZ fixes application bugs - failover still loses in-flight transactions without retry. Fix: idempotent writes and pooler reconnect.
Using default parameter group - max_connections, logging, and work_mem often wrong for workload. Fix: custom parameter group per environment.
Public accessibility enabled - internet-exposed Postgres. Fix: private subnet + VPN/bastion only.
Superuser-like master user in app - RDS postgres or admin in DSN. Fix: app-specific roles with grants.
Ignoring storage autoscaling ceiling - disk max still hit. Fix: forecast growth; alert at 80%.
Extension drift staging vs prod - deploy fails on missing vector. Fix: extension manifest in migrations repo.

Alternatives

Alternative	Use When	Don't Use When
Self-hosted Patroni	Need exotic extensions, kernel tuning	Small team without DBA bench
Kubernetes operators (CloudNativePG)	K8s-native, GitOps	Want fully hands-off patching
Serverless (Neon)	Branching dev, variable load	Predictable heavy OLTP cheaper on RDS
Aurora	AWS scale + fast failover	Multi-cloud requirement

FAQs

Is managed cheaper than VMs?

Often yes at small/medium scale when labor included. Large steady workloads may favor reserved instances or self-hosted.

Who patches Postgres minor?

Provider schedules; you choose maintenance window and test extensions.

Can I SSH to instance?

Generally no on RDS/Cloud SQL. Use logs, metrics, and SQL only.

pg_hba control?

Limited - network + security groups + IAM auth replace much of hba on cloud.

Backup enough for DR?

Provider backups help; you still run restore drill and document RPO/RTO.

Read replicas managed?

Yes as button-click; you configure routing and lag SLO.

Compliance BAA/HIPAA?

Sign provider agreement; you still configure encryption, access, audit.

Vendor lock-in?

Logical migration via pg_dump/replication possible but costly; abstract pooler and driver.

Dev/staging parity?

Same major version and extension set; smaller instance class is fine.

When leave managed?

Rare extensions, custom WAL archiving, or cost at very large steady scale.

AWS RDS & Aurora Postgres - AWS specifics
Google Cloud SQL & AlloyDB - GCP specifics
Neon & Supabase - serverless patterns
Cloud Selection ADR - decision matrix
Managed Postgres Best Practices - operational checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Managed Postgres Basics

Search Documentation

Managed Postgres Basics