How to Run High-Scale Postgres on AWS Without Breaking the Bank

PostgreSQL is the default database choice for most AWS workloads — and for good reason. It is battle-tested, feature-rich, and supported by every major managed service. But “managed” does not mean “cheap by default.” Teams that deploy RDS PostgreSQL on autopilot routinely pay 2–3× what they need to, while teams that over-engineer toward Aurora Serverless sometimes pay more than a simple EC2 deployment would cost.

This guide breaks down the real TCO numbers across RDS, Aurora, and self-managed Postgres, then covers the operational levers — connection pooling, indexing, partitioning — that have the highest dollar impact per hour of engineering effort.

The Deployment Decision: RDS vs Aurora vs Self-Managed on EC2

Before optimizing a Postgres deployment, you need to know whether you are on the right platform. Each option has a different cost floor, operational burden, and scaling ceiling.

RDS PostgreSQL

RDS PostgreSQL is the right default for most workloads under 1 TB and under 10,000 connections/day. It handles backups, patching, Multi-AZ failover, and parameter management. You pay for instance compute, storage, I/O, and data transfer.

Storage: $0.115/GB/month for gp3 (general purpose). A 500 GB database costs $57.50/month just in storage. gp3 decouples IOPS and throughput from storage size: you get 3,000 IOPS and 125 MB/s baseline free, and can provision additional IOPS at $0.02/IOPS/month.

I/O: With RDS using gp3, I/O is bundled into IOPS — you pay for provisioned IOPS above the 3,000 baseline. With older gp2 volumes, I/O costs were baked into storage pricing. The I/O model matters when comparing to Aurora.

Compute: A db.r7g.large (2 vCPU, 16 GB RAM) runs $0.24/hr on-demand = ~$175/month. A 1-year reserved instance brings that to ~$108/month. Multi-AZ doubles compute cost but provides synchronous standby replication with automatic failover.

Total RDS estimate (500 GB, db.r7g.large, Multi-AZ, 1-year reserved):

• Compute: $216/month (Multi-AZ reserved)
• Storage: $115/month
• Automated backups (7 days retention): ~$10/month
• Total: ~$341/month

Aurora PostgreSQL

Aurora uses a distributed storage layer that separates compute from storage. All data is stored as a shared cluster volume across 3 AZs by default. Replicas do not replicate storage — they read from the same cluster volume.

Storage: $0.10/GB/month — slightly cheaper than RDS gp3 at $0.115. But Aurora charges for I/O on the standard tier: $0.20 per million read I/O requests, $0.20 per million write I/O requests. For an I/O-heavy workload processing 500 million I/O operations/month, that is $100/month in I/O charges alone that RDS gp3 with provisioned IOPS would not have.

Aurora Optimized I/O: At 25% higher storage cost ($0.125/GB/month), Optimized I/O eliminates per-I/O charges. The breakeven is when I/O charges exceed 25% of storage cost. For a 500 GB database with >500 million I/O operations/month, Optimized I/O saves money.

Aurora Serverless v2: Charges in ACUs (Aurora Capacity Units). One ACU = ~2 GB RAM. Minimum 0.5 ACU ($0.06/hr), scaling to 128 ACU. For intermittent workloads, Serverless v2 eliminates idle compute cost — a database idle for 16 hours/day costs $0.06 × 16 = $0.96/day vs $0.24/hr × 16h = $3.84/day for a running db.r7g.large. That is a 75% reduction for idle-heavy workloads.

Aurora instance: A db.r7g.large Aurora writer costs $0.26/hr on-demand, slightly more than RDS. Aurora readers add $0.26/hr each but share storage at no additional cost.

Total Aurora estimate (500 GB, db.r7g.large writer + 1 reader, moderate I/O, 1-year reserved):

• Writer: ~$112/month reserved
• Reader: ~$112/month reserved
• Storage: $50/month
• I/O (200M requests): $40/month
• Total: ~$314/month

The numbers are competitive at this scale. Aurora pulls ahead when you add more read replicas, when Serverless v2 matches your scaling profile, or when Global Database eliminates the need for a custom cross-region replication setup.

Self-Managed Postgres on EC2

Self-managed Postgres on EC2 is the lowest-cost option at scale — but cost is not free of complexity. You own everything: backups, failover, upgrades, monitoring, and parameter tuning.

