maximum columns for brin bloom indexes

[Jaime Casanova]

Hi everyone,

When testing brin bloom indexes I noted that we need to reduce the
PAGES_PER_RANGE parameter of the index to allow more columns on it.

Sadly, this could be a problem if you create the index before the table
grows, once it reaches some number of rows (i see the error as early as
1000 rows) it starts error out.

        create table t1(i int, j int);
        
        -- uses default PAGES_PER_RANGE=128
        create index on t1 using brin(i int4_bloom_ops, j int4_bloom_ops ) ;
        
        insert into t1 
                select random()*1000, random()*1000 from generate_series(1, 
1000);
        ERROR:  index row size 8968 exceeds maximum 8152 for index "t1_i_j_idx"

if instead you create the index with a minor PAGES_PER_RANGE it goes
fine, in this case it works once you reduce it to at least 116

        create index on t1 using brin(i int4_bloom_ops, j int4_bloom_ops ) 
                with (pages_per_range=116);


so, for having:
two int columns - PAGES_PER_RANGE should be max 116
three int columns - PAGES_PER_RANGE should be max 77
one int and one timestamp - PAGES_PER_RANGE should be max 121 

and so on

-- 
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL