Index types supported in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL using extensions (Bloom, pg_trgm, and pg_bigm)

This article explores three PostgreSQL extensions for specialized indexing in Aurora PostgreSQL and RDS for PostgreSQL: Bloom, pg_trgm, and pg_bigm.

• Bloom indexes enable space-efficient multi-column equality filtering using probabilistic Bloom filters
• pg_trgm provides fuzzy text matching and similarity searches using trigram (3-character) segments
• pg_bigm optimizes full-text search for Asian languages using bigram (2-character) sequences
• Bloom indexes support tunable accuracy parameters (length, col bits) but only work with equality operators
• pg_trgm supports GIN and GiST indexes with configurable similarity thresholds for pattern matching
• pg_bigm uses GIN indexes with gin_bigm_ops for efficient LIKE searches on non-whitespace languages
• All three extensions trade-off index size, query flexibility, and accuracy for specialized search capabilities

This concludes a four-part series on PostgreSQL indexing strategies, providing comprehensive toolkit for optimizing diverse database workloads.