Compare PostgreSQL versus a columnar warehouse for raw event data

WHAT THIS TESTS: This question probes whether you can translate storage engine internals into architectural decisions for analytics. The interviewer wants to see that you understand how linear memory layout, row-oriented versus column-oriented, shapes CPU cache behavior, compression, and mutation cost. They also want to know if you can match those mechanics to workload patterns like high-volume event ingestion, large aggregations, and point lookups.

A GOOD ANSWER COVERS: A strong response walks through four mechanics in order. First, layout: row stores keep all columns of a record contiguous, which is great for single-row reads and transactional updates but pulls unnecessary data during analytical scans. Second, cache and scan efficiency: columnar stores read only the relevant columns into CPU cache, so a query touching three columns on a hundred-column table avoids ninety-seven percent of the IO. Third, compression: homogeneous data in a column allows dictionary or run-length encoding, often yielding five to ten times better compression than row stores, which directly reduces disk IO and cloud storage cost. Fourth, mutation penalty: updating a single row in a columnar format typically requires rewriting multiple underlying column files, making them poor for OLTP but ideal for append-only event streams. The candidate should then map these mechanics to the specific pipeline: raw event data is insert-heavy and queried with wide aggregations, so a columnar warehouse is usually the right default, while Postgres remains useful for metadata or user-facing operational queries.

COMMON WRONG ANSWERS: Red flags include saying Postgres is faster for all queries, ignoring that analytical scans on a row store become IO-bound as table width grows. Another mistake is treating columnar stores as just bigger Postgres instances without mentioning compression or mutation costs. Claiming that raw event data needs frequent updates is also a miss, since experiment events are immutable. Finally, failing to mention CPU cache locality or linear memory layout suggests a superficial understanding of the underlying representation.

LIKELY FOLLOW-UPS: The interviewer may ask how you would handle late-arriving events or backfills in a columnar store, which tests partitioning and merge strategies. They might also ask for a hybrid architecture, such as streaming inserts through Postgres before bulk-loading to the warehouse, or how to optimize query performance with clustering keys and materialized views. Cost estimation is another common pivot, for example comparing on-demand scan pricing in BigQuery against provisioned Redshift nodes.

ONE CONCRETE EXAMPLE: Imagine an A/B test logging ten million events per day, each with a hundred attributes. A daily aggregation query in Postgres that sums revenue per variant must read every row and every column, likely scanning hundreds of gigabytes from disk. In a columnar warehouse, the same query reads only the variant and revenue columns, and because those columns compress at a ten-to-one ratio, the scan drops to a few gigabytes. The query runtime falls from tens of minutes to under thirty seconds, while the storage cost is an order of magnitude lower.