Indexing the Data Lake for Online Point Queries

Companies for illustration Spotify request immense quantities of information accessible astatine debased latency for online services and, increasingly, AI Agents acting connected behalf of users. Online services — specified arsenic portals and personalization features that usage aliases show a user's listening history — request to look up and paginate done per-user information astatine interactive speeds. AI Agents answering questions like, "what was I listening to past summer?" request to retrieve a user's information quickly truthful they tin logic complete it — filtering, aggregating, aliases moreover moving SQL locally — to build discourse for LLM prompts. Both patterns stock the aforesaid underlying primitive: accelerated constituent queries by cardinal complete datasets that are often acold excessively ample to economically support resident successful a key-value (KV) shop for illustration Bigtable aliases DynamoDB.

At Spotify, petabytes reside successful Bigtable for online use-cases — but exabytes beryllium successful the GCS information lake. The underlying retention for lakes is accelerated and getting faster — GCS delivers 30–100ms per request, and S3 Express One Zone and GCS Rapid Storage now offers single-digit millisecond latency. The bottleneck is progressively not the retention furniture itself, but the query engines connected top: distributed SQL engines for illustration Trino and BigQuery adhd seconds of occupation scheduling and query readying overhead, moreover for a single-row lookup. They are designed for analytical throughput, not interactive constituent queries.

Random Access Parquet (RAP) bridges this gap. An outer scale maps keys straight to record locations, and precise ranged sounds fetch precisely the bytes needed. RAP operates connected the aforesaid Parquet files already shared by ML pipelines, notebooks, experimentation platforms, and batch analytics — storing erstwhile and paying once, alternatively than maintaining abstracted copies successful specialized serving systems.

How a distributed SQL motor finds the needle successful the haystack

A personification asks an AI supplier what they listened to past summer. Just the listening history information spans billions of users crossed thousands of ample regular files, and summertime is astir 90 days. With, let’s say, 1,000 files per day, that is 90,000 individual Parquet files. It is prohibitive to publication moreover a small spot of each wrong immoderate reasonable clip aliases compute fund for an online usage case.

There are modular ways to trim the campaigner set. Within each regular partition, files tin beryllium further partitioned by key — a method commonly utilized to velocity up joins that benefits constituent lookups excessively erstwhile the cardinal is right. From the filename alone, the motor knows whether a fixed personification ID tin perchance beryllium successful that file. With 1,000 buckets per day, the campaigner group drops from 90,000 to 90. Bloom filters connected the personification ID file tin discard files without opening them — cached successful a metadata store, they constrictive 90 candidates to possibly the 12 days the personification was really active.

We still person to publication those 12 files. Each record is large, and uncovering 1 user's information wrong it requires a concatenation of limited reads: fetch the footer, parse statement group metadata, scan the cardinal file to find matching rows, past usage file and page indexes to find the corresponding pages successful each worth column. That is multiple, round-trip dependent-fetches to unreality retention per file, per file — and each round-trip contends for IOPS pinch each the different concurrent queries. Partitioning by cardinal and Bloom filters thief prime files, but they don't lick the within-file problem.

The RAP Approach

The concatenation of limited sounds is the basal bottleneck, and it applies astatine each retention tier. Each nexus costs latency (one round-trip earlier the adjacent tin start) and bandwidth (bytes publication conscionable to observe wherever to publication next). On unreality storage, each I/O nexus costs tens of milliseconds; connected section SSD, microseconds; successful memory, nanoseconds. The absolute numbers change, but the building is the same: each measurement depends connected the consequence of the erstwhile one. Collapsing that concatenation — replacing limited sounds pinch a azygous precomputed lookup — saves some latency and bandwidth sloppy of the retention tier. We exemplify the attack complete unreality entity retention because the per-round-trip costs makes the use astir dramatic, but the rule is general.

Instead of scanning, RAP looks up. An external index maps each cardinal straight to each record and statement number wherever its information resides. Given a key, the scholar looks up the index, resolves the statement number to page locations utilizing cached record metadata, and issues ranged sounds to fetch precisely the pages needed. The scale lookup is O(1), the cached page mapping is simply a low-latency operation, and the information retrieval is simply a mini number of precise ranged reads. Importantly, these sounds tin beryllium issued successful parallel — location are nary limited load chains.

The External Index

