A new engineer asks why the 8 TB events table in the lakehouse has no index on customer_id when the same column is indexed in the operational Postgres the data came from. What is the actual reason?

The mechanism

An index in an operational database exists to answer "find me this one row" without reading the table. It is a tree you descend, and it pays for itself when a query touches a very small fraction of the rows.

An analytical query touches a large fraction, and its cost is bytes read, not seeks performed. A columnar file carries a footer holding the minimum and maximum of every column for every row group, typically around 100,000 rows. The engine reads those footers, discards whole files and whole row groups that cannot contain a match, and scans what is left. That is the index, and it comes free with the file format.

So the lakehouse equivalent of "index this column" is sort or cluster the data by it, so that each file's minimum and maximum cover a narrow range. An unsorted 8 TB table has customer_id values scattered uniformly, every file's range spans the whole key space, nothing prunes, and the query reads everything. Clustering is what makes the statistics selective; without it the statistics exist and are useless.

Why the other options fail

• "Object storage cannot support secondary indexes." Table formats do maintain auxiliary structures, and some engines keep bloom filters or secondary indexes over columnar data. The constraint is economic, not technical: maintaining a per-row index across bulk loads of billions of rows costs more write amplification than it saves on read.
• "Too large to fit in memory." Operational databases routinely index tables far larger than RAM; B-trees are designed for exactly that. This answer confuses the index with a cache.
• "Analytical queries are infrequent." Frequency has nothing to do with it. The distinguishing property is selectivity: a query returning 20 rows wants a seek, a query aggregating 400 million rows wants a scan, and the same table can face both.

The decision rule, and when not to apply it

Cluster by the column that most queries filter on; partition by a low-cardinality column that bounds the time range. A useful rough test: a partition column should produce partitions in the hundreds to low thousands with roughly 100 MB to 1 GB of data each. customer_id with 4 million values fails that test badly and belongs in the clustering key instead.

It flips when you genuinely need single-row lookups at interactive latency — a customer-facing "show me my last 10 events" endpoint, for instance. That workload does not belong in the lakehouse at all. Serve it from the operational store or a purpose-built serving index, and let the lakehouse answer the questions it is shaped for. The mistake is not choosing wrongly between index and scan; it is putting two workloads with opposite access patterns on one copy of the data and then tuning forever.