A post becomes popular and suddenly every interaction updates the same database row. Likes increment a total, comments adjust another field and views arrive far faster than either. The database may have plenty of overall capacity while requests queue behind a tiny piece of shared state.

The problem is concentration. Adding application servers sends more concurrent work towards the same row, and replacing a read-modify-write loop with an atomic increment fixes correctness without necessarily fixing contention. A scalable design must separate interaction truth from the numbers displayed beside a post.

Introduction

This article designs likes, comments and view counts for a social application with occasional viral posts. We will start with a correct relational design, then introduce partitioned counters, asynchronous aggregation, versioned updates and reconciliation where traffic requires them.

The focus is engagement storage and counting, rather than building a personalised feed. A feed may consume these counts as ranking signals, but its fan-out and candidate-selection architecture does not solve the write hotspot inside one popular post's counters.

The central question is which facts must be exact immediately. A user's like relationship should be reliable. A visible total may tolerate a short delay. A view estimate may be approximate by definition. Treating all three as the same kind of counter creates unnecessary cost and confusing behaviour.

Define the Meaning of Each Number

A like count usually represents active user-to-post relationships. If a user likes the same post twice because a request was retried, the total should still increase once. If they unlike it, the relationship disappears and the total decreases once.

A comment count might mean all comments, visible comments, top-level comments or comments including replies. Moderation and deletion change those sets differently. Define the public metric before choosing where to increment it.

A view count is even less obvious. Does opening a page count, or must the post remain visible for two seconds? Do repeated views count? Are bots excluded? Is the number unique viewers over a day, a month or the post's lifetime?

These are product contracts. A counter can be internally consistent and still be wrong because it counts a different event from the one users assume. Record the definition alongside the pipeline and version it if the definition changes.

Also identify numbers used for decisions. A displayed popularity total can lag, but an exact quota or reward threshold may require authoritative validation. Do not use an eventually consistent display counter to enforce a financial or access-control invariant.

Understand Why the Single Row Becomes Hot

A simple post table might contain like_count, comment_count and view_count. Every interaction updates that row. Even when the fields differ, the storage engine may need to coordinate concurrent changes to the same row and its indexes.

An atomic statement such as SET like_count = like_count + 1 avoids losing increments through an application-side read-modify-write race. PostgreSQL's UPDATE documentation describes the statement semantics. The row still represents a serial coordination point for those writes.

Suppose a viral post receives one million likes in a minute, roughly 16,700 per second, plus far more view events. That rate may overwhelm one counter key long before it overwhelms a well-partitioned database as a whole. The exact limit depends on engine, hardware, transaction cost and surrounding traffic.

Separate view, like and comment storage so high-volume telemetry does not delay a user's meaningful interaction. Then distribute the hot operation itself. Moving the same key into a faster store can postpone saturation, but it does not remove the concentration.

Store the Like Relationship as the Durable Fact

Represent a like with a unique key over post and user, including tenant or application scope if needed. The relationship table records who currently likes the post and when the relationship was created.

CREATE TABLE post_likes (
    post_id bigint NOT NULL,
    user_id bigint NOT NULL,
    created_at timestamptz NOT NULL,
    PRIMARY KEY (post_id, user_id)
);

The unique constraint makes repeated insertion of the same active relationship detectable. PostgreSQL's INSERT and ON CONFLICT documentation provides a concrete mechanism for handling that race without a separate “does this exist?” check.

The API should express the intended state, such as PUT /posts/{id}/like and DELETE /posts/{id}/like, rather than a generic toggle that changes state on every retry. Retrying “set liked” is easier to make safe than retrying “invert whatever state currently exists”.

Authorise the actor and post before mutation. A user identifier in the request body must not let a caller create likes on another person's behalf. Deleted or inaccessible posts also need a clear policy for new interactions.

At modest scale, count the relationship rows or maintain a transactionally updated aggregate. Keep this simpler design until measurements show that its contention or read cost is a real problem.

