A poll system lets users create questions with multiple choice answers, vote on them, and see real-time results. Used in social networks, news sites, Slack, and internal tools. Core challenges: preventing duplicate votes, displaying real-time results efficiently without individual-query fan-out, handling anonymous vs authenticated voting, setting expiry on polls, and showing percentage results without floating-point inconsistency.
Core Data Model
CREATE TABLE Poll (
poll_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
creator_id UUID NOT NULL,
question TEXT NOT NULL,
allow_multiple BOOLEAN NOT NULL DEFAULT FALSE, -- multi-select vs single-choice
is_anonymous BOOLEAN NOT NULL DEFAULT FALSE, -- hide voter identities
total_votes INT NOT NULL DEFAULT 0, -- denormalized for fast %
status TEXT NOT NULL DEFAULT 'open', -- 'open', 'closed'
closes_at TIMESTAMPTZ, -- NULL = never expires
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE PollOption (
option_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
poll_id UUID NOT NULL REFERENCES Poll(poll_id) ON DELETE CASCADE,
text TEXT NOT NULL,
vote_count INT NOT NULL DEFAULT 0, -- denormalized per-option count
sort_order INT NOT NULL DEFAULT 0,
UNIQUE (poll_id, text)
);
CREATE INDEX idx_option_poll ON PollOption (poll_id, sort_order);
-- Voter record: enforces one vote per user per poll (or option for multi-select)
CREATE TABLE PollVote (
vote_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
poll_id UUID NOT NULL REFERENCES Poll(poll_id),
option_id UUID NOT NULL REFERENCES PollOption(option_id),
voter_id UUID, -- NULL for anonymous votes
voter_token TEXT, -- fingerprint for anonymous (cookie/IP hash)
voted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (poll_id, voter_id) -- one vote per user per poll (single-choice)
-- For multi-select: UNIQUE (poll_id, option_id, voter_id)
);
CREATE INDEX idx_vote_poll ON PollVote (poll_id, option_id);
Casting a Vote
from uuid import uuid4
from datetime import datetime, timezone
import psycopg2
def cast_vote(conn, poll_id: str, option_id: str, voter_id: str | None,
voter_token: str | None = None) -> dict:
"""
Cast a vote. Prevents duplicate votes.
Atomically increments option vote_count and poll total_votes.
Returns current poll results.
"""
now = datetime.now(timezone.utc)
with conn.cursor() as cur:
# Verify poll is open and option belongs to poll
cur.execute("""
SELECT p.status, p.closes_at, p.allow_multiple, o.option_id
FROM Poll p
JOIN PollOption o ON o.poll_id = p.poll_id AND o.option_id = %s
WHERE p.poll_id = %s
FOR UPDATE OF p
""", (option_id, poll_id))
row = cur.fetchone()
if not row:
raise ValueError("Poll or option not found")
status, closes_at, allow_multiple, _ = row
if status != 'open' or (closes_at and now > closes_at):
raise ValueError("This poll is closed")
# Insert vote — UNIQUE constraint prevents duplicate
try:
with conn.cursor() as cur:
cur.execute("""
INSERT INTO PollVote (vote_id, poll_id, option_id, voter_id, voter_token)
VALUES (%s, %s, %s, %s, %s)
""", (str(uuid4()), poll_id, option_id, voter_id, voter_token))
# Atomic increment
cur.execute(
"UPDATE PollOption SET vote_count = vote_count + 1 WHERE option_id = %s",
(option_id,)
)
cur.execute(
"UPDATE Poll SET total_votes = total_votes + 1 WHERE poll_id = %s",
(poll_id,)
)
conn.commit()
except psycopg2.errors.UniqueViolation:
conn.rollback()
raise ValueError("You have already voted on this poll")
return get_poll_results(conn, poll_id)
def get_poll_results(conn, poll_id: str) -> dict:
"""
Return poll options with vote counts and percentages.
Uses denormalized counts for O(1) percentage calculation.
"""
with conn.cursor() as cur:
cur.execute("""
SELECT p.poll_id, p.question, p.total_votes, p.status, p.closes_at,
o.option_id, o.text, o.vote_count, o.sort_order
FROM Poll p
JOIN PollOption o ON p.poll_id = o.poll_id
WHERE p.poll_id = %s
ORDER BY o.sort_order
""", (poll_id,))
rows = cur.fetchall()
if not rows:
return {}
total_votes = rows[0][2]
return {
"poll_id": poll_id,
"question": rows[0][1],
"total_votes": total_votes,
"status": rows[0][3],
"closes_at": rows[0][4].isoformat() if rows[0][4] else None,
"options": [
{
"option_id": str(r[5]),
"text": r[6],
"vote_count": r[7],
"percentage": round(100 * r[7] / total_votes, 1) if total_votes > 0 else 0
}
for r in rows
]
}
Real-Time Results via WebSocket
import redis, json
r = redis.Redis(host='redis', decode_responses=True)
def broadcast_vote_update(poll_id: str, results: dict):
"""Push updated results to all clients watching this poll."""
r.publish(f"poll:updates:{poll_id}", json.dumps(results))
# WebSocket handler — subscribe to poll updates channel
async def watch_poll_results(websocket, poll_id: str):
"""Stream real-time vote count updates to the client."""
pubsub = r.pubsub()
await pubsub.subscribe(f"poll:updates:{poll_id}")
async for message in pubsub.listen():
if message["type"] == "message":
await websocket.send(message["data"])
def close_expired_polls(conn):
"""Cron job: close polls whose closes_at has passed."""
with conn.cursor() as cur:
cur.execute("""
UPDATE Poll SET status = 'closed'
WHERE status = 'open' AND closes_at < NOW()
RETURNING poll_id
""")
closed = [row[0] for row in cur.fetchall()]
conn.commit()
for poll_id in closed:
# Broadcast final results
results = get_poll_results(conn, poll_id)
broadcast_vote_update(poll_id, {**results, "final": True})
Key Interview Points
- Unique constraint vs application-level dedup: Application-level duplicate check (SELECT first, then INSERT) has a race condition — two concurrent votes can both pass the SELECT check and both INSERT. The UNIQUE(poll_id, voter_id) constraint enforces exactly one vote at the database level. Catch UniqueViolation in the application and return a friendly error. For anonymous polls using voter_token: UNIQUE(poll_id, voter_token) — IP/cookie fingerprint deduplication is weaker but sufficient for casual polls.
- Denormalized vote_count: Computing percentages via COUNT(*) GROUP BY option_id on every results request is expensive under high read load. Atomic UPDATE vote_count = vote_count + 1 at vote time keeps counts current with no extra reads. The denormalized count is safe because both the PollVote insert and the vote_count increment are in the same transaction — no inconsistency possible. Recovery: if corruption is suspected, recompute: UPDATE PollOption SET vote_count = (SELECT COUNT(*) FROM PollVote WHERE option_id = PollOption.option_id).
- Percentage integer math: Calculate percentage server-side with rounding (round to 1 decimal). Sum of percentages may not equal exactly 100% due to rounding — this is acceptable and expected (show “99.9%” or “100.1%” when summed). Don’t adjust individual percentages to force sum = 100% — that’s data manipulation. Display context: “Based on 1,234 votes.”
- Redis caching for high-read polls: A viral poll may get 100K concurrent viewers refreshing results. Cache the results JSON in Redis with a 5-second TTL: SET poll:results:{poll_id} $json EX 5. This reduces DB queries from 100K/5s to 1/5s. On each vote, update the cache immediately (so the voter sees their vote reflected). For real-time streaming clients, Redis pub/sub pushes updates without polling — each subscriber gets notified within milliseconds of a new vote.
- Multi-select polls: Change the UNIQUE constraint to (poll_id, option_id, voter_id) — a user can vote for multiple options but not the same option twice. Remove the Poll.total_votes field (ambiguous for multi-select). Instead, report “X people voted on this poll” by counting DISTINCT voter_id. Results show “70% of voters chose Option A” (70% of 500 voters = 350 people chose A, not 70% of total votes cast).
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How does the UNIQUE constraint prevent duplicate votes more reliably than application code?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Application-level duplicate prevention (SELECT first, INSERT if not found) has a race condition: two concurrent vote requests can both execute the SELECT and find no record, both proceed to INSERT, and both votes are recorded — a duplicate vote. The database UNIQUE(poll_id, voter_id) constraint prevents this at the storage level: the constraint is enforced atomically during the INSERT. Even under the highest concurrency, only one INSERT per (poll_id, voter_id) pair can succeed. The second INSERT raises a UniqueViolation exception, which the application catches and converts to a user-facing error message ("You have already voted"). No distributed locks, no coordination between application servers, no race condition possible — the database serializes it.”}},{“@type”:”Question”,”name”:”Why use denormalized vote_count rather than COUNT(*) for real-time percentages?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”At high vote velocity (a viral poll with 10,000 votes/minute), COUNT(*) FROM PollVote WHERE option_id=X is expensive under concurrent load — it creates contention on the Polls table. With 4 options and 1,000 concurrent readers, that is 4,000 COUNT queries/minute — each needs a table scan or index scan. Denormalized vote_count: each vote atomically increments the counter (UPDATE PollOption SET vote_count = vote_count + 1) in the same transaction as the INSERT. Reads are O(1): SELECT vote_count FROM PollOption WHERE option_id = X. The tradeoff: if the server crashes between the PollVote INSERT and the vote_count UPDATE (partial transaction), counts diverge. Solution: always do both in a single transaction — they succeed or fail together.”}},{“@type”:”Question”,”name”:”How do you show real-time vote updates to thousands of concurrent poll viewers?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Long-polling approach: clients poll GET /polls/{id}/results every 5 seconds — simple but creates 1000 requests/5s for 1000 concurrent viewers. WebSocket approach: clients subscribe to a WebSocket channel (poll:{poll_id}). On each vote, the server publishes to Redis pub/sub channel (poll:updates:{poll_id}) with the new vote counts. All WebSocket servers subscribed to that channel push the update to their connected clients. Latency: <100ms from vote to update. Scale: a Redis pub/sub channel can handle 100K messages/second. For polls with >50K concurrent viewers: broadcast the aggregated result (total counts) rather than individual vote events — reduces message size and fan-out work. Rate-limit broadcasts to one update per second to avoid flooding clients during vote bursts.”}},{“@type”:”Question”,”name”:”How do you implement anonymous polls while still preventing duplicate votes?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Anonymous polls hide voter identity but still need to prevent one user from voting multiple times. Two approaches: (1) Authenticated anonymous: the user is logged in, but voter identities are hidden from other users and even from poll creators. Store voter_id in PollVote but never expose it in the API. The UNIQUE(poll_id, voter_id) constraint prevents duplicates. (2) Truly unauthenticated: use a browser fingerprint (cookie ID + IP hash) as voter_token. UNIQUE(poll_id, voter_token) prevents duplicates per device. This is weaker — a determined user can clear cookies or use a VPN. Accept this limitation for casual polls. Never claim "truly anonymous" for compliance-sensitive polls (exit polls, employee surveys) — use authenticated anonymous instead.”}},{“@type”:”Question”,”name”:”How do you close a poll and ensure no race condition on the last vote?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Two operations must be consistent: the poll closing (UPDATE Poll SET status=’closed’) and the last votes being recorded. If the close and a vote happen concurrently, the FOR UPDATE lock on Poll in cast_vote() serializes them: the vote transaction holds the lock, the close waits. Or the close acquires the lock first, setting status=’closed’; the vote transaction reads status=’closed’ and raises "poll is closed." No race condition — the poll lock in cast_vote() ensures the status check and vote recording are atomic for each vote. Auto-close by worker: UPDATE Poll SET status=’closed’ WHERE status=’open’ AND closes_at <= NOW(). After closing, broadcast final results with a final=True flag so WebSocket clients can render the final state and close their subscription.”}}]}
Poll and voting system design is discussed in Twitter system design interview questions.
Poll and interactive content system design is covered in Snap system design interview preparation.
Poll and survey system design is discussed in LinkedIn system design interview guide.
See also: Meta Interview Guide 2026: Facebook, Instagram, WhatsApp Engineering