Troubleshoot EKS to RDS PostgreSQL

Build an evidence-first runbook for EKS to RDS PostgreSQL incidents.

Introduction

30 Second Summary

A payment service can look healthy while every customer request still fails. In a core-banking system, a rushed fix can turn one fault into a wider outage.

In this project, you will build an evidence-first operations runbook for a simulated core-banking incident. The finished runbook turns captured Amazon EKS evidence plus Amazon RDS for PostgreSQL evidence into a root-cause diagnosis with a controlled response.

What You'll Build

Picture opening operations-runbook.md during an incident to follow one evidence trail from an unready payment service to a reversible database-network remediation.

By the end of this project, you'll have:

  • An evidence-backed incident report that traces the payments-api symptom to a mismatched RDS inbound source.
  • A proactive monitoring matrix that connects RDS health trends with database load from CloudWatch Database Insights.
  • A SQL optimization proposal that links resource pressure to query statistics before recommending a candidate index with validation criteria.
  • Secret Mission: Detect sessions marked idle in transaction before they create locking, bloat, or autovacuum trouble.

Are there any prerequisites?

You need Windows with Visual Studio Code, AWS CLI, and kubectl already installed. You do not need an AWS account, cluster access, or database credentials because every investigation uses supplied simulated evidence.

Before We Start

Before any investigation begins, lock in the service you are protecting. An evidence-first commitment keeps this simulation away from costly unapproved infrastructure or database changes.

Prepare a Safe Operator Workspace

In a core-banking incident, pressure can turn an uncertain symptom into an unapproved production change. A written evidence trail keeps each decision anchored to facts.

An offline runbook in Visual Studio Code creates that record. It also makes the approval boundary visible before you inspect simulated Amazon EKS or Amazon RDS for PostgreSQL evidence.

In this step, get ready to:
  • Verify Visual Studio Code, AWS CLI v2, and kubectl locally.
  • Create a Markdown runbook with the incident-report sections.
  • Record the safety boundary for every later investigation.
Verify the local operator tools

These checks confirm that your inspection tools are available before the incident work begins. The client-only checks do not require AWS credentials or cluster access.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open system search.
  • Type Visual Studio Code into system search.
  • Press Enter to open Visual Studio Code.
  • Select View in the top menu.
  • Select Terminal to open the integrated terminal.

Before you run the checks, do you expect any command to contact an AWS account or Kubernetes cluster?

  • Verify the three local command-line clients by running these commands:
code --version
aws --version
kubectl version --client

What do these checks prove?

  • The code output identifies your local editor build.
  • The aws output confirms the AWS CLI v2 installation.
  • The kubectl output reports the client version without contacting a server.

You should see version information beneath all three commands. No production connection is opened by these checks.

Good progress. Your local inspection tools are available without touching a live system.

Missing version information?

  • Close Visual Studio Code so Windows can reload the command search path.
  • Reopen Visual Studio Code through system search.
  • Run the three checks again in a new integrated terminal.

Help me diagnose why a local version check is not returning version information.

Build the runbook structure

A consistent incident structure prevents evidence from being mixed with assumptions. Each section gives one part of the investigation a fixed home.

  • Create and open operations-runbook.md in the folder shown by your terminal prompt by running this command:
code operations-runbook.md

What does this command do?

The command opens operations-runbook.md in Visual Studio Code. It creates the file when it does not already exist.

  • Confirm that the editor has a tab named operations-runbook.md.

The open editor tab confirms that your evidence record is ready for its structure.

Runbook tab did not open?

  • Check that you ran the command in the Visual Studio Code integrated terminal.
  • Check that the filename is exactly operations-runbook.md.

Help me open my Markdown runbook from the Visual Studio Code terminal.

  • Add the incident-report structure to the blank operations-runbook.md editor by pasting this content:
## Safety Boundary

## Incident Summary

## Evidence Timeline

## Hypotheses

## Root Cause

## Remediation

## Validation

## Rollback

## Proactive Monitoring

## SQL Optimization

