Optimizing queries on a billion-row fact table

WHAT THIS TESTS It checks whether you choose optimizations from query patterns and articulate the write, storage, and freshness costs each one imposes, rather than reflexively adding indexes.

PARTITIONING Split the table into partitions, commonly by a time column matching how the data is queried. Queries with a time predicate then prune to a few partitions instead of scanning billions of rows, and old partitions can be dropped cheaply for retention. Trade-offs: choosing the right key is critical, queries that span or ignore partitions gain little, and there is management overhead. Partitioning controls how much data is touched.

INDEXING Add indexes on selective columns used in filters and joins so the engine seeks instead of scanning. Effective for high-selectivity predicates. Trade-offs: every index slows inserts and updates because it must be maintained, consumes storage, and indexes on low-selectivity columns (few distinct values) are often ignored by the optimizer. In analytical stores, sort keys or zone maps frequently beat traditional B-tree indexes.

MATERIALIZED VIEWS Precompute and store the results of expensive aggregations or joins, so dashboard queries read a small summary instead of scanning the fact table. Trade-offs: extra storage and a refresh cost, plus staleness, the view lags the base data between refreshes, so you must accept eventual consistency or pay for incremental or frequent refresh.

COMBINING AND OTHER LEVERS These compose: partition by time, index selective dimensions, and materialize the heaviest rollups. Columnar storage, compression, and clustering further help analytical scans.

LIKELY FOLLOW-UPS How do you pick a partition key, why are low-cardinality indexes useless, and how do incremental materialized-view refreshes work.

ONE CONCRETE EXAMPLE A billions-row events table partitioned by day lets a last-7-days query scan seven partitions; a materialized daily-revenue rollup answers the dashboard instantly; and an index on customer_id speeds targeted lookups, while you accept slower ingest from index maintenance.