Skip to main content
These actions can improve the throughput of INSERT/UPDATE/COPY statements to the BM25 index.

​
Move Merging to the Background

During every INSERT/UPDATE/COPY/VACUUM, the BM25 index runs a compaction process that looks for opportunities to merge segments together. The goal is to consolidate smaller segments into larger ones, reducing the total number of segments and improving query performance. Segments become candidates for merging if their combined size meets or exceeds one of several configurable layer thresholds. These thresholds define target segment sizes — such as 10KB, 100KB, 1MB, etc. For each layer, the compactor checks if there are enough smaller segments whose total size adds up to the threshold. By default, layer sizes 1KB, 10KB, 100KB and 1MB are merged in the foreground, while layer sizes 10MB, 100MB, 1GB, 10GB, 100GB, and 1TB are merged in the background. layer_sizes configures the foreground layers, while background_layer_sizes configures the background layers. 
-- Set the layer sizes at CREATE INDEX time
CREATE INDEX search_idx ON mock_items USING bm25 (id, description, rating) WITH (key_field = 'id', background_layer_sizes = '100MB, 1GB');

-- Change the layer sizes
ALTER INDEX search_idx SET (background_layer_sizes = '100MB, 1GB');
Setting background_layer_sizes to 0 disables background merging, and setting layer_sizes to 0 disables foreground merging. 
ALTER INDEX search_idx SET (layer_sizes = '0');
As a general rule of thumb, merging more layers in the background improves write throughput, since it is non-blocking. For instance, the following command moves all merging to the background. 
ALTER INDEX search_idx SET (layer_sizes = '0', background_layer_sizes = '100kb, 1mb, 100mb');

​
Increase Work Memory for Bulk Updates

work_mem controls how much memory to allocate to a single INSERT/UPDATE/COPY statement. Each statement that writes to a BM25 index is required to have at least 15MB memory. If work_mem is below 15MB, it will be ignored and 15MB will be used. If your typical update patterns are large, bulk updates (not single-row updates) a larger value may be better. 
SET work_mem = 64MB;
Since many write operations can be running concurrently, this value should be raised more conservatively than maintenance_work_mem.

​
Enable Mutable Segments

The mutable_segment_rows setting enables use of mutable segments, which buffer the rows written to the index in order to amortize the cost of indexing them. 
ALTER INDEX search_idx SET (mutable_segment_rows = 1000);
Setting mutable_segment_rows to a value greater than 0 will cause that many rows to be cheaply buffered at write time, and to instead be indexed at read time (i.e. during SELECT statements). This can significantly increase the number of writes that an index can sustain, at the cost of a proportional decrease in read throughput.
The content of mutable segments are indexed and held in memory at read time, so large mutable_segment_rows values are not advised.
⌘I