What index should be used for searching by varchar array in PostgreSQL? What are BRIN and Bloom indexes?

For searching in a varchar array in PostgreSQL, GIN indexes with the gin_trgm_ops operator are often used when full-text or similar full-text indexing is needed.

BRIN (Block Range Index) is an index optimized for very large tables where data is physically ordered. It stores the minimum and maximum values per block of pages, saving space, but is suitable for range queries.

Bloom indexes are probabilistic data structures that allow quick membership testing with some probability of false positives. In PostgreSQL, Bloom indexes are used for multi-column indexes when regular indexes are inefficient.

Example of creating a GIN index for a varchar array:

CREATE INDEX idx_array_gin ON table USING GIN (array_column gin_trgm_ops);