What indexing strategies does PostgreSQL offer beyond standard B-Tree indexes?
PostgreSQL provides specialized index types tailored to diverse data structures:
• B-Tree: Default index for comparisons (<, <=, =, >=, >) and sort operations. • GIN (Generalized Inverted Index): Best for composite items containing multiple values, such as JSONB, Arrays, and Full-Text Search vectors. • GiST (Generalized Search Tree): Ideal for geometric, range types, and nearest-neighbor search. • BRIN (Block Range Index): Extremely compact indexes for massive tables where column values correlate with physical disk storage (e.g., sequential timestamps).
-- Create a GIN index on a JSONB metadata column for instant sub-document querying
CREATE INDEX idx_products_specs ON products USING gin (specifications);
-- Extremely fast lookup using the containment operator (@>)
SELECT id, title
FROM products
WHERE specifications @> '{"color": "Graphite", "ram_gb": 32}';The GIN index breaks the JSONB document into constituent keys and values, allowing PostgreSQL to locate documents matching multiple sub-criteria without parsing JSON rows individually.
Ready for JSONB & Arrays?
Take a quick test to evaluate yourself, or move straight to the next module.