When EC2 wins: Above 10 TB of data, above db.r7g.8xlarge equivalent workloads, or when you need extensions that RDS does not support (e.g., TimescaleDB, pg_partman fully managed, Citus for horizontal scaling).

True TCO of self-managed Postgres on EC2:

• r7g.2xlarge (8 vCPU, 64 GB): $0.504/hr on-demand, ~$184/month reserved
• 2 TB gp3 EBS: $160/month
• EBS snapshots for backup (30% data per day): ~$15/month
• Engineering time for ops: even at 2 hours/month × $150/hr = $300/month

At small scale, the engineering time makes EC2 uncompetitive. At large scale (10+ TB, dedicated DBA), EC2 becomes the cheapest option per GB.

Read Replicas: ROI Formula

Read replicas are frequently added reactively (“the primary is slow, let’s add a replica”) without verifying the economics.

When a Replica Is Cheaper Than Vertical Scaling

For a db.r7g.large primary under read pressure, the next instance size is db.r7g.xlarge at ~$350/month reserved (Multi-AZ). A read replica on db.r7g.large runs ~$108/month reserved (single-AZ, no replication charge for RDS).

ROI condition: If adding a replica costs less than upgrading the primary, and read traffic is separable, the replica wins.

Replica cost = $108/month
Vertical upgrade delta = $350 - $216 = $134/month

Replica wins if it offloads enough read traffic to prevent the upgrade.

The formula: add a replica if (read query volume / total query volume) > 0.4 and you can route reads to the replica. The 40% threshold accounts for replication lag risk — if replicas serve fewer than 40% of queries, vertical scaling usually provides better ROI with simpler architecture.

Cross-Region Read Replicas

Cross-region replicas incur data transfer costs that are easy to miss. RDS charges $0.02–$0.09/GB for replication data transfer depending on regions. A database with 10 GB/day of write activity replicating to a second region costs:

• Data transfer: 10 GB/day × $0.02/GB × 30 days = $6/month (us-east-1 to us-west-2)
• Replica instance: ~$108/month

The data transfer cost is manageable for disaster recovery. For read serving, evaluate whether CloudFront or application-layer caching provides lower cost than cross-region read traffic.

Replica Lag Management

Replica lag causes stale reads. A replica lagging 30 seconds behind primary during a write burst means sessions reading from the replica see 30-second-old data. This is acceptable for analytics but catastrophic for financial balances or inventory counts.

Monitor lag with:

-- On replica
SELECT
  now() - pg_last_xact_replay_timestamp() AS replication_lag,
  pg_is_in_recovery() AS is_replica;

If lag consistently exceeds your application’s staleness tolerance (typically < 1 second for OLTP), route those queries back to primary or increase the replica’s instance size to reduce processing latency.

PgBouncer vs RDS Proxy: The Real Cost Comparison

PostgreSQL connections are expensive. Each connection spawns a backend process consuming 5–10 MB of RAM. An application with 200 microservice instances × 5 connection pool size = 1,000 Postgres connections. On a db.r7g.large (16 GB RAM), 1,000 connections consume 5–10 GB of RAM before executing a single query.

RDS Proxy Pricing

RDS Proxy costs $0.015 per vCPU of the underlying database instance per hour, plus connection charges. For a db.r7g.2xlarge (8 vCPU):

$0.015 × 8 vCPU × 730 hours/month = $87.60/month

Plus, the connection overhead tracking adds latency — RDS Proxy adds ~1–2ms per query vs direct connection.

PgBouncer on ECS Fargate

PgBouncer is open source and runs on a tiny container. A task with 0.25 vCPU and 512 MB handles thousands of application connections pooled into tens of database connections.

ECS Fargate: 0.25 vCPU × $0.04048/vCPU-hr × 730 hrs = ~$7.40/month
             0.5 GB × $0.004445/GB-hr × 730 hrs = ~$1.60/month
Total: ~$9/month vs $87.60/month for RDS Proxy

Annual savings: ~$945 on a single db.r7g.2xlarge instance.

PgBouncer Configuration

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=mydb.cluster-xyz.us-east-1.rds.amazonaws.com port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 5432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Transaction pooling: reuses connections between transactions
# Do NOT use session pooling — it negates the pooling benefit
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 3

# Keep idle server connections alive
server_idle_timeout = 600
client_idle_timeout = 0

# Logging (keep minimal in production for performance)
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

# Stats for monitoring
stats_period = 60

