query-api

Java REST API where the read path is the study. Spring Boot 3 + JDBC + Postgres 16, with hand-tuned queries, EXPLAIN-verified plans, a CI gate that fails on plan regressions, and a virtual-threads-vs-Tomcat-pool comparison run on local hardware.

Real load numbers

Single M-series MacBook Pro, Postgres 16 in Colima, app on the host JVM (Java 21, JIT warmed). Seed: 500 products / 2000 orders / 8000 line items (V4__seed_small.sql). Workload mix: 60% list, 25% detail, 10% recent orders, 5% top-sellers.

Mode	Target	Achieved rps	P50	P95	Errors
Smoke (CI gate)	200 rps	200	2.2 ms	13.6 ms	0
VT ON (Tomcat 200, virtual threads enabled)	1500 rps	1426	152.7 ms	921.9 ms	0
VT OFF (Tomcat 200, classic pool)	1500 rps	1447	45.7 ms	686.4 ms	0

The 2k-rps target was the design goal; the seed-data + single-host run hit ~1450 rps with the planner and connection pool both saturated. Honest take: production would need either the larger seed (full 100k product / 1M order set in scripts/seed.py), Postgres tuning, or horizontal scaling to reach 2k sustained — this repo documents the queries needed to support it, not the cluster topology to deploy it. Raw outputs in bench/results/.

Virtual threads on this workload didn't win. They're useful when many requests are I/O-bound and would otherwise pin platform threads — at this scale, with HikariCP capping concurrent DB calls anyway, the classic Tomcat pool stays cheaper. See ARCHITECTURE.md § "Virtual threads vs Tomcat pool".

What this studies

• Query optimization as the central skill. Every endpoint ships with a committed EXPLAIN (ANALYZE, BUFFERS) plan in docs/explain/ and a query-count assertion in tests. The CI job explain-check regenerates and re-asserts plans on every PR — Seq Scans over a >1000-row table fail the build.
• N+1 elimination via fan-in queries. /api/orders/{userId}/recent is the canonical trap: naive code does 1 + N + N*M queries. This repo does it in two, asserted by QueryCountIntegrationTest.
• Index strategy. Composite (category_id, price_cents, id) for the filter+sort+keyset path; a partial index on inventory.in_stock=TRUE; descending (user_id, ordered_at DESC, id) for recent-orders.
• Materialized denormalization with documented trade-off. top_sellers_7d materialized view; refresh cost vs request-time aggregation cost discussed in docs/query-optimization.md.
• Connection-pool sizing under load. HikariCP capped at 30 against Postgres 16 with max_connections=200. Pool exhaustion behavior captured in the load runs.
• Virtual threads vs Tomcat pool. Same workload, two switches, the numbers above are what was measured.

Module table

Path	Purpose
src/main/java/com/say5/queryapi/QueryApiApplication.java	Spring Boot entrypoint.
src/main/java/com/say5/queryapi/controller/ProductController.java	Six REST endpoints, validation, error envelope.
src/main/java/com/say5/queryapi/repository/ProductRepository.java	All hand-tuned queries; comments explain the EXPLAIN-driven choices.
src/main/java/com/say5/queryapi/middleware/RequestIdFilter.java	X-Request-ID propagation + MDC.
src/main/java/com/say5/queryapi/error/	Structured error envelope, @ControllerAdvice.
src/main/resources/db/migration/V1__schema.sql	Seven-table read schema.
src/main/resources/db/migration/V2__indexes.sql	Strategic index set; each index annotated with the query that justifies it.
src/main/resources/db/migration/V3__matview.sql	top_sellers_7d materialized view + concurrent-refresh index.
src/main/resources/db/migration/V4__seed_small.sql	Deterministic seed (CI + local).
src/test/java/com/say5/queryapi/QueryCountIntegrationTest.java	The N+1 defense — asserts exact SQL execution counts per endpoint.
src/test/java/com/say5/queryapi/ExplainDumpIntegrationTest.java	Regenerates + asserts EXPLAIN plans (docs/explain/*.json).
src/test/java/com/say5/queryapi/EndpointsIntegrationTest.java	One full HTTP test per endpoint (200 / 404 / 422 / cursor round-trip).
scripts/seed.py	Full ~100k-product / 1M-order seed for local 2k-rps target.
scripts/load.k6.js, scripts/load_compare.k6.js, scripts/load.sh	Smoke & comparison load tests.
bench/results/	Committed real load numbers.
docs/query-optimization.md	Per-endpoint query rationale.
docs/explain/*.json	Committed plan baselines (the regression bar).
ARCHITECTURE.md	Concurrency, materialized-view trade-off, "what's not here".

Quickstart

make up          # docker-compose: postgres + app
curl http://localhost:8080/api/healthz
make load-smoke  # 200rps x 30s smoke load against the running compose

Local development (without containers, against a local Postgres):

JAVA_HOME=$(/usr/libexec/java_home -v 21) mvn -B verify
# or
make compile test it-test

The it-test target spawns a testcontainers Postgres so it's hermetic — no docker-compose needed for the test suite.

Architecture (one diagram)

   client
     │  HTTP (X-Request-ID propagated)
     ▼
 ┌───────────────────────────────────┐
 │ Spring Boot 3.3 (Tomcat)          │
 │  ├ RequestIdFilter (MDC)          │
 │  ├ ProductController              │
 │  └ GlobalExceptionHandler         │
 └─────────────┬─────────────────────┘
               │ JdbcTemplate
               ▼
        HikariCP (max=30)
               │
               ▼
       ┌─────────────────┐
       │ Postgres 16     │
       │  ├ products     │   indexes:
       │  ├ inventory    │   - (category_id, price, id)
       │  ├ orders       │   - partial WHERE in_stock
       │  ├ order_items  │   - (user_id, ordered_at DESC)
       │  ├ reviews      │   - matview top_sellers_7d
       │  ├ categories   │
       │  └ users        │
       └─────────────────┘

What this is not

• Not an API gateway. No auth, no rate limiting, no API keys, no usage metering — see SAY-5/api-platform for the TypeScript study of those concerns.
• Not a write API. No POST/PUT/DELETE endpoints in this repo — by design, "read-mostly query optimization" is the focus.
• Not GraphQL. REST only.
• Not cached. No Redis, no in-process LRU. A read-through cache layer is its own study.
• Not horizontally sharded. Single Postgres, no read-replica routing.
• Not real-time. No SSE, WebSocket, or change-data-capture.
• Not load-balanced. Single JVM behind Tomcat in the load runs; scaling out is documented in ARCHITECTURE.md but not implemented here.

License

MIT — see LICENSE.