# PostgreSQL reference application benchmark

This study measures a disclosed synthetic HTTP application backed by PostgreSQL. It is first-party evidence about this exact reference workload, not a universal PostgreSQL capacity claim and not a comparison with another control panel.

## What it measures

- HTTP throughput, p50/p95/p99 latency, and errors at fixed concurrency.
- A 70% indexed account-read, 20% regional aggregate, and 10% transactional-write mix.
- PostgreSQL connection count with one persistent PDO connection per PHP worker and no external pooler.
- Combined PostgreSQL and PHP CPU time, working set, and Windows process I/O transfer counters.
- A deterministic 100,000-account and 500,000-event starting dataset.

## Requirements

- Windows with PowerShell.
- PostgreSQL 18 installed at `C:\Program Files\PostgreSQL\18`.
- PHP 8.3+ with `pdo_pgsql`.
- Node.js 20+.

The runner does not use or modify the machine's installed PostgreSQL service. It creates a temporary loopback-only cluster, loads the deterministic dataset, starts eight PHP development-server workers, runs one warm-up plus five measured samples, and then shuts down and removes only that validated temporary cluster directory.

## Reproduce

From the repository root:

```powershell
powershell -ExecutionPolicy Bypass -File .\benchmarks\postgresql-reference\run.ps1
```

The default evidence directory is intentionally immutable: the runner refuses to overwrite it. Supply a new directory name beneath `public/benchmarks/` for a new study date or environment.

## Output contract

- `environment.json`: host, software, database, dataset, workload, and observer metadata.
- `run-N.json`: request-level operation, response status, latency, and run aggregate.
- `resources-N.json`: 500 ms resource and database-connection samples.
- `summary.json` and `summary.csv`: medians, ranges, errors, and per-run values.
- `postgresql.log`, application logs, and `run.log`: retained execution logs.
- `schema.sql`: exact dataset definition copied beside the results.
- `verify.mjs`: independently recomputes request counts and percentiles and verifies every published checksum.

## Important limitations

- The PHP built-in server is a development server, not Nginx, Apache, PHP-FPM, Node.js, or a production Core Panel deployment.
- Client, application, and database run on one host, so network latency is nearly absent and load generation competes for the same CPU.
- The dataset and query mix are synthetic. They do not model every application, schema, cache, index, write ratio, or contention pattern.
- PDO persistent connections are process-local reuse, not a queueing pool such as PgBouncer.
- Windows process I/O transfer counters include cached and non-disk transfers; they are not physical-device throughput.
- Warm-cache steady state is measured after a disclosed warm-up. Cold starts, failover, backups, replication, and restore behavior are excluded.
- A single developer-machine run should be independently reproduced on a controlled Linux server before being used for cross-product or production-sizing claims.