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).
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