How is the runbook organized?

  • The incident sections separate the symptom record from the final root cause.
  • The remediation sections reserve space for validation plus rollback.
  • The final sections extend the record into monitoring plus query analysis.
  • Save operations-runbook.md.
  • Confirm that all ten section headings are visible in the editor.

You should see a blank area beneath each heading. Those spaces are ready for evidence from the later steps.

Headings not rendering clearly?

  • Check that every heading begins with two hash characters.
  • Check that each heading remains on its own line.

Help me correct the Markdown section structure in my operations runbook.

Set the safety boundary

The safety boundary separates inspection from mutation. It also protects credentials, customer data, SQL text, and infrastructure identifiers during evidence collection.

  • Place your cursor beneath the Safety Boundary heading in operations-runbook.md.
  • Record the operating rules by pasting this list:
- Simulation only.
- Use read-only inspection before approval.
- Never paste credentials or customer data.
- Redact sensitive SQL and identifiers.
- Require organizational change control for every mutation.

Why define the boundary now?

Read-only inspection preserves the current state while you gather evidence. Change control creates a visible approval point before any mutation.

Redaction keeps the runbook useful without turning it into a store for sensitive operational data.

  • Save operations-runbook.md.

Safety rules in the wrong section?

  • Move the five bullets directly beneath the Safety Boundary heading.
  • Keep the Incident Summary heading below the complete safety list.

Help me place the safety rules correctly in my operations runbook.

✔️ Awesome, I've got everything!

  • Confirm that operations-runbook.md is saved in Visual Studio Code.

ⓧ I'd like to double check the full code

  • Compare operations-runbook.md with this complete reference:
## Safety Boundary

- Simulation only.
- Use read-only inspection before approval.
- Never paste credentials or customer data.
- Redact sensitive SQL and identifiers.
- Require organizational change control for every mutation.

## Incident Summary

## Evidence Timeline

## Hypotheses

## Root Cause

## Remediation

## Validation

## Rollback

## Proactive Monitoring

## SQL Optimization

What should match?

Your file should contain the five safety rules beneath the first heading. The nine remaining headings should follow in the same order.

Before you make the final check, which two surfaces should prove that your workspace is ready?

  • Review the integrated terminal from earlier.
  • Confirm that each of the three local checks printed version information.
  • Confirm that operations-runbook.md remains open in the editor.
  • Compare the saved safety boundary with the complete reference above.

You should see version information for Visual Studio Code, AWS CLI, and kubectl in the terminal. You should also see the complete runbook structure in the open editor.

That's the foundation secured: your local tools are verified without touching a live system.

Your offline operator workspace is ready. Next, you will use this runbook to record the first evidence from the simulated incident.

Triage the Failing EKS Service

Your runbook now provides a safe evidence trail for investigating the simulated Amazon EKS incident. Your next goal is to identify what the first application symptom proves.

The affected application looks broken at first glance. Stopping at Pod status would leave you guessing whether the failure sits in the service, Kubernetes, the network path, or the database.

In this step, get ready to:
  • Inspect the supplied Pod status evidence without connecting to a cluster.
  • Trace the failed readiness check to a database connection timeout.
  • Separate confirmed facts from competing failure hypotheses.
Inspect the first Pod signal

A running container can still fail readiness. Kubernetes keeps an unready Pod out of normal service traffic until its readiness check succeeds.

  • Switch back to the operations-runbook.md tab from earlier.
  • Place your cursor under the Evidence Timeline section.
  • Add the first read-only command pattern by copying this reference line:
kubectl get pods -n banking

What does this pattern inspect?

  • The pattern asks kubectl to list Pods in the simulated namespace.
  • The -n option scopes the request to banking.
  • You are recording the pattern for the runbook. You remain offline while reviewing its supplied output.
  • Save operations-runbook.md.

Before you inspect the first capture, do you think a Running status proves that the application is ready to serve traffic?

  • Review the supplied captured output for the first command pattern.

You see payments-api-7d9f6b7c8f-2kq4m in the Running state. Its readiness value is 0/1.

That incomplete signal is intentional. It confirms that the container is running without revealing why the application remains unready.

