Identify and fix performance bottlenecks in your data pipelines. This guide shows you how to profile pipeline execution, select optimal caching strategies, choose the right backends, and optimize queries for production workloads.
Xorq’s deferred execution and caching system enables significant performance improvements, but you need to understand where time is spent and how to optimize each stage.
TipInstallation
If you haven’t installed Xorq yet, then see the installation guide for setup instructions.
Profile pipeline performance
Before optimizing, identify where your pipeline spends time. Use timing measurements and Python’s profiler to measure execution time for each stage and find bottlenecks.
Import time for timing measurements, xorq.api for Xorq operations, xorq.expr.ml for ML pipeline components, sklearn for preprocessing and models, and numpy for data generation. Connect to Xorq and create sample training data using memtable for in-memory profiling.
2
Time the train/test split operation and store the duration.
3
Define an ML pipeline using scikit-learn’s Pipeline and wrap it with Pipeline.from_instance(). Then time the model training (fit) operation.
4
Time the prediction operation on the test set.
5
Calculate and display performance breakdown with percentages, then identify the slowest stage (bottleneck).
The breakdown lists each stage, its execution time, and its percentage of total time. The bottleneck stage is identified at the end, focus optimization efforts on stages with the highest percentages, then re-profile after optimizations to measure improvement.
Profile with Python profiler
Use Python’s cProfile for detailed function-level profiling:
Import cProfile and pstats for function-level profiling, xorq.api for Xorq operations, and numpy for data generation. Connect to Xorq and create sample data for profiling.
2
Initialize Python’s cProfile to track function calls and execution time, then start profiling.
3
Execute the query operation to profile: filters values > 100, groups by category, and counts rows per group.
4
Stop profiling after the query completes.
5
Analyze profiling results: sort by cumulative time (includes subcalls) and display the top 20 functions.
The output shows function-level timing information sorted by cumulative time. Focus on functions with high cumulative time to identify optimization targets.
Choose cache strategy
Select the right cache type based on your data freshness requirements, storage needs, and cost constraints. Use SourceCache for automatic invalidation on data changes, ParquetCache for durable disk-based caching with auto-invalidation, or snapshot caches for manual control over the cache lifecycle.
Cache type comparison
Cache Type
Auto-Invalidation
Storage
Use Case
SourceCache
Yes
Source backend
Production pipelines requiring data freshness
SourceSnapshotCache
No
Source backend
One-off analyses, manual cache control
ParquetCache
Yes
Parquet files (disk)
Iterative development, durable persistence
ParquetSnapshotCache
No
Parquet files (disk)
Reproducible research, fixed snapshots
Use SourceCache for production
Use SourceCache when you need automatic cache invalidation in production:
Create use_source_cache.py:
import xorq.api as xofrom xorq.caching import SourceCacheimport numpy as npcon = xo.connect()np.random.seed(42)data = xo.memtable({"value": np.random.randint(0, 500, 10000),"category": np.random.choice(["A", "B", "C"], 10000)}, name="sample_data")cache = SourceCache.from_kwargs(source=con)cached = data.filter(xo._.value >100).cache(cache=cache)result = cached.execute()print("You should see a result like this:")print(result)
1
Import xorq.api for Xorq operations, SourceCache for automatic cache invalidation, and numpy for data generation. Connect to Xorq and create sample data.
2
Create a SourceCache that automatically invalidates when source data changes. SourceCache uses the source backend for storage and checks modification times.
3
Apply filter and cache the result. If the source data changes, then the cache will automatically invalidate.
4
Execute the cached query and display the result.
The result is a table with filtered rows where value > 100, containing value and category columns.
SourceCache is ideal for production pipelines where data freshness matters, using the source backend for storage (Postgres, Snowflake, etc.).
Use ParquetCache for development
Use ParquetCache for iterative development with durable persistence:
Create use_parquet_cache.py:
import xorq.api as xofrom xorq.caching import ParquetCachefrom pathlib import Pathimport numpy as npcon = xo.connect()np.random.seed(42)data = xo.memtable({"value": np.random.randint(0, 500, 10000),"category": np.random.choice(["A", "B", "C"], 10000)}, name="sample_data")cache = ParquetCache.from_kwargs( source=con, relative_path="./pipeline-cache", base_path=Path(".").absolute())cached = ( data .filter(xo._.value >100) .group_by("category") .agg(count=xo._.value.count()) .cache(cache=cache))result = cached.execute()print("You should see a result like this:")print(result)
1
Import xorq.api for Xorq operations, ParquetCache for disk-based caching, pathlib.Path for path handling, and numpy for data generation. Connect to Xorq and create sample data.
2
Create ParquetCache for durable disk-based caching. ParquetCache stores results as Parquet files and auto-invalidates on source changes. The relative_path is the cache directory relative to base_path.
3
Build query pipeline: filter rows where value > 100, group by category, count rows in each group, and cache the aggregated result. The cache persists across sessions and invalidates when source data changes.
4
Execute the cached query and display the result.
You’ll see an aggregated table with category and count columns, where each row shows the count of values > 100 for that category.
ParquetCache provides durable persistence across sessions, making it excellent for iterative development workflows.
Use snapshot caches for reproducibility
Use snapshot caches when you need fixed snapshots without automatic invalidation:
Create use_snapshot_cache.py:
import xorq.api as xofrom xorq.caching import ParquetSnapshotCachefrom pathlib import Pathimport numpy as npcon = xo.connect()np.random.seed(42)data = xo.memtable({"value": np.random.randint(0, 500, 10000),"category": np.random.choice(["A", "B", "C"], 10000)}, name="sample_data")snapshot_cache = ParquetSnapshotCache.from_kwargs( source=con, relative_path="./experiment-v1", base_path=Path(".").absolute())cached = data.filter(xo._.value >100).cache(cache=snapshot_cache)result = cached.execute()print("You should see a result like this:")print(result)
1
Import xorq.api for Xorq operations, ParquetSnapshotCache for snapshot caching, pathlib.Path for path handling, and numpy for data generation. Connect to Xorq and create sample data.
2
Create ParquetSnapshotCache for fixed snapshots without auto-invalidation. Snapshot caches must be manually cleared when data updates occur. The relative_path is the cache directory for this experiment.
3
Apply filter and cache with snapshot cache. This cache will NOT invalidate automatically, but you control when to clear it.
4
Execute the cached query and display the result.
The filtered result contains rows where value > 100, with columns for value and category.
Snapshot caches are ideal for reproducible research and experiments that require manual control over the cache lifecycle.
Clear cache when needed
Clear snapshot caches manually when data updates or to free disk space:
Import shutil for directory operations and pathlib.Path for path handling. Delete the cache directory to clear all cached results for that cache instance. After removing, the subsequent execution will recompute and re-cache the results.
For ParquetCache, clear the cache directory when source data changes and you want to force recomputation:
import shutilfrom pathlib import Path# Clear ParquetCache directorycache_dir = Path("./pipeline-cache")if cache_dir.exists(): shutil.rmtree(cache_dir)print("Cache cleared - next execution will recompute")
Clear caches when:
Source data has changed, and you want fresh results
The cache directory is consuming too much disk space
You want to test performance without caching
Switching between different data versions or experiments
Cost vs. compute tradeoff
Balance cache storage costs against recomputation costs:
High compute cost, low storage cost: Use aggressive caching
Import ParquetCache for disk-based caching. Create a cache for storing ML pipeline results. When computation is expensive but storage is cheap, cache aggressively.
2
Cache the model training (fit) step. This stores the trained model to avoid recomputation.
3
Cache the transformation step: Cache both the model training and transformation steps to maximize performance benefits.
Low compute cost, high storage cost: Cache selectively
result = ( data .filter(xo._.value >100) .group_by("category") .agg(count=xo._.value.count()) .cache(cache=cache))
1
When storage is expensive, cache only the final expensive result. Skip caching cheap operations like filters and simple aggregations. Filter rows (don’t cache—cheap operation), group by category (don’t cache—cheap grouping operation), aggregate (relatively inexpensive), and cache only the final aggregated result.
Cache expensive operations (ML training, complex joins, aggregations) and skip caching cheap operations (simple filters, column selections). Monitor cache disk usage versus recomputation time to optimize the tradeoff.
Select backends for performance
Choose backends based on dataset size, query complexity, and performance requirements.
Backend performance characteristics
Backend
Dataset Size
Query Speed
Memory
Best For
Embedded backend, DataFusion
<10GB
Fast
Moderate
UDFs, complex transformations
DuckDB
10-100GB
Very Fast
Low
Analytical queries, Parquet files
PostgreSQL
>100GB
Moderate
Very Low
Massive datasets, existing databases
Pandas
<1GB
Moderate
High
Small datasets, prototyping
Note
These size thresholds are guidelines based on typical workloads. Actual performance depends on query complexity, available memory, and data distribution. Test with your specific workload to determine optimal backend selection.
Switch backends dynamically
Move data between backends for optimal performance:
Create switch_backends.py:
import xorq.api as xoimport numpy as npcon = xo.connect()duckdb = xo.duckdb.connect()np.random.seed(42)data = xo.memtable({"value": np.random.randint(0, 500, 10000),"category": np.random.choice(["A", "B", "C"], 10000)}, name="sample_data")data_duck = data.into_backend(duckdb)result = ( data_duck .filter(xo._.value >100) .group_by("category") .agg(count=xo._.value.count()) .execute())print("You should see a result like this:")print(result)
1
Import xorq.api for Xorq operations and numpy for data generation. Connect to different backends: Embedded backend DataFusion for datasets <10GB with UDF support, and DuckDB for datasets 10-100GB with fast analytics.
2
Create sample data in the embedded backend with a reproducible seed.
3
Move data to DuckDB backend for analytical queries. into_backend() uses zero-copy Arrow transfers for efficient data movement.
4
Execute analytical query on DuckDB backend. DuckDB is optimized for aggregations and analytical workloads: Filtering rows, grouping by category, and counting rows per group.
The result displays aggregated data with category and count columns, showing the count of values > 100 per category.
Choose the backend based on dataset size and query type, and test performance on your specific workload since results vary.
Backend selection guidelines
Use Embedded backend, DataFusion, when: Dataset <10GB, need custom UDFs, complex transformations, rapid iteration
Use DuckDB when: Dataset 10-100GB, analytical queries (aggregations, joins), working with Parquet files, need persistent storage
Use PostgreSQL when: Dataset >100GB, existing PostgreSQL infrastructure, need distributed query capabilities, complex SQL operations
Use Pandas when: Dataset <1GB, interactive prototyping, integration with existing Pandas code, quick exploration
Optimize query performance
Optimize individual queries and expressions for better performance.
Select only needed columns
Reduce data transfer by selecting only required columns:
result = data.execute()result = data.select("col1", "col2", "col3").execute()
1
Inefficient: Loads all columns from the table. This transfers unnecessary data and increases memory usage.
2
Efficient: Select only the columns you actually need. This reduces memory usage and speeds up data transfer.
Select only the columns you need to reduce memory usage and transfer time. This is especially important for wide tables with many columns.
Filter early
Apply filters as early as possible in the pipeline:
aggregated = ( data .group_by("category") .agg(count=xo._.value.count()))result = aggregated.filter(aggregated["count"] >100).execute()result = ( data .filter(xo._.value >100) .group_by("category") .agg(count=xo._.value.count()) .execute())
1
Inefficient: Processes all data, then filters after aggregation. This means the aggregation processes more rows than necessary. Group all rows first, count all rows in each group, then filter groups after aggregation using bracket notation to reference the aggregated column (aggregated["count"]).
2
Efficient: Filter before aggregation to reduce data volume. Filtering first means aggregation processes fewer rows, improving performance. Filter rows early to reduce data volume, group only the filtered rows, then count them per group.
Cache intermediate results
Cache expensive intermediate computations:
Create cache_intermediate.py:
import xorq.api as xofrom xorq.caching import ParquetCachefrom pathlib import Pathimport numpy as npcon = xo.connect()np.random.seed(42)data = xo.memtable({"id": range(10000),"value": np.random.randint(0, 500, 10000),"category": np.random.choice(["A", "B", "C"], 10000),"region": np.random.choice(["North", "South", "East", "West"], 10000)}, name="sample_data")cache = ParquetCache.from_kwargs( source=con, relative_path="./cache", base_path=Path(".").absolute())aggregated = ( data .filter(xo._.value >100) .group_by("category", "region") .agg( count=xo._.value.count(), avg_value=xo._.value.mean(), max_value=xo._.value.max() ) .cache(cache=cache))result1 = aggregated.filter(aggregated["count"] >50).execute()result2 = aggregated.group_by("category").agg(total_count=aggregated["count"].sum()).execute()print("You should see a result like this:")print(result1)print("\nRe-aggregated by category:")print(result2)
1
Import xorq.api for Xorq operations, ParquetCache for disk-based caching, pathlib.Path for path handling, and numpy for data generation.
2
Connect to Xorq and create sample data with multiple columns for aggregation.
3
Create a ParquetCache for storing intermediate results on disk.
4
Build an expensive aggregation (multi-column group-by) and cache the result. This aggregation is computationally costly, so caching allows reuse.
5
Filter the cached aggregation result. Uses bracket notation (aggregated["count"]) to reference aggregated columns—using xo._.count would call the method instead of accessing the column.
6
Re-aggregate the cached result by category, summing count values. Both operations benefit from the cached intermediate result.
7
Display both results to show how the cached intermediate result is reused.
The first result shows filtered aggregated rows where count > 50, with columns for category, region, count, avg_value, and max_value. The second result shows re-aggregated data grouped by category only, with category and total_count columns.
Balance cache storage costs against recomputation costs based on your workload patterns.
Troubleshooting
When optimizing performance, you may encounter issues with caching, backend selection, or query performance.
Issue: Pipeline is still slow after caching
Error: Performance doesn’t improve after adding cache
Cause: Cache never hits, caching wrong operations, or cache overhead exceeds benefit
Recovery:
Verify cache hits: Measure execution time to detect cache hits (cache hits are typically 5-50x faster than cache misses)
Profile to identify real bottleneck: Cache may not be the issue
Cache more selectively: Cache only expensive operations
Check cache I/O performance: A slow disk can negate cache benefits
Import time for timing measurements, xorq.api for Xorq operations, ParquetCache for caching, pathlib.Path for path handling, and numpy for data generation. Create sample data and cache.
2
First execution is a cache miss: Xorq computes and caches the result. This takes longer as it performs the actual computation.
3
Second execution is a cache hit: Xorq retrieves the result from cache. This is much faster (typically 5-50x) as it only reads from disk.
4
Calculate speedup ratio and verify results match. Cache hits should be significantly faster than cache misses. If speedup is <2x, the cache may not be working correctly.
The output displays execution times for both cache miss and cache hit, the calculated speedup, for example “5.2x faster”, and confirms that both results are identical (Results match: True).
Prevention:
Profile before and after caching to measure improvement
Cache only expensive operations (joins, aggregations, ML training)
Use fast storage (SSD) for cache directories
Issue: Wrong backend selected
Error: Performance worse than expected for dataset size
Cause: Using the wrong backend for the dataset size or query type
Recovery:
Profile on different backends: Test performance on Embedded, DuckDB, and PostgreSQL
Check dataset size: Verify actual data volume
Consider query type: Some backends are better for specific operations
Switch backends: Use .into_backend() to test different backends
Prevention:
Test backend performance on your specific workload
Start with Embedded, switch to DuckDB/PostgreSQL as data grows
Document backend selection criteria for your use case
Issue: Memory errors during optimization
Error: MemoryError or system freezes during optimization
Cause: Processing too much data at once or inefficient memory usage
Recovery:
Reduce data volume: Filter or sample data before processing
Process in chunks: Use batch processing for large datasets
Select fewer columns: Reduce memory footprint
Switch to a more memory-efficient backend: DuckDB or PostgreSQL
Prevention:
Monitor memory usage during development
Test on data samples before the full dataset
Use appropriate backends for dataset size
Next steps
You now have techniques for profiling, caching, backend selection, and query optimization to achieve production-ready pipeline performance.