Self-hosted music streaming services frequently experience non-linear latency increases during deep pagination and complex aggregations on large libraries. To establish concrete operational characteristics, we conducted comprehensive latency sampling across PureStream’s Subsonic protocol read paths using a benchmark dataset containing 500,000 songs.
Test Scope and Dataset Construction
Enthusiast libraries regularly scale to hundreds of thousands of tracks. Common Subsonic protocol endpoints (including search3 enumeration, getMusicDirectory browsing, and getAlbumList2 album listings) exhibit divergent cost profiles across different catalog sizes, with some queries leveraging covering indexes while others require table-wide deduplication and grouping. This benchmark round completed the following tasks:
- [x] Generated a 500,000-row media catalog (covering approximately 42k albums, 2,000 artists, and 100 genres) through the full database migration pipeline to create a production-equivalent SQLite database
- [x] Implemented a covering read index for genre queries and integrated it into schema contract validation to prevent runtime drift or index drops
- [x] Sampled P50 and P95 latency profiles across 14 core read paths to establish baseline metrics
Core Read Path Metrics
| Endpoint | 100k | 500k | Evaluation |
|---|---|---|---|
| search3 main enumeration | 53µs | 69µs | Stable |
| search3 deep page (offset=250k) | — | 25ms | Stable |
| getMusicDirectory single page | 1.3ms | 1.4ms | Stable |
| getAlbumList2 three paging modes | 8–82ms | 86–333µs | Stable |
| getSongsByGenre | untested | 113µs | Index effective |
| FTS text search | 24.8ms | 137ms | Linear growth |
| getArtists dedup enumeration | 3.4ms | 543ms | Aggregation bound |
| getGenres aggregation | untested | 600ms | Within bounds |
| Large single-directory deep page | — | 734ms | Edge case |
Key Observations:
- Main Enumeration (69µs): Default search3 queries maintain microsecond latency at 500,000 rows. The query utilizes a partial covering index on active rows, insulating read operations from concurrent catalog scanner writes.
- Deep Pagination (25ms): Fetching a single page at an offset of 250,000 tracks completes in 25 milliseconds, maintaining responsive millisecond-level execution over wide offsets.
- Genre Filtering (113µs): The newly deployed covering index transforms the query plan from a sequential scan into an index lookup, reducing overall latency.
Bottleneck Analysis
The benchmark also isolated two computationally intensive aggregation scenarios:
- getArtists Deduplicated Enumeration (543ms): Artist deduplication computes a union across primary and participating artist attributes followed by group aggregation, requiring full-table scans. Execution remains sub-second at 500,000 tracks and is maintained on the active observation list.
- getGenres Aggregation (600ms): Genre statistics calculate unique album counts via
COUNT(DISTINCT album)across group boundaries, requiring 600 milliseconds at the 500,000-track scale.
Delivered Improvements
- [x] Established complete P50/P95 latency baselines for 14 core read endpoints across 500,000 tracks
- [x] Deployed genre read indexes with schema registration and query plan validation tests
- [x] Quantified computational hotspots across deduplication and grouping queries to guide future optimization
Upcoming Milestones
- [ ] Protocol pagination enhancements: Implement cursor-based navigation on offset-free endpoints to minimize repeated scanning
- [ ] Consolidation of session identity checks for in-flight audio streaming