Setting up table partitioning for 1C-Bitrix

Setup of table partitioning for 1C-Bitrix

The b_stat_hit table has grown to 50 million rows, DELETE old records blocks it for minutes, and SELECT by date range does full scan. Partitioning splits one table into physically separate sections (partitions) — queries access only needed section, deleting old data — instant DROP PARTITION instead of heavy DELETE.

Which Bitrix tables should be partitioned

Not all tables benefit from partitioning. Candidates — large tables with time dimension:

Statistics tables — first candidate: data older than 3 months rarely needed, and DELETE FROM b_stat_hit WHERE DATE_HIT < '2025-01-01' on 30 million rows — 10 minutes of blocking.

Implementation: RANGE partitioning by date

Example for b_stat_hit:

ALTER TABLE b_stat_hit
PARTITION BY RANGE (TO_DAYS(DATE_HIT)) (
    PARTITION p_2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')),
    PARTITION p_2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')),
    PARTITION p_2025_03 VALUES LESS THAN (TO_DAYS('2025-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Important: MySQL requires partition column to be part of every unique index and primary key. For b_stat_hit primary key is ID. Either change PK to (ID, DATE_HIT) or use PARTITION BY RANGE(ID) — but then lose date binding.

Practical solution: remove auto-increment PK, create composite:

ALTER TABLE b_stat_hit DROP PRIMARY KEY, ADD PRIMARY KEY (ID, DATE_HIT);
ALTER TABLE b_stat_hit PARTITION BY RANGE (TO_DAYS(DATE_HIT)) (...);

Verify Bitrix doesn't use ID for JOIN — if not, composite PK is safe (ID remains unique in each partition, auto-increment continues).

Partition maintenance

Monthly cron script for rotation:

1. Create new partition — ALTER TABLE b_stat_hit REORGANIZE PARTITION p_future INTO (PARTITION p_2025_04 VALUES LESS THAN (TO_DAYS('2025-05-01')), PARTITION p_future VALUES LESS THAN MAXVALUE).
2. Delete old — ALTER TABLE b_stat_hit DROP PARTITION p_2024_10. Instant operation: partition file physically deleted.

Limitations in Bitrix context

• Core updates. main module on update can execute ALTER TABLE — if table structure changed and partitions not considered, update can break. Keep list of partitioned tables, check before update.
• D7 ORM. Bitrix\Main\ORM doesn't know about partitions — queries work transparently, but optimizer executes partition pruning only if WHERE has partition column condition.
• InnoDB limits. Max 8192 partitions per table (MySQL 8). For daily partitioning — 22 years.

What we configure

• Analyze table candidates: size, query pattern, growth
• Change primary keys for partitioning compatibility
• Create RANGE partitions by date
• Cron script for automatic partition rotation
• Check compatibility with Bitrix core updates
• Test: SELECT and DELETE execution time before and after partitioning