Can't match the first capture?

  • Confirm that you are reading the supplied output for the banking namespace.
  • Check the readiness column for payments-api-7d9f6b7c8f-2kq4m.
  • Keep the command inside the runbook instead of executing it against a live cluster.

Help me interpret this simulated Pod status.

Trace readiness and application evidence

Pod details expose the health-check result plus recent lifecycle evidence. This helps you distinguish a failed dependency from a scheduling problem.

  • Place your cursor below the first command pattern in operations-runbook.md.
  • Add the read-only Pod description pattern by copying this reference line:
kubectl describe pod payments-api-7d9f6b7c8f-2kq4m -n banking

What does this pattern inspect?

  • The pattern targets the affected Pod inside the simulated namespace.
  • Pod details expose container state plus readiness information.
  • Recent events help rule out scheduling or image-pull failures.
  • Save operations-runbook.md.
  • Review the supplied Pod description capture.

The readiness evidence points to a failed dependency. The capture contains no scheduling or image-pull failure.

Readiness evidence unclear?

  • Find the readiness result in the supplied Pod description capture.
  • Compare the dependency failure with the recent event evidence.
  • Keep missing scheduling evidence separate from the confirmed readiness failure.

Help me separate the readiness evidence from unrelated Pod events.

The application log is the next evidence layer. Its timestamps place the reported dependency failure into the incident sequence.

  • Place your cursor below the Pod description pattern.
  • Add the read-only log pattern by copying this reference line:
kubectl logs payments-api-7d9f6b7c8f-2kq4m -n banking --tail=50 --timestamps=true

What does this pattern inspect?

  • The pattern targets logs from the affected Pod.
  • The --tail=50 option limits the capture to recent log lines.
  • The --timestamps=true option includes timestamps for evidence ordering.
  • Save operations-runbook.md.
  • Review the supplied application log capture.

The application reports a database connection timeout. This connects the failed readiness check to a database dependency without proving which boundary caused the timeout.

Can't find the timeout evidence?

  • Confirm that the supplied log capture belongs to payments-api-7d9f6b7c8f-2kq4m.
  • Locate the line that reports the database connection timeout.
  • Avoid copying sensitive SQL or identifiers into the runbook.

Help me connect the readiness failure to the simulated application log.

Your symptom assessment should preserve what each evidence source proves. It should also preserve the limits of that evidence.

  • Return to the Incident Summary section from earlier.
  • Add the symptom assessment by copying this Markdown:
### Symptom Assessment
- Affected Pod: `payments-api-7d9f6b7c8f-2kq4m`.
- Container state: `Running`.
- Readiness: `0/1`.
- Readiness finding: The dependency check failed.
- Application finding: The log reports a database connection timeout.
- Exclusions: No scheduling or image-pull failure is present.

Why record the symptoms this way?

  • The container state records what Kubernetes successfully started.
  • The readiness finding records why the Pod cannot serve normal traffic.
  • The exclusions prevent scheduling or image retrieval from distracting the investigation.
  • Save operations-runbook.md.
  • Compare the assessment with all three supplied captures.

The assessment now forms a complete symptom chain from Pod status to readiness failure to database timeout.

Separate facts from hypotheses

A timeout can fit several explanations. An Amazon RDS outage, a DNS failure, an egress control, an AWS network control, or authentication could each interrupt the path.

None of those explanations is confirmed in this step. Your runbook keeps them separate so later evidence can eliminate each one methodically.

  • Place your cursor under the existing Incident Summary section.
  • Add the affected service plus user impact by copying this Markdown:
### Impact
- Affected service: `payments-api`.
- Technical impact: The affected Pod remains unready because its database connection times out.
- User impact: Traffic that depends on this Pod cannot be served reliably while readiness remains `0/1`.

What does this impact record establish?

  • The service entry names the workload under investigation.
  • The technical impact ties the unavailable dependency to failed readiness.
  • The user impact states the observable service risk without inventing a root cause.
  • Save operations-runbook.md.
  • Reread the impact record for unsupported root-cause claims.

The impact record stays within the evidence. It describes the service risk without claiming that the database itself has failed.

  • Place your cursor under the existing Evidence Timeline section.
  • Add the evidence order by copying this Markdown:
