Lesson 3.3: Partial Indexes
The Concept
Index only the rows you actually query:
-- Problem: Table has 10M rows, but only 100k are 'active'
SELECT * FROM users WHERE status = 'active' AND city = 'NYC';
-- Regular index: 10M entries
CREATE INDEX users_city_idx ON users(city);
-- Partial index: 100k entries (100x smaller!)
CREATE INDEX users_active_city_idx ON users(city)
WHERE status = 'active';
Real-World Examples
1. Recent data queries:
-- AI logs table: 1B rows, but queries only last 7 days
CREATE INDEX model_logs_recent_idx ON model_logs(created_at, model_version)
WHERE created_at > NOW() - INTERVAL '7 days';
-- Index stays small (auto-expires old data)
-- Much faster than indexing all 1B rows
2. Soft deletes:
-- Only index non-deleted rows
CREATE INDEX documents_active_idx ON documents(user_id, category)
WHERE deleted_at IS NULL;
-- Queries on active documents use small index
SELECT * FROM documents
WHERE user_id = 123 AND deleted_at IS NULL;
3. Status-based queries:
-- E-commerce: Only index pending/processing orders
CREATE INDEX orders_active_idx ON orders(user_id, created_at)
WHERE status IN ('pending', 'processing');
-- Completed orders (99% of data) not indexed
-- Index stays small and fast
Partial Index Strategy for AI Workloads
Feature stores:
-- Index only recent features (last 30 days)
CREATE INDEX features_recent_idx ON user_features(user_id)
INCLUDE (feature_vector)
WHERE computed_at > NOW() - INTERVAL '30 days';
Model predictions:
-- Index only successful predictions
CREATE INDEX predictions_success_idx ON ml_predictions(user_id, model_version)
INCLUDE (prediction)
WHERE status = 'success' AND confidence > 0.7;
Key Takeaways
- Partial indexes only index rows matching a WHERE condition
- Can be 100x smaller than full indexes for selective queries
- Perfect for time-based queries (recent data only)
- Excellent for soft deletes and status-based filtering
- Query must include the partial index condition to use the index
- Dramatically reduces storage and maintenance costs for large tables
التالي · الدرس 18 من 45
Expression Indexes
قراءة 1 دقيقة
أنهيت 17 من 45 درسًا

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

