Why are bitmap indexes considered unsuitable for tables receiving concurrent row-level updates from many transactions, yet perfectly acceptable on a table that is rebuilt or loaded in a nightly batch?

## The mechanism, precisely A bitmap index stores, per distinct value, a bit vector over row positions. To keep it small it is compressed — run-length or word-aligned schemes — and physically divided into pieces, each covering a contiguous range of row positions (commonly called segments or chunks). A chunk is an opaque compressed blob: you cannot flip bit 4,271 inside it without decoding and re-encoding. Now update one row from `status='NEW'` to `status='PAID'`. The engine must: 1. Locate the chunk of the `NEW` bitmap covering that row's position, decompress it, clear the bit, recompress, write it back. 2. Do the same for the `PAID` bitmap: locate, decompress, set the bit, recompress, write. 3. Log both rewrites (before and after images), since they must be recoverable. That is two whole-chunk rewrites and their log records for a single-row change — write amplification an order of magnitude beyond a B+Tree, where the same update is two small leaf entries. ## Why concurrency collapses Because the chunk is the smallest editable unit, it becomes the effective unit of concurrency control. A transaction modifying one row must hold the chunk against concurrent modification for the duration of the transaction — otherwise two overlapping decompress-edit-recompress cycles would lose one of the updates. The consequence is severe and counter-intuitive: **two transactions updating completely unrelated rows serialize** if those rows fall in the same chunk and touch the same value. With a chunk covering, say, a few thousand rows, and a low-cardinality column where nearly every row shares one of a few values, collisions are the norm rather than the exception. Row-level concurrency, the whole point of a modern OLTP engine, is silently downgraded to range-level. Secondary effects follow: - **Deadlocks.** Transactions acquiring several chunks in different orders deadlock, on an index they never mention. - **Long waiters.** A single long transaction holding a chunk stalls every other writer whose rows fall in it. - **Index growth and fragmentation.** Repeated in-place rewrites under concurrency leave chunks split, poorly compressed and progressively larger, so the index degrades while the workload runs, and reads get slower too. - **Log volume.** Whole-chunk before/after images per row change flood the log and the replication stream. This is why the standard operational advice is blunt: do not put bitmap indexes on tables with concurrent DML. It is not a tuning problem; it is structural. ## Why batch loading is fine Every premise above fails in a batch-loaded warehouse table: - **One writer.** No concurrent transaction can collide on a chunk, so the locking granularity is irrelevant. - **Bulk build instead of incremental maintenance.** The standard pattern is drop the bitmap indexes, load the data, rebuild. A bitmap build is extremely fast: one pass over the loaded column, accumulating bits, then compressing each vector once. There is no decompress-recompress cycle per row and no logging of intermediate states. - **Read-mostly afterwards.** Between loads the index is only read, which is where bitmaps excel — compact representation, bitwise combination, counts by popcount. - **Partition-scoped loads.** If the table is partitioned by load period, only the new partition's indexes need building; the rest are untouched. The structure encodes a workload assumption: *write once in bulk, read many times with arbitrary predicate combinations*. Satisfy that assumption and the weakness never appears. ## What modern systems do instead Columnar analytic engines face the same tension and resolve it differently: data is organised into immutable segments with per-segment auxiliary structures (including bitmap-like ones), and changes are handled by appending to a delta area plus a deletion bitmap, then rebuilding segments in the background during compaction. The bitmap is never edited in place; it is regenerated. That is the general lesson — compressed, densely packed read structures should be rebuilt, not patched. ## Diagnosing it in the wild Symptoms of a bitmap index that has been let loose on an OLTP table: lock waits and deadlocks concentrated on that index, throughput collapsing as concurrency rises while single-threaded throughput looks fine, index size growing far beyond its rebuilt size, and a delete or update workload whose cost per row rises over the day and resets after a rebuild. The remedy is to drop the bitmap index and replace it with an appropriate B+Tree, or to move the analytical query to a replica or warehouse copy where a bitmap is legitimate. ## One-liner "The unit of update is a compressed chunk covering many rows, so single-row DML rewrites and locks a whole range — row-level concurrency degrades to range-level. In a batch-loaded table there is one writer and the index is rebuilt rather than patched, so the weakness never appears."