### Captured Evidence Order
1. Pod status shows `payments-api-7d9f6b7c8f-2kq4m` as `Running` with `0/1` ready.
2. Readiness evidence records a failed dependency.
3. Application log evidence records a database connection timeout.

Why preserve the evidence order?

The sequence shows how the investigation moves from a broad workload symptom to a specific dependency error. It also keeps the captured timestamps attached to the evidence source that produced them.

  • Save operations-runbook.md.
  • Compare the recorded order with the supplied capture timestamps.

Your timeline now moves from Pod status to readiness evidence to the application timeout.

  • Place your cursor under the existing Hypotheses section.
  • Add the competing hypotheses by copying this Markdown:
### Competing Hypotheses
- RDS outage. Status: unconfirmed.
- DNS failure. Status: unconfirmed.
- Kubernetes egress-control block. Status: unconfirmed.
- AWS network-control block. Status: unconfirmed.
- Database authentication failure. Status: unconfirmed.

Why keep five hypotheses?

  • Each hypothesis represents a different boundary along the application-to-database path.
  • The unconfirmed status prevents a plausible explanation from becoming a premature root cause.
  • The separate entries make the next evidence review easier to audit.
  • Save operations-runbook.md.
  • Confirm that the Root Cause section remains unchanged.

Before your final review, do you expect every recorded conclusion to be supported by one of the three supplied captures?

  • Review operations-runbook.md from the Incident Summary section through the Hypotheses section.
  • Confirm that all three read-only command patterns appear in the runbook.
  • Confirm that the symptom assessment records Running plus 0/1 readiness.
  • Confirm that the database connection timeout appears as application evidence.
  • Confirm that all five hypotheses remain unconfirmed.

You should see a complete evidence chain with the container running, readiness failing on a dependency, plus a database connection timeout. You should also see separate RDS outage, DNS failure, Kubernetes egress-control, AWS network-control, and authentication hypotheses.

Does a hypothesis look confirmed?

  • Move any unsupported conclusion out of the Root Cause section.
  • Place the unsupported conclusion under Hypotheses.
  • Label its status as unconfirmed.

Help me audit my evidence and hypotheses.

Your first-pass triage now distinguishes application symptoms from possible causes. Next, you will test the infrastructure boundaries until one hypothesis becomes the evidence-backed root cause.

Isolate the EKS-to-RDS Failure Boundary

Your earlier triage proved that the payments-api-7d9f6b7c8f-2kq4m Container is running. Its readiness check still fails on a database connection timeout.

The failure boundary remains unknown. Amazon RDS for PostgreSQL could be unavailable.

DNS or Kubernetes egress could block the path. The inbound AWS network control is another candidate.

This step checks the supplied evidence in sequence. You will eliminate healthy layers before proposing a reversible fix.

In this step, get ready to:
  • Add a read-only RDS inspection pattern to the runbook.
  • Eliminate healthy failure domains using the supplied connectivity evidence.
  • Write an unexecuted remediation proposal with validation criteria and a rollback path.
Inspect the RDS evidence

The AWS CLI instance description provides metadata for one RDS database. These fields separate service availability from the connection path used by the application.

  • Switch back to operations-runbook.md in Visual Studio Code.
  • Find the Evidence Timeline section.
  • Add the read-only RDS command pattern by copying this line:
aws rds describe-db-instances --db-instance-identifier training-rds-postgresql --no-cli-pager

What does this command establish?

  • The describe-db-instances subcommand requests metadata about an RDS DB instance.
  • The --db-instance-identifier argument scopes the request to training-rds-postgresql.
  • The --no-cli-pager argument keeps the response in the terminal output.
  • In this simulation, the command remains a reference pattern. You inspect the supplied response without connecting to AWS.

Which response fields matter?

  • Availability evidence comes from DBInstanceStatus.
  • Connection details come from Endpoint and PubliclyAccessible.
  • Network attachment evidence comes from VpcSecurityGroups.
  • Recovery posture comes from BackupRetentionPeriod, LatestRestorableTime, and MultiAZ.
  • Monitoring context comes from DatabaseInsightsMode.
  • Record every inspection field from the checklist above beneath the command pattern.
  • Save operations-runbook.md.
  • Confirm that the command pattern and all eight field names are visible in the runbook.

