Database Indexing

Indexing strategies and best practices for Lager Guru database.

Overview

Proper indexing is crucial for database performance. This document outlines indexing strategies for the Lager Guru database schema.

Index Types

B-Tree Indexes (Default)

Standard indexes for most use cases.

CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);

GIN Indexes

For JSONB columns and full-text search.

CREATE INDEX idx_inventory_metadata_gin ON inventory_items USING GIN(metadata);

GiST Indexes

For geometric data and range queries.

CREATE INDEX idx_zones_coordinates_gist ON zones USING GIST(coordinates);

Core Indexes

Tenant Isolation

All tenant-scoped tables should have indexes on tenant_id:

-- Orders table
CREATE INDEX idx_orders_tenant_id ON orders(tenant_id);
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at DESC);

-- Inventory table
CREATE INDEX idx_inventory_tenant_id ON inventory_items(tenant_id);
CREATE INDEX idx_inventory_tenant_sku ON inventory_items(tenant_id, sku);
CREATE INDEX idx_inventory_tenant_location ON inventory_items(tenant_id, location);

Foreign Key Indexes

Index all foreign key columns:

-- Order items
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_item_id ON order_items(item_id);

-- Tasks
CREATE INDEX idx_tasks_worker_id ON tasks(worker_id);
CREATE INDEX idx_tasks_tenant_id ON tasks(tenant_id, status);

Lookup Indexes

Index frequently queried columns:

-- Users
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_tenant_id ON users(tenant_id);

-- Inventory
CREATE INDEX idx_inventory_sku ON inventory_items(sku);
CREATE INDEX idx_inventory_location ON inventory_items(location);

-- Orders
CREATE UNIQUE INDEX idx_orders_order_number ON orders(order_number);

Composite Indexes

Multi-Column Indexes

Create composite indexes for common query patterns:

-- Tenant + Status queries
CREATE INDEX idx_tasks_tenant_status ON tasks(tenant_id, status);

-- Tenant + Date range queries
CREATE INDEX idx_orders_tenant_date ON orders(tenant_id, created_at DESC);

-- Tenant + Location queries
CREATE INDEX idx_inventory_tenant_location ON inventory_items(tenant_id, location);

Covering Indexes

Include frequently selected columns in indexes:

CREATE INDEX idx_orders_covering ON orders(tenant_id, status) 
INCLUDE (order_number, created_at, customer_info);

JSONB Indexing

GIN Indexes for JSONB

Index JSONB columns for efficient queries:

-- Metadata queries
CREATE INDEX idx_inventory_metadata_gin ON inventory_items USING GIN(metadata);

-- Settings queries
CREATE INDEX idx_tenants_settings_gin ON tenants USING GIN(settings);

JSONB Path Indexes

Index specific JSONB paths:

-- Index specific JSONB field
CREATE INDEX idx_shipments_route_gin ON shipments USING GIN(route);

-- Query example
SELECT * FROM shipments WHERE route @> '{"status": "in_transit"}'::jsonb;

Performance Monitoring

Index Usage Statistics

Monitor index usage:

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

Unused Indexes

Identify unused indexes:

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND idx_scan = 0
  AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Best Practices

Index Creation

1. Index Foreign Keys: Always index foreign key columns
2. Index Tenant Columns: Index tenant_id in all tenant-scoped tables
3. Composite Indexes: Create for common multi-column queries
4. Covering Indexes: Use INCLUDE for frequently selected columns
5. JSONB Indexes: Use GIN indexes for JSONB columns

Index Maintenance

1. Regular Monitoring: Check index usage statistics
2. Remove Unused: Drop indexes that are never used
3. Rebuild When Needed: REINDEX large tables periodically
4. Analyze Tables: Run ANALYZE after bulk operations

Query Optimization

1. Use Indexes: Write queries that can use indexes
2. Avoid Functions: Don't use functions on indexed columns
3. Limit Results: Use LIMIT to reduce index scan size
4. Explain Plans: Use EXPLAIN ANALYZE to verify index usage

Example: Complete Index Strategy

-- Orders table indexes
CREATE INDEX idx_orders_tenant_id ON orders(tenant_id);
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at DESC);
CREATE UNIQUE INDEX idx_orders_order_number ON orders(order_number);

-- Inventory table indexes
CREATE INDEX idx_inventory_tenant_id ON inventory_items(tenant_id);
CREATE INDEX idx_inventory_tenant_sku ON inventory_items(tenant_id, sku);
CREATE INDEX idx_inventory_tenant_location ON inventory_items(tenant_id, location);
CREATE INDEX idx_inventory_metadata_gin ON inventory_items USING GIN(metadata);

-- Tasks table indexes
CREATE INDEX idx_tasks_tenant_id ON tasks(tenant_id);
CREATE INDEX idx_tasks_tenant_status ON tasks(tenant_id, status);
CREATE INDEX idx_tasks_worker_id ON tasks(worker_id);
CREATE INDEX idx_tasks_tenant_created ON tasks(tenant_id, created_at DESC);

Troubleshooting

Slow Queries

1. Check if indexes exist for query columns
2. Verify index usage with EXPLAIN ANALYZE
3. Consider composite indexes for multi-column queries
4. Review query patterns and adjust indexes

Index Bloat

1. Monitor index sizes
2. Rebuild indexes when needed: REINDEX INDEX index_name;
3. Consider partial indexes for filtered queries
4. Use VACUUM regularly

Related Documentation

• Database Schema Overview - Schema structure
• Database Schema - Complete schema reference
• RLS Policies - Security policies
• Migrations - Schema migrations

Next Steps

• Database Schema - Complete schema documentation
• Performance Tuning - Deployment optimization