Increment Only When State Actually Changes

If the application maintains an aggregate, update it only when the underlying relationship changed. A duplicate like request should not increment the counter simply because the HTTP request reached the service twice.

In PostgreSQL, a transaction can insert the relationship with ON CONFLICT DO NOTHING, inspect whether a row was returned, and increment the aggregate only for a successful insertion. The relationship and aggregate change commit together when they share the transaction boundary.

For unlike, delete the relationship and decrement only if a row was removed. A second delete becomes a successful no-op according to the API contract. The count must not fall below zero because the service counted repeated requests instead of transitions.

If the aggregate lives in another system, use an outbox event recorded with the relationship change. The event describes the transition and carries a stable event identity or relationship revision. Publishing an increment after the database commit without durable dispatch leaves a failure gap.

Concurrent like and unlike commands for the same user-post pair need an ordering policy. If the interface sends a desired state with a client operation sequence or expected version, reject stale transitions. Otherwise, define the result by the server's committed order and ensure retries do not recreate an old intent accidentally.

A sharded counter represents one logical total with several physical buckets. Each write selects a bucket, and the total is the sum of all relevant buckets.

post:123:likes:g1:bucket:0   -> 8301
post:123:likes:g1:bucket:1   -> 8198
post:123:likes:g1:bucket:2   -> 8277
...
displayed total = aggregate of the current bucket set

With 128 evenly used buckets, the illustrative 16,700 likes per second becomes about 130 writes per second per bucket before retries and skew. This is arithmetic, not a throughput guarantee; validate the storage engine's behaviour under realistic load.

A stable hash of the user identifier can select the like bucket. Store or derive the same bucket when removing that relationship. If shard configuration changes, the unlike operation must still know which generation owns the original contribution.

AWS describes the general distribution technique in its DynamoDB write-sharding guidance. The same tradeoff appears elsewhere: spreading writes improves parallelism but increases the work required to read or aggregate the logical total.

If the relationship and bucket update must be atomic, keep them within a supported transaction boundary. Sharding them across unrelated databases requires a different consistency mechanism, not an assumption that two successful calls are equivalent to one commit.

Avoid Turning Every Read into a Scatter-Gather

Reading 128 buckets for each post on a feed page would exchange a write bottleneck for an expensive read path. A page with fifty posts could trigger thousands of bucket reads before returning any counts.

Maintain a compact materialised total for display. An aggregation worker periodically folds bucket state or committed deltas into a versioned total, and a cache serves that total to high-volume readers.

Coalesce frequent changes. If a post receives ten thousand likes in one second, readers rarely need ten thousand separate cache invalidations. Publish a bounded-rate total update, such as a few updates per second for the hottest posts, according to the product's freshness requirement.

Carry a version or aggregation position with the displayed total. A delayed update should not overwrite a newer cached value merely because it arrived later. Conditional cache writes or an ordered updater can enforce that boundary.

The total can decrease legitimately after unlikes or moderation, so “take the maximum number seen” is not a valid general merge rule. Order updates by their source or aggregation version, not by the numeric count.

Choose Between Deltas and State Snapshots

An event-driven counter often consumes +1 and -1 deltas. Deltas are compact, but each must affect the aggregate the intended number of times. Duplicate delivery of +1 is not harmless just because addition is atomic.

One consumer design stores the event identity and aggregate update in the same transaction. A repeated event then becomes a no-op. Another processes ordered partitions and commits a contiguous progress position with the corresponding aggregate state.

Deduplication retention matters. If event identifiers are discarded after a day but operators can replay a month's history, an old event may be counted again. Align the replay window, deduplication scheme and rebuild procedure explicitly.

State-based updates can simplify some paths. A worker can publish “bucket 7 is now 8,277 at revision 91” rather than “increment by one”. A newer revision replaces an older state without double-counting repeated delivery, provided revisions and bucket ownership are reliable.

