Ad Click Aggregator — Naive (Single Service + SQL)

Ad click aggregation is a deceptively simple problem that hides enormous scale challenges. At its core, the requirement is straightforward: when a user clicks an ad on a publisher website, record that click and aggregate counts per campaign so advertisers can see how their ads are performing. The naive approach — a single service writing directly to a SQL database — works fine for a prototype, but it fails spectacularly at production scale for reasons that are deeply instructive.

The fundamental tension in ad click aggregation is between write throughput and query freshness. Advertisers want real-time dashboards showing click counts per campaign, per ad, per hour. Publishers want to know which ads are generating revenue. And the billing system needs exact click counts — not approximate, not eventually consistent, but precisely correct — because every click translates to money changing hands. A single miscounted click across millions of campaigns can result in billing disputes, lost advertiser trust, and regulatory issues.

The naive architecture uses synchronous writes: every click event triggers an INSERT into PostgreSQL. The dashboard reads aggregate counts via GROUP BY queries on the same table. This means every read competes with every write for database resources. At 100 clicks per second, this works fine — PostgreSQL handles the mixed workload comfortably. At 1,000 clicks per second, write latency starts climbing as the WAL (write-ahead log) becomes a bottleneck. At 10,000 clicks per second, the system falls over: the connection pool is exhausted, GROUP BY queries take seconds instead of milliseconds, and the write path starts dropping clicks.

This template exists to make these failure modes visible and measurable. By running the simulation at increasing traffic levels, you can observe exactly when the database becomes the bottleneck, how write amplification from indexes degrades INSERT performance, and why the synchronous write path creates a hard ceiling on throughput. The comparison with stream processing and lambda architecture variants quantifies the dramatic improvement that async ingestion provides.

Ad click aggregation appears frequently in system design interviews at Google, Meta, Amazon, Twitter, and LinkedIn — any company with an advertising platform. Interviewers expect candidates to identify the naive approach's limitations, propose async ingestion via message queues, discuss deduplication strategies for click fraud prevention, and reason about consistency models for billing accuracy. This template provides the concrete simulation data to support those discussions.

The naive ad click aggregator is a linear four-component architecture: Client, Load Balancer, Click Service, and PostgreSQL database. There is no message queue, no cache, no stream processor, and no separation between the write path and the read path.

All click events arrive at the Load Balancer, which distributes them across Click Service pods using round-robin. The Load Balancer adds approximately 1.5ms of routing latency and acts as the single entry point. It handles up to 5,000 RPS before becoming a bottleneck itself, but the database will saturate long before the load balancer does.

The Click Service is a stateless API server running multiple pods with configurable thread pools. Each click event triggers two operations: (1) an INSERT into the clicks table with the click metadata (campaign_id, ad_id, user_id, timestamp, publisher_id, ip_address), and (2) an UPDATE to the campaign_aggregates table incrementing the click count for the relevant campaign. Both operations happen synchronously within the same request — the service does not return a 200 OK until both the INSERT and UPDATE have been committed to the database.

This synchronous dual-write pattern is the root cause of the architecture's scaling limitations. Each click event requires two round trips to PostgreSQL: one for the raw event INSERT (~10ms) and one for the aggregate UPDATE (~15ms with row-level locking). At 1,000 RPS, the service spends 25ms per click on database operations alone, requiring at least 25 concurrent threads just to keep up — not counting CPU processing time for request parsing, validation, and response serialization.

PostgreSQL stores two tables. The clicks table receives every raw click event with a B-tree index on (campaign_id, created_at) for time-range queries. As the table grows, INSERT performance degrades because each new row requires updating the index. The campaign_aggregates table maintains running totals with a row-level lock on UPDATE to prevent lost updates from concurrent increments. Under high concurrency, this row-level locking creates contention — multiple threads waiting to increment the same campaign's counter.

Dashboard queries run directly against the same database instance. A typical query aggregates clicks by campaign and hour using GROUP BY with a WHERE clause on the time range. At 1 million rows, this query takes approximately 50ms. At 100 million rows, it takes several seconds — competing with the write path for I/O bandwidth and buffer pool memory. There is no materialized view, no pre-computed aggregate, and no read replica to offload query traffic.

The architecture has zero redundancy at the data layer. A single PostgreSQL instance handles all reads and writes. If the database fails, both the write path (click ingestion) and the read path (dashboard queries) go down simultaneously. There is no deduplication mechanism — if a user's browser retries a click request (network timeout, double-click), the system records two clicks for one actual interaction. This is a billing accuracy risk that more sophisticated architectures address with idempotency keys or stream-level deduplication.

Vertical scaling for PostgreSQL: db.r7g.large (500 RPS ceiling) → db.r7g.xlarge (~1K RPS) → db.r7g.2xlarge (~1.5K RPS, diminishing returns). Horizontal scaling for Click Service: 2 → 4 → 8 pods via ECS auto-scaling (CPU >70% for 3 minutes). The database is always the bottleneck — adding more service pods only helps until the connection pool is saturated. Read replicas can offload dashboard queries but do not help the write path. Hard ceiling: ~1.5K RPS with maximum vertical scaling. Beyond this, architectural changes are required (Kafka buffering, async writes) — see V1.

Key metrics: (1) PostgreSQL active connections — alert at 70% of max_connections (~140/200), critical at 85%. (2) Click ingestion p99 latency — alert if >100ms sustained for 5 minutes (normal is ~25ms). (3) Write throughput (clicks/sec) — alert if >400 RPS (80% of the ~500 ceiling). (4) campaign_aggregates row lock wait time — alert if p95 >30ms (indicates hot-campaign contention). (5) Dashboard query latency — alert if p99 >500ms. (6) Disk I/O utilization — alert at 75% (WAL write saturation precedes connection pool issues). Dashboard: Grafana with panels for live RPS, DB connection pool gauge, write latency heatmap, lock contention per campaign, and disk IOPS. SLIs: click ingestion p99 <100ms, dashboard p99 <500ms, error rate <0.1%.

At ~500 RPS target: PostgreSQL RDS db.r7g.large ($185/month), 500 GB gp3 storage ($45/month), ECS Fargate Click Service 2 pods ($130/month), Application Load Balancer ($25/month). Total: ~$385/month. Cost per million clicks: ~$0.30. This is the cheapest variant but has a hard ceiling at ~500 RPS. Scaling to db.r7g.xlarge ($370/month) extends the ceiling to ~1K RPS but doubles the DB cost. Beyond 1K RPS, vertical scaling has diminishing returns — the V1 Stream variant at 50K RPS costs ~$2,200/month but handles 100x more traffic, reducing cost per million clicks to ~$0.017.

Authentication: API key validation on every /click request (~1ms overhead). Rate limiting: per-IP rate limit of 100 clicks/minute at the Load Balancer to prevent basic bot attacks. Click fraud prevention: limited to IP-based heuristics — no deduplication, no click_id tracking, no device fingerprinting. IP hashing: store SHA-256 hash of IP addresses instead of raw IPs to comply with GDPR pseudonymization requirements. Data privacy: click data contains user_id and hashed IP — implement 90-day retention policy with automated deletion for GDPR Article 17 compliance. TLS termination at the Load Balancer ensures in-transit encryption.

Rolling deployment for the Click Service — replace one pod at a time while the ALB drains connections from the old pod (30-second deregistration delay). Database schema migrations run during low-traffic windows (2-4 AM) using pg_migrate with transactional DDL. Rollback: revert the Click Service to the previous container image via ECS task definition update (~60 seconds). No blue-green needed at this scale — the simplicity of rolling deployments matches the simplicity of the architecture.