Problem
Every call to the video listing endpoint (GET /api/v1/videos) generates two Astra DB performance warnings:
WARNING:astrapy.utils.api_commander:The Data API returned a warning: ZERO_FILTER_OPERATIONS
- Zero filters were provided. Returning all rows in the table.
WARNING:astrapy.utils.api_commander:The Data API returned a warning: IN_MEMORY_SORTING_DUE_TO_NON_PARTITION_SORTING
- Sorted on column: added_date (not a partition sort column). Sorted in memory.
These appear on every page load that triggers a video listing (home page, related videos, etc.) and will degrade as the table grows.
Root Cause
list_videos_with_query in video_service.py issues a find() with:
- No filter (
filter={}) when listing all videos — triggers ZERO_FILTER_OPERATIONS
- Sort by
added_date — but added_date is not a partition sort column on the videos table, so Astra must sort the full result set in memory — triggers IN_MEMORY_SORTING_DUE_TO_NON_PARTITION_SORTING
The videos table has added_date as an SAI index (for filtering) but not as a clustering column (for sorted access on disk).
Options to Fix
Option A — Add added_date as clustering column (schema change)
Change the videos table primary key to PRIMARY KEY (some_partition_key, added_date) so Astra can use on-disk ordering. Requires a data migration.
Option B — Introduce a time-bucketed listing table (denormalization)
Add a videos_by_date table partitioned by month/week with added_date as the clustering key. The listing query reads from this table instead. Consistent with the existing video_activity design pattern.
Option C — Accept in-memory sort, suppress warnings
The table currently has ~500 rows — in-memory sort is fast at this scale. Add a logging filter (same pattern as safe_count) to suppress the advisory warnings until the table grows to a size where it actually matters.
Acceptance Criteria
Problem
Every call to the video listing endpoint (
GET /api/v1/videos) generates two Astra DB performance warnings:These appear on every page load that triggers a video listing (home page, related videos, etc.) and will degrade as the table grows.
Root Cause
list_videos_with_queryinvideo_service.pyissues afind()with:filter={}) when listing all videos — triggersZERO_FILTER_OPERATIONSadded_date— butadded_dateis not a partition sort column on thevideostable, so Astra must sort the full result set in memory — triggersIN_MEMORY_SORTING_DUE_TO_NON_PARTITION_SORTINGThe
videostable hasadded_dateas an SAI index (for filtering) but not as a clustering column (for sorted access on disk).Options to Fix
Option A — Add
added_dateas clustering column (schema change)Change the
videostable primary key toPRIMARY KEY (some_partition_key, added_date)so Astra can use on-disk ordering. Requires a data migration.Option B — Introduce a time-bucketed listing table (denormalization)
Add a
videos_by_datetable partitioned by month/week withadded_dateas the clustering key. The listing query reads from this table instead. Consistent with the existingvideo_activitydesign pattern.Option C — Accept in-memory sort, suppress warnings
The table currently has ~500 rows — in-memory sort is fast at this scale. Add a logging filter (same pattern as
safe_count) to suppress the advisory warnings until the table grows to a size where it actually matters.Acceptance Criteria
IN_MEMORY_SORTING_DUE_TO_NON_PARTITION_SORTINGwarning no longer appears in logs on video listingZERO_FILTER_OPERATIONSwarning no longer appears in logs on video listing