You should now see a repeatable RDS inspection pattern beside the exact fields an operator needs to review.

Missing a field from your notes?

Compare your field list with the supplied RDS response. Keep each field name exactly as it appears in the evidence.

Use the editor search field to locate any missing name before continuing.

Help me check the RDS inspection fields in my operations runbook.

Trace the connectivity path

Connectivity evidence becomes useful when you inspect each boundary in order. Every healthy layer removes one competing hypothesis from the incident.

  • In Hypotheses, mark the RDS outage hypothesis as unsupported because DBInstanceStatus is available.
  • Mark the DNS failure hypothesis as unsupported because the private endpoint resolves.
  • Compare both conclusions with the supplied RDS evidence.

The database service is available. Its private endpoint also resolves, so the evidence moves the investigation deeper into the connection path.

  • Mark the Kubernetes egress-control hypothesis as unsupported because the policy permits the destination.
  • In Evidence Timeline, record that the configured database port is 5432.
  • Trace the recorded path from the resolved private endpoint through permitted Kubernetes egress.

Your evidence now reaches the AWS inbound boundary. The unresolved question is whether the database accepts the workload source on the configured port.

  • Compare the source in VpcSecurityGroups with the workload's current source security group.
  • In Root Cause, record that the RDS security group authorizes an obsolete source security group.
  • Record that the workload uses a different current source security group.
  • Save operations-runbook.md.

You should see one evidence chain that starts with an available database. It ends at a mismatched VPC security group source.

Why does the security group close the case?

An RDS security group must permit the intended source on the database port. The supplied rule authorizes a source that the workload no longer uses.

The database is available. Name resolution works. Kubernetes egress also permits the destination.

Those confirmed facts leave the obsolete inbound source as the connectivity root cause.

Write the reversible change proposal

Security-group changes can affect a critical network boundary. This project keeps the proposal unexecuted so the approval point stays unmistakable.

A controlled proposal states the intended change before defining success. It also names the exact condition that triggers rollback.

  • In Remediation, record a proposal to authorize the intended workload source on port 5432.
  • Add a separate statement that preserves private accessibility.
  • Review the proposal to confirm that it requires approval before execution.

The remediation now changes only the mismatched inbound source. Private database access remains part of the design.

  • In Validation, record that Pod readiness must recover.
  • Add database connection recovery as a separate success criterion.
  • Review both criteria against the original incident symptoms.

The validation criteria now test the exact failure that users experienced. A successful rule change must restore readiness plus database connectivity.

  • Add error-rate monitoring to the Validation section.
  • In Rollback, record that the proposed security-group rule must be rolled back if validation fails.
  • Review the rollback path to confirm that it reverses only the proposed rule.

The proposal now pairs service recovery with error-rate monitoring. Its rollback path is limited to the unvalidated security-group change.

  • Mark the proposal as unexecuted and subject to organizational change control.
  • Save operations-runbook.md.

Before you review the finished runbook, which hypothesis do you expect the complete evidence chain to support?

  • Review the captured RDS response against the inspection fields in the runbook.
  • Confirm that Root Cause names the obsolete source security group as the blocked boundary.
  • Confirm that Remediation preserves private accessibility.
  • Confirm that Validation covers readiness recovery, connection recovery, and error-rate monitoring.
  • Confirm that Rollback reverses the proposed security-group rule if validation fails.

Your runbook should identify the mismatched inbound source as the root cause. It should also contain a reversible proposal that remains unexecuted until approval.

That is the failure boundary isolated: your runbook moves from an application timeout to a specific AWS network-control mismatch without touching production. Next, you will turn the same evidence-first method into a proactive RDS monitoring baseline.

Build an RDS Monitoring Baseline

Your runbook already traces the outage to an obsolete security-group source. It also protects production with an unexecuted remediation proposal.

Fixing one outage only explains what happened once. A historical monitoring baseline for Amazon RDS for PostgreSQL helps you spot connection pressure through Amazon CloudWatch metrics before the banking service is affected.

