Building a User Subscription System for Mobile Apps

Note: when user count exceeds 500 thousand and subscription count exceeds 10 million, a plain follows table without an index starts to slow down: counting followers becomes a full scan. We are a team with 5+ years of experience developing social applications; we have delivered over 30 projects with subscription systems handling millions of loads. We solve this problem comprehensively — from data schema with indexes and denormalization to push notifications and recommendations. An index on followee_id speeds up COUNT(*) by a thousand times at a million records, and denormalized counters in users give a response under 1 ms. Order the development of your subscription system from us — we guarantee stability under any load. Our experience covers all nuances: App Store Review Guidelines (Section 4.2/5.1), confidentiality requirements, and performance. In one project for a social network with 2M DAU, we designed a subsystem processing 10K actions per second without downtime. In this article, we'll discuss which database schema is optimal, how to avoid performance degradation under growth, and why optimistic UI updates are critical for user experience.

Data Schema and Queries

Table follows:

CREATE TABLE follows ( follower_id BIGINT NOT NULL, followee_id BIGINT NOT NULL, created_at TIMESTAMP DEFAULT NOW(), PRIMARY KEY (follower_id, followee_id) ); CREATE INDEX idx_follows_followee ON follows (followee_id); 

An index on followee_id is mandatory — it speeds up subscriber counting by 1000x at 1M records. Denormalization of counters in the users table (fields followers_count and following_count) updated via a trigger or queue gives response times under 1 ms.

Another way to speed up mutual subscription checking is caching in Redis: 100x faster than SQL at 1M records. For frequent queries (who follows me), we store a hash set follower_id → Set<followee_id>.

How to Implement a Follow/Unfollow Button with Optimistic Update

Optimistic update is mandatory — the button toggles instantly before the server responds. On error, the state reverts. On iOS we use Combine, on Android — StateFlow. Implementation steps:

1. Toggle the UI button state (e.g., from “Follow” to “Following”).
2. Send an asynchronous follow/unfollow request.
3. On success, commit the new state.
4. On error, revert the UI to the original state.

// iOS func toggleFollow(userId: String, currentlyFollowing: Bool) { let optimisticState = !currentlyFollowing updateFollowButton(isFollowing: optimisticState) let request = optimisticState ? apiService.follow(userId) : apiService.unfollow(userId) request.sink( receiveCompletion: { [weak self] completion in if case .failure = completion { self?.updateFollowButton(isFollowing: currentlyFollowing) // revert } }, receiveValue: { _ in } ).store(in: &cancellables) } 

Three button states: “Follow”, “Following”, and “Unfollow” (shown on long press). It's important not to make “Unfollow” the primary text — users might mistake it for confirming a subscription. For SwiftUI we use @State with the optimistic update pattern, for Jetpack Compose — mutableStateOf.

Private Accounts and Follow Requests

If the app supports private profiles, we introduce a table follow_requests (requester_id, target_id, status, created_at). The target user sees incoming requests, accepts or rejects them. Upon acceptance, the record moves to follows, and a push notification is sent to the requester.

How Pagination of the Subscriber List is Organized

Pagination is cursor-based by created_at DESC. For each user in the list, a batch query checks isFollowedByMe: SELECT followee_id FROM follows WHERE follower_id = ? AND followee_id IN (?) — one query per page.

SELECT followee_id FROM follows WHERE follower_id = ? AND followee_id IN (?, ?, ?); 

SELECT DISTINCT f2.followee_id FROM follows f1 JOIN follows f2 ON f1.followee_id = f2.follower_id WHERE f1.follower_id = :me AND f2.followee_id != :me AND NOT EXISTS (SELECT 1 FROM follows WHERE follower_id = :me AND followee_id = f2.followee_id) LIMIT 20;