ECS Task Definition for PgBouncer

{
  "family": "pgbouncer",
  "networkMode": "awsvpc",
  "requiresCompatibilities": ["FARGATE"],
  "cpu": "256",
  "memory": "512",
  "executionRoleArn": "arn:aws:iam::ACCOUNT:role/ecsTaskExecutionRole",
  "taskRoleArn": "arn:aws:iam::ACCOUNT:role/pgbouncerTaskRole",
  "containerDefinitions": [
    {
      "name": "pgbouncer",
      "image": "bitnami/pgbouncer:1.22.0",
      "portMappings": [
        {
          "containerPort": 5432,
          "protocol": "tcp"
        }
      ],
      "environment": [
        {
          "name": "POSTGRESQL_HOST",
          "value": "mydb.cluster-xyz.us-east-1.rds.amazonaws.com"
        },
        {
          "name": "POSTGRESQL_PORT",
          "value": "5432"
        },
        {
          "name": "PGBOUNCER_POOL_MODE",
          "value": "transaction"
        },
        {
          "name": "PGBOUNCER_MAX_CLIENT_CONN",
          "value": "1000"
        },
        {
          "name": "PGBOUNCER_DEFAULT_POOL_SIZE",
          "value": "25"
        }
      ],
      "secrets": [
        {
          "name": "POSTGRESQL_PASSWORD",
          "valueFrom": "arn:aws:secretsmanager:us-east-1:ACCOUNT:secret:prod/postgres-password"
        }
      ],
      "logConfiguration": {
        "logDriver": "awslogs",
        "options": {
          "awslogs-group": "/ecs/pgbouncer",
          "awslogs-region": "us-east-1",
          "awslogs-stream-prefix": "pgbouncer"
        }
      },
      "healthCheck": {
        "command": [
          "CMD-SHELL",
          "pg_isready -h localhost -p 5432 || exit 1"
        ],
        "interval": 30,
        "timeout": 5,
        "retries": 3
      }
    }
  ]
}

Important caveat: PgBouncer in transaction mode does not support SET commands, advisory locks, or LISTEN/NOTIFY that span transactions. If your application relies on these, use session mode or RDS Proxy.

Indexing for Cost Efficiency

Indexes are a double-edged cost factor. Too few indexes means slow queries that consume more compute time. Too many indexes means every INSERT/UPDATE/DELETE pays an index maintenance overhead.

Covering Indexes with INCLUDE

A covering index allows PostgreSQL to satisfy a query entirely from the index without fetching the heap (the table data pages). This eliminates heap I/O, which is significant for large tables on EBS where random I/O is expensive.

-- Without covering index: index scan + heap fetch for each row
CREATE INDEX idx_orders_status ON orders (status);

-- Query: SELECT order_id, created_at FROM orders WHERE status = 'pending'
-- Problem: index has status, but order_id and created_at require heap fetch

-- With covering index: index-only scan, no heap I/O
CREATE INDEX idx_orders_status_covering ON orders (status)
  INCLUDE (order_id, created_at);

-- Verify index-only scan is used:
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at FROM orders WHERE status = 'pending';
-- Look for "Index Only Scan" and "Heap Fetches: 0"

For frequently-run queries on large tables, covering indexes can reduce query time by 50–80% and lower your effective IOPS consumption.

Partial Indexes for Filtered Queries

A partial index only indexes rows satisfying a WHERE clause. This dramatically reduces index size when a small fraction of rows is frequently queried.

-- Full index: indexes all 50 million orders
CREATE INDEX idx_orders_status_full ON orders (status);
-- Size: ~2 GB

-- Partial index: indexes only 100k active/pending orders (0.2% of rows)
CREATE INDEX idx_orders_status_active ON orders (status, created_at)
WHERE status IN ('active', 'pending');
-- Size: ~4 MB (500× smaller)

-- Only useful when query includes the WHERE condition
SELECT * FROM orders WHERE status = 'active' AND created_at > NOW() - INTERVAL '7 days';

The storage saving translates directly to lower EBS usage and faster index maintenance on writes.

BRIN Indexes for Append-Only Time-Series

BRIN (Block Range INdex) is designed for columns where physical storage order correlates with column values — the classic case being a created_at or event timestamp column in an append-only table.

-- B-tree index on 100GB timestamp column: ~3-5 GB
CREATE INDEX idx_events_created_btree ON events (created_at);