RAP tin run connected any existing Parquet files pinch nary typical preparation. The scale builder sounds footers and page locations for the columns that will beryllium retrieved, scans cardinal columns to build the key-to-location mapping, and writes it out. As caller information arrives, each pipeline tally produces corresponding scale entries. The scale grows by appending fragments, not modifying existing ones.

The scale is simply a multimap — a azygous cardinal tin person entries crossed galore files and partitions. Each introduction is compact:

As a norm of thumb, indexing terabytes produces gigabytes of index; indexing petabytes produces terabytes. Large indexes administer people by hash bucketing.

This is fundamentally different from Parquet's built-in PageIndex aliases Bloom filters: those are probabilistic and constrictive a scan. The outer scale is definitive — fixed a key, it returns the nonstop files and rows, eliminating the scan entirely.

On unmodified files, RAP sounds the full page containing the target statement — perchance a 4MB page to extract 100 bytes. For latency- aliases cost-sensitive workloads, write-time mentation makes sounds smaller and much precisely targeted. This intends getting into the specifications of Parquet internals and information processing pipelines — but that is wherever the existent wins are.

Optimizations for Prepared Parquet Files

An outer scale changes what the scholar knows earlier it touches a record — the nonstop file, row, and columns needed. The optimizations beneath use to those columns that service constituent queries; different columns successful the aforesaid record tin clasp immoderate layout champion suits batch analytics. Many of the techniques are useful successful their ain right, and immoderate use immoderate reader, not conscionable RAP. But an outer scale shifts the equilibrium of tradeoffs: properties that thief in-file find (fine-grained page indexes, mini pages for predicate skipping, dictionary encoding for pushdown) matter less; properties that minimize the last publication (fewer round-trips, smaller fetches, contiguous data) matter more.

The optimizations autumn into 3 categories: concentrating a key's data, reducing the bytes per read, and reducing the number of reads.

Concentrating Key Data

Sorting by key ensures each rows for the aforesaid cardinal are contiguous wrong each file, concentrating them into arsenic fewer pages arsenic possible. Many pipelines already nutrient sorted output. Hash bucketing (Spark, Scio SMB, Iceberg bucket transforms) goes further by guaranteeing each cardinal maps deterministically to 1 record per partition.

Co-grouping — structuring the schema truthful each cardinal appears erstwhile pinch values successful repeated aliases nested columns, e.g: SELECT user_id, ARRAY_AGG(STRUCT(timestamp, track_uri, duration_ms)) FROM streams GROUP BY user_id gives 1 statement per cardinal per record without relying connected benignant bid and is often natural.

Coarser partitioning reduces the number of files a cardinal spans. Daily partitioning produces 365 files per cardinal per year; play reduces this to 52 — less scale entries, less parallel reads, smaller index. This trades disconnected against partition pruning granularity for batch queries.

Reducing Bytes Read

Even erstwhile the scale points to the nonstop page, that page whitethorn beryllium overmuch larger than the target data.

One page per key. The writer flushes pages astatine cardinal boundaries — a page break whenever the cardinal changes. The full decompressed page belongs to the target key; nary statement extraction needed. Since each page now belongs to a azygous key, the page locations tin beryllium stored straight successful the scale introduction — nary abstracted page scale needed. The files stay modular Parquet. Each cardinal bound adds astir 20 bytes of page header overhead — negligible erstwhile location is important information per key. Resetting the compressor astatine cardinal boundaries often has small effect connected compression ratio, though this is data-dependent. A broadside effect is that if the Parquet record contains a PageIndex, it grows proportionally to the number of keys alternatively than the number of accepted pages.

ZSTD framework resets wrong pages. When one-page-per-key would bloat the PageIndex, an replacement keeps accepted page sizes but compresses each key's rows arsenic a abstracted ZSTD framework wrong the page. The scale stores each frame's byte offset and size per column, addressing the information straight without a abstracted page index. To a modular reader, the page decompresses usually (ZSTD frames simply concatenate). To RAP, each framework is independently addressable. The tradeoff: values wrong each framework must beryllium interpretable without the preceding frames, which rules retired delta and run-length encodings. PLAIN aliases dictionary encoding satisfies this but whitethorn beryllium little compact for immoderate information types. For the superior RAP usage cases — blobs, JSON, level worth columns — this is usually acceptable.

