13주차 코드 10항목 — 같은 조회를 인덱스 전후로 재고 증거로 남기기

services:
  db:
    image: postgres:17.10-alpine
    ports:
      - "${FCL_DB_PORT:-5432}:5432"

    environment:
      POSTGRES_DB: financial_core
      POSTGRES_USER: app
      POSTGRES_PASSWORD: ${FCL_DB_PASSWORD:?set FCL_DB_PASSWORD in this terminal}

    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d financial_core"]
      interval: 2s
      timeout: 2s
      retries: 30

    volumes:
      - financial-core-db:/var/lib/postgresql/data

volumes:
  financial-core-db:

\set ON_ERROR_STOP on

DROP SCHEMA IF EXISTS perf_w13 CASCADE;
CREATE SCHEMA perf_w13;

CREATE TABLE perf_w13.account(id bigint PRIMARY KEY,owner_id varchar(64) NOT NULL,balance bigint NOT NULL CHECK(balance>=0));

CREATE TABLE perf_w13.business_tx(id uuid PRIMARY KEY,status varchar(16) NOT NULL);

CREATE TABLE perf_w13.ledger_entry(id bigint PRIMARY KEY,business_tx_id uuid NOT NULL REFERENCES perf_w13.business_tx(id),account_id bigint NOT NULL REFERENCES perf_w13.account(id),entry_type varchar(32) NOT NULL,signed_amount bigint NOT NULL,created_at timestamptz NOT NULL);

INSERT INTO perf_w13.account SELECT g,'perf-owner-'||g,0 FROM generate_series(1,1000) g;

INSERT INTO perf_w13.business_tx SELECT md5('perf-'||g)::uuid,'COMPLETED' FROM generate_series(1,100000) g;

INSERT INTO perf_w13.ledger_entry
SELECT g,md5('perf-'||g)::uuid,CASE WHEN g<=90000 THEN 1 ELSE 2+((g-90001)%999) END,'PERF_CREDIT',1,timestamptz '2026-01-01 00:00:00+00'+g*interval '1 second'
FROM generate_series(1,100000) g;

UPDATE perf_w13.account a SET balance=x.n FROM (SELECT account_id,sum(signed_amount)::bigint n FROM perf_w13.ledger_entry GROUP BY account_id)x WHERE x.account_id=a.id;

ANALYZE perf_w13.account; ANALYZE perf_w13.business_tx; ANALYZE perf_w13.ledger_entry;

EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON)
SELECT id,entry_type,signed_amount,created_at FROM perf_w13.ledger_entry
WHERE account_id=1 ORDER BY created_at DESC,id DESC LIMIT 50;

CREATE INDEX idx_ledger_account_created_id ON perf_w13.ledger_entry(account_id,created_at DESC,id DESC);

ANALYZE perf_w13.ledger_entry;

DO $gate$ DECLARE a bigint;t bigint;l bigint;h bigint;m bigint; BEGIN
 SELECT count(*) INTO a FROM perf_w13.account; SELECT count(*) INTO t FROM perf_w13.business_tx; SELECT count(*) INTO l FROM perf_w13.ledger_entry; SELECT count(*) INTO h FROM perf_w13.ledger_entry WHERE account_id=1;
 SELECT count(*) INTO m FROM perf_w13.account x LEFT JOIN(SELECT account_id,sum(signed_amount) b FROM perf_w13.ledger_entry GROUP BY account_id)y ON y.account_id=x.id WHERE x.balance<>coalesce(y.b,0);
 IF a<>1000 OR t<>100000 OR l<>100000 OR h<>90000 OR m<>0 THEN RAISE EXCEPTION 'W13 counts a=% t=% l=% h=% m=%',a,t,l,h,m; END IF;
 IF (SELECT count(*) FROM pg_indexes WHERE schemaname='perf_w13' AND indexname='idx_ledger_account_created_id')<>1 THEN RAISE EXCEPTION 'W13 index missing'; END IF;
END $gate$;

SELECT 'W13_ASSERT 1000|100000|100000|90000|0';

