DEVOURED
How We Cut BigQuery Slot Usage by 90% On One Of Our Most Resource Hungry Service After a Production Outage
Teads slashed BigQuery slot usage by over 90% on their Audience Planning service following an outage, by coalescing requests, optimizing SQL queries, and refining data models and partitioning.
What: Teads significantly reduced BigQuery slot consumption for their Audience Planning service. This was achieved through application-level changes like using Redis distributed locks to coalesce duplicate queries and implementing fail-fast validation for oversized filters, alongside SQL query rewrites (e.g., replacing large IN clauses with semi-joins). Data model improvements included data type compression, precomputing repeated work, and a better partitioning strategy, which collectively cut the effective table footprint by approximately 95%.
Why it matters: This case study illustrates how a combination of application logic optimization, SQL query tuning, and data modeling best practices can yield dramatic cost and performance improvements in cloud data warehouses like BigQuery, especially under load. It also underscores the importance of responding to production outages with holistic architectural reviews.
Takeaway: Regularly audit and optimize your application's interaction with cloud data warehouses: coalesce duplicate queries, validate inputs early, rewrite inefficient SQL patterns (e.g., large IN clauses), and ensure your data model and partitioning strategy are efficient for your query patterns.
Decoder
- BigQuery: A fully managed, serverless enterprise data warehouse offered by Google Cloud that enables super-fast SQL queries using the processing power of Google's infrastructure. "Slots" represent units of computational capacity for queries.
- Redis distributed locks: A mechanism using Redis to ensure that only one process or application instance can execute a specific block of code or access a resource at a time across a distributed system, preventing race conditions or duplicate work.
- Semi-join: A type of database join operation that returns all rows from the left table for which there is at least one match in the right table, without including any columns from the right table.
Original article
Teads dramatically cut BigQuery slot usage by 90%+ on their Audience Planning service through application fixes (request coalescing with Redis distributed locks to eliminate duplicate queries, fail-fast validation for huge filters, and rewriting large IN clauses as semi-joins) combined with data model optimizations (compressing data types, precomputing repeated work, and an improved partitioning strategy), reducing the effective table footprint by ~95%.
Explore all 58 articles in today's digest
Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi