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
التالي · الدرس 20 من 45
Vector Indexes (pgvector)
قراءة 2 دقيقة
أنهيت 19 من 45 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

