Shopify's Path to a Faster Trino Query Execution: Custom Verification, Benchmarking, and Profiling Tooling

Summary

Shopify needed safer ways to evaluate Trino configuration changes and software upgrades as its forked, hundreds-node cluster handled more data and data scientists depended on fast queries. The Data Reliability team built a lightweight Python library that centralizes Trino query orchestration, then exposed it through PyTest fixtures for repeatable verification, including correctness checks and candidate-versus-control comparisons. The broader framework also supports benchmarking and profiling concepts such as replaying historical queries, reproducing traffic patterns, and testing autoscaling, although the project ultimately focused most of its performance-testing scope on verification. Extensive correctness tests helped the Data Foundations team complete a successful Trino upgrade, while p95 query execution time remained stable over the reported period; the library was designed to leave room for more advanced interfaces later.

Context

Shopify's Trino cluster had grown to hundreds of nodes and tens of thousands of virtual CPUs while handling over 500 Gbps, and the team targeted p95 query results in five seconds or less. Configuration experiments and software updates, especially on a Trino fork, required constant vetting because failures could interrupt data scientists and force engineers to roll back changes manually.

Approach / What changed

The team created a lightweight Python library built around shared classes for Trino query orchestration. Its higher-level Library class manages connections, query states, and multithreaded execution or cancellation, while Query handles annotations, safety checks, and result fetching. A PyTest interface exposes these capabilities through fixtures and test marks for repeatable correctness and performance checks, including running queries against candidate and standby clusters. The framework also accommodates benchmarking and profiling workflows.

Takeaways

• The shared library separates connection, query-state, multithreaded execution, and cancellation handling from lower-level query annotations, safety checks, and individual result fetching.
• PyTest marks and fixtures let engineers group correctness or performance tests, initialize repeatable cluster connections, clean up after tests, and compare candidate and standby Trino clusters.
• Performance testing required attention to statistics, environment differences, and late-arriving data such as resource usage; the team focused the project mainly on verification while keeping the library extensible.

Related reading