In this step, get ready to:
  • Group the supplied RDS metrics into operational categories.
  • Correlate the simulated 24-hour trend against the workload's historical baseline.
  • Extend the matrix with CloudWatch Database Insights signals plus recovery-readiness evidence.
Structure the monitoring matrix

Amazon RDS publishes instance metrics in the AWS/RDS namespace. Grouping these signals by operational purpose keeps the runbook focused on the type of pressure each signal can reveal.

  • Switch back to operations-runbook.md in VS Code.
  • Locate the Proactive Monitoring section from earlier.
  • Create a monitoring matrix with columns named Signal, Baseline, Deviation, Evidence, Owner, Safe Next Action, plus Change Approval Required.

You should now see one signal column plus six columns that turn each observation into an operational decision.

  • Add compute and memory rows for CPUUtilization, FreeableMemory, plus SwapUsage.
  • Add a connections row for DatabaseConnections.

The first four rows now connect compute pressure with memory availability and connection demand.

  • Add storage and I/O rows for FreeStorageSpace, ReadIOPS, WriteIOPS, ReadLatency, WriteLatency, plus DiskQueueDepth.
  • Add PostgreSQL growth and replication rows for MaximumUsedTransactionIDs, ReplicationSlotDiskUsage, plus ReplicaLag.

Your matrix should now contain 13 infrastructure and database-health signals from the supplied evidence.

What does each column prove?

  • The Baseline column captures the workload's normal historical range or expected state.
  • The Deviation column records movement away from that normal state.
  • The Evidence column points to the supplied metric or inspection record.
  • The Owner column identifies the role responsible for investigating the signal.
  • The Safe Next Action column keeps the first response read-only.
  • The Change Approval Required column makes the boundary before a mutation explicit.

Why use a historical baseline?

Acceptable performance values depend on the workload. Consistent deviations from the banking service's own history provide stronger evidence than a universal threshold.

A baseline gives every signal context. That context keeps normal workload peaks from becoming false incidents.

  • Populate each Baseline cell from the historical ranges or expected states in the supplied evidence.
  • Describe each Deviation cell relative to that historical baseline.

The matrix now uses workload history as its reference point without inventing universal alarm thresholds.

Correlate the 24-hour trend

A single metric can suggest several causes. Correlation shows which signals move together during the same period.

  • Review the supplied 24-hour evidence in timestamp order.
  • Record that CPUUtilization rises with DatabaseConnections.
  • Record that FreeableMemory declines during the same period.

These three signals move together during the simulated pressure window.

  • Compare FreeStorageSpace with its historical baseline.
  • Record that storage remains healthy.
  • Compare ReadLatency with the CPU trend.
  • Record that read latency does not rise proportionally.

Healthy storage plus comparatively stable read latency makes storage capacity and read I/O weaker explanations for this trend.

  • Classify the simulated pressure as primarily connection and compute-related.
  • Flag memory pressure for continued observation.

That correlation is the key operational win. Your runbook now separates the leading pressure pattern from healthy storage and stable read latency.

Add load and recovery signals

CloudWatch Database Insights adds database-level context through DB Load and Top SQL. The existing RDS inspection evidence adds instance status plus recovery posture.

  • Add a DB Load row for database demand.
  • Add a Top SQL row for the statements contributing to that demand.

The matrix now connects instance pressure with the database workload that may be driving it.

  • Add recovery-readiness rows for DBInstanceStatus, BackupRetentionPeriod, LatestRestorableTime, plus MultiAZ.
  • Set the expected DBInstanceStatus baseline to available.
  • Record the supplied BackupRetentionPeriod as recovery evidence.
  • Record the supplied LatestRestorableTime as point-in-time recovery evidence.
  • Record the supplied MultiAZ value as availability-posture evidence.

You should now see 19 signal rows covering compute, memory, connections, storage, I/O, database load, query contribution, instance health, plus recovery posture.

  • Assign the platform owner to instance-health, capacity, storage, and I/O rows.
  • Assign the database owner to DB Load plus Top SQL rows.