-- BRIN index: ~128 KB (24,000× smaller)
CREATE INDEX idx_events_created_brin ON events USING BRIN (created_at);

-- Tune pages_per_range: smaller = more precise but larger index
-- Default 128 pages per range; reduce for faster range scans
CREATE INDEX idx_events_created_brin ON events USING BRIN (created_at)
  WITH (pages_per_range = 32);

BRIN is not suitable for randomly-distributed data or columns that are frequently updated. For time-series event logs, metrics tables, and audit trails, BRIN can save gigabytes in index storage.

Monitoring Index Bloat

Indexes accumulate dead tuples from updates and deletes. Bloated indexes consume extra storage and slow scans.

-- Monitor index bloat (requires pgstattuple extension or estimation)
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Unused indexes (prime candidates for removal)
  AND pg_relation_size(indexrelid) > 10 * 1024 * 1024  -- Larger than 10 MB
ORDER BY pg_relation_size(indexrelid) DESC;

-- Dead tuple and bloat monitoring
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC;

An index with idx_scan = 0 over a 7-day window is likely unused. Dropping unused indexes saves storage and reduces write overhead:

-- Before dropping, verify with longer observation period
-- Then drop during low-traffic window
DROP INDEX CONCURRENTLY idx_orders_old_unused_index;

Use CONCURRENTLY to avoid locking the table during the drop.

Table Partitioning for Time-Series Data

Declarative range partitioning allows PostgreSQL to skip entire partitions for time-bounded queries — a technique called partition pruning. For tables growing by 50–100 GB/month, partitioning makes old data detachment and archival seamless.

Setting Up Range Partitioning