Do not combine snapshots and deltas casually. Applying a delta already included in a snapshot double-counts it; replacing a newer delta-adjusted total with an older snapshot loses it. Every aggregation boundary needs a clear position or version that says what is included.

Scale Comments Through Their Lifecycle

Comments are durable content, not just a counter increment. Store the comment, parent relationship, author, moderation state and creation identity independently from the post's displayed count.

Use an idempotency key or stable client-generated comment identifier for retried submissions. Otherwise, a response lost after commit can cause the user to create two identical comments and increment the count twice legitimately from the database's perspective.

Define which state transitions affect the public count. Moving from pending moderation to visible may increment it; editing visible text should not. Moving from visible to removed decrements it once, while repeatedly processing the same removal event must not continue subtracting.

Replies complicate the definition. A post may display all visible comments while a thread displays only direct replies. Maintain separate projections with explicit names rather than overloading one comment_count field for several meanings.

Deleting a parent comment can leave replies visible, hide the whole subtree or replace the parent with a tombstone. Each policy changes counting and query behaviour differently. Model the transition first, then derive the appropriate counter changes from committed state.

Treat Views as an Ingestion Pipeline

View traffic can be orders of magnitude larger than likes. Sending every view directly to the post's primary database row makes a relatively low-value signal compete with durable user actions.

Collect eligible view events through an ingestion service or client batching mechanism, validate them and write them to a durable stream when the retention and accuracy requirements justify it. Aggregate by post and time window before updating display projections.

Assign event identities if repeated delivery must be deduplicated. A client retry after a timeout should not create a new event identity for the same observed view. The server still needs to limit abuse and reject malformed or implausible submissions.

Separate raw events from accepted views. Eligibility may depend on visibility duration, authenticated state, rate limits or bot detection. Keep enough processing metadata to explain why a displayed total differs from the number of raw requests received.

If the product deliberately uses an approximate count, say so in the internal contract and choose suitable display precision. Showing an exact-looking number does not make a sampled or filtered estimate exact.

Count Unique Viewers with the Right Tool

Counting total view events and counting distinct viewers are different problems. Exact distinct counts require retaining enough identity information to recognise repeats within the chosen window, which can be expensive for very large audiences.

Probabilistic structures such as HyperLogLog estimate cardinality with compact state. Redis's HyperLogLog documentation describes that approach and its accuracy characteristics.

Use the estimate for metrics that tolerate its error. It is unsuitable as an exact entitlement, billing or prize-allocation record. The product should also distinguish daily unique viewers from lifetime unique viewers; adding daily distinct counts double-counts people who return on multiple days.

Windowing needs a deliberate design. Separate structures per day can support daily estimates, while a union over the relevant structures can estimate a longer period. Removing an arbitrary individual from a sketch is not equivalent to deleting a row from an exact set.

Privacy requirements affect identity retention and replay. Hashing a user identifier does not automatically make the data anonymous or remove the need for a deletion policy. Keep raw identifiers and analytical state only for the purposes and durations the product requires.

Preserve Immediate User Feedback

When a user likes a post, the interface should show their own successful action promptly even if the global total is updated asynchronously. Return the authoritative liked state and operation version from the mutation endpoint.

The client can render the local state immediately and reconcile the total when a newer aggregate arrives. Avoid repeatedly applying a local +1 on top of a server total that already includes the user's like. Track whether the optimistic adjustment has been incorporated or replace it with the authoritative response state.

Read the user's like relationship separately from the public total. A cached count of 10,000 cannot answer whether this particular person liked the post. Batched relationship lookups can supply that information efficiently for a page of posts.

On a failed mutation, revert the local state and explain the failure appropriately. On an uncertain response, retry the idempotent desired-state operation or read current state. Do not blindly toggle again, because that can undo an operation that actually succeeded.

This design gives the user a responsive interface without forcing every viewer to observe a globally exact total at the same instant.

