Project Roomba

This post details the diagnosis and resolution of performance issues within Trello's Redshift database cluster, codenamed 'Project Roomba'. The core problems identified were inefficient data compression and sorting, leading to frequent long-running queries, query cancellations, and increased operational burden. The solution involved doubling the cluster size, replacing problematic tables with new versions utilizing high data compression encoding (which also re-sorted the data), and implementing ongoing sorting. This resulted in a 62% reduction in overall data size, a 65% reduction in the size of the 'atomic.events' table, and all events tables becoming 100% sorted. The improvements led to the elimination of 'killed' long-running queries, reduced the need for workaround code in data pipelines, fewer query alerts, and freed up data engineering time for other tasks.