Build a survey platform: creators design forms and share a link, respondents answer by the millions, and results aggregate into a live dashboard. It is a system with two personalities - a write-optimized ingestion pipe and a read-optimized analytics engine - joined in the middle.
A survey platform looks like a CRUD app until you notice it is two systems wearing one product. The first system is an ingestion pipe: accept millions of responses, some days a hundred million, with spikes when a survey goes viral - and never lose one. A lost response is invisible (the creator never knows it existed) and a duplicated one silently corrupts the results, so the write path needs durability and deduplication as first-class features, not afterthoughts. The second system is an analytics engine: a creator opens a dashboard on a survey with two million responses and expects per-question distributions in under two seconds. That is not "read a row" - it is "aggregate thirty million answers, grouped and filtered," a completely different shape of work. These two personalities want opposite storage designs - one wants fast, safe, row-at-a-time writes; the other wants massive columnar scans - and the heart of this design is refusing to make one store do both jobs. Everything else on this page hangs off that split.
The dedupe requirement has a twist the login-based designs in this set never face: respondents are anonymous. There is no user_id to hang uniqueness on - just a browser that loaded a form. So we mint one: on first load, the form gets a respondent token (stored in the browser), and the final submit carries an idempotency key derived from it. Retries, double-clicks, and back-button resubmits all carry the same key and collapse into one stored response - the accidental duplicates, which are the overwhelming majority, are dead. Be honest about the limit, because interviewers probe it: a token identifies a browser, not a human. Incognito mode, a second device, or clearing storage yields a fresh token and a second response. True one-response-per-person is impossible without authentication (or invasive fingerprinting, which is a privacy decision, not a technical one). What the product actually promises is: no duplicates from accidents and retries, plus optional friction (one-per-email, one-per-invite-link) for surveys that need more. Scoping the guarantee honestly - accidents prevented, determined adversaries not - is the senior answer here, and the same "what does the promise actually cover" discipline as the delivery guarantees in Design WhatsApp.
Look at what the two halves of the system ask of the same responses table. Ingestion asks row-shaped questions: write THIS response, check THIS idempotency key, fetch THIS response. Row-oriented transactional databases (PostgreSQL) are built for exactly this - constraints for dedupe, transactions for durability, and 320/sec average (10K/sec bursts) of 2KB writes is comfortable territory. Analytics asks column-shaped questions: the average of question 5 across two million responses; the distribution of question 2 filtered by date. A row store answers that by reading every full row and throwing away 95% of each - millions of rows, all columns, to aggregate one field. A columnar store (ClickHouse) lays data out the other way: all values of one column stored together, so the same query reads only the needed columns, and survey data compresses brutally well besides (a column holding "Yes/No/Maybe" two million times shrinks 10-100x). That is where the dashboard's 100x speedup comes from - not magic, just layout matched to the question. So responses live twice, on purpose: PostgreSQL as the source of truth (row-shaped, constrained, durable) and ClickHouse as a derived, rebuildable copy (column-shaped, aggregation-fast), connected by an event stream that lags by seconds. In front of both, Redis holds pre-aggregated live counters for the dashboard tiles everyone actually stares at, updated as responses flow - the Design a Newsfeed buffered-counter pattern working a different job. And the judgment flip, as always: at small scale - thousands of responses per survey - PostgreSQL with a few indexes does both jobs fine, and the second store plus its sync pipeline would be pure operational cost. The split earns its keep only when dashboards start scanning millions of rows. Know which side of that line the numbers put you on.
BUILD + PUBLISH
[Creator] --> [Builder Service] --> [PostgreSQL: survey definitions,
| versioned, immutable once published]
+--publish--> definition JSON cached at [CDN] (by version)
COLLECT - the write path (protect this at all costs)
[Respondent] --form shell + definition--> [CDN]
| partial saves (respondent token) | final submit
v v (+ idempotency key)
[Progress buffer (Redis, TTL)] [Ingestion Service]
| validate against definition
| dedupe (unique constraint)
v
[PostgreSQL: responses = TRUTH]
| (same transaction: outbox row)
v
[Event Stream (Kafka)]
| |
v v
[ClickHouse loader] [Live-stats aggregator]
| |
v v
[ClickHouse (columnar, [Redis: per-question
derived, rebuildable)] counters, funnels]
ANALYZE - the read path
[Creator dashboard] --live tiles-----------> [Redis pre-aggregates]
--filtered deep queries-> [ClickHouse]
EXPORT
[Creator] --> [Export Service] --> [job queue] --> worker streams from
ClickHouse --> file in [Object Storage] --> signed URLA row store keeps each response together on disk: all forty answers of response #1, then all of response #2. Perfect for "fetch response #1" - one seek, everything there. Terrible for "average of question 5 across two million responses": the database must read all two million full rows and discard 39/40ths of every one. A columnar store flips the layout: all two million answers to question 5 sit together, followed by all answers to question 6. Now the same aggregation reads exactly one column and skips the rest of the table entirely - that alone is most of the speedup. Compression stacks on top. A column is millions of values of the same kind, often from a tiny set ("Yes", "No", "Maybe" over and over), which compresses 10-100x - so even the column we do read is a fraction of its raw bytes. Add partition pruning (a date filter skips whole months of data without touching them) and vectorized execution (aggregating values in CPU-friendly batches), and a 30-second row-store scan becomes a 300ms columnar query. The 100x is not one trick; it is four multiplying. The cost, and why the row store stays: columnar layouts are miserable at row-shaped work - writing one response means touching forty column files, and "give me response #1 in full" reassembles it from forty places. Neither layout is better; each is optimal for one query shape. That is the whole argument for running both.
Responses exist in three places - PostgreSQL, ClickHouse, Redis - and the design only stays sane because exactly one of them is the truth. PostgreSQL is the truth: the copy with the constraints, the transactions, the backups, and the durability promise. Every engineering hour spent on "never lose data" is spent here and only here. ClickHouse and Redis are derived: computed FROM the truth via the event stream, and rebuildable from it. If the ClickHouse cluster burned down, dashboards would go dark for the hours a reload takes - painful, but zero responses lost. If Redis vanished, live tiles would rebuild from the stream. Because derived copies are disposable, they are allowed to be fast and loose: no backups to manage, seconds of lag tolerated, schema reshaped freely for query speed. This asymmetry is a load-bearing design tool, the same one as Design a Newsfeed's feed store (derived pointers, rebuildable) versus its Posts table (truth): decide which copy is the truth, put all the durability engineering there, and let every other copy be cheap. Systems that treat three copies as three truths end up reconciling forever. One honest wrinkle: deletion. When a creator (or GDPR) demands a response gone, the truth row deletes cleanly - but columnar stores hate point deletes, so ClickHouse takes a tombstone-and-rewrite path that runs batched and lagged. "Delete everywhere, eventually, provably" needs its own small pipeline, and pretending otherwise is how compliance incidents happen.
Database Schemas
PostgreSQL - the truth side
- surveys
survey_id UUID PRIMARY KEY,
creator_id UUID NOT NULL,
slug VARCHAR(80) UNIQUE, -- the public link
current_version INT NOT NULL DEFAULT 1,
status survey_status_enum, -- draft | live | closed
created_at TIMESTAMP DEFAULT NOW()
- survey_versions (immutable once published)
survey_id UUID, version INT,
definition JSONB NOT NULL,
-- { questions: [{ id, type, title, required, options[],
-- validation{}, show_if{question_id, equals} }], settings{} }
published_at TIMESTAMP,
PRIMARY KEY (survey_id, version)
- responses (partitioned by hash(survey_id), month)
response_id UUID PRIMARY KEY,
survey_id UUID NOT NULL,
survey_version INT NOT NULL, -- pins meaning: data outlives schema
respondent_token VARCHAR(64),
idempotency_key VARCHAR(128) NOT NULL,
answers JSONB NOT NULL, -- { "q_nps": 9, "q_feedback": "..." }
source VARCHAR(30), -- link | email | embed
completion_seconds INT,
submitted_at TIMESTAMP DEFAULT NOW(),
UNIQUE (survey_id, idempotency_key) -- THE dedupe referee
- outbox (same-transaction event relay -> Kafka)
event_id BIGSERIAL PRIMARY KEY,
response_id UUID, survey_id UUID,
payload JSONB, relayed BOOLEAN DEFAULT FALSE
- export_jobs
job_id UUID PRIMARY KEY, survey_id UUID, creator_id UUID,
filters JSONB, format export_format_enum,
status job_status_enum, -- queued | running | done | failed
progress_pct INT DEFAULT 0,
file_ref VARCHAR(512), expires_at TIMESTAMP
ClickHouse - the derived side (rebuildable from truth)
- answers -- one row per (response, question): the flattened analytics copy
CREATE TABLE answers (
survey_id UUID, question_id String, question_version Int32,
response_id UUID, submitted_at DateTime,
source LowCardinality(String),
answer_text Nullable(String),
answer_number Nullable(Float64),
answer_choice LowCardinality(Nullable(String))
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(submitted_at) -- date filters prune whole months
ORDER BY (survey_id, question_id, submitted_at);
-- LowCardinality: "Yes/No/Maybe" columns compress 10-100x
Redis
- Live tiles: "stats:{survey_id}:{question_id}" -> {option: count, ...}
- Funnel: "funnel:{survey_id}" -> {loaded, started, completed}
- Partial saves: "progress:{respondent_token}" -> answers-so-far, TTL 24h
- Counter buffers: flushed to tiles every few seconds (Newsfeed pattern){
"survey_id": "s_8f3e2d1c",
"survey_version": 3,
"idempotency_key": "s_8f3e2d1c:rt_7a2b8f4e",
"answers": {
"q_nps": 9,
"q_feedback": "Loved the onboarding flow."
},
"source": "email",
"completion_seconds": 74
}{
"response_id": "r_4c8e6f2a",
"status": "recorded"
}
// A retried or double-clicked submit carries the same idempotency_key,
// hits the unique constraint, and receives THIS same response_id with a
// 200 - the caller cannot tell it was a duplicate, which is the point.
// The outbox event was written in the same transaction, so analytics
// can never disagree with the truth store about this response existing.{
"live": {
"responses": 184203,
"completion_rate": 81.4,
"freshness": "updated 3s ago",
"questions": {
"q_nps": { "avg": 7.9,
"distribution": { "9": 41230, "10": 38854, "...": "..." } }
}
},
"filtered_query": {
"filters": { "date_from": "2026-06-01", "source": "email",
"completed": true },
"q_nps_avg": 8.3,
"rows_scanned": 2941000,
"query_ms": 340
}
}
// Two tiers on one screen: "live" comes from Redis pre-aggregates
// (seconds fresh, zero query cost); "filtered_query" ran on ClickHouse -
// ~3M rows scanned in 340ms, which is the columnar layout earning its keep.{
"format": "csv",
"filters": {
"date_range": { "start": "2026-06-01", "end": "2026-06-30" },
"completed_only": true
}
}// immediately:
{ "job_id": "exp_9d4f1a2b", "status": "queued" }
// GET /v1/exports/exp_9d4f1a2b, a minute later:
{
"job_id": "exp_9d4f1a2b",
"status": "running",
"progress_pct": 62,
"rows_so_far": 6200000
}
// ...and when done:
{
"job_id": "exp_9d4f1a2b",
"status": "done",
"row_count": 10014312,
"file_size_mb": 812,
"download_url": "https://exports.example.com/exp_9d4f1a2b.csv?sig=...&exp=...",
"url_expires_at": "2026-07-13T18:04:11Z"
}
// Minutes of work behind an honest job: progress instead of a hung
// request, and a signed expiring link because an export is bulk PII.// 422 - server-side validation is the gate (client validation
// was only a courtesy)
{
"error": "validation_failed",
"message": "Question q_nps requires a number between 0 and 10",
"question_id": "q_nps"
}
// 410 - survey closed by its creator or past its end date
{
"error": "survey_closed",
"message": "This survey is no longer accepting responses"
}
// 404 - survey does not exist or belongs to another creator: identical
// on purpose, so survey ids cannot be probed across tenants
{
"error": "not_found",
"message": "Survey not found"
}Transactional outbox: the pattern for "write to my database AND tell the event stream" without a gap. Writing to two systems separately means a crash between them either loses the event (analytics never hears about a real response) or invents one (analytics counts a response that rolled back). The outbox fixes it by writing the event as a ROW in the same database transaction as the response; a relay process then ships outbox rows to Kafka. The response and its announcement commit atomically or not at all. Design Calendly used the identical move for calendar sync jobs. Respondent token: the anonymous identity substitute - a random id minted on first form load and kept in the browser. It anchors partial-progress saves and the idempotency key. It deliberately identifies a browser, not a person; the requirements note covers what that does and does not promise. Pre-aggregation: keeping the answer to a known query up to date as data arrives, instead of recomputing it per request. The live tiles are pre-aggregation (Redis counters updated per response, read 50M times/day for free); the filtered queries are its opposite - computed on demand, because the filter combinations are unbounded. The dividing rule: pre-aggregate what everyone asks constantly; compute on demand what anyone might ask once.