A new RDS Postgres comes up with the default parameter group. max_connections is 100, shared_buffers is set as a function of instance class but suboptimal for the workload, work_mem is the upstream Postgres default of 4MB, and the log retention is configured to drop anything more than a day old. The application owner asks why their dashboard queries spike, and the answer is “the database is running on factory settings.” The default parameter group is meant to be safe, not fast. Tuning it is one of the highest-leverage operations on an RDS database, and the only mechanism that catches teams out is the static-versus-dynamic restart distinction.

What’s happening

Parameter groups are AWS’s way of exposing engine configuration. The defaults are conservative — they assume the smallest reasonable instance, generic workload, and an operator who has not thought about tuning. Most production workloads need a custom parameter group from day one.

Two categories of parameters matter operationally:

Dynamic parameters. Take effect without a restart. Applied as soon as the parameter group is modified, on the next connection or query. Examples on Postgres: work_mem, log_min_duration_statement, statement_timeout. The change is reflected within seconds.

Static parameters. Require a database restart to take effect. Examples on Postgres: max_connections, shared_buffers, wal_level. Changes are saved, the parameter group shows pending-reboot, and nothing changes until the instance is rebooted. A team that changes max_connections from 300 to 600 and waits for the change to apply will wait forever — they have to reboot.

The trap: a parameter group can have both pending-reboot static changes and applied dynamic changes at once. The team applies a dynamic change, sees it take effect, assumes the parameter group is fully active, and is surprised when the static change still has not applied. The pg_settings view (or the RDS console’s parameter status column) is the source of truth.

For Postgres on RDS, the parameters that matter most for a typical OLTP SaaS workload:

max_connections — the absolute ceiling on concurrent backends. Static. Set high enough for the application’s pool size at peak, plus headroom for ad-hoc sessions and replication slots.
shared_buffers — Postgres’s page cache. Default on RDS is {DBInstanceClassMemory/32768} pages (8KB each), roughly 25% of instance memory. Static. The right setting is 25–40% of instance memory for OLTP, lower for analytical workloads that rely on the OS page cache.
work_mem — the per-operation sort/hash memory. Default is 4MB upstream, RDS picks a small value. Dynamic. Too low and queries spill to disk; too high and a single complex query can use work_mem * hash_join_count * concurrent_backends of memory. Set per-session for known-heavy queries.
maintenance_work_mem — memory for VACUUM, CREATE INDEX, etc. Dynamic. Default is too low for any production-size database. Set to 256MB–2GB.
effective_cache_size — Postgres’s hint about total cache available (Postgres + OS). Dynamic. Set to ~75% of instance memory.
random_page_cost — cost-model parameter, defaults assume spinning disks. Dynamic. Set to 1.1 on gp3/io1 EBS or Aurora (SSD-class IO).
log_min_duration_statement — log queries slower than N ms. Dynamic. Set to a value that surfaces problematic queries without flooding logs (typically 1000ms for OLTP, 5000ms for analytical).
log_statement — log DDL, mod, all, or none. Dynamic. Set to ddl for production (every CREATE/ALTER/DROP gets logged); set to mod if compliance requires.
statement_timeout — kill queries running longer than N ms. Dynamic. Set per-role, not globally; the analytical role gets 30 minutes, the application role gets 30 seconds.
idle_in_transaction_session_timeout — kill sessions idle inside a transaction for N ms. Dynamic. Set to 60 seconds — long enough for human DBAs in psql, short enough to prevent leaked transactions from holding locks.

For MySQL on RDS, the analogous parameters:

max_connections — same role. Static-equivalent on some versions.
innodb_buffer_pool_size — MySQL’s page cache. The most important MySQL tuning parameter. Dynamic in MySQL 5.7+, static in older versions. Set to 50–75% of instance memory for OLTP.
innodb_log_file_size — redo log file size. Static. Default is too small for write-heavy workloads.
tmp_table_size / max_heap_table_size — temp table thresholds. Dynamic. Default is too low for queries with intermediate result sets.
slow_query_log / long_query_time — slow query logging. Dynamic.
innodb_flush_log_at_trx_commit — durability vs throughput. Stays at 1 for production; do not set to 2 to gain speed unless you understand the durability trade-off.

Aurora-specific notes: most parameters are identical to upstream, but a subset are managed by Aurora and not user-tunable (e.g. synchronous_commit is managed; you cannot turn it off). The console shows a “modifiable” column — if it says no, it is locked.

How to verify

Check the current parameter group:

aws rds describe-db-instances \
  --db-instance-identifier prod-db-1 \
  --query 'DBInstances[].[DBInstanceIdentifier,DBParameterGroups]' \
  --output json

If DBParameterGroupName says default.postgres15 or similar, the database is on the AWS default. You cannot modify the default group — create a custom one, attach it, reboot, and only then tune it.

Dump the parameters that differ from defaults:

aws rds describe-db-parameters \
  --db-parameter-group-name prod-db-1-pg \
  --source user \
  --query 'Parameters[].[ParameterName,ParameterValue,ApplyType,IsModifiable]' \
  --output table

ApplyType is static or dynamic. The static ones tell you what needs a restart.

Inside Postgres, the runtime view of the same:

SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE source = 'configuration file'
ORDER BY name;

pending_restart = true is the smoking gun for “we changed this but did not reboot.”

For a current snapshot of memory pressure, check what work_mem is doing:

-- Top 20 queries by total time
SELECT query, calls, mean_exec_time, total_exec_time, temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

temp_blks_written > 0 means the query spilled to disk — work_mem was too low for that query.

The fix

