Lesson 3.5: GIN and GiST Indexes

GIN (Generalized Inverted Index)

Use cases:

• Full-text search
• JSONB queries
• Array containment
• ANY queries

Full-text search example:

-- Add tsvector column
ALTER TABLE documents ADD COLUMN search_vector tsvector;

-- Populate with trigger
CREATE FUNCTION documents_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        to_tsvector('english', COALESCE(NEW.title, '')) ||
        to_tsvector('english', COALESCE(NEW.content, ''));
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER documents_search_update
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW EXECUTE FUNCTION documents_search_trigger();

-- Create GIN index
CREATE INDEX documents_search_gin_idx ON documents
USING GIN(search_vector);

-- Fast full-text queries
SELECT * FROM documents
WHERE search_vector @@ to_tsquery('english', 'machine & learning');

JSONB indexing:

-- Index entire JSONB column
CREATE INDEX products_metadata_gin_idx ON products
USING GIN(metadata);

-- Fast JSONB queries
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata ? 'color';
SELECT * FROM products WHERE metadata->>'category' = 'electronics';

Array containment:

-- Index array column
CREATE INDEX documents_tags_gin_idx ON documents USING GIN(tags);

-- Fast array queries
SELECT * FROM documents WHERE tags @> ARRAY['AI', 'ML'];  -- Contains both
SELECT * FROM documents WHERE tags && ARRAY['AI', 'ML'];  -- Contains either

GiST (Generalized Search Tree)

Use cases:

• Geometric data (PostGIS)
• Range types
• Full-text search (alternative to GIN)
• Custom data types

Range queries:

-- Create range column for feature validity
ALTER TABLE user_features ADD COLUMN valid_period tstzrange;

-- GiST index on range
CREATE INDEX features_period_gist_idx ON user_features
USING GIST(valid_period);

-- Fast range queries
SELECT * FROM user_features
WHERE valid_period @> NOW();  -- Currently valid

SELECT * FROM user_features
WHERE valid_period && tstzrange('2024-01-01', '2024-12-31');  -- Overlaps

GIN vs GiST Comparison

Feature	GIN	GiST
Index size	Larger	Smaller
Build time	Slower	Faster
Query speed	Faster	Slower
Update speed	Slower	Faster
Best for	Read-heavy	Write-heavy

Recommendation for AI workloads:

• Full-text search: GIN (faster queries)
• JSONB: GIN (unless frequent updates)
• Embeddings: Specialized (IVFFlat, HNSW)

Key Takeaways

• GIN indexes are inverted indexes optimized for multi-value columns
• GIN excels at full-text search, JSONB, and array queries
• GiST is a balanced tree for geometric and range data
• GIN is read-optimized, GiST is write-optimized
• For AI workloads, use GIN for metadata and full-text search
• Both index types support custom operators and data types