I am running into scaling issues around sorting by successRate (vote ratio) in my NextJS project.

Schema
- Listing and PcListing only store raw Vote records via relations.
- No aggregate counters are persisted.
- successRate is derived as upVotes / (upVotes + downVotes) (in memory)

Current behavior

I experimented with 2 approaches that didn't require db changes.

- Get Listings (web api) coreRouter.get https://github.com/Producdevity/EmuReady/blob/master/src/server/api/routers/listings/core.ts
When sorting by successRate, we load the entire filtered result set (no skip/take), compute successRate with a per-listing vote.count, sort in JS, then paginate in memory. (This is by far the worst approach)

- Get Listings (mobile api)mobileListingsRouter.get https://github.com/Producdevity/EmuReady/blob/master/src/server/api/routers/mobile/listings.ts
Mobile endpoints paginate in the DB, then compute successRate per listing via two vote.count calls (+ optional user vote lookup), i.e. N+1/3N queries per page; no DB-side successRate ordering.

---

What I've considering (not asking for full solutions; want guidance/war stories)

1. Denormalized counters on listings (upVotes, downVotes, voteCount, maybe successRate).
2. Separate listing_stats tables maintained by triggers or app hooks.
3. DB-level aggregation (JOIN/GROUP BY + ratio) with ORDER BY and pagination in SQL.
4. Materialized view refreshed async.
5. Cache layer (Redis) with reconciliation.
6. Postgres generated (stored) column for success_rate (indexable, always in sync).
7. Expression index on the ratio using persisted counters (no extra column).
8. Deterministic ORDER BY policy that handles “no votes” and tie-breaks by vote volume.


Public repo: https://github.com/Producdevity/emuready

⸻

Any recommendations for how to handle this in Prisma + Postgres + Next.js in a way that scales but stays maintainable (migrations, caching, etc.)?