Performance optimisation
Diagnose and fix slow databases on RDS and Aurora: read DB load and wait events in CloudWatch Database Insights (the successor to Performance Insights), find top SQL with pg_stat_statements and Query Store, read execution plans, design indexes, and tame parameter sniffing on SQL Server and PostgreSQL.
Assumes: .NET 10 LTS Β· Npgsql 10.0.3 Β· AWS SDK for .NET v4 (AWSSDK.PI 4.1.0.2) Β· PostgreSQL 17 Β· SQL Server 2022 Β· CloudWatch Database Insights Β· Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Use a repeatable method β measure, find the top consumer, explain, fix, verify β instead of guessing at database performance problems.
- Read DB load, average active sessions and wait events in CloudWatch Database Insights, and explain how it replaced the Performance Insights console in 2026.
- Find the most expensive queries with
pg_stat_statements(PostgreSQL) and Query Store (SQL Server), and read execution plans withEXPLAIN (ANALYZE, BUFFERS). - Design composite, covering and partial indexes for Harbourline's real access patterns β and know when an index makes things worse.
- Recognise and fix parameter sniffing on RDS for SQL Server and its PostgreSQL equivalent (generic vs custom plans), including the Npgsql angle.
- Decide when the fix is in the query, the schema, the application, or the instance (class, storage, Aurora I/O-Optimized).
Prerequisites
- Transactions, isolation levels and consistency
- Amazon Redshift and S3 as data stores β the
transactionstable in the local PostgreSQL container - Module 11: Monitoring for CloudWatch alarms and dashboards in depth
- Docker Desktop, .NET 10 SDK; optional SQL Server 2022 container for the T-SQL section
Why this matters in banking
Every first business day of the month, Harbourline's "statement generation" job slows the mobile app. The on-call engineer's usual fix is to scale the Aurora writer up a size. It works β for a month β and the bill has quietly doubled over a year.
The real cause, found later in ten minutes with the right tools: one statement query that runs well for normal customers picks a terrible plan when its first execution after a restart happens to be for Harbourline's settlement account, which has 40 million rows. Every customer then gets the settlement-account plan. SQL Server developers know this as parameter sniffing. It exists on PostgreSQL too, in a different form.
Performance in banking is not only speed. Slow queries hold locks longer (more deadlocks), fill connection pools (outages), and push batch work past cut-off times for payment schemes. A disciplined method saves money and incidents.
The idea in plain language
A database is busy doing one of a few things at any moment: using CPU, waiting for I/O (reading pages from storage), waiting for a lock held by someone else, or waiting on something else (network, commit to log, client). DB load is the number of sessions actively doing or waiting on work, averaged over time β the average active sessions (AAS). Compare it with the number of vCPUs: if AAS is far above vCPUs and the waits are CPU, the database is overloaded; if waits are I/O or locks, more CPU will not help.
The method:
- Measure: DB load, waits, top SQL β over the slow period, compared with a normal period.
- Find the top consumer: usually one to three queries cause most of the load.
- Explain: read that query's execution plan with real row counts.
- Fix the cause: index, rewrite, statistics, plan stability, application change β and only then capacity.
- Verify: the same metrics after the change, and add an alarm so it doesn't come back silently.
Analogy: A branch with long queues. Hiring more cashiers (bigger instance) helps only if cashiers are the bottleneck. If customers are queuing because one cashier keeps walking to the vault for every coin (I/O), or because everyone waits for the one manager's signature (locks), you fix the process. Database Insights is the CCTV that shows where people are standing.
What problem the AWS tools solve
| Tool | What it shows | Notes (checked 2026-11-18) |
|---|---|---|
| CloudWatch Database Insights β Standard mode | DB load by wait, SQL, host, user; database metrics with alarms | Default mode; detailed per-query metrics retained 7 days free; paid retention for 1β24 months |
| CloudWatch Database Insights β Advanced mode | Everything in Standard plus per-query statistics, execution plans (Aurora PostgreSQL, RDS for Oracle, RDS for SQL Server), lock analysis (PostgreSQL), slow-query analysis from exported logs, fleet views, on-demand analysis | Charged β VERIFY current pricing on the CloudWatch pricing page; 1β24 months retention included |
| Performance Insights | The original DB-load dashboard | AWS set the console's end-of-life for 31 July 2026 and migrated users to Database Insights; the Performance Insights API continues with no pricing change |
| Enhanced Monitoring | OS-level metrics (per-process CPU, memory, I/O) at 1β60 s granularity | Agent on the DB host; metrics in CloudWatch Logs |
| CloudWatch RDS metrics | CPUUtilization, FreeableMemory, ReadIOPS, ReadLatency, DiskQueueDepth, DatabaseConnections, ReplicaLag | Basic health; alarms |
| Engine tools | pg_stat_statements, EXPLAIN, auto_explain, pg_stat_activity, pg_locks; SQL Server Query Store, DMVs, Extended Events | Same as on-premises; available on RDS within the limits of the managed service (no OS access) |
| DevOps Guru for RDS | ML anomaly detection and recommendations | Optional, paid per resource-hour β VERIFY |
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Azure SQL Query Performance Insight / Intelligent Insights | CloudWatch Database Insights | Engine-neutral; DB load by wait is the central metric |
| SQL Server Query Store | Query Store on RDS for SQL Server (same feature); pg_stat_statements on PostgreSQL | pg_stat_statements aggregates by normalised query; no plan history unless you add auto_explain or Advanced mode |
sys.dm_exec_query_stats, sys.dm_os_wait_stats | Same DMVs on RDS SQL Server; pg_stat_statements, pg_stat_activity.wait_event on PostgreSQL | Wait-event names differ entirely between engines |
| Actual execution plan in SSMS | EXPLAIN (ANALYZE, BUFFERS) | Text/JSON output; tools like pgAdmin visualise it |
Parameter sniffing, OPTION (RECOMPILE), OPTIMIZE FOR | PostgreSQL generic vs custom plans, plan_cache_mode | PostgreSQL chooses after five executions of a prepared statement |
| Azure SQL automatic tuning (force last good plan) | Query Store plan forcing on RDS SQL Server; no built-in equivalent on PostgreSQL | On PostgreSQL you fix with query/index/statistics changes or plan_cache_mode |
| Azure SQL service tiers (DTU/vCore) | Instance class + storage type (gp3/io2) or Aurora Standard/I/O-Optimized | You size compute and storage separately |
Architecture
Security-boundary view: performance tools see SQL text, which can contain literals with personal data if the application does not parameterise queries. Database Insights supports fine-grained IAM control of who can see SQL text dimensions (pi:GetResourceMetrics with dimension restrictions); pg_stat_statements normalises literals to $1, $2. Grant these views to the DBA/on-call role, not to everyone with console access.
Diagnosing with wait events
| Wait (PostgreSQL) | Wait (SQL Server) | Usually means | Typical fix |
|---|---|---|---|
CPU | SOS_SCHEDULER_YIELD, CPU | Too much work per query (scans, sorts, hash joins) or too many queries | Index, rewrite, cache; then scale |
IO:DataFileRead | PAGEIOLATCH_SH | Reading pages not in memory | Index to read fewer pages; more memory; Optimized Reads instance classes |
Lock:transactionid, Lock:tuple | LCK_M_X, LCK_M_U | Row-lock contention | Shorter transactions; consistent lock order; less hot rows |
LWLock:BufferMapping/buffer_content | PAGELATCH_EX | In-memory contention on hot pages | Reduce hot-spot inserts (e.g. random vs sequential keys), partitioning |
IO:XactSync (Aurora) / IO:WALWrite | WRITELOG | Commit latency; many tiny transactions | Batch commits where business rules allow |
Client:ClientRead | ASYNC_NETWORK_IO | The application is slow to consume results | Stream results; don't fetch 100,000 rows to display 50 |
Step-by-step implementation
All steps use the local PostgreSQL container and the transactions table from the previous lesson. Re-seed it if you archived most rows.
Restart PostgreSQL with pg_stat_statements, then seed skewed data
pg_stat_statements must be in shared_preload_libraries, which needs a server restart, so recreate the container first:
docker rm -f hl-m06-pg
docker run --name hl-m06-pg -e POSTGRES_PASSWORD=LocalOnly_ChangeMe1 -p 5432:5432 -d postgres:17 -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=allThen create the transactions table (the CREATE TABLE from the previous lesson's transactions.sql) and run:
-- 200,000 rows for 2,000 ordinary accounts (about 100 each)β¦
INSERT INTO transactions
SELECT gen_random_uuid(), 'ACC-' || lpad((1 + (g % 2000))::text, 10, '0'),
DATE '2026-01-01' + (g % 300), round((random() * -200)::numeric, 2), 'GBP',
'card_payment', 'card', '5411', 'booked', now()
FROM generate_series(1, 200000) g;
-- β¦and 1,000,000 rows for one settlement account.
INSERT INTO transactions
SELECT gen_random_uuid(), 'ACC-9000000001', DATE '2026-01-01' + (g % 300),
round((random() * 500)::numeric, 2), 'GBP', 'transfer_in', 'batch', NULL, 'booked', now()
FROM generate_series(1, 1000000) g;
ANALYZE transactions;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;On RDS for PostgreSQL, pg_stat_statements is preloaded through the default parameter group β VERIFY for your engine version; you only run CREATE EXTENSION.
Read the plan of the statement query
EXPLAIN (ANALYZE, BUFFERS)
SELECT booking_date, amount, type
FROM transactions
WHERE account_id = 'ACC-0000000042'
ORDER BY booking_date DESC
LIMIT 50;You will see a Seq Scan (or a parallel one) reading all 1.2 million rows, Buffers: shared read=β¦ in the thousands, and a top-N sort. Every statement request does this.
Add the right index
CREATE INDEX CONCURRENTLY ix_tx_account_date
ON transactions (account_id, booking_date DESC)
INCLUDE (amount, type);
ANALYZE transactions;Re-run the EXPLAIN. You should get an Index Only Scan using ix_tx_account_date, reading a handful of buffers, in well under a millisecond.
- Column order follows the query: equality column first (
account_id), then the sort column. DESCmatchesORDER BY β¦ DESC, so no sort node is needed.INCLUDE (amount, type)makes it covering: the table is not touched (helped by an up-to-date visibility map β runVACUUMon busy tables).CONCURRENTLYbuilds without blocking writes β essential on a live RDS database (it cannot run inside a transaction block).
See the parameter-sensitivity problem
PREPARE stmt(text) AS
SELECT count(*), sum(amount) FROM transactions WHERE account_id = $1 AND booking_date >= DATE '2026-06-01';
-- Run six times for an ordinary account, then once for the settlement account.
EXECUTE stmt('ACC-0000000042'); EXECUTE stmt('ACC-0000000042'); EXECUTE stmt('ACC-0000000042');
EXECUTE stmt('ACC-0000000042'); EXECUTE stmt('ACC-0000000042'); EXECUTE stmt('ACC-0000000042');
EXPLAIN (ANALYZE) EXECUTE stmt('ACC-9000000001');After five executions, PostgreSQL considers a generic plan (one plan for any parameter value) and keeps it if its estimated cost is not worse than the average custom plan. A generic plan estimates rows for an "average" account. If PostgreSQL has switched to it (typical with this skew, though the choice is cost-based), the settlement account's rows= estimate and actual row count differ by orders of magnitude, and the plan shape chosen for 100-row accounts is used for a 400,000-row one.
SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE) EXECUTE stmt('ACC-9000000001');With custom plans, PostgreSQL plans each execution with the actual value. Compare the plans and timings.
Find top SQL
Run the console app from Complete code to generate load, then:
SELECT left(query, 80) AS query, calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
shared_blks_read, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;Rank by total time, not mean: a 2 ms query called 5 million times matters more than a 3 s query called once a day.
AWS Console instructions
- Turn on Database Insights: Console β RDS β Databases β (instance or cluster) β Modify β Monitoring β Database Insights: choose Standard (default) or Advanced; set retention. Advanced mode for execution plans and per-query statistics may require Performance Insights data collection to be on β VERIFY the current prerequisite wording.
- View load: Console β CloudWatch β Insights β Database Insights (fleet health view) β select the instance β Database load chart, slice by Waits, then by Top SQL. Compare the slow window with the same window a week earlier.
- Enhanced Monitoring: RDS β Modify β Monitoring β Enable Enhanced Monitoring, granularity 15 s; requires the
rds-monitoring-role. - Logs: RDS β Modify β Log exports β postgresql (or SQL Server error/agent logs) to CloudWatch Logs, then CloudWatch β Log groups β /aws/rds/β¦ β set retention (e.g. 30 days).
- Parameter group: RDS β Parameter groups β (custom group):
log_min_duration_statement = 500(ms),auto_explain.log_min_duration = 1000, addauto_explaintoshared_preload_libraries(requires reboot). Never edit the default parameter group β create a custom one.
AWS CLI
# Enable Database Insights Standard mode and 7-day retention on an RDS instance.
aws rds modify-db-instance --db-instance-identifier hl-m06-pg \
--database-insights-mode standard --enable-performance-insights \
--performance-insights-retention-period 7 --apply-immediately \
--profile learn-sandbox --region us-east-1
# Enhanced Monitoring every 15 seconds (role must exist).
aws rds modify-db-instance --db-instance-identifier hl-m06-pg \
--monitoring-interval 15 --monitoring-role-arn arn:aws:iam::111122223333:role/rds-monitoring-role \
--apply-immediately --profile learn-sandbox --region us-east-1
# Custom parameter group with slow-query logging and auto_explain.
aws rds create-db-parameter-group --db-parameter-group-name hl-m06-pg17-perf \
--db-parameter-group-family postgres17 --description "Slow query logging" \
--profile learn-sandbox --region us-east-1
aws rds modify-db-parameter-group --db-parameter-group-name hl-m06-pg17-perf --parameters \
"ParameterName=log_min_duration_statement,ParameterValue=500,ApplyMethod=immediate" \
"ParameterName=shared_preload_libraries,ParameterValue='pg_stat_statements,auto_explain',ApplyMethod=pending-reboot" \
"ParameterName=auto_explain.log_min_duration,ParameterValue=1000,ApplyMethod=immediate" \
--profile learn-sandbox --region us-east-1
# DB load grouped by wait event for the last hour, through the Performance Insights API.
RID=$(aws rds describe-db-instances --db-instance-identifier hl-m06-pg \
--query "DBInstances[0].DbiResourceId" --output text --profile learn-sandbox --region us-east-1)
aws pi get-resource-metrics --service-type RDS --identifier "$RID" \
--metric-queries '[{"Metric":"db.load.avg","GroupBy":{"Group":"db.wait_event","Limit":5}}]' \
--start-time "$(date -u -d '-1 hour' +%Y-%m-%dT%H:%M:%SZ)" --end-time "$(date -u +%Y-%m-%dT%H:%M:%SZ)" \
--period-in-seconds 60 --profile learn-sandbox --region us-east-1Check the --database-insights-mode flag against your CLI version (aws rds modify-db-instance help) β VERIFY; older CLI builds will not recognise it.
Infrastructure as Code
using Amazon.CDK;
using Amazon.CDK.AWS.CloudWatch;
using Amazon.CDK.AWS.RDS;
using Constructs;
namespace Harbourline.Infra;
public sealed class DbPerformanceAlarms : Construct
{
public DbPerformanceAlarms(Construct scope, string id, IDatabaseInstance db, int vCpus) : base(scope, id)
{
// DB load (average active sessions) persistently above the vCPU count = saturated.
var dbLoad = new Metric(new MetricProps
{
Namespace = "AWS/RDS",
MetricName = "DBLoad",
DimensionsMap = new Dictionary<string, string> { ["DBInstanceIdentifier"] = db.InstanceIdentifier },
Statistic = "Average",
Period = Duration.Minutes(1)
});
_ = new Alarm(this, "DbLoadHigh", new AlarmProps
{
AlarmName = $"{db.InstanceIdentifier}-dbload-above-vcpus",
Metric = dbLoad,
Threshold = vCpus,
EvaluationPeriods = 15,
DatapointsToAlarm = 12,
ComparisonOperator = ComparisonOperator.GREATER_THAN_THRESHOLD,
TreatMissingData = TreatMissingData.NOT_BREACHING
});
// Read latency is a leading indicator for I/O-bound waits.
_ = new Alarm(this, "ReadLatencyHigh", new AlarmProps
{
AlarmName = $"{db.InstanceIdentifier}-read-latency",
Metric = db.Metric("ReadLatency", new MetricOptions { Statistic = "Average", Period = Duration.Minutes(5) }),
Threshold = 0.02, // seconds
EvaluationPeriods = 3,
ComparisonOperator = ComparisonOperator.GREATER_THAN_THRESHOLD
});
}
}DBLoad is published to CloudWatch when Performance Insights / Database Insights collection is enabled.
Complete code
A small load generator and "top SQL" reporter. It runs the statement query for many accounts, including the settlement account, with Npgsql's automatic preparation on β which is exactly the situation where generic plans appear.
using System.Diagnostics;
using Npgsql;
// Max Auto Prepare: Npgsql prepares frequently used statements on the server automatically.
// That improves throughput β and makes PostgreSQL's generic-plan behaviour relevant.
var cs = "Host=localhost;Username=postgres;Password=LocalOnly_ChangeMe1;Database=postgres;" +
"Max Auto Prepare=20;Auto Prepare Min Usages=2;Maximum Pool Size=20";
await using var ds = NpgsqlDataSource.Create(cs);
const string sql = """
SELECT count(*), coalesce(sum(amount), 0)
FROM transactions
WHERE account_id = $1 AND booking_date >= $2
""";
var mode = args.FirstOrDefault() ?? "auto"; // auto | custom
var accounts = Enumerable.Range(1, 200).Select(i => $"ACC-{i:D10}").ToList();
accounts.Insert(50, "ACC-9000000001"); // the settlement account appears mid-stream
var timings = new List<(string Account, double Ms)>();
await using (var conn = await ds.OpenConnectionAsync())
{
if (mode == "custom")
{
// Session-level: plan every execution with its actual parameter values.
await using var set = new NpgsqlCommand("SET plan_cache_mode = force_custom_plan", conn);
await set.ExecuteNonQueryAsync();
}
foreach (var account in accounts)
{
await using var cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.AddWithValue(account);
cmd.Parameters.AddWithValue(new DateOnly(2026, 6, 1));
var sw = Stopwatch.StartNew();
await using var r = await cmd.ExecuteReaderAsync();
await r.ReadAsync();
sw.Stop();
timings.Add((account, sw.Elapsed.TotalMilliseconds));
}
}
var ordinary = timings.Where(t => t.Account != "ACC-9000000001").Select(t => t.Ms).Order().ToList();
var p50 = ordinary[ordinary.Count / 2];
var p99 = ordinary[(int)(ordinary.Count * 0.99)];
var settlement = timings.Single(t => t.Account == "ACC-9000000001").Ms;
Console.WriteLine($"mode={mode} ordinary p50={p50:0.00} ms p99={p99:0.00} ms settlement={settlement:0.0} ms");
// Top SQL by total time from pg_stat_statements.
await using var top = ds.CreateCommand("""
SELECT left(regexp_replace(query, '\s+', ' ', 'g'), 70), calls,
round(total_exec_time::numeric, 1), round(mean_exec_time::numeric, 3), shared_blks_hit + shared_blks_read
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC LIMIT 5
""");
await using var rd = await top.ExecuteReaderAsync();
Console.WriteLine($"{"query",-70} {"calls",6} {"total_ms",10} {"mean_ms",9} {"blocks",9}");
while (await rd.ReadAsync())
Console.WriteLine($"{rd.GetString(0),-70} {rd.GetInt64(1),6} {rd.GetDecimal(2),10} {rd.GetDecimal(3),9} {rd.GetInt64(4),9}");Run dotnet run -- auto, then SELECT pg_stat_statements_reset(); and dotnet run -- custom. Compare ordinary p50/p99 and the settlement time in both modes, with and without the index from step 3.
Reading Database Insights data from C#
using Amazon.PI;
using Amazon.PI.Model;
namespace Harbourline.Ops;
// Prints the top wait events by average DB load for the last hour.
public static class DbLoadReport
{
public static async Task PrintAsync(IAmazonPI pi, string dbiResourceId, CancellationToken ct)
{
var resp = await pi.GetResourceMetricsAsync(new GetResourceMetricsRequest
{
ServiceType = ServiceType.RDS,
Identifier = dbiResourceId, // e.g. "db-ABCDEFGHIJKLMNOPQRSTUVWXY0"
StartTime = DateTime.UtcNow.AddHours(-1),
EndTime = DateTime.UtcNow,
PeriodInSeconds = 300,
MetricQueries =
[
new MetricQuery { Metric = "db.load.avg", GroupBy = new DimensionGroup { Group = "db.wait_event", Limit = 5 } }
]
}, ct);
foreach (var series in resp.MetricList ?? [])
{
var label = series.Key.Dimensions is { Count: > 0 } d ? d.GetValueOrDefault("db.wait_event.name", "total") : "total";
var avg = series.DataPoints?.Where(p => p.Value is not null).Select(p => p.Value!.Value).DefaultIfEmpty(0).Average() ?? 0;
Console.WriteLine($"{label,-35} avg active sessions {avg:0.00}");
}
}
}The caller needs pi:GetResourceMetrics on arn:aws:pi:us-east-1:111122223333:metrics/rds/db-β¦. Restrict access to SQL-text dimensions separately if your policy requires it.
SQL Server: the same problem, the T-SQL way
-- Query Store is on by default for new databases in SQL Server 2022; confirm and set capture mode.
ALTER DATABASE HarbourlineLedger SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);
CREATE OR ALTER PROCEDURE dbo.GetAccountSummary @AccountId varchar(20), @From date
AS
SELECT COUNT(*) AS Txns, SUM(Amount) AS Total
FROM dbo.Transactions
WHERE AccountId = @AccountId AND BookingDate >= @From;
GO
-- Option 1: recompile per execution (CPU cost per call; good for infrequent, skew-sensitive queries).
-- ... WHERE AccountId = @AccountId AND BookingDate >= @From OPTION (RECOMPILE);
-- Option 2: SQL Server 2022 Parameter Sensitive Plan optimisation (compatibility level 160) caches several plans per query.
ALTER DATABASE HarbourlineLedger SET COMPATIBILITY_LEVEL = 160;
-- Option 3: find the regressed query in Query Store and force the known-good plan.
SELECT TOP (5) q.query_id, p.plan_id, rs.avg_duration, rs.count_executions
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
ORDER BY rs.avg_duration DESC;
-- EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;On RDS for SQL Server you have db_owner-level rights in your databases, so Query Store, DMVs and plan forcing work as on-premises; server-level settings change through the DB parameter group instead of sp_configure β VERIFY which settings are exposed for your edition.
Code explained line by line
Max Auto Prepare=20;Auto Prepare Min Usages=2β Npgsql prepares a statement on the server after it has been used twice on a connection. Prepared statements skip parsing and planning on later runs β and are the statements PostgreSQL may switch to a generic plan.accounts.Insert(50, "ACC-9000000001")β the skewed value appears after the statement has been executed many times, mimicking a production pattern.SET plan_cache_mode = force_custom_planβ plans every execution with its real values. Costs planning CPU per call; worth it for skew-sensitive queries. Can also be set per role (ALTER ROLE statements_job SET plan_cache_mode = force_custom_plan) or in the RDS parameter group.Stopwatcharound execute and first read β measures database time plus round trip, which is what customers feel.- Percentiles (p50, p99) β averages hide the slow tail that customers complain about.
pg_stat_statementsquery ordered bytotal_exec_timeβ the "top SQL" view.shared_blks_hit + shared_blks_readshows how many 8 KB pages a query touches β the best proxy for "work done".GetResourceMetricsAsyncwithdb.load.avggrouped bydb.wait_eventβ the same data as the Database Insights chart, available for automation (e.g. a daily report or a Slack summary).series.Key.Dimensionsβ the total series has no dimensions; grouped series carrydb.wait_event.name.- T-SQL
OPTION (RECOMPILE), PSP optimisation (compatibility level 160), and Query Store plan forcing β three standard SQL Server fixes, all available on RDS for SQL Server 2022.
Index design rules for Harbourline
| Rule | Example |
|---|---|
| Index for the access pattern, equality columns first, then range/sort | (account_id, booking_date DESC) |
Make hot read paths covering with INCLUDE | INCLUDE (amount, type) |
| Partial indexes for small hot subsets | CREATE INDEX β¦ ON payments (created_at) WHERE status IN ('received','screening') |
| BRIN for huge append-only time columns | USING brin (created_at) on an audit table: tiny, good for range scans |
| Don't index low-selectivity columns alone | status alone rarely helps; (status, created_at) partial may |
| Every index slows writes and uses memory | Remove unused indexes: pg_stat_user_indexes.idx_scan = 0 over a full business cycle |
| Foreign keys need indexes on the referencing column (PostgreSQL does not create them) | ledger_entries(account_id) |
| Build online | CREATE INDEX CONCURRENTLY (PostgreSQL); ONLINE = ON (SQL Server Enterprise edition β check your RDS edition) |
When to change the instance or storage
Only after the query-level work, and with evidence:
| Evidence | Consider |
|---|---|
| CPU waits dominate, top SQL already efficient, AAS above vCPUs at peak | Larger instance class, or offload reads to replicas, or cache |
IO:DataFileRead dominates, working set larger than memory | Memory-optimised class (r-family); Aurora Optimized Reads classes with local NVMe β VERIFY engine support |
RDS gp3 at its IOPS/throughput limit (ReadIOPS flat at the configured value) | Raise gp3 IOPS/throughput, or io2 for consistently high IOPS |
| Aurora I/O charges are a large share of the database bill | Aurora I/O-Optimized cluster configuration (no per-I/O charge, higher instance and storage prices) |
| Many idle connections, connection storms | RDS Proxy (see this module's connection pooling lesson) |
| Read-heavy, tolerant of lag | Read replicas / Aurora replicas with the reader endpoint |
Security considerations
β Learning shortcut β not for production
The demos run as the postgres superuser, and pg_stat_statements.track=all plus auto_explain log queries. In production, grant pg_read_all_stats (PostgreSQL) or VIEW SERVER PERFORMANCE STATE/VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) to a dedicated monitoring role, keep log retention bounded, and make sure application queries are parameterised so logs do not capture personal data as literals.
- SQL text is sensitive. Literals in non-parameterised queries can contain names, IBANs or card tokens. Parameterise everything; restrict SQL-text visibility in Database Insights with IAM.
- Plans can leak schema details useful to an attacker. Treat performance dashboards as internal-confidential.
- Logs:
log_min_duration_statementandauto_explainwrite to CloudWatch Logs β encrypt the log group with KMS where policy requires, set retention, and restrictlogs:GetLogEvents. - Changes are production changes. New indexes, parameter-group edits and plan forcing go through change management with a rollback plan β especially on the ledger.
Banking use case
The month-start incident, resolved with the method:
- Measure: Database Insights showed DB load at 3Γ vCPUs from 06:00, dominated by
IO:DataFileRead, top SQL = the statement summary query. - Explain: the plan was a sequential scan with an estimated 600 rows vs 40 million actual β a generic plan chosen after the settlement account ran first.
- Fix:
(account_id, booking_date DESC) INCLUDE (amount, type)index builtCONCURRENTLY; the statements job role gotplan_cache_mode = force_custom_plan; the settlement account was excluded from customer statement runs (it has its own reconciliation report). - Verify: DB load at month start fell below 0.5Γ vCPUs; the writer was scaled down one size, saving more than the index cost in storage.
- Prevent: DB load alarm, a monthly top-SQL review, and a test with production-like skew in the performance-test environment (fictional data with the same distribution).
Common mistakes
- Scaling up first β it's the fastest change β measure first; a bigger instance hides the problem and costs money every hour.
- Optimising by mean time β the dashboard shows averages β look at total time and p99.
- Adding an index per slow query β write amplification, memory pressure β design indexes for access patterns and remove unused ones.
- Testing with uniform data β generators spread rows evenly β reproduce production skew (hot accounts, month-end peaks).
CREATE INDEXwithoutCONCURRENTLYon a live table β blocks writes for minutes β always concurrent (and monitor for anINVALIDindex if it fails).- Leaving Performance Insights-era runbooks β the console now redirects to Database Insights β update runbooks and IAM policies to the new console paths and modes.
OPTION (RECOMPILE)everywhere β CPU cost on every call β use selectively, or PSP optimisation / plan forcing.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
pg_stat_statements must be loaded via shared_preload_libraries | Library not preloaded | Add to shared_preload_libraries in the parameter group; reboot |
| No data in Database Insights | Collection not enabled, or IAM lacks pi:* read actions | Enable on the instance; grant pi:GetResourceMetrics, pi:DescribeDimensionKeys |
| Execution plans not visible | Standard mode, or engine not supported for plans | Advanced mode supports plans for Aurora PostgreSQL, RDS for Oracle, RDS for SQL Server |
| Index exists but is not used | Wrong column order, function on the column (lower(x)), type mismatch, stale statistics | Match the predicate; expression index; ANALYZE |
CREATE INDEX CONCURRENTLY leaves an INVALID index | Build failed (deadlock, unique violation) | DROP INDEX CONCURRENTLY and retry |
| Query fast in psql, slow from the app | Generic plan from prepared statements; different parameter types (e.g. text vs varchar) | Compare plans with EXPLAIN EXECUTE; consider plan_cache_mode; match parameter types |
High Client:ClientRead waits | App fetching too many rows or slow consumer | Page results; stream; fix N+1 query patterns in EF Core |
Cost and cleanup
Everything here runs locally and is free. In AWS:
- Database Insights Standard mode: free for 7 days of detailed metrics; longer retention is paid.
- Advanced mode: paid (priced per vCPU or per Aurora Serverless capacity β VERIFY on the CloudWatch pricing page) β enable it for production fleets where you need plans and long history, not for a lab.
- Enhanced Monitoring: billed as CloudWatch Logs ingestion and storage; 15-second granularity on one small instance is a few cents a day.
- Log exports: CloudWatch Logs ingestion and storage β set retention.
docker rm -f hl-m06-pg# If you created AWS resources for this lesson:
aws cloudwatch delete-alarms --alarm-names hl-m06-pg-dbload-above-vcpus --profile learn-sandbox --region us-east-1
aws rds delete-db-parameter-group --db-parameter-group-name hl-m06-pg17-perf --profile learn-sandbox --region us-east-1
aws logs delete-log-group --log-group-name /aws/rds/instance/hl-m06-pg/postgresql --profile learn-sandbox --region us-east-1Exercises
- π’ Guided: Reproduce steps 2β3: record the plan, buffers and time before and after the index. Then drop
INCLUDE (amount, type)and explain the difference in the plan. - π‘ Challenge: Using
pg_stat_user_indexesandpg_stat_statements, write a SQL report of (a) indexes never scanned and (b) the top 5 queries by total time with their average blocks touched. Propose one index to drop and one to add, with evidence. - π΄ Live problem: After migrating the statements service from SQL Server to Aurora PostgreSQL, p99 latency doubled, but average latency improved. The team suspects Aurora. Database Insights shows mostly
CPUandClient:ClientRead. What do you investigate, in what order?
Assignment
Write a one-page database performance runbook for Harbourline: the five-step method, the first three Database Insights views to open, the wait-event table, how to capture a plan safely in production, the change process for indexes and parameter groups, and the alarms you deploy by default. Add the C# load generator as a tool in your repository.
Solutions
Before: a parallel sequential scan of ~1.2 M rows, thousands of buffers, tens to hundreds of ms. After: Index Only Scan using ix_tx_account_date, a few buffers, sub-millisecond. Without INCLUDE, the plan becomes an Index Scan that visits the table heap for amount and type for each of the 50 rows β still fast, but more buffers. For the hottest read path, covering pays off; for others, the extra index size may not be worth it.
-- (a) Unused indexes (over a full business cycle!)
SELECT s.relname AS table_name, s.indexrelname AS index_name, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;
-- (b) Top queries by total time with average blocks per call
SELECT left(query, 80) AS query, calls, round(total_exec_time::numeric) AS total_ms,
round((shared_blks_hit + shared_blks_read)::numeric / greatest(calls, 1)) AS avg_blocks
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;Why: never drop an index used only by month-end or year-end jobs β check the statistics after a full cycle and confirm when they were last reset (pg_stat_database.stats_reset). Unique and primary-key indexes enforce constraints and are excluded.
- Client:ClientRead means the database is waiting for the client: check the application for fetching large result sets, N+1 query patterns (EF Core lazy loading), and synchronous processing between reads. Compare row counts per request before and after migration.
- CPU with a worse tail: find the top SQL by total time and look for plan differences for skewed parameters β generic plans from Npgsql auto-prepare are a common migration surprise. Test with
plan_cache_mode = force_custom_planfor that role. - Check indexes that existed in SQL Server but were not migrated (or were clustered there and are not in PostgreSQL β PostgreSQL has no clustered indexes; a covering index may be needed).
- Check connection pooling: pool size, RDS Proxy pinning, and the number of round trips per request.
- Only then consider instance size. Report findings with before/after p99 measurements.
Knowledge check
Answer: CloudWatch Database Insights (Standard and Advanced modes). AWS set the Performance Insights console end-of-life for 31 July 2026 and migrated users; the Performance Insights API continues to work.
Answer: Not much. The sessions are waiting for storage reads. Reduce pages read (indexes, better queries) or increase memory so the working set is cached.
Answer: Generic plans for prepared statements. After five executions, PostgreSQL may switch to a single generic plan that ignores the actual parameter value, which can be badly wrong for skewed data. plan_cache_mode controls the behaviour.
Answer: Total time reflects the load a query puts on the database (calls Γ mean). A cheap query called millions of times often matters more than a slow, rare one.
Answer: A normal build takes a lock that blocks writes for the whole build. CONCURRENTLY allows writes to continue, at the cost of a longer build and the need to handle a failed (INVALID) index.
Interview questions
- "How do you troubleshoot a slow database?" β Measure DB load and waits over the slow period vs a baseline, find top SQL by total time, read the actual plan, fix the cause (index, query, statistics, plan stability, app pattern), verify, alarm. Scale only with evidence.
- "Explain parameter sniffing and three fixes." β The plan compiled for the first parameter value is reused for all values. Fixes:
OPTION (RECOMPILE), PSP optimisation in SQL Server 2022, Query Store plan forcing,OPTIMIZE FOR; on PostgreSQL,plan_cache_mode = force_custom_planor query/index changes. - "What is a covering index?" β An index containing all columns a query needs, so the engine never reads the table β
INCLUDEcolumns in PostgreSQL and SQL Server. - "Aurora Standard or I/O-Optimized?" β I/O-Optimized when I/O charges are a large share of the Aurora bill (AWS guidance cites roughly a quarter or more β VERIFY); Standard for low-I/O workloads.
Certification practice
An RDS for PostgreSQL instance shows high CPU at month start. The team wants to identify which SQL statements and wait events contribute most to database load, with minimal setup. What should they use?
A. Amazon RDS Enhanced Monitoring. B. CloudWatch Database Insights for the instance. C. AWS CloudTrail data events for RDS. D. VPC Flow Logs for the database subnet.
Answer: B. Database Insights shows DB load sliced by SQL and wait events. Enhanced Monitoring shows OS processes, not SQL (A). CloudTrail records API calls, not queries (C). Flow logs show network traffic (D).
A .NET application using Npgsql runs the same parameterised query for many customers. After a few minutes in production, queries for one very large customer become extremely slow, while others are fine. Restarting the application temporarily fixes it. What is the most likely cause?
A. Connection pool exhaustion. B. A generic plan chosen for the prepared statement that suits typical customers but not the skewed one. C. Missing TLS configuration. D. DynamoDB throttling.
Answer: B. Npgsql auto-prepares frequent statements; PostgreSQL may switch to a generic plan after several executions, which is bad for skewed values. A restart clears prepared statements. Pool exhaustion affects all customers (A); TLS and DynamoDB are unrelated (C, D).
A data engineer must be alerted when an Aurora PostgreSQL writer is saturated, without alerting on short spikes. Which approach is best?
A. Alarm on CPUUtilization above 50% for one datapoint. B. Alarm on DBLoad above the instance's vCPU count for most datapoints in a 15-minute window. C. Alarm on FreeStorageSpace below 10 GB. D. Alarm on DatabaseConnections above 10.
Answer: B. DBLoad (average active sessions) compared with vCPUs over a sustained window indicates saturation while ignoring brief spikes. One CPU datapoint is noisy (A). Storage and connection counts measure other things (C, D).
Completion checklist
- I can explain DB load, AAS and the main wait-event families
- I read a plan before and after adding a covering index
- I reproduced a generic-plan problem and fixed it with
plan_cache_mode - I can find top SQL with
pg_stat_statementsand Query Store - I know where Database Insights replaced Performance Insights and what each mode costs
- I removed the local container and any alarms or parameter groups I created
Key takeaways
- Follow the method: measure, find top consumers, explain the plan, fix the cause, verify β scale last.
- DB load by wait event tells you what kind of fix is needed; compare with vCPUs.
- CloudWatch Database Insights replaced the Performance Insights console (EOL 31 July 2026); Standard is the free default, Advanced adds plans and per-query history.
- Index for access patterns: equality first, then sort; cover hot paths; build concurrently; remove unused ones.
- Parameter sniffing exists on both engines: SQL Server (recompile, PSP, plan forcing) and PostgreSQL (generic plans,
plan_cache_mode).
Further reading
- CloudWatch Database Insights
- Overview of Performance Insights on Amazon RDS
- PostgreSQL documentation β Using EXPLAIN
- PostgreSQL documentation β pg_stat_statements
- Monitor performance by using the Query Store (SQL Server)
- Parameter Sensitive Plan optimization (SQL Server)
- Next: Database migration
Independent learning resource β not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Amazon Redshift and S3 as data stores
Separate operational and analytical data: move transaction history out of the OLTP database into a partitioned S3 data lake with a reconciled archive job, query it with Athena, and know when a Redshift Serverless warehouse (or zero-ETL) earns its cost.
Database migration
Plan and rehearse a SQL Server migration to AWS: choose between RDS for SQL Server, Babelfish for Aurora PostgreSQL and a full conversion; convert schemas with DMS Schema Conversion; move data with AWS DMS full load plus CDC; and prove correctness with independent validation before cut-over.