Grow and Rebalance Counter Shards Safely

Most posts never need hundreds of buckets. Start with a small layout and promote hot posts when measured contention or write rate justifies the additional aggregation cost.

Use a shard generation in the counter identity. New interactions can use a new generation while existing relationships retain the location of their prior contribution. The total includes all active generations until a controlled migration consolidates them.

Simply changing hash(user) % 16 to hash(user) % 128 breaks unlike accounting if the contribution remains in an old bucket. Either store the assigned bucket with the relationship or use a migration protocol that preserves ownership and prevents double counting.

During migration, define a boundary between copied state and subsequent changes. Versioned snapshots, paused per-key transitions or a durable change position can provide that boundary. Do not copy buckets and continue applying all old deltas to both generations without tracking overlap.

Keep promotion hysteresis so a post does not continually switch layouts as traffic fluctuates around a threshold. Retaining a larger layout briefly after a viral burst is often simpler than immediately consolidating it.

Reconcile Totals from Durable Facts

Aggregates are derived data and should be repairable. For likes, the active relationship set can reconstruct the total. For visible comments, the comment state defines membership in the count. Views need their documented event or sketch reconstruction policy.

Run reconciliation in bounded partitions and compare expected totals with materialised values at a known boundary. A live count scan performed while interactions continue may differ simply because the observations cover different moments.

A safe rebuild can use a consistent snapshot plus subsequent change replay, or another explicit cutover protocol. Publish the repaired total with a new aggregation version so old queued updates cannot overwrite it.

Investigate repeated discrepancies. A repair job that corrects the same post every hour may be masking a missing unlike event, duplicate comment transition or faulty migration. Record discrepancy size and cause rather than reporting only that the repair succeeded.

Do not repair a negative counter by clamping it to zero and forgetting the cause. That hides the accounting error while leaving later updates unreliable. Preserve evidence, rebuild from the correct source and fix the transition that created the mismatch.

Protect Against Abuse and Noisy Neighbours

A scalable counter system can count fraudulent activity very efficiently. Rate-limit interaction endpoints by appropriate actor and resource dimensions, validate eligibility and separate suspicious traffic from accepted engagement.

Per-post and per-tenant budgets prevent one viral or abusive resource from exhausting shared workers. Fair scheduling is useful when a huge view backlog could delay ordinary like and comment updates for everyone else.

Do not use a single global rate-limit counter that recreates the hotspot the engagement system was designed to avoid. Apply limits at meaningful distributed boundaries with documented approximation where appropriate.

Keep public counts and fraud-analysis signals separate. Retroactive removal of invalid views may legitimately reduce a total, and that operation needs a versioned correction path. An append-only assumption that totals can never decrease makes later moderation difficult.

Operational tools that replay or adjust engagement should record scope, reason and before-and-after versions. A bulk repair should not be indistinguishable from ordinary user activity in audit and analytics data.

Test a Viral Spike and Interrupted Processing

Load-test one hot post as well as traffic spread across millions of posts. A uniform benchmark can hide the exact skew that causes production contention. Measure write latency, lock or partition pressure, queue age and displayed-count freshness.

Repeat like and unlike requests, reorder events, crash an aggregator after committing but before acknowledging, and replay old batches. The final relationship state and aggregate should converge to the intended result.

Test moderation transitions and a counter-generation migration while traffic continues. Verify that old cache updates cannot overwrite newer totals and that user-specific liked state remains accurate during aggregate lag.

Measure recovery after the spike ends. A system that accepts a burst but requires hours to restore useful freshness may not meet the product's needs. The arrival rate must fall below effective processing capacity for the backlog to shrink.

Use the results to tune shard count, batching, admission and freshness objectives. Adding capacity is useful only when it addresses the measured constrained resource.

Walk Through a Like, Retry and Unlike

