Skip to Content
Cloud Engineering Academy
6. Databases & Data Architecture

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.

advanced⏱ 2 h 30 min🟒 FreeSAA-C03DVA-C03DEA-C01

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 with EXPLAIN (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

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:

  1. Measure: DB load, waits, top SQL β€” over the slow period, compared with a normal period.
  2. Find the top consumer: usually one to three queries cause most of the load.
  3. Explain: read that query's execution plan with real row counts.
  4. Fix the cause: index, rewrite, statistics, plan stability, application change β€” and only then capacity.
  5. 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

ToolWhat it showsNotes (checked 2026-11-18)
CloudWatch Database Insights β€” Standard modeDB load by wait, SQL, host, user; database metrics with alarmsDefault mode; detailed per-query metrics retained 7 days free; paid retention for 1–24 months
CloudWatch Database Insights β€” Advanced modeEverything 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 analysisCharged ⚠ VERIFY current pricing on the CloudWatch pricing page; 1–24 months retention included
Performance InsightsThe original DB-load dashboardAWS 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 MonitoringOS-level metrics (per-process CPU, memory, I/O) at 1–60 s granularityAgent on the DB host; metrics in CloudWatch Logs
CloudWatch RDS metricsCPUUtilization, FreeableMemory, ReadIOPS, ReadLatency, DiskQueueDepth, DatabaseConnections, ReplicaLagBasic health; alarms
Engine toolspg_stat_statements, EXPLAIN, auto_explain, pg_stat_activity, pg_locks; SQL Server Query Store, DMVs, Extended EventsSame as on-premises; available on RDS within the limits of the managed service (no OS access)
DevOps Guru for RDSML anomaly detection and recommendationsOptional, paid per resource-hour ⚠ VERIFY

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
Azure SQL Query Performance Insight / Intelligent InsightsCloudWatch Database InsightsEngine-neutral; DB load by wait is the central metric
SQL Server Query StoreQuery Store on RDS for SQL Server (same feature); pg_stat_statements on PostgreSQLpg_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_statsSame DMVs on RDS SQL Server; pg_stat_statements, pg_stat_activity.wait_event on PostgreSQLWait-event names differ entirely between engines
Actual execution plan in SSMSEXPLAIN (ANALYZE, BUFFERS)Text/JSON output; tools like pgAdmin visualise it
Parameter sniffing, OPTION (RECOMPILE), OPTIMIZE FORPostgreSQL generic vs custom plans, plan_cache_modePostgreSQL 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 PostgreSQLOn 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-OptimizedYou 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 meansTypical fix
CPUSOS_SCHEDULER_YIELD, CPUToo much work per query (scans, sorts, hash joins) or too many queriesIndex, rewrite, cache; then scale
IO:DataFileReadPAGEIOLATCH_SHReading pages not in memoryIndex to read fewer pages; more memory; Optimized Reads instance classes
Lock:transactionid, Lock:tupleLCK_M_X, LCK_M_URow-lock contentionShorter transactions; consistent lock order; less hot rows
LWLock:BufferMapping/buffer_contentPAGELATCH_EXIn-memory contention on hot pagesReduce hot-spot inserts (e.g. random vs sequential keys), partitioning
IO:XactSync (Aurora) / IO:WALWriteWRITELOGCommit latency; many tiny transactionsBatch commits where business rules allow
Client:ClientReadASYNC_NETWORK_IOThe application is slow to consume resultsStream 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=all

Then create the transactions table (the CREATE TABLE from the previous lesson's transactions.sql) and run:

skew.sql
-- 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.
  • DESC matches ORDER 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 β€” run VACUUM on busy tables).
  • CONCURRENTLY builds 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, add auto_explain to shared_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-1

Check 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

DbPerformanceAlarms.cs
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.

Program.cs
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#

DbLoadReport.cs
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

sqlserver-sniffing.sql
-- 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

  1. 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.
  2. accounts.Insert(50, "ACC-9000000001") β€” the skewed value appears after the statement has been executed many times, mimicking a production pattern.
  3. 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.
  4. Stopwatch around execute and first read β€” measures database time plus round trip, which is what customers feel.
  5. Percentiles (p50, p99) β€” averages hide the slow tail that customers complain about.
  6. pg_stat_statements query ordered by total_exec_time β€” the "top SQL" view. shared_blks_hit + shared_blks_read shows how many 8 KB pages a query touches β€” the best proxy for "work done".
  7. GetResourceMetricsAsync with db.load.avg grouped by db.wait_event β€” the same data as the Database Insights chart, available for automation (e.g. a daily report or a Slack summary).
  8. series.Key.Dimensions β€” the total series has no dimensions; grouped series carry db.wait_event.name.
  9. 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

RuleExample
Index for the access pattern, equality columns first, then range/sort(account_id, booking_date DESC)
Make hot read paths covering with INCLUDEINCLUDE (amount, type)
Partial indexes for small hot subsetsCREATE INDEX … ON payments (created_at) WHERE status IN ('received','screening')
BRIN for huge append-only time columnsUSING brin (created_at) on an audit table: tiny, good for range scans
Don't index low-selectivity columns alonestatus alone rarely helps; (status, created_at) partial may
Every index slows writes and uses memoryRemove 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 onlineCREATE 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:

EvidenceConsider
CPU waits dominate, top SQL already efficient, AAS above vCPUs at peakLarger instance class, or offload reads to replicas, or cache
IO:DataFileRead dominates, working set larger than memoryMemory-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 billAurora I/O-Optimized cluster configuration (no per-I/O charge, higher instance and storage prices)
Many idle connections, connection stormsRDS Proxy (see this module's connection pooling lesson)
Read-heavy, tolerant of lagRead 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_statement and auto_explain write to CloudWatch Logs β€” encrypt the log group with KMS where policy requires, set retention, and restrict logs: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:

  1. Measure: Database Insights showed DB load at 3Γ— vCPUs from 06:00, dominated by IO:DataFileRead, top SQL = the statement summary query.
  2. 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.
  3. Fix: (account_id, booking_date DESC) INCLUDE (amount, type) index built CONCURRENTLY; the statements job role got plan_cache_mode = force_custom_plan; the settlement account was excluded from customer statement runs (it has its own reconciliation report).
  4. 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.
  5. 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 INDEX without CONCURRENTLY on a live table β†’ blocks writes for minutes β†’ always concurrent (and monitor for an INVALID index 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

SymptomLikely causeFix
pg_stat_statements must be loaded via shared_preload_librariesLibrary not preloadedAdd to shared_preload_libraries in the parameter group; reboot
No data in Database InsightsCollection not enabled, or IAM lacks pi:* read actionsEnable on the instance; grant pi:GetResourceMetrics, pi:DescribeDimensionKeys
Execution plans not visibleStandard mode, or engine not supported for plansAdvanced mode supports plans for Aurora PostgreSQL, RDS for Oracle, RDS for SQL Server
Index exists but is not usedWrong column order, function on the column (lower(x)), type mismatch, stale statisticsMatch the predicate; expression index; ANALYZE
CREATE INDEX CONCURRENTLY leaves an INVALID indexBuild failed (deadlock, unique violation)DROP INDEX CONCURRENTLY and retry
Query fast in psql, slow from the appGeneric 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 waitsApp fetching too many rows or slow consumerPage 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-1

Exercises

  • 🟒 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_indexes and pg_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 CPU and Client: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

Knowledge check

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_plan or query/index changes.
  • "What is a covering index?" β€” An index containing all columns a query needs, so the engine never reads the table β€” INCLUDE columns 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

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_statements and 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

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.

On this page