Phase Project: Tune & Secure a Transactional System

What you'll learn

Diagnose a realistic workload, add evidence-based indexes, repair transaction races, and implement least-privilege access. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Workload analysis to a realistic data question
Apply Execution plans to a realistic data question
Apply Index design to a realistic data question
Apply Concurrency tests to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Workload	The actual mix and frequency of queries and changes	Tune important production-shaped operations, not isolated syntax
Invariant race	A rule violated only by overlapping transactions	Reproduce with coordinated sessions before selecting isolation or locks
Privilege matrix	Roles crossed with allowed operations	Generate grants and tests from a reviewed least-privilege design

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the secure transactional workload question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Capture tuning evidence

A small catalog connects each critical query to its target, plan artifact, index, and before/after result.

tuning_evidence.sql

CREATE TABLE engineering.query_evidence (
  query_key text PRIMARY KEY,
  purpose text NOT NULL,
  target_p95_ms numeric NOT NULL,
  before_plan_path text NOT NULL,
  after_plan_path text NOT NULL,
  migration_version text NOT NULL,
  verified_at timestamptz NOT NULL DEFAULT now()
);

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A faster query is not a successful phase project if the new index harms writes, the race remains, or the application role still has excessive authority.

Independent workshop

Repair, tune, migrate, and secure a deliberately flawed order-processing database with before/after evidence.

Your finished workshop must include:

Workload analysis
Execution plans
Index design
Concurrency tests
Security roles
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Workload: Tune important production-shaped operations, not isolated syntax
Invariant race: Reproduce with coordinated sessions before selecting isolation or locks
Privilege matrix: Generate grants and tests from a reviewed least-privilege design

Quick check

1. Which rule best applies to Workload?

2. Which rule best applies to Invariant race?

3. Which rule best applies to Privilege matrix?

Next: Stored Functions, Procedures & Triggers