SQL or NoSQL for high-volume semi-structured event ingestion?
Choose an event store by access patterns, write volume, consistency and operational cost. Document stores fit flexible payloads; PostgreSQL JSONB is also a valid option.
What's really being asked
Match semi-structured event storage to access patterns, throughput, consistency and operating constraints rather than choosing a database label by habit.
The full answer
A document or key-value store can fit flexible event payloads and distributed ingestion. PostgreSQL JSONB is also a valid design when relational queries, joins or transactions matter; new JSON keys can be stored without adding a table column for every key. Explain the relevant trade-offs: write volume, partitioning, query patterns, indexes, consistency, cost and operational complexity. An ingestion store and a separate analytics store may be useful, but a two-store design is not compulsory. A concise answer that chooses by access patterns, recognizes document-store flexibility and explains SQL/JSONB alternatives is valid. Additional throughput or downstream detail improves coverage, not whether that answer is factually correct.
The mistakes people make
Explicitly claiming SQL cannot store flexible JSON. Explicitly claiming each new JSON key requires ALTER TABLE. Explicitly claiming a database guarantees unlimited write scaling without partitioning or operational limits. Merely choosing SQL/JSONB, or omitting a named NoSQL vendor, is not a false claim.
A concrete example
Store an event with a JSONB properties field. A later event can add an experiment key within that JSON value without changing the table definition; indexing and access patterns still need deliberate design.
Interview question
When designing a pipeline to ingest millions of semi-structured mobile events per second, why is NoSQL typically chosen for the initial landing zone?
- a.It requires multi-row ACID transactions to guarantee exactly-once ingestion of immutable logs.
- b.It performs relational joins and complex aggregations faster than a data warehouse on raw JSON.
- c.It enforces strict schemas upfront to prevent bad data from entering the pipeline.
- d.It allows schema flexibility and horizontal write scaling without blocking ingestion during field additions.Correct
Why? this is the answer
D fits the stated high-write, changing-payload workload: a suitable document or key-value store can support flexible fields and horizontal partitioning. This is a workload choice, not proof that SQL cannot ingest these events. PostgreSQL JSONB also stores new JSON keys without a table schema migration; choose it when joins, transactions, access patterns and measured capacity justify it. ACID alone does not guarantee exactly-once event delivery, and faster analytical joins or rigid schema enforcement are not the reasons for choosing this landing-zone design.
Just read this? Test yourself on what you have been reading.
Read the original → postgresql.org
- #system design
- #data engineering
- #nosql
- #sql
- #event ingestion
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on system design — each one lists the topics its interview covers.
See open roles