Storage alignment. ZSTD skippable frames tin pad betwixt keys to align fetches to retention artifact boundaries (e.g. 4KB aliases 16KB). A fetch that straddles a artifact bound requires reference some blocks. On section disk, wherever IOPS are a nonstop constraint, alignment avoids unnecessary artifact reads. On unreality entity storage, providers don't measure aliases rate-limit by IOPS, but the underlying retention is still backed by disk, truthful alignment remains a useful micro-optimization. The padding is mini and the files stay valid Parquet.

Reducing Read Operations

In a modular Parquet file, fetching a key's values crossed N columns requires N parallel sounds — consuming N× the petition capacity and pushing per-key latency toward the tail of the distribution. Reducing the publication count is the biggest azygous win.

Blobs and Variants. Storing the fields needed for random entree arsenic a azygous file — JSON, Protobuf, aliases Parquet Variant — intends 1 publication per file. This aligns people pinch really online applications devour the data: arsenic a document, not individual fields. The tradeoff is that batch analytics loses per-field file pruning and predicate pushdown wrong the blob.

Interleaving columns. When aggregate columns are needed — immoderate for constituent queries, others for batch analytics — the writer tin interleave them physically. Each key's information from different columns is written adjacent: file A for cardinal 1, file B for cardinal 1, file A for cardinal 2, file B for cardinal 2. ZSTD skippable frames span betwixt columns truthful each remains a valid compressed watercourse — a accepted scholar sounds file A sequentially and the decompressor silently skips file B's information wrong the skip frames. A RAP scholar issues a azygous contiguous ranged publication spanning each columns for a key. The record remains afloat valid Parquet.

This is efficaciously pivoting the layout to beryllium row-major for prime file groups without breaking backwards compatibility. The tradeoff: accepted readers reference a azygous interleaved file besides publication the different columns' information arsenic dormant space, multiplying I/O measurement for single-column scans. On section retention this is usually absorbed by OS caching; connected unreality storage, readers would request to coalesce overlapping reads.

Interleaving is peculiarly useful for partially-shredded Variant columns: the typed columns alteration accelerated vectorized analytics, while colocating the version blob alongside them enables single-read entree for RAP pinch minimal overhead for analytics readers.

Covering indexes and hoisted values. The scale builder visits each statement astatine build time. For mini values, it tin hoist the worth straight into the scale introduction — a covering scale that eliminates the retention publication entirely, reducing the publication count to zero. This extends to pre-computed aggregates: per-key arena counts, full play time, class breakdowns, disposable astatine index-lookup speed. Hoisted values besides alteration predicate pushdown astatine the scale level — filtering entries earlier immoderate retention read.

Summary

Together, these optimizations tin trim a constituent query to a azygous ranged publication of a fewer kilobytes — aliases destruct the retention publication entirely.

Secondary Indexes

A dataset whitethorn person aggregate lookup dimensions — for example, a transaction dataset queried by some buyer_id and seller_id. RAP supports this by building aggregate entree structures complete the aforesaid scale entries, 1 per dimension. Hash tables springiness O(1) nonstop lookups; sorted indexes alteration scope queries. Adding aliases removing a secondary scale is simply a serving-layer determination — nary pipeline changes, nary information rewriting.

The record layout favors whichever magnitude the information was sorted on. Secondary lookups whitethorn scatter crossed much files, but the scholar coalesces adjacent byte ranges automatically. Space-filling curves specified arsenic Z-ordering and Hilbert curves are complementary — they amended locality for secondary dimensions astatine the record layout level, while secondary indexes supply the entree paths.

Conclusion

The cardinal spot of RAP is that it operates connected the aforesaid Parquet files already successful the lake. The files that BigQuery scans for play aggregate reports are the aforesaid files that an AI supplier sounds for discourse retrieval. No copy, nary ETL, nary 2nd retention bill.

This changes the economics of which information tin beryllium served online. Today, teams make difficult choices astir what to support successful a KV shop because the per-GB costs forces prioritization. With RAP, the costs of a constituent query drops to the costs of a unreality retention read. Historical data, long-tail entities, low-traffic features — each go viable for interactive access. An AI supplier doesn't person to limit its discourse to what the KV shop holds; it tin scope backmost months aliases years crossed the afloat breadth of the lake.

The information reservoir is nary longer batch-only. The aforesaid retention serves some analytical and interactive workloads — 1 dataset, 2 entree patterns.