param(
 [Parameter(Mandatory=$true)][ValidateSet('Prepare','BeforePlan','AfterPlan','Selectivity','Benchmark','Report')][string]$Phase,
 [ValidateSet('Compose','Container')][string]$Mode='Compose',
 [string]$Container='',
 [string]$ComposeProject='w13-perf-lab',
 [Parameter(Mandatory=$true)][string]$EvidenceDir
)
$ErrorActionPreference='Stop'
$root=Split-Path -Parent $PSScriptRoot
if(-not [IO.Path]::IsPathRooted($EvidenceDir)){
 throw 'EvidenceDir must be an absolute learner-owned path'
}
$e=[IO.Path]::GetFullPath($EvidenceDir)
New-Item -ItemType Directory -Force $e|Out-Null
$compose=Join-Path $root 'compose.yaml'
$owned=$false
$dbPhases=@('BeforePlan','AfterPlan','Selectivity','Benchmark')

function Write-JsonAtomic([string]$Name,$Value){
 $target=Join-Path $e $Name;$temporary="$target.tmp"
 $Value|ConvertTo-Json -Depth 6|Set-Content -Encoding utf8 $temporary
 Move-Item -LiteralPath $temporary -Destination $target -Force
}

function PsqlFile([string]$Relative){
 Get-Content -Raw -LiteralPath (Join-Path $root $Relative)|docker exec -i $Container psql -X -v ON_ERROR_STOP=1 -U app -d financial_core
 if($LASTEXITCODE-ne0){throw "psql file failed: $Relative exit=$LASTEXITCODE"}
}

function MeasurePlan([string]$Variant,[string]$Output){
 $rows=@()
 for($sample=1;$sample-le30;$sample++){
  $json=Get-Content -Raw (Join-Path $root 'sql/w13/measure.sql')|docker exec -i $Container psql -X -qAt -v ON_ERROR_STOP=1 -U app -d financial_core
  if($LASTEXITCODE-ne0){throw "measure failed: phase=$Variant sample=$sample exit=$LASTEXITCODE"}
  $object=$json|ConvertFrom-Json
  $rows+=[pscustomobject]@{variant=$Variant;sample=$sample;ms=[double]$object[0].'Execution Time'}
 }
 $rows|Export-Csv -NoTypeInformation -Encoding utf8 -LiteralPath $Output
 return $rows
}

function Quantile($Rows,[double]$P){
 $values=@($Rows.ms|Sort-Object)
 if($values.Count-ne30){throw "quantile requires 30 values, got $($values.Count)"}
 return $values[[Math]::Min(29,[Math]::Ceiling(30*$P)-1)]
}

function FindIndex($Node){
 if($Node.'Index Name'){return [pscustomobject]@{name=[string]$Node.'Index Name';type=[string]$Node.'Node Type'}}
 foreach($child in @($Node.Plans)){$found=FindIndex $child;if($found){return $found}}
 return $null
}

function SourceHashes(){
 $files=@('scripts/run-w13-perf.ps1','sql/w13/seed.sql','sql/w13/measure.sql','sql/w13/create-index.sql','sql/w13/assert.sql')
 return @($files|ForEach-Object{[pscustomobject]@{file=$_;sha256=(Get-FileHash -Algorithm SHA256 (Join-Path $root $_)).Hash.ToLowerInvariant()}})
}

if($Phase-eq'Prepare'){
 if(!(Test-Path -LiteralPath $compose -PathType Leaf)){throw 'packaged compose.yaml missing'}
 $futureFiles=@('before-day.json','before-plan.txt','after-day.json','after-plan.txt','selectivity-day.json','benchmark-manifest.json','before-raw.csv','after-raw.csv','benchmark-after-plan.txt','perf-manifest.json')
 $stale=@($futureFiles|Where-Object{Test-Path -LiteralPath (Join-Path $e $_) -PathType Leaf})
 if($stale.Count-ne0){throw "stale W13 D2-D6 artifact exists before Prepare: $($stale -join ',')"}
 if([string]::IsNullOrWhiteSpace($env:FCL_DB_PASSWORD)){$env:FCL_DB_PASSWORD='w13-prepare-config-only'}
 $docker=& docker version --format '{{.Server.Version}}'
 if($LASTEXITCODE-ne0-or[string]::IsNullOrWhiteSpace($docker)){throw 'Docker engine is unavailable'}
 $services=@(& docker compose -f $compose config --services)
 if($LASTEXITCODE-ne0-or$services.Count-ne1-or$services[0]-cne'db'){throw "compose service contract mismatch: $services"}
 $futureCount=@($futureFiles|Where-Object{Test-Path -LiteralPath (Join-Path $e $_) -PathType Leaf}).Count
 if($futureCount-ne0){throw "Prepare created a future W13 artifact: count=$futureCount"}
 $body=[ordered]@{phase='Prepare';docker_server=[string]$docker;compose_services=@($services);hashes=SourceHashes;native_exit=0;future_artifacts_created=$futureCount}
 Write-JsonAtomic 'prepare.json' $body
 'W13_PREPARE_GREEN compose=db hashes=5 future_artifacts=0 native_exit=0'
 exit 0
}

