Two extensions are particularly valuable for real applications. The bloom extension, enabled with CREATE EXTENSION bloom, supplies a Bloom filter index that supports many equality predicates across multiple columns in a compact form; it is a strong fit for star-schema fact tables where any combination of filter columns may appear in queries. The pg_trgm extension provides trigram (three-character substring) similarity functions and GIN or GiST operator classes, most importantly gin_trgm_ops, which accelerate LIKE, ILIKE, and similarity (%) searches on text far faster than sequential scans when the pattern is not anchored to the prefix.
Full-text search is another extension-flavored workload that lives primarily on GIN. The tsvector data type stores the normalized lexeme form of a document, produced by to_tsvector('english', text), and tsquery represents a parsed search expression. A typical setup creates GIN indexes like CREATE INDEX idx ON articles USING GIN (to_tsvector('english', body)), so queries of the form body @@ to_tsquery('english', 'postgres & indexing') are accelerated by the index. Exclusion constraints are also worth knowing here: they pair GiST indexes with operators such as && to prevent overlapping rows, so a booking system can forbid two reservations for the same room during overlapping periods.
These specialized indexes share the usual cost calculus. Bloom, trigram, and full-text GIN all accelerate reads dramatically but add cost to writes, and many of them grow quickly if the data is highly varied. The rule of thumb is to enable them only when a clear workload demands the access pattern, and to verify with EXPLAIN that the planner is actually choosing them.