Designing a Favorite Feature: Data Model, Idempotency, and High-Concurrency Optimization Practices
In most content, community, or tool products, favorites (bookmarks) look like a trivial button but put real pressure on the backend: high-frequency writes, massive concurrent reads, and counters that must stay fresh. Teams often start with a single table and only later run into duplicate favorites, counter drift, and hot-key contention. This article walks through the feature from its semantics to a production-ready design: relationship modeling with a composite unique index, denormalized counters, idempotent APIs, read-path caching and hot-key handling, cursor-based list pagination, eventual consistency strategies, and vendor-neutral cloud deployment options. The same patterns carry over to likes, follows, and any "user-to-resource relationship" feature.
Introduction: Why Favorites Are Never Simple
In almost every content, community, or tool product, favorites are one of the most fundamental and most underestimated features. A click on a favorite button looks effortless, yet the backend behind it usually has to handle high-frequency writes, massive concurrent reads, and counting requirements that must stay real-time — much like likes. Many teams implement it initially with one simple table, and only when data volume and traffic climb do they start hitting duplicate favorites, counter drift, and hot-key contention. This article breaks the feature down from its requirements semantics and offers optimization advice aimed at production environments.
1. Clarifying Requirements and Core Semantics
Before implementing anything, pinning down the semantics is the most important step. Fundamentally, a favorite is a many-to-many relationship between a user and a resource: one user can favorite many resources, and one resource can be favorited by many users. Around that relationship, four semantics need to be clarified:
- State semantics: Favoriting is a binary state — “favorited” or “not favorited.” A user clicking the same button repeatedly must end up in a consistent state, which is the basic requirement for idempotency.
- Reversibility semantics: Favorites must be removable at any time, so the system stores not only “who favorited what” but also supports reverse lookup and deletion.
- Traceability semantics: In many scenarios the favorites list is displayed in time order, and the favorite timestamp may be needed for operational analysis, so the relationship itself usually carries more information than a single flag.
- Visibility semantics: Whether a favorites list is private or public, and whether access control applies, directly affects the design of the query APIs.
With these semantics settled, the data model and API design have a solid basis.
2. Data Model Design
2.1 Modeling the Relationship Table
The most common approach is a dedicated favorites table in a relational database, where “user identifier + resource identifier” uniquely determine one record:
CREATE TABLE favorite (id BIGINT NOT NULL AUTO_INCREMENT,user_id BIGINT NOT NULL COMMENT 'User identifier',target_type TINYINT NOT NULL COMMENT 'Resource type, e.g. article / video / product',target_id BIGINT NOT NULL COMMENT 'Resource identifier',created_at DATETIME NOT NULL COMMENT 'Favorite timestamp',PRIMARY KEY (id),UNIQUE KEY uk_user_target (user_id, target_type, target_id),KEY idx_target (target_type, target_id),KEY idx_user_time (user_id, created_at));
Three design points are worth emphasizing. First, the uk_user_target composite unique index is the last line of defense against duplicate favorites: it guarantees at the database level that a user has only one record per resource, avoiding concurrency holes that come from relying on application-layer checks. Second, the target_type column makes the feature reusable — one system can support articles, videos, products, and more with a single table. Third, the idx_user_time index serves the high-frequency “my favorites, newest first” query and prevents performance degradation on deep pagination.
2.2 Denormalized Counters
The total favorite count of a resource is a field that will almost certainly be displayed. Computing it live with COUNT(*) on every render creates enormous database pressure at scale. The common industry practice is to store the count as a denormalized field and adjust it as favorites are added and removed. Such counters typically live on the resource’s main table or in a dedicated counter table, and the benefit is especially clear in read-heavy, write-light workloads.
3. API Design and Idempotency Control
Favorite APIs should be strictly idempotent. Taking “add favorite” as an example, it is better modeled as a declarative state assignment than as an incremental operation:
public void addFavorite(long userId, int targetType, long targetId) {// Rely on the unique index plus insert-ignore for natural idempotencyFavorite f = new Favorite(userId, targetType, targetId);try {favoriteMapper.insertIgnore(f); // Duplicate rows are ignored, no error throwncounterService.incr(targetType, targetId); // Increment the counter asynchronously} catch (DuplicateKeyException e) {// Unique key conflict under concurrency: treat as already favorited and return success}}
The key is “ignore duplicate inserts.” No matter how many times a user clicks, the end result is “one favorite record exists,” which avoids the data corruption caused by repeated clicks or network retries. Counter increments are better offloaded asynchronously through a message queue, which reduces latency on the critical path and leaves room for retries on failure.
4. High-Concurrency Read/Write Optimization
The typical characteristic of favorites is read-heavy, write-light: a content detail page gets hundreds or thousands of times more views than favorites, and every view may trigger reads such as “has this user favorited it?” and “what is the favorite count?” Optimization effort should therefore go into the read path.
- Caching the favorited state: User detail pages frequently ask whether the current user has favorited a given resource. Cache these results and invalidate them when a favorite is added or removed, so that not every request hits the database.
- Count caching and aggregation: A resource’s favorite count can be cached in memory or a distributed cache; writes update the counter incrementally and reads hit the cache directly, freeing the database from high-frequency reads.
- Protecting hot resources: Viral resources accumulate huge favorite counts, and write contention on a single counter becomes intense. For extremely hot resources, shard the counter and merge periodically, spreading single-point contention across multiple shards.
- Batch query optimization: The favorites list page needs resource details in bulk. Avoid N+1 queries by fetching rows in batches keyed on resource identifiers, then composing the results afterwards.
5. Bulk and List Queries
The personal favorites list is the other main battleground. It needs pagination, newest-first ordering, and bulk enrichment of resource information. The design should account for:
- Using cursor-based pagination instead of the traditional large-offset
LIMIT offset, size, avoiding the performance cliff caused by full scans on deep pages; - Fetching resource details for a favorites list in a single batched query, then assembling them at the application layer;
- For public favorites lists, layering in permission and privacy filtering.
6. Error Handling and Consistency
In a distributed environment, problems are unavoidable: the favorite write succeeds while the counter update fails, or cache invalidation and the database write disagree. Engineering practice generally follows an eventual consistency principle — the relationship table is the source of truth, while counters and caches are derived data that can be rebuilt. When a discrepancy between the counter and the real data is detected, an offline job can recalibrate it periodically. Meanwhile, to handle out-of-order concurrent requests between removal and addition, resolve conflicts using a version number or timestamp so that a later “add” update is not overwritten by an older “remove” request.
7. Deployment Recommendations in the Cloud
When deploying a favorites service in production, you can assemble a complete pipeline from generic cloud service categories without binding to any vendor-specific implementation:
- Compute and deployment: Run the favorites service stateless on a container platform, with elastic scaling to absorb traffic spikes.
- Caching layer: Use a cloud cache service to hold hot data such as favorited state and favorite counts.
- Message-based decoupling: Process counter increments and cache invalidation asynchronously through a message queue, smoothing out peaks and filling in troughs.
- Data storage: Keep relationships and resource data in a relational database; object storage can handle large-file scenarios such as bulk exports of lists.
- Observability: Use logging and monitoring/alerting services to track key metrics — API success rate, latency, and counter deviation — so anomalies surface quickly.
This combination can be trimmed or extended to match business scale. The core idea is to keep reads on the cache path as much as possible, make writes asynchronous wherever feasible, and close the loop with monitoring.
Conclusion
Favorites may look simple on the surface, but behind them lies a combined test of data modeling, idempotency, high-concurrency reads and writes, counter aggregation, and consistency guarantees. Lay a solid foundation with the relationship table and its unique index, separate the read and write paths for counters and caches, then tie the whole pipeline together with a message queue and monitoring — and you get a favorites service that survives high concurrency while keeping data accurate. These ideas apply beyond favorites to likes, follows, adding to a list, and every other “user-to-resource relationship” feature, and they are worth refining over time in everyday engineering work.