if($Phase-eq'Report'){
 $prepare=Get-Content -Raw (Join-Path $e 'prepare.json')|ConvertFrom-Json
 $before=Get-Content -Raw (Join-Path $e 'before-day.json')|ConvertFrom-Json
 $after=Get-Content -Raw (Join-Path $e 'after-day.json')|ConvertFrom-Json
 $selectivity=Get-Content -Raw (Join-Path $e 'selectivity-day.json')|ConvertFrom-Json
 $benchmark=Get-Content -Raw (Join-Path $e 'benchmark-manifest.json')|ConvertFrom-Json
 if($prepare.future_artifacts_created-ne0-or$before.cleanup-ne1-or$after.cleanup-ne1-or$selectivity.cleanup-ne1-or$benchmark.cleanup-ne1){throw 'W13 predecessor phase/cleanup contract invalid'}
 if($benchmark.before_rows-ne30-or$benchmark.after_rows-ne30-or$benchmark.chosen_index-cne'idx_ledger_account_created_id'){throw 'W13 benchmark contract invalid'}
 $body=[ordered]@{
  schema='perf_w13';query_kind='top-N';accounts=$selectivity.accounts;transactions=$selectivity.transactions;ledger=$selectivity.ledger;hot=$selectivity.hot
  before_rows=$benchmark.before_rows;after_rows=$benchmark.after_rows
  before_median_ms=$benchmark.before_median_ms;before_p95_ms=$benchmark.before_p95_ms;after_median_ms=$benchmark.after_median_ms;after_p95_ms=$benchmark.after_p95_ms
  chosen_index=$benchmark.chosen_index;plan_node=$benchmark.plan_node
  verdict='relative improvement observed on this deterministic lab; no absolute latency claim';native_exit=0;cleanup=1;hashes=@($prepare.hashes)
  phase_evidence=@('prepare.json','before-day.json','after-day.json','selectivity-day.json','benchmark-manifest.json')
 }
 Write-JsonAtomic 'perf-manifest.json' $body
 "W13_REPORT_GREEN phases=5 before=30 after=30 index=$($body.chosen_index) hashes=5 cleanup=1 native_exit=0"
 exit 0
}

if($Phase-notin$dbPhases){throw "unsupported W13 phase=$Phase"}
$body=$null
try{
 if($Mode-eq'Compose'){
  if($Container){throw 'Compose mode rejects -Container'}
  if([string]::IsNullOrWhiteSpace($env:FCL_DB_PASSWORD)){$env:FCL_DB_PASSWORD='w13-disposable-password'}
  # The unique Compose project is ours before startup; a partial `up`
  # failure must still be cleaned by finally.
  $owned=$true
  & docker compose -f $compose -p $ComposeProject up -d --wait db
  if($LASTEXITCODE-ne0){throw "compose up failed: phase=$Phase exit=$LASTEXITCODE"}
  $Container=(& docker compose -f $compose -p $ComposeProject ps -q db).Trim()
 }elseif([string]::IsNullOrWhiteSpace($Container)){throw 'Container mode requires -Container'}
 if([string]::IsNullOrWhiteSpace($Container)){throw 'PostgreSQL container resolution failed'}
 PsqlFile 'sql/w13/seed.sql'|Out-Null