Building a View Counter System With Postgres

Context

I needed a page view counter for a content site. Requirements: track views per page, deduplicate by session, serve counts quickly, and handle traffic spikes without losing data. No third-party analytics, no Redis dependency. Just Postgres.

Problem

Naive view counters (UPDATE posts SET views = views + 1) break under concurrent writes. They double-count refreshes. They create hot rows with lock contention. And querying aggregate counts from a large events table is slow without pre-computation.

Constraints

Single Postgres instance (Neon, 4 vCPU, 16GB RAM)
Expected traffic: 10,000-50,000 views/day, with spikes to 200,000 during viral posts
Session deduplication: same visitor viewing the same page within 30 minutes counts as one view
Read latency for displaying count: under 50ms
Write latency for recording a view: under 100ms, but data loss for a few views during spikes is acceptable
No external caching layer

Design

Schema

CREATE TABLE page_views (
  id BIGSERIAL PRIMARY KEY,
  page_slug TEXT NOT NULL,
  session_id TEXT NOT NULL,
  viewed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE INDEX idx_page_views_slug ON page_views (page_slug);
CREATE INDEX idx_page_views_dedup ON page_views (page_slug, session_id, viewed_at);
 
CREATE TABLE page_view_counts (
  page_slug TEXT PRIMARY KEY,
  view_count BIGINT NOT NULL DEFAULT 0,
  last_updated TIMESTAMPTZ NOT NULL DEFAULT now()
);

Two tables: page_views for raw events, page_view_counts for pre-aggregated counts.

Write Path

-- Check for duplicate within 30-minute window
INSERT INTO page_views (page_slug, session_id)
SELECT $1, $2
WHERE NOT EXISTS (
  SELECT 1 FROM page_views
  WHERE page_slug = $1
    AND session_id = $2
    AND viewed_at > now() - interval '30 minutes'
);
 
-- If a row was inserted, increment the counter
UPDATE page_view_counts
SET view_count = view_count + 1, last_updated = now()
WHERE page_slug = $1;

These two operations run in a single transaction. The NOT EXISTS subquery handles deduplication.

Read Path

SELECT view_count FROM page_view_counts WHERE page_slug = $1;

Single row lookup on a primary key. Sub-millisecond.

Trade-offs

Approach	Writes/sec	Read Latency	Accuracy	Complexity
Single counter row (naive)	~500 (lock contention)	under 1ms	No dedup	Low
Event table + COUNT(*)	~2,000	50-200ms at scale	With dedup	Low
Event table + materialized count	~2,000	under 1ms	With dedup	Medium
Batch insert + periodic rollup	~10,000+	under 1ms (slightly stale)	With dedup	High

I chose the third approach for the base implementation: event table with a materialized count table updated synchronously. For the spike scenario, I added an async batch variant.

Batch Variant for Spikes

During high traffic, synchronous dedup checks become the bottleneck. The batch variant:

Writes raw events to an unlogged table (no WAL overhead)
A background worker runs every 10 seconds, deduplicates, and flushes to the main tables
Counter display lags by up to 10 seconds

CREATE UNLOGGED TABLE page_views_buffer (
  page_slug TEXT NOT NULL,
  session_id TEXT NOT NULL,
  buffered_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The flush query:

WITH new_views AS (
  DELETE FROM page_views_buffer RETURNING *
),
deduped AS (
  SELECT DISTINCT ON (page_slug, session_id) *
  FROM new_views nv
  WHERE NOT EXISTS (
    SELECT 1 FROM page_views pv
    WHERE pv.page_slug = nv.page_slug
      AND pv.session_id = nv.session_id
      AND pv.viewed_at > now() - interval '30 minutes'
  )
)
INSERT INTO page_views (page_slug, session_id, viewed_at)
SELECT page_slug, session_id, buffered_at FROM deduped;

Failure Modes

Hot row contention on page_view_counts: The UPDATE ... SET view_count = view_count + 1 creates a hot row for viral posts. Under 500 concurrent writes/second to the same slug, I measured 15% of transactions waiting on row locks. Mitigation: use the batch variant or shard counters across multiple rows and sum on read.

Buffer data loss: The unlogged buffer table does not survive a Postgres crash. If the database restarts, buffered views are lost. For a view counter, this is acceptable. For billing or compliance, it is not.

Session ID collision: If session IDs are generated client-side (e.g., random UUIDs in cookies), collisions are negligible. But if they are derived from IP + User-Agent hashing, shared networks and identical devices will under-count.

Dedup index bloat: The idx_page_views_dedup index grows with every view event. Without pruning, it slows down the NOT EXISTS check. A nightly job to delete events older than 24 hours keeps the index performant:

DELETE FROM page_views WHERE viewed_at < now() - interval '24 hours';

Scaling Considerations

At 1M views/day, the page_views table grows by ~30M rows/month before pruning. With the 24-hour retention, it stays under 1M rows.
Partitioning page_views by date (monthly) enables efficient bulk deletes and keeps query performance stable.
For multi-region deployments, each region can maintain its own counter and a periodic job can reconcile totals. Eventual consistency is fine for view counts.
At 10M+ views/day, consider moving writes to a queue (SQS, Kafka) and processing asynchronously. Postgres should not be the write buffer at that scale.

Observability

Track pg_stat_user_tables.n_tup_ins for the buffer table to monitor ingestion rate
Alert on buffer table row count exceeding 10,000 (flush is falling behind)
Monitor pg_stat_activity for lock waits on the page_view_counts table
Log flush duration and deduplication ratio (what percentage of buffered views were duplicates)
Dashboard the counter drift: compare SUM from page_views against page_view_counts to detect desync

Key Takeaways

Separate the write-optimized event log from the read-optimized counter. Never scan an event table to serve a count in real time.
Deduplication at write time is cheaper than deduplication at read time, but it adds contention. Batch deduplication is the pragmatic middle ground.
Unlogged tables are a legitimate tool for ephemeral buffers where crash-safety is not required.
Hot row contention is the primary scaling bottleneck. Sharded counters or async batching are the two escape hatches.
Prune aggressively. A view counter does not need a permanent event log.

Final Thoughts

This system handled 200,000 views/day on a single Postgres instance with the batch variant, maintaining sub-millisecond read latency and sub-10-second counter freshness. The total infrastructure cost was zero beyond the existing database. For most content sites, this is more than sufficient. When it stops being sufficient, the migration path to Redis or a dedicated analytics pipeline is straightforward because the schema separates events from aggregates cleanly.