Best Practices for Single Row Lookups (Point Queries)

This blog post covers different techniques to improve the performance of point queries in DataFusion. It is based on a talk (slides, recording) given at the Boston DataFusion meetup, in September 2026.

Introduction

While DataFusion is commonly seen as an engine to run analytical workloads -- which are comprised by queries that access large sets of data -- some of its users regularly require random lookups to large files. During the DataFusion meetup in Boston, I met several community members that shared their experiences with these types of workloads, and how they hit some of the same challenges I highlighted in my talk.

For this blog post, I will cover some best practices on how to handle random lookups (aka point reads) based on my past experience with these workloads in DataFusion. Since we usually have many more queries to execute comparatively to analytical workloads, the challenge here is completing each query as fast as possible (< 1ms) to achieve a reasonable throughput.

The first part goes over optimizations in the storage (in this case, Parquet files), while the second optimizes the execution in DataFusion.

Methodology

The dataset we will be using here is formed by a single Parquet file with 100M rows (unless otherwise stated), using a key (bigint, unique) and value (varchar, md5 of the key) columns, generated with the following code:

select i as k, md5(i::varchar) as v
from generate_series(1, {rows}) t(i)

Unless otherwise stated, the queries tested retrieve a random row from the table by key (i.e., select v from t where k = {}). Most tests run on a c4d-standard-4 (4 vCPUs, 15GB RAM) Google Cloud virtual machine, using a 100GB HyperDisk (6k IOPS, 500 MB/s) to store the data.

Storage (Optimizing Parquet Settings)

File Ordering

Having the Parquet file sorted by key will allow random lookups to access the minimum amount of data, assuming our predicate is also based on the key. To confirm this, the first test measures the throughput of random reads against a sorted (True) and non sorted (False) file.

The overall throughput of the sorted version is 6000x faster than the non-sorted one. In this case, the execution plans show that the non-sorted version needs to access, on average, 67 row groups and 122 total pages, while the sorted version only accesses one page in one group.

In case you are not familiar, Parquet files are divided into multiple row groups, with each column in each group divided into multiple pages. DataFusion uses the min/max statistics for each group/page to determine if it needs to access it or not. If the file is not sorted, the min/max bounds will be wide, forcing more groups/pages to be accessed.

Additional Metadata

By default, DataFusion writes Parquet files with dictionary encoding enabled. Dictionary encoding maps column values to integers in an attempt to compact the space used, working pretty well for columns that have many duplicate values.

However, if a column contains many distinct values (which is the case in our dataset), the dictionary will only add overhead. The following test compares enabling (True, default) and disabling (False) the dictionary, showing that the latter achieves a higher throughput.

Another optional metadata commonly used in Parquet files are bloom filters, which much like min/max statistics help speedup predicates. In the context of Parquet files, a bloom filter is an array of bits that can determine if a value is definitely not or maybe is in a specific row group.

In our case, since the column we filter by only contains unique values and is already sorted, the min/max statistics suffice, and thus there is no need to add bloom filters. By default, DataFusion already does not add bloom filters when writing Parquet files.

In summary, at least for sorted files with many unique values, it is better to disable both of these structures when writing Parquet files. This can be set in the OPTIONS part of the SQL COPY command, or by changing the TableParquetOptions in DataFrame::write_parquet:

COPY (...) to 't.parquet'
OPTIONS (
    DICTIONARY_ENABLED false,
    BLOOM_FILTER_ON_WRITE false
)

File Configuration

Next up, we are going to take a look at what should be the optimal row group and data page sizes for point reads. We can conclude that the optimal configurations for point reads are the values around 128k rows per row group and 1k rows per page, which are quite different from the defaults used in DataFusion (1M sized row groups and 20k sized pages). Tiny groups or pages will lead to a large number of them, which means more time when pruning. On the other hand, large groups or pages means less data is pruned and thus more rows are scanned.

But what if we need our Parquet files for both point reads and analytical workloads? While large reads favor larger pages, and smaller reads favor smaller pages, we can use around 2k-4k rows per page to achieve a good throughput for both workloads, meaning we don't need to sacrifice one over the other.

COPY (...) to 't.parquet'
OPTIONS (
    MAX_ROW_GROUP_SIZE 131072,
    DATA_PAGE_ROW_COUNT_LIMIT 2048
)

Compression

Finally, we will examine how compression affects performance. The main conclusion here is that, for our dataset, we should use lz4 if we favor faster speeds, while zstd shows the best space/speed tradeoff (default used in DataFusion). The snappy algorithm, which is commonly used, uses around the same space as lz4 for this dataset but is slower in larger scans in DataFusion.

Execution (Optimizing DataFusion Settings)

Target Partitions

We are now going to optimize the execution itself, starting with the target_partitions config in DataFusion. Since point reads require so little work, the overhead of distributing load to multiple cores outweighs its benefits. We observe that small reads are faster with a single partition. On the other hand, larger reads are faster as they can use the additional parallelism, so this option is very much workload dependent.

Pushdown Filters

Next up, we are going to take a look at another DataFusion config: pushdown_filters. This config pushes filters directly to the Parquet scan, instead of having an extra FilterExec node. For both smaller (<10k rows) and larger (>100k rows) reads, we conclude that enabling pushdown_filters results in a higher throughput.

Metadata Cache

To find data in a Parquet file, DataFusion first has to retrieve and parse the file's built-in metadata. DataFusion caches Parquet metadata by default since version 50.0.0 to avoid having to do this at every read. It is better to keep this caching enabled if we intend to query the same file more than once.

Prepared Statements

For point reads specifically, the optimization process of building logical and physical plans has quite a significant weight on performance. Reusing the logical plan offers an improvement over not reusing anything, but it's clear that reusing the physical plan leads to a considerably higher throughput, doubling or even tripling performance. Currently, this is not available in DataFusion.

Memory Allocator

Another important contributor to the overall performance in DataFusion is the memory allocator used. The results show that the allocators have widely different throughputs. In our workload, mimalloc appears to be the overall best choice.

Runtime

After experimenting with different configurations, I found that having a separate single-threaded runtime for each client scaled better. We see in the results that the default version does not really improve with multiple clients, as the CPU is not being fully utilized, unlike the multiple single-threaded runtimes version.

Summary

Table 1 shows an overview of the main optimizations tested. In total, we managed to improve the throughput in almost 8x over the baseline and reduce the response time to sub 0.2ms, demonstrating that DataFusion is well capable of handling point read workloads.