Follow-up from #22. The scalability measurements show that cursor paging is depth-independent in principle but still scans and sorts, because no index supports any of the sort keys. In TITLE_ASC at 1,000,000 synthetic albums: | Rows already scrolled | Cursor (no sort index) | OFFSET (no sort index) | Cursor (with `(lower(title), id)`) | OFFSET (with index) | | --------------------- | ---------------------: | ---------------------: | ---------------------------------: | ------------------: | | 50,000 | 295 ms | 865 ms | 0.07 ms | 15 ms | | 500,000 | 227 ms | 1167 ms | 0.09 ms | 164 ms | The index is 47 MB at one million albums. The experiment was run in the performance database only and is not in the schema. ## Tasks - [ ] Decide which sorts get an index: `TITLE_ASC/DESC` (`lower(title), id`), `RELEASE_YEAR_ASC/DESC` (`release_year, id`, `NULLS LAST` to match the ORDER BY), `AVERAGE_RATING_ASC/DESC` (`average_rating, id`, `NULLS LAST`). - [ ] Measure the write cost: seed time, `db:import:musicbrainz` time, and index size per index at 1,000,000 albums. - [ ] Measure the read gain with `server/scripts/paging-perf.sql` before and after, including `RELEASE_YEAR` and `AVERAGE_RATING`, not only title. - [ ] Add migrations for the indexes that pay for themselves, and update the README scalability tables. - [ ] If a sort does not get an index, say so in the README and remove the implication that cursor paging is constant time for it. ## Why not in #22 Adding migrations, measuring write amplification and deciding per sort is more than a documentation change, and the bærekraft argument (47 MB per index at 1M rows, plus maintenance on every import) needs its own review. ## Acceptance Either the browsing sorts page in constant time at 1,000,000 albums with measured write cost documented, or the README states which sorts are depth-dependent and why they were left unindexed.