BBO - POC

Ref: #557874 (closed)

What does this MR do and why?

POC to demonstrates how to design the new BBO framework using LIST partitioning.

This table will be used as a queuing table and all invalid records will be move to a new partition in order to purge old partitions.

flowchart TD
    NEW[New Record] --> ACTIVE[Current Active Partition]
    ACTIVE --> SUCCESS[✅ Success → Stays]
    ACTIVE --> FAIL[❌ Failure]
    FAIL --> DELETE[Delete from old partition]
    DELETE --> REINSERT[Insert into current partition]
    
    OLD[Old Partition] --> EMPTY{Empty?}
    EMPTY -->|Yes| DETACH[Detach & Drop]
    EMPTY -->|No| WAIT[Has failed records →<br/>Move them forward]

Note:

• delete+re-insert the job upon failure so it always gets into the most recent partition.
• Getting first N rows sorted by a column (like timestamp) is not going to be a simple order by + limit (slow query plan).

(select * from partition_1 order by x limit 20)
UNION ALL
(select * from partition_2 order by x limit 20)
order by x limit 20