A concrete diff for a 4-core 32GB Postgres on RDS, OLTP SaaS workload, modest analytical load. Engine: Postgres 15. Instance: db.r6g.xlarge.

Create the parameter group:

aws rds create-db-parameter-group \
  --db-parameter-group-name prod-db-1-pg \
  --db-parameter-group-family postgres15 \
  --description "Production Postgres 15 - 4c/32G OLTP"

Apply the tuning:

aws rds modify-db-parameter-group \
  --db-parameter-group-name prod-db-1-pg \
  --parameters \
    "ParameterName=max_connections,ParameterValue=400,ApplyMethod=pending-reboot" \
    "ParameterName=shared_buffers,ParameterValue={DBInstanceClassMemory*25/100/8192},ApplyMethod=pending-reboot" \
    "ParameterName=work_mem,ParameterValue=32768,ApplyMethod=immediate" \
    "ParameterName=maintenance_work_mem,ParameterValue=1048576,ApplyMethod=immediate" \
    "ParameterName=effective_cache_size,ParameterValue={DBInstanceClassMemory*75/100/8192},ApplyMethod=immediate" \
    "ParameterName=random_page_cost,ParameterValue=1.1,ApplyMethod=immediate" \
    "ParameterName=log_min_duration_statement,ParameterValue=1000,ApplyMethod=immediate" \
    "ParameterName=log_statement,ParameterValue=ddl,ApplyMethod=immediate" \
    "ParameterName=log_lock_waits,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=log_temp_files,ParameterValue=0,ApplyMethod=immediate" \
    "ParameterName=idle_in_transaction_session_timeout,ParameterValue=60000,ApplyMethod=immediate" \
    "ParameterName=track_io_timing,ParameterValue=1,ApplyMethod=immediate"

Attach to the instance:

aws rds modify-db-instance \
  --db-instance-identifier prod-db-1 \
  --db-parameter-group-name prod-db-1-pg \
  --apply-immediately

Reboot to apply the static parameters:

aws rds reboot-db-instance --db-instance-identifier prod-db-1

Verify in Postgres:

SHOW max_connections;       -- 400
SHOW shared_buffers;        -- 8GB (32G * 25%)
SHOW work_mem;              -- 32MB
SHOW effective_cache_size;  -- 24GB
SELECT name, pending_restart FROM pg_settings WHERE pending_restart;
-- should be empty

The reasoning behind each value:

max_connections=400 — at 32GB instance, 400 connections is the upper bound before Postgres process memory becomes meaningful (each backend uses ~10MB of base memory plus work_mem). Above 400, switch to RDS Proxy or pgbouncer.
shared_buffers=25% — Postgres on Linux benefits from the OS page cache also caching pages; setting shared_buffers higher than 40% gives Postgres a bigger cache but starves the OS cache, often a net loss.
work_mem=32MB — generous for OLTP. Concurrent queries (400 max) each doing a hash join can theoretically consume 400 * 32MB = 12GB. Set higher per-session for known reporting queries.
maintenance_work_mem=1GB — VACUUM and CREATE INDEX run faster with more memory. Used only when those operations run, not at all otherwise.
effective_cache_size=75% — informs the planner about cache size; does not allocate memory.
random_page_cost=1.1 — tells the planner that random IO is almost as cheap as sequential, true on SSD-class storage.
log_min_duration_statement=1000 — surfaces slow queries without logging every fast one.
log_lock_waits=1 — logs when a query waits on a lock; surfaces contention.
log_temp_files=0 — logs every temp file creation; surfaces work_mem undersizing.
idle_in_transaction_session_timeout=60000 — 60 seconds. Prevents zombie transactions from holding row-locks and bloating tables.
track_io_timing=1 — surfaces IO time in pg_stat_statements; small CPU cost.

For MySQL on a similar instance, the analogous diff:

aws rds modify-db-parameter-group \
  --db-parameter-group-name prod-db-1-pg \
  --parameters \
    "ParameterName=max_connections,ParameterValue=400,ApplyMethod=immediate" \
    "ParameterName=innodb_buffer_pool_size,ParameterValue={DBInstanceClassMemory*65/100},ApplyMethod=immediate" \
    "ParameterName=innodb_log_file_size,ParameterValue=536870912,ApplyMethod=pending-reboot" \
    "ParameterName=innodb_flush_log_at_trx_commit,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=long_query_time,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=tmp_table_size,ParameterValue=67108864,ApplyMethod=immediate" \
    "ParameterName=max_heap_table_size,ParameterValue=67108864,ApplyMethod=immediate"

Three rules we hold across parameter group work:

Never modify the production parameter group during an incident. A change that needs a restart triggers a database reboot, and the restart can take longer than the original problem. Make tuning changes during planned windows; emergency tuning is usually wrong tuning.

Track parameter group changes in version control. The infrastructure-as-code definition of the parameter group should be the source of truth. The console changes drift unless someone owns them.

Tune by measurement, not by template. A 4c/32G OLTP workload is a reasonable starting point. The actual workload may want different values — measure pg_stat_statements, check temp files, watch buffer cache hit ratio, adjust. The diff above is a starting line, not a finish line.

Tuning is iterative. The first tuning pass captures 80% of the available win. The next pass, three months later, adjusts based on real production patterns. The third pass, six months later, is mostly fine-tuning. After that, the database stays tuned until the workload or the engine version changes.

Stack Harbor designs and operates production RDS / Aurora topologies as part of Managed AWS and the ongoing Managed Cloud Ops layer. If your database posture is “Multi-AZ checkbox + hope,” start with an infrastructure consult.