Every row should now identify who investigates the evidence before a production change is considered.

  • Use Safe Next Action to record a read-only follow-up for every deviation.
  • Mark Change Approval Required for every action that would mutate infrastructure or database configuration.

Before you check the finished matrix, which operational domain do you expect the correlation to identify as the leading source of pressure?

  • Scan the Signal column to confirm that all 19 named signals are present.
  • Review the six operational columns to confirm that every signal has decision context.
  • Review the monitoring correlation to confirm that it uses the workload's historical baseline.
  • Confirm that the conclusion identifies primarily connection and compute-related pressure.
  • Confirm that the conclusion keeps memory pressure under observation.

You should see a complete matrix that rules out storage as the leading concern and avoids a universal alarm threshold. The evidence points to connection and compute pressure while preserving memory as a signal to watch.

Missing a monitoring row?

Compare the signal column with the grouped metric lists above. Check that DB Load plus Top SQL appear as separate rows.

Check that the recovery group includes instance status, backup retention, latest restorable time, and Multi-AZ posture.

Audit my RDS monitoring matrix for missing signals.

Your runbook now turns RDS evidence into a proactive operating baseline. Next, you will connect that resource pressure to query statistics and an execution-plan finding.

Propose a SQL Optimization

Your Amazon RDS baseline has narrowed the simulated pressure to connections plus compute. The next question is whether a specific SQL workload helps explain that pressure.

Infrastructure metrics show where pressure appears. CloudWatch Database Insights plus PostgreSQL 18 query statistics show which workload deserves review.

Every conclusion remains inside the supplied evidence bundle. Your runbook will propose a controlled optimization without executing a database change.

In this step, get ready to:
  • Trace the resource symptom to Top SQL evidence.
  • Distinguish cumulative query load from individual-call latency.
  • Propose a controlled index validation plan.
Connect resource pressure to query evidence

Top SQL identifies workloads contributing to DB Load. pg_stat_statements adds execution-count evidence plus timing evidence.

The buffer fields add another view of resource demand. Together, these signals separate cumulative workload pressure from one unusually slow call.

  • Return to the SQL Optimization section in operations-runbook.md.
  • Review the supplied Database Insights Top SQL evidence.
  • Add an evidence-chain note linking the connection plus compute symptom to the identified Top SQL workload.
  • Save operations-runbook.md.

You should see the infrastructure symptom connected to a specific query workload in your runbook.

How Do the Statistics Fit Together?

  • Use calls to assess execution frequency.
  • Use total_exec_time to assess cumulative execution time.
  • Use mean_exec_time to assess the average time per call.
  • Use shared_blks_hit to assess blocks found in shared buffers.
  • Use shared_blks_read to assess block-read activity.
  • Write an assessment that classifies the supplied query as a high-cumulative-load candidate driven by repeated calls.
  • Save operations-runbook.md.

Your assessment should identify execution frequency as the cumulative-load driver. It should keep individual-call latency as a separate diagnosis.

Read the supplied execution plans

An execution plan shows how PostgreSQL expects to retrieve the requested rows. The supplied plans let you compare the current access path with the candidate post-change path.

  • Locate the supplied plain-plan statement by matching this reference:
EXPLAIN SELECT account_id, posted_at, amount FROM transactions WHERE customer_id = 48271 ORDER BY posted_at DESC LIMIT 20;

What Does This Plan Check?

  • Plain EXPLAIN displays the planner's execution plan without executing the statement.
  • The supplied Seq Scan indicates a sequential scan of the table.
  • The supplied Sort handles the requested descending order.
  • The supplied post-change Index Scan shows the candidate indexed access path.
  • EXPLAIN ANALYZE executes the statement. That behavior requires approval plus controlled testing on a critical workload.
  • Add a plan comparison documenting the plain query plus the supplied Seq Scan, Sort, plus post-change Index Scan.
  • Save operations-runbook.md.

You should see both supplied plan shapes recorded beside the exact query. The comparison now connects the workload pattern to a candidate access-path improvement.

  • Add a guardrail prohibiting EXPLAIN ANALYZE on a critical workload without approval plus controlled testing.
  • Save operations-runbook.md.

The SQL Optimization section should now distinguish the safe plain plan from the execution behavior of EXPLAIN ANALYZE.

