Here's my "analysis" of your unusual situation. But first, let me paraphrase what you are doing --
* Data is flowing into the table all the time.
* At (say) midnight, a length process is started, it does:
* Summarize yesterday's data (while ignoring today's)
* Then (possibly hours later), deleted yesterday's data.
That is, the table is never empty.
You may have a good use case for 2 partitions.
I have a similar technique in
"Using a Staging Table for Efficient MySQL Data Warehouse Ingestion" --
http://mysql.rjweb.org/doc.php/staging_table
It uses 2 _tables_ instead of 2 _partitions_. It avoids any "downtime" by doing an atomic RENAME TABLE to ping-pong the two tables.
There is probably little or no difference in performance, assuming one more thing...
Make it 3 partitions:
Yesterday, Today, and Future.
The goal is to
* Summarize Yesterday, then drop it.
* Load into Today
* `Future` is kept empty, and use REORGANIZE PARTITION to split Future into a new Future, plus a new Today. More discussion of "future" is in
http://mysql.rjweb.org/doc.php/partitionmaint
Keep in mind that most partition operations take an amount of time proportional to the number of rows that need to be shoveled around. I assume you want next to zero downtime for the summarization and partition reorganization.
(Yes, there may be some other way to do the partition maintenance for your 2-partition use case.)