Maya likes a post while its aggregate shows 52,000. The API inserts her relationship and records a corresponding change event in one transaction. The response is lost, so her client retries the same desired-state operation.

The unique relationship key prevents the retry from creating another active like. The API returns the current liked state. Only the original committed transition produces an increment event, and the event's stable identity prevents a repeated delivery from affecting the aggregate twice.

Before the aggregator catches up, Maya changes her mind and unlikes the post. The delete commits and records a second transition. Her interface immediately shows the authoritative unliked state, while the displayed global count may briefly reflect an older aggregate.

If the two events arrive out of order, the aggregate design must still converge. A pure commutative delta sum can tolerate their order if each is applied exactly once to the same accounting scope. A projection that tracks relationship state instead must compare relationship revisions and reject stale states. Mixing these approaches without an explicit rule can apply the unlike and then incorrectly restore the old like.

Now imagine the increment was applied, the worker crashed before acknowledgement, and both events are replayed. Transactional deduplication or a correctly committed partition frontier prevents the original increment from being applied again. The final total excludes Maya's relationship, regardless of how many delivery attempts occurred.

This history exposes the real correctness requirement. The system counts committed state transitions, not requests, delivery attempts or button presses.

Publish Realtime Counts at a Controlled Rate

A live page may have hundreds of thousands of viewers watching the same post. Broadcasting every individual increment can create more network work than storing the interactions themselves.

Publish a versioned total periodically and let clients replace their last observed aggregate when the version is newer. A message can contain the post identifier, count values, aggregation revision and timestamp. Multiple interactions within the interval collapse into one update.

Use a subscription layer that can fan out efficiently and apply its own backpressure. Slow clients should skip intermediate totals and receive the latest useful state, rather than buffering every count update indefinitely.

On reconnect, fetch a current snapshot and resume updates from a known revision where supported. Count displays usually do not need every intermediate value, so replaying thousands of missed updates is wasted work. The current total and its freshness are what matter.

Keep comments themselves on a separate event path when users need to see each new message. Dropping intermediate count updates can be harmless; dropping actual comment content may violate the product's expected conversation behaviour. Similar-looking realtime data can require different delivery contracts.

Distinguish Popularity Signals from Exact Accounting

A ranking system may need a rapidly changing measure of engagement velocity rather than a perfectly current lifetime total. Compute that signal from suitable time windows and record its own definition instead of repeatedly querying the exact like relationships.

For example, a post's recent engagement score might weight eligible comments and likes over the last fifteen minutes while discounting suspected automation. That score can be approximate and periodically refreshed because it influences candidate ordering, not a user's ownership of an interaction.

Do not write the ranking score back into the displayed count field. Different consumers need different projections, and naming those projections clearly prevents an optimisation in feed ranking from changing public accounting accidentally.

If a milestone triggers a reward, validate the qualifying condition against the authoritative rule at the point of granting it. A cached display passing one million views is useful for presentation but may include estimates, delayed corrections or activity that later fails eligibility checks.

Keeping these boundaries separate lets the application optimise each workload honestly. Durable relationships support user expectations, aggregates support fast display, and analytical projections support discovery and experimentation.

Review those definitions when the product changes its interaction model. Adding reactions, private comments or autoplay video views can invalidate earlier assumptions about uniqueness and eligibility. A migration should identify how historical counts map to the new definition, rather than silently combining incompatible measurements under the same label.

Summary

Scaling engagement starts by separating durable interaction facts from displayed aggregates. Unique like relationships, explicit comment state transitions and clearly defined view eligibility provide the source from which correct counts can be derived.

Distribute hot writes with counter shards, aggregate at a bounded rate and serve versioned totals from a read-optimised path. Handle duplicates, out-of-order updates, deletion, shard migration and reconciliation deliberately.

The result can give each user reliable immediate feedback while allowing global counts to converge within a useful freshness window. Exactness belongs where the business needs it; approximation and delayed aggregation belong where their tradeoffs are explicit and acceptable.