mirror of
https://github.com/barkeser2002/offline-db.git
synced 2026-09-25 03:40:05 +03:00
Barış Keserandgoogle-labs-jules[bot]
4fb722a31a
Introduced an `_anime_database_index` global dictionary within `scraper_module/adapters/anizle.py`. It is dynamically populated by `load_anime_database()`. Refactored `get_anime_episodes` and `get_anime_details` to use `_anime_database_index.get(slug)` (O(1)) rather than a linear scan (O(N)) over `_anime_database`. This drastically improves query time from the cached database list. Also cleared leftover orphaned performance test scripts in the root directory. Co-authored-by: google-labs-jules[bot] <161369871+google-labs-jules[bot]@users.noreply.github.com>
72 lines
11 KiB
Markdown
72 lines
11 KiB
Markdown
## 2024-10-27 - Badge System Optimization
|
|
**Learning:** The badge system uses multiple independent queries to filter `WatchLog` for different time windows.
|
|
**Action:** Optimize `WatchTimeBadgeStrategy` by combining overlapping queries.
|
|
|
|
## 2024-10-27 - Badge System CompletionBadgeStrategy Optimization
|
|
**Learning:** `CompletionBadgeStrategy` originally fetched the most recent `WatchLog` in a completely separate query with joins (`select_related('episode__season__anime')`) for the `super-fan` badge, even if it had already just fetched exactly the same log with a similar query (`select_related('episode__season')`) for the `season-completist` badge.
|
|
**Action:** Optimize `CompletionBadgeStrategy` to fetch the most recent log once, eagerly loading `episode__season__anime`, and reuse this data for evaluating both `season-completist` and `super-fan` badges. This reduces redundant database hits.
|
|
|
|
## 2025-03-01 - Global Badge System Optimization (Cache Context)
|
|
**Learning:** We had individual strategies successfully optimized internally, but different strategies shared the exact same queries (e.g. `WatchLog.objects.filter(user=user).select_related('episode__season__anime').order_by('-watched_at').first()`). Due to isolation in strategy pattern, redundant database calls were made.
|
|
**Action:** Introduced a `cache` argument into the base `BadgeStrategy.check()` signature and propagated a `cache` dictionary instance from the service layer, enabling strategies to store and retrieve results of expensive or commonly repeated queries (e.g., `last_log`, `anime_ids`) across boundaries.
|
|
|
|
## 2025-03-01 - Global Global Badge System Optimization (Avoid Heavy WatchLog Joins)
|
|
**Learning:** Multiple badge strategies (`ConsumptionBadgeStrategy`, `SpecificGenreBadgeStrategy`, `GenreBadgeStrategy`) were performing expensive `JOIN`s from `Anime` down to `WatchLog` (e.g., `seasons__episodes__watch_logs__user=user`) multiple times per evaluation cycle. This was particularly heavy given `WatchLog` is the largest table in the database.
|
|
**Action:** Changed the strategies to reuse the locally cached list of `anime_ids` and `episode_ids` and replaced the heavy 4-table join with a simple `id__in=anime_ids` and `id__in=episode_ids` check directly against the `Anime` and `Genre` queries.
|
|
|
|
## 2025-03-03 - Badge System Optimization (Eliminate Redundant check_badges calls)
|
|
**Learning:** `check_badges(user)` was being called manually in DRF viewsets (e.g., `ReviewViewSet.perform_create`, `WatchLogViewSet.perform_create`) even though `post_save` signals for `Review` and `WatchLog` were already configured to trigger `check_badges` automatically. This double-fired the entire badge evaluation logic for common user actions.
|
|
**Action:** Removed redundant `check_badges(user)` calls from views that are already covered by Django signals, halving the database overhead on `WatchLog` and `Review` creations.
|
|
|
|
## 2025-03-03 - Badge System Optimization (Cache All Badges)
|
|
**Learning:** Even with strategies optimized internally, `Badge.objects.all()` was being queried from the database every single time `check_badges` or `check_chat_badges` was executed. Because badges change very infrequently, this is a prime candidate for application-level caching.
|
|
**Action:** Introduced Django's `cache` mechanism to store the `all_badges` dictionary for 1 hour, retrieving it from memory instead of the database, saving a redundant SQL lookup on every badge evaluation trigger.
|
|
|
|
## 2025-03-03 - Template Tag Query Optimization
|
|
**Learning:** Template tags like `get_ad` that are used in the main application layout (`base.html`) and contain database queries (e.g., fetching an `AdSlot`) cause a hidden N+1-like issue by triggering an independent query on *every single page load* for non-premium users.
|
|
**Action:** Cached the output of the template tag via Django's `core.cache` and added signal hooks to invalidate the cache only when the underlying `AdSlot` model is saved or deleted.
|
|
|
|
## 2025-03-05 - Badge System Query Count Optimization
|
|
**Learning:** `CommunityBadgeStrategy`, `ChatBadgeStrategy`, and `ConsistencyBadgeStrategy` were issuing multiple independent `.count()` and `.exists()` queries against the database to evaluate different thresholds of the same data (e.g., checking if a user hosted 5 rooms, then immediately checking if they hosted a room with 5 participants).
|
|
**Action:** Replaced separate DB aggregation queries with a single query that fetches the relevant distinct rows into the shared `cache` dictionary (e.g., `Room.objects.filter(host=user).values('max_participants')`). The `.count()` and `.exists()` logic is then evaluated in memory using Python's `len()`, `any()`, and `sum()`, drastically reducing the total database queries per badge evaluation cycle.
|
|
|
|
## 2025-03-07 - Badge System Strategy Caching
|
|
**Learning:** Even though we had implemented caching inside `users/badge_system.py` using a shared `cache` dict, it was partially unused in strategies that needed a `.distinct().count()`. Querying `.distinct().count()` skips memory and always hits the database.
|
|
**Action:** Used `len(cache['episode_ids'])` where `episode_ids` was already stored as a flat list, saving a whole `.distinct().count()` aggregation query from `ConsumptionBadgeStrategy`. This demonstrates that when you already have a distinct list of IDs cached, doing `len()` in python is much faster and saves an extra DB query.
|
|
|
|
## 2025-03-09 - Badge System Pilot Connoisseur Optimization
|
|
**Learning:** `pilot-connoisseur` badge check was performing an expensive `JOIN` traversing `WatchLog`, `Episode`, `Season` and `Anime` to count distinct anime series. And because `.distinct().count()` skips memory and hits the DB, it was issuing a new heavy aggregation query.
|
|
**Action:** Leveraged the shared `cache['episode_ids']` and replaced the expensive `WatchLog` join with an `id__in=episode_ids` check directly on the `Episode` model. Additionally, fetched the results as a flat list and evaluated uniqueness in memory using Python's `len(set(...))` to prevent the `.distinct().count()` database query overhead.
|
|
|
|
## 2025-03-09 - Badge System Genre Optimization
|
|
**Learning:** `GenreBadgeStrategy` queries for `genre-explorer`, `genre-master`, and `genre-savant` were extremely inefficient. They relied on `.distinct().count()`, reverse relationship `.annotate(Count())`, and complex 4-table `.exists()` lookups which bypassed our previously implemented memory caching and heavily taxed the database with redundant JOIN operations on the largest tables (`WatchLog` and `Episode`).
|
|
**Action:** Replaced these heavy database queries with simpler `values_list(..., flat=True)` queries to fetch raw IDs directly related to the user's cached `anime_ids` and `episode_ids`. We then process these IDs in-memory using pure Python (e.g., `len(set())` and `collections.Counter()`). This dramatically reduces database CPU load and memory usage by offloading computation to the Python runtime, and drops total queries per user evaluation cycle.
|
|
|
|
## 2024-10-28 - Badge System distinct().count() Optimization
|
|
**Learning:** `CompletionBadgeStrategy` (`season-completist`, `super-fan`) and `ConsumptionBadgeStrategy` (`loyal-fan`) were issuing `.distinct().count()` aggregation queries with JOINs on `WatchLog` to evaluate watched episodes for specific seasons and animes. Since `.distinct().count()` skips memory and always hits the database, these queries were adding unnecessary database load.
|
|
**Action:** Refactored these strategies to utilize the shared `cache['episode_ids']`. By fetching the target `Episode` IDs (e.g., for a season or anime) as a flat list and intersecting them with the cached `episode_ids` set in memory using Python (`len(target_ep_ids.intersection(user_ep_ids))`), we eliminate the heavy `WatchLog` joins and redundant aggregation queries, significantly reducing database load per evaluation cycle.
|
|
|
|
## 2025-03-09 - WatchTimeBadgeStrategy count optimization
|
|
**Learning:** `WatchTimeBadgeStrategy` checks for `binge-watcher`, `marathon-runner`, `weekend-warrior`, and `speedster` badges were issuing `.distinct().count()` aggregation queries with JOINs on `WatchLog`. Since `.distinct().count()` skips memory and always hits the database, these queries were adding unnecessary database load.
|
|
**Action:** Replaced these heavy database queries with `len(set(WatchLog.objects.filter(...).values_list('episode_id', flat=True)))`. This pattern avoids the expensive database-level `.distinct().count()` aggregation by executing a simpler select and evaluating uniqueness and size in Python memory, reducing database load per evaluation cycle.
|
|
## 2026-03-05 - Bolt: Convert genre_savant episode_ids to subquery
|
|
**Learning:** Fetching a large dataset of IDs into a Python list (`list(values_list("episode_id", flat=True))`) to perform in-memory aggregations or subquery lookups creates massive SQL queries and high memory overhead, especially for complex relationships like genre counts.
|
|
**Action:** Replaced the in-memory list with an un-evaluated Django QuerySet subquery (`episode_qs = WatchLog.objects.filter(user=user).values("episode_id")`), and utilized `.annotate(count=Count("id", distinct=True))` to evaluate genre distributions purely at the database level without transferring records.
|
|
|
|
## 2025-03-10 - [Optimize genre-savant badge strategy]
|
|
**Learning:** `WatchLog.objects.filter(user=user).values('episode_id')` would pull episode ids into memory which were then passed to `.filter(id__in=episode_qs)`. This resulted in excessive database fetching and memory use.
|
|
**Action:** Substituted the two-step evaluation process into a single optimized query string that handles filtering, related joins, and aggregations purely at the database level by traversing models via double-underscores (`episode__season__anime__genres__id`).
|
|
|
|
## 2026-03-05 - NotificationViewSet Performance Improvement
|
|
**Learning:** To significantly improve performance of DRF ViewSets like `NotificationViewSet` that frequently filter and order by specific fields (e.g., `filter(user=request.user).order_by('-created_at')` and `filter(is_read=False)`), add composite database indexes to the Django model's `Meta` class (e.g., `models.Index(fields=['user', 'is_read'])` and `models.Index(fields=['user', '-created_at'])`).
|
|
**Action:** Added composite indexes `['user', 'is_read']` and `['user', '-created_at']` to the `Notification` model's `Meta` class in `users/models.py` and created the corresponding database migrations.
|
|
|
|
## 2024-03-13 - [Add composite indexes to WatchLog and Subscription models]
|
|
**Learning:** To significantly improve performance of querying large tables like `WatchLog` and `Subscription` which are frequently filtered by combinations of `user` and `watched_at` or `user` and `anime`, composite database indexes should be added.
|
|
**Action:** Added `['user', 'watched_at']` to `WatchLog` and `['user', 'anime']` to `Subscription` in `users/models.py` and `content/models.py` respectively, and generated migrations.
|
|
|
|
## 2026-03-13 - [Add index to Room is_active]
|
|
**Learning:** The `Room` model is frequently filtered by `is_active=True` across the application. Adding an index to this boolean field can improve query performance.
|
|
**Action:** Added `models.Index(fields=['is_active'])` to the `Room` model in `apps/watchparty/models.py` and generated the corresponding migration.
|
|
- Optimized `scraper_module/adapters/anizle.py` to use a global `_anime_database_index` dictionary for O(1) lookups in `get_anime_episodes` and `get_anime_details`, significantly reducing time complexity from O(N) linear scans when querying the loaded database.
|