Plan Comparison Still Unclear?

Check that the current plan contains both Seq Scan plus Sort. Check that the supplied post-change plan contains Index Scan.

Ask for help connecting the nodes to the proposed access path: Help me compare the supplied PostgreSQL plans.

Define the controlled optimization proposal

A composite index can support filtering by customer_id plus ordering by posted_at. It can also increase write work plus storage use.

Your deliverable is an approval-gated proposal. The evidence must define success before any DDL is considered.

  • Add an unexecuted proposal for a composite index on customer_id plus posted_at.
  • Save operations-runbook.md.

You should see the index recorded as a candidate only. No production DDL should appear in the runbook.

  • Add a non-production validation checklist covering plan shape, latency, rows, buffer use, write overhead, storage growth, plus service-load reduction.
  • Save operations-runbook.md.

The proposal should now define the evidence required to accept the candidate index.

  • Add rollback criteria for increased write cost without a reduction in service load.
  • Save operations-runbook.md.

Before you review the finished section, do you expect every link in the evidence chain to support the candidate index?

  • Review the completed SQL Optimization section from the resource symptom through rollback.
  • Confirm that every mutation remains behind approval plus non-production validation.

You should see the connection plus compute symptom linked to Top SQL. The chain should continue through query statistics, plan nodes, the candidate index, validation criteria, plus rollback.

That is a strong operator outcome. You have turned a broad resource signal into a testable optimization proposal without experimenting on a critical system.

Secret mission

Catch an Idle-in-Transaction Risk

A quiet database session can outlive the work that opened it. In this mission, you will catch supplied idle-in-transaction evidence before it becomes a locking, bloat, or autovacuum problem. You will add an approval-gated response to your runbook without touching production.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your local work, pause until later, or remove it entirely. This drill stayed offline and created no cloud resources, so there are no ongoing costs.

Resources you used:

  • Your local operations-runbook.md file in the Visual Studio Code workspace.

Keep everything running

No action needed. Choose this if you want to keep your incident evidence and operational decisions available for future practice.

  • Keep operations-runbook.md in the workspace from earlier.
  • Use the runbook as a reference for evidence-first triage and controlled remediation.
  • Return to the monitoring matrix when you want to practise interpreting another simulated trend.

Pause - I'll come back to this later

There are no cloud or database processes to shut down because the drill stayed offline. Pausing only means preserving the runbook and closing the editor.

  • Save operations-runbook.md.
  • Close Visual Studio Code when you are ready to pause.
  • Return to the same workspace when you want to continue reviewing the simulated evidence.

Delete - I don't want to use this again

Deleting your runbook removes the only project artifact you created. That makes this the one cleanup choice worth checking twice.

Keep What Matters

The runbook holds your incident reasoning and approval boundaries. Keep it if you want a reusable example of safe infrastructure operations.

  • Select operations-runbook.md in the explorer sidebar of the workspace from earlier.
  • Press the Delete key to remove the file.
  • Confirm the deletion if Windows asks.
  • Remove any recoverable copy from the Windows recycle bin if you want the file removed completely.
  • Confirm that operations-runbook.md no longer appears in the explorer sidebar.

Nice Work!

Nice Work!

You did it! You built an evidence-first operations runbook that traces a simulated Amazon EKS service failure to an Amazon RDS for PostgreSQL network boundary without touching production.

You've learned how to:

  • Apply evidence-first triage to trace failed Pod readiness through application logs to a mismatched RDS security-group source.
  • Build an RDS operational baseline across compute, memory, connections, storage, I/O, backups, and recovery readiness using Amazon CloudWatch evidence.
  • Connect DB Load plus Top SQL from CloudWatch Database Insights to pg_stat_statements plus execution-plan evidence. Turn that chain into a composite-index proposal with non-production validation plus rollback criteria.
  • Secret Mission: Detect idle in transaction sessions in a supplied pg_stat_activity snapshot. Compare their age with the organization's baseline to assess lock, bloat, plus autovacuum risk. Place the application owner plus approval boundary before any session termination or timeout change.

Ready to quiz yourself?