-- Create partitioned table (no data stored here directly)
CREATE TABLE events (
  event_id    BIGSERIAL,
  event_type  TEXT NOT NULL,
  payload     JSONB,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Default partition catches out-of-range inserts (prevents errors, enables auditing)
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Create indexes on each partition (or use global indexes in PG 17+)
CREATE INDEX ON events_2026_01 (created_at, event_type);
CREATE INDEX ON events_2026_02 (created_at, event_type);
CREATE INDEX ON events_2026_03 (created_at, event_type);

Partition Pruning Verification

PostgreSQL prunes partitions at planning time when the WHERE clause includes the partition key:

-- Partition pruning: only scans events_2026_03
EXPLAIN SELECT * FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

-- Look for: "Partitions: events_2026_03"
-- NOT: "Partitions: events_2026_01, events_2026_02, events_2026_03"

-- Enable constraint exclusion for older syntax (should be on by default)
SET enable_partition_pruning = on;

Automating Old Partition Detachment

Detaching old partitions (and optionally archiving to S3 via aws_s3 extension on RDS) keeps the primary table lean:

-- Detach partition (keeps data, removes from partitioned table)
ALTER TABLE events DETACH PARTITION events_2024_01;

-- After detaching, table still exists independently
-- Archive to S3 using pg_dump or aws_s3.export_table_to_s3()
-- Then drop when confirmed archived
DROP TABLE events_2024_01;

-- Automate with a stored procedure (call from pg_cron or Lambda)
CREATE OR REPLACE PROCEDURE detach_old_partitions(retention_months INT DEFAULT 6)
LANGUAGE plpgsql AS $$
DECLARE
  partition_name TEXT;
  cutoff_date DATE := DATE_TRUNC('month', NOW()) - (retention_months || ' months')::INTERVAL;
BEGIN
  FOR partition_name IN
    SELECT inhrelid::regclass::text
    FROM pg_inherits
    JOIN pg_class parent ON inhparent = parent.oid
    JOIN pg_class child ON inhrelid = child.oid
    WHERE parent.relname = 'events'
      AND child.relname < 'events_' || TO_CHAR(cutoff_date, 'YYYY_MM')
  LOOP
    EXECUTE FORMAT('ALTER TABLE events DETACH PARTITION %I', partition_name);
    RAISE NOTICE 'Detached partition: %', partition_name;
  END LOOP;
END;
$$;

Backup and PITR Cost Reduction

RDS automated backups are stored in S3 at $0.095/GB/month. The free storage tier equals your allocated database storage — beyond that, every gigabyte costs.

Default retention is 7 days. Some teams set it to 35 days without thinking about cost. At 500 GB of database with a 2× daily change rate:

Daily backup size: ~50 GB (incremental, not full)
35-day retention: ~1,750 GB of backup storage above free tier
Cost: 1,750 × $0.095 = $166.25/month in backup storage alone

7-day retention: ~350 GB above free tier
Cost: 350 × $0.095 = $33.25/month

Annual savings from 35→7 days: ~$1,596

Only keep 35-day retention if your SLA actually requires PITR to any point in the last 35 days. Most teams need 7 days and a weekly snapshot exported to S3 Glacier for long-term retention:

# Export RDS snapshot to S3 (pay for S3 storage, not RDS backup storage)
aws rds start-export-task \
  --export-task-identifier weekly-export-$(date +%Y%m%d) \
  --source-arn arn:aws:rds:us-east-1:ACCOUNT:snapshot:weekly-snapshot \
  --s3-bucket-name my-db-archive \
  --s3-prefix postgres-exports/ \
  --iam-role-arn arn:aws:iam::ACCOUNT:role/RDSExportRole \
  --kms-key-id alias/aws/s3

S3 Glacier Instant Retrieval costs $0.004/GB/month vs $0.095/GB/month for RDS backup storage — a 95% reduction for long-term archival.

Terraform for RDS with Custom Parameter Group

A production RDS deployment requires a custom parameter group to tune memory and connection settings. The defaults are conservative and rarely optimal.

# rds_postgres.tf

resource "aws_db_parameter_group" "postgres16_production" {
  family = "postgres16"
  name   = "postgres16-production"

  # Connections: default max_connections is LEAST(DBInstanceClassMemory/9531392, 5000)
  # For db.r7g.large (16 GB): ~1700 connections. Reduce if using PgBouncer.
  parameter {
    name  = "max_connections"
    value = "200"  # PgBouncer handles connection multiplexing
  }

  # Shared buffers: PostgreSQL recommends 25% of RAM
  # RDS sets this dynamically but you can override (in 8 KB units)
  # db.r7g.large = 16 GB → 4 GB = 524288 pages × 8 KB
  parameter {
    name         = "shared_buffers"
    value        = "524288"
    apply_method = "pending-reboot"
  }

  # Effective cache size: hint to planner, typically 75% of RAM
  # Tells planner how much OS + PG cache is available for index scans
  parameter {
    name  = "effective_cache_size"
    value = "1572864"  # 12 GB in 8 KB pages
  }

  # WAL compression reduces replication data transfer
  parameter {
    name         = "wal_compression"
    value        = "on"
    apply_method = "pending-reboot"
  }

  # Autovacuum tuning: more aggressive on high-churn tables
  parameter {
    name  = "autovacuum_max_workers"
    value = "5"
  }

  parameter {
    name  = "autovacuum_vacuum_cost_delay"
    value = "2"  # ms, default 20ms
  }

  parameter {
    name  = "autovacuum_vacuum_cost_limit"
    value = "400"  # default 200
  }

  # Log slow queries for analysis (>1 second)
  parameter {
    name  = "log_min_duration_statement"
    value = "1000"  # milliseconds
  }

  # Enable query ID tracking for pg_stat_statements
  parameter {
    name         = "compute_query_id"
    value        = "on"
    apply_method = "pending-reboot"
  }
}

resource "aws_db_instance" "postgres_primary" {
  identifier        = "myapp-postgres-primary"
  engine            = "postgres"
  engine_version    = "16.6"
  instance_class    = "db.r7g.large"
  allocated_storage = 500
  storage_type      = "gp3"
  storage_encrypted = true
  kms_key_id        = aws_kms_key.rds.arn

  db_name  = "myapp"
  username = "myapp_admin"
  password = random_password.db_password.result

  parameter_group_name = aws_db_parameter_group.postgres16_production.name

  # Multi-AZ for production failover
  multi_az = true

  # Backup retention (7 days is usually sufficient with manual weekly snapshots)
  backup_retention_period = 7
  backup_window           = "03:00-04:00"
  maintenance_window      = "sun:04:00-sun:05:00"

  # Performance Insights for query analysis (free for 7 days retention)
  performance_insights_enabled          = true
  performance_insights_retention_period = 7

  # Enhanced monitoring (1-second granularity, small cost ~$0.01/hr)
  monitoring_interval = 60
  monitoring_role_arn = aws_iam_role.rds_monitoring.arn

  vpc_security_group_ids = [aws_security_group.rds.id]
  db_subnet_group_name   = aws_db_subnet_group.private.name

  deletion_protection = true
  skip_final_snapshot = false
  final_snapshot_identifier = "myapp-postgres-final"

  tags = {
    Environment = "production"
    Team        = "platform"
  }
}

Edge Cases That Blow Your Budget

Connection Storms and OOM Restarts

A connection storm occurs when your application restarts after a deployment and every instance simultaneously opens its full connection pool. For 50 app instances × 20 connections = 1,000 connections opening in under 10 seconds.

PostgreSQL allocates shared memory segments for each connection during establishment. On a db.r7g.large with max_connections = 500, 1,000 connection attempts cause the backend process forking to exhaust available memory, triggering the Linux OOM killer — which kills Postgres, causing an unplanned instance restart.

Prevention:

1. Use PgBouncer as described above, limiting actual Postgres connections to 25–50.
2. Add connection retry with exponential backoff in your application:

# Python psycopg2 with retry
import psycopg2
import time
import random

def get_connection(max_retries=5, base_delay=0.5):
    for attempt in range(max_retries):
        try:
            return psycopg2.connect(dsn="postgresql://pgbouncer:5432/myapp")
        except psycopg2.OperationalError as e:
            if attempt == max_retries - 1:
                raise
            delay = base_delay * (2 ** attempt) + random.uniform(0, 0.1)
            time.sleep(delay)

3. Set max_connections conservatively and let PgBouncer queue excess connections rather than having Postgres reject or OOM on them.

Autovacuum Blocking Under High-Update Workloads

When a table receives heavy UPDATE traffic (e.g., a last_seen_at column updated on every user request), autovacuum runs more frequently. But autovacuum and user transactions can conflict in two ways:

Scenario 1: A long-running transaction holds a lock that prevents autovacuum from advancing. Autovacuum waits, dead tuples accumulate, table bloat grows.

-- Find long-running transactions blocking vacuum
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state = 'active';

-- Check if any lock is blocking autovacuum
SELECT
  blocked_locks.pid AS blocked_pid,
  blocking_locks.pid AS blocking_pid,
  blocked_activity.query AS blocked_query,
  blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Scenario 2: The table is updated so frequently that autovacuum cannot keep up with the dead tuple generation rate. Per-table autovacuum settings help:

-- For a high-churn table: vacuum more aggressively
ALTER TABLE user_sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- Trigger at 1% dead tuples (default 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- Analyze at 0.5% changes
  autovacuum_vacuum_cost_delay = 0           -- No delay for this table
);

Table Bloat from UPDATE-Heavy Workloads

PostgreSQL’s MVCC model means UPDATE creates a new row version rather than updating in-place. The old version becomes a dead tuple. On a table with 10 million rows where every row is updated daily, the live data is 10M rows but actual storage may contain 20M+ row versions until vacuum reclaims space.

Detect and address table bloat:

-- Estimate table bloat (simplified version without pgstattuple extension)
SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
  pg_size_pretty(pg_relation_size(oid)) AS table_size,
  pg_size_pretty(pg_total_relation_size(oid) - pg_relation_size(oid)) AS index_size,
  n_dead_tup,
  n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_class
JOIN pg_stat_user_tables ON relid = oid
WHERE relkind = 'r'
  AND n_dead_tup > 50000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- For tables with > 20% bloat ratio, run VACUUM FULL during maintenance
-- WARNING: VACUUM FULL acquires exclusive lock. Use pg_repack for zero-downtime.
-- pg_repack is available as an RDS extension
CREATE EXTENSION pg_repack;
-- Then run: pg_repack -t user_sessions mydb

For RDS, pg_repack is available as an extension on PostgreSQL 12+. It rewrites the table without holding an exclusive lock, making it safe for production use during low-traffic windows.

Putting It All Together: A Realistic Cost Reduction Roadmap

For a 500 GB production PostgreSQL database on RDS serving a mid-scale SaaS application, here is a realistic optimization path ordered by effort-to-impact ratio:

For most teams, the first four items alone save $200–$250/month with under 8 hours of work. That is $2,400–$3,000/year on a single database.

For architectural guidance on choosing the right managed database service for your workload, see our RDS vs Aurora comparison guide. If your Postgres costs are part of a broader AWS optimization initiative, the AWS cost control architecture playbook covers cross-service patterns that compound these database savings.