The one-million table partitions challenge in an ATLAS experiment DB application @ CERN

Abstract

The presentation describes the challenges in designing a new DB system in the ATLAS experiment at CERN which needs to manage billions of rows with relatively short lifetime (weeks to months). For that system an approach of data grouping organised in table partitions might be appropriate. In the past years in ATLAS we made use of several partitioning techniques. With the broadly used range partitioning and its extension of automatic interval partitioning we add our own logic in PLSQL procedures and scheduler jobs to sustain data sliding windows in order to enforce various data retention policies. We also make use of list, reference and virtual column based partitioning. Some of our tables have 70000+ list partitions, others have 20000+ list sub-partitions. However for a first time we challenge ourselves with the one-million partitions limit per table in the database. What choices(options) we have and which one is potentially most suitable will be presented to the audience