# Production performance notes

Written 2026-09-10 after resolving a multi-day slowdown on the production
deployment (`nucleus.gravitipharma.com`). Recorded here because **part of the fix
does not live in this repository**, and a droplet rebuilt from this codebase alone
would silently lose it.

## The problem

Sign-in, the material dropdown on `/manual_dispense`, and PDF downloads all became
slow over roughly three days. Two distinct causes, plus one red herring.

### Cause 1 — missing foreign-key indexes (the dominant factor)

Six tables in the dispensing read path carried only their primary key, so every
lookup on `dispensing_request_id` / `batch_details_id` scanned the whole table.
`list_view_dc` (`GET /api/v1/dispense/dc-id`) runs three of those scans per BMR
material in a sequential loop, 7-21 times per page load.

Measured on production with `EXPLAIN (ANALYZE, BUFFERS)` before the change:

| Table | Time | Rows discarded to return a handful |
|---|---|---|
| `new_dispensary_containers` | 97 ms | 524,284 |
| `dispensing_request_pdfs` | 53 ms | 404,057 |
| `dispense_sub_batches` | 22 ms | 166,284 |

That is ~172 ms and ~180 MB of buffer traffic **per material**, per page load.

Fixed by migration `20260909120000-add-fk-indexes-for-dispense-hot-paths.js`.
Read the comments in that file before touching the indexes -- the trailing `id`
column is load-bearing and must not be removed.

### Cause 2 — no compression on JSON responses (NOT IN THIS REPO)

Every API response is `application/json` and Apache was serving all of it
uncompressed. The material payload measured **800 KB**.

Fixed by adding one line to `/etc/apache2/mods-available/deflate.conf` on the
droplet:

```apache
AddOutputFilterByType DEFLATE application/json
```

- Backup of the original: `/root/backups/deflate.conf.bak-2026-09-09`
- Measured effect: material payload 800 KB -> 61 KB (13x); a representative
  9,699-byte response compresses to 2,626 bytes
- **Always run `apache2ctl configtest` before reloading**, then `systemctl reload
  apache2` (graceful -- does not drop connections). A malformed edit here takes
  the whole site down.

**This is the piece that is not version-controlled.** If the droplet is rebuilt or
Apache is reinstalled, re-apply it.

### Red herring — PDF download latency

PDF generation timings were **flat** across the whole period (median
1,316-1,381 ms, Sep 4 through Sep 9). PDF slowness was not a regression; it is a
permanent floor. What changed was that requests began queueing behind the
unindexed scans above, which made PDFs *appear* to degrade.

The floor itself is caused by Puppeteer's `waitUntil: 'networkidle0'`, which
accounts for ~1,956 ms of a ~2,330 ms render (84%). Chrome launch is only 215 ms.
Replacing it with `'load'` plus `await page.evaluate(() => document.fonts.ready)`
renders in ~226 ms and produces a byte-identical PDF.

**If you attempt that change: dropping the wait without `document.fonts.ready`
silently breaks web fonts** -- the PDF still renders but falls back to different
typefaces (28,834 bytes vs 17,383, different hash). Not yet implemented.

## Results

Production request timings, Sep 8 (pre-index) vs Sep 10 (post-index), from the
morgan output in the PM2 logs:

| Endpoint | p50 | p95 | max |
|---|---|---|---|
| `dispense/dc-id` | 2,834 -> 323 ms | 12,640 -> 5,785 ms | 42,384 -> 8,858 ms |
| `material/all-material-batches` | 463 -> 31 ms | 8,267 -> 8,928 ms | 19,519 -> 15,992 ms |
| `dispense/get-all` | 156 -> 127 ms | 1,945 -> 1,038 ms | 44,734 -> 2,735 ms |
| `material/all` | 433 -> 195 ms | 2,163 -> 501 ms | 10,010 -> 13,941 ms |

Caveat: the post-fix window is morning-only while the pre-fix window spans
afternoon and evening, so treat these as strongly indicative rather than a
controlled experiment.

## Known-pending work

Not addressed, in rough order of measured cost:

1. `material/all-material-batches` p95 is still ~8.9 s. The indexes fixed its
   median (15x) but not its tail.
2. `dispense/dc-id` p95 is still ~5.8 s. The remaining cost is the N+1 loop and
   Sequelize model hydration, not I/O -- no index will help. Needs
   `separate: true` / `raw: true` or a restructured loop.
3. `dispense/dc-update` max is ~52 s and was never investigated.
4. `container/all` p95 measured *worse* after the change (618 -> 2,700 ms). Cause
   unconfirmed -- could be the different sampling window or a plan change from
   the new `containers` index. Worth checking before assuming the index work is
   clean.
5. The PDF floor (see above).
6. No explicit `ORDER BY` on the positional reads -- see the migration comments.

## Operational gotchas discovered

- **`db:migrate` cannot run on production.** Migration
  `20260601000000-mvpatch-add-tolerance-id-to-weighing-gross-weights.js` reports
  as pending while the column it adds already exists, so the runner aborts before
  reaching anything newer. The indexes were applied to production as direct DDL
  for this reason. Fixing that migration is a prerequisite for ever running
  `db:migrate` there again.
- **`pg_stat_user_tables` row estimates on production were badly wrong** before
  `ANALYZE` (off by 40-120x). Do not diagnose table size from `n_live_tup`
  without checking `last_analyze` first.
- **`autoanalyze` had never run** on any of the six tables. The migration now
  runs `ANALYZE` explicitly.
- **PM2 runs in fork mode, single process.** Cluster mode is currently blocked by
  `node-cron` schedules declared inside controllers, which would fire once per
  worker.
- **Apache logs use the `combined` format**, which has no `%D`/`%T` -- there are
  no response times in the Apache access logs. Request timings come from
  `morgan('dev')` in the PM2 output logs (`/root/.pm2/logs/server-out.log`),
  which are rotated by `pm2-logrotate` at 50 MB with 14 retained.