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

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. One of our tables in the production DB has 74000+ list partitions (180+ billion rows), other has 20000+ list sub-partitions. However, for a first time we challenge ourselves with the one-million partitions limit per table in the database. What options we have and which one is most suitable for our new use case will be presented to the audience.