Lager Guru Docs

English

Deutsch
Български

English

Deutsch
Български

Appearance

Database Performance Optimization ​

This document describes the performance optimizations applied to the Lager Guru database.

Last Updated: 2025-02-03

Overview ​

Performance optimizations were applied via migration 20250203000000_performance_optimization.sql to improve query performance without breaking existing functionality.

Key Principles:

  • ✅ Additive-only changes (indexes, views, functions)
  • ✅ No schema changes or data modifications
  • ✅ Backward compatible
  • ✅ RLS security maintained

Part 1: Missing Indexes ​

Strategy ​

Indexes were added on:

  1. tenant_id columns - Critical for multi-tenant filtering
  2. Frequently queried columns - status, created_at, assigned_to, etc.
  3. Composite indexes - For common query patterns (tenant_id + status, tenant_id + created_at)

Indexes Added ​

Zones ​

  • idx_zones_tenant_id - Tenant filtering
  • idx_zones_code - Code lookups
  • idx_zones_tenant_code - Composite for tenant + code queries

Inventory Items ​

  • idx_inventory_items_tenant_id - Tenant filtering
  • idx_inventory_items_tenant_sku - Tenant + SKU lookups
  • idx_inventory_items_tenant_zone - Tenant + zone filtering
  • idx_inventory_items_tenant_low_stock - Partial index for low stock items

Stock Movements ​

  • idx_stock_movements_tenant_id - Tenant filtering
  • idx_stock_movements_tenant_item - Tenant + item queries
  • idx_stock_movements_tenant_created - Tenant + date sorting
  • idx_stock_movements_tenant_type - Tenant + movement type

Pick Lists ​

  • idx_pick_lists_tenant_id - Tenant filtering
  • idx_pick_lists_tenant_status - Tenant + status filtering
  • idx_pick_lists_tenant_created - Tenant + date sorting
  • idx_pick_lists_tenant_assigned - Tenant + assignee filtering
  • idx_pick_lists_tenant_completed - Tenant + completion date

Shipments ​

  • idx_shipments_tenant_id - Tenant filtering
  • idx_shipments_tenant_status - Tenant + status filtering
  • idx_shipments_tenant_created - Tenant + date sorting
  • idx_shipments_tenant_driver - Tenant + driver filtering
  • idx_shipments_tenant_zones - Tenant + zone filtering

Safety Tables ​

  • idx_safety_incidents_tenant_id - Tenant filtering
  • idx_safety_incidents_tenant_created - Tenant + date sorting
  • idx_safety_incidents_tenant_category - Tenant + category filtering
  • idx_safety_incidents_tenant_severity - Tenant + severity filtering
  • idx_safety_actions_tenant_id - Tenant filtering
  • idx_safety_actions_tenant_assigned - Tenant + assignee filtering
  • idx_safety_actions_tenant_status - Tenant + status filtering
  • idx_safety_actions_tenant_due_date - Tenant + due date filtering

RLS Performance ​

  • idx_tenant_users_user_id - User lookups
  • idx_tenant_users_tenant_id - Tenant lookups
  • idx_tenant_users_user_tenant - Composite for user + tenant
  • idx_user_roles_user_id - User role lookups
  • idx_user_roles_role - Role filtering
  • idx_user_roles_user_role - Composite for user + role

Index Creation Method ​

Indexes are created using CREATE INDEX IF NOT EXISTS to avoid errors if they already exist.

Note: For production databases with large tables, consider creating indexes during a maintenance window as they may briefly lock tables. For very large tables, you can create indexes manually using CREATE INDEX CONCURRENTLY outside of a transaction block.

Part 2: RLS Policy Optimization ​

Strategy ​

RLS policies were optimized to:

  1. Reduce nested EXISTS queries - Use helper functions instead
  2. Reuse tenant_id filtering - Cache tenant IDs in helper functions
  3. Minimize per-row execution cost - Use STABLE functions

Helper Function ​

get_user_tenant_ids() - Returns all tenant IDs for the current user

  • Uses SECURITY DEFINER to bypass RLS recursion
  • Marked as STABLE for query planner optimization
  • Cached by PostgreSQL for the duration of the query

Policy Pattern ​

Before:

sql
USING (
  EXISTS (
    SELECT 1 FROM public.tenant_users tu
    WHERE tu.tenant_id = table.tenant_id
    AND tu.user_id = auth.uid()
  )
)

After:

sql
USING (
  tenant_id IN (SELECT tenant_id FROM public.get_user_tenant_ids())
  OR public.is_admin()
)

This reduces the number of EXISTS subqueries executed per row.

Part 3: Read-Optimized Views ​

Strategy ​

Created materialized views for common dashboard queries to:

  1. Pre-join related tables - Reduce join overhead
  2. Pre-calculate aggregations - Item counts, overdue flags, etc.
  3. Respect RLS - Views use helper functions for tenant filtering

Views Created ​

Admin Dashboard Views ​

admin_dashboard_zones

  • Zones with utilization data pre-joined
  • Includes utilization_percentage and active_shipments
  • Respects tenant isolation

admin_dashboard_inventory

  • Inventory items with zone info pre-joined
  • Includes low_stock flag (calculated)
  • Respects tenant isolation

admin_dashboard_pick_lists

  • Active pick lists with item count pre-calculated
  • Filters to pending/in_progress status
  • Respects tenant isolation

Safety Dashboard Views ​

safety_dashboard_incidents

  • Recent incidents with reporter and zone info pre-joined
  • Limited to 100 most recent
  • Respects tenant isolation and role-based access

safety_dashboard_actions

  • Open actions with assignee info pre-joined
  • Includes overdue flag (calculated)
  • Respects tenant isolation and role-based access

Worker Dashboard Views ​

worker_dashboard_pick_lists

  • Assigned pick lists with progress tracking
  • Includes items_picked and items_pending counts
  • Filters to pending/picking status
  • Respects tenant isolation and assignment

View Usage ​

Views can be queried directly:

sql
SELECT * FROM public.admin_dashboard_zones;
SELECT * FROM public.safety_dashboard_incidents;
SELECT * FROM public.worker_dashboard_pick_lists;

RLS policies are automatically applied through the helper functions used in the view definitions.

Part 4: Query Planner Optimization ​

ANALYZE Commands ​

The migration runs ANALYZE on all major tables to update statistics for the query planner. This helps PostgreSQL:

  • Choose optimal query plans
  • Use indexes effectively
  • Estimate row counts accurately

Note: ANALYZE should be run periodically (weekly/monthly) or after large data changes.

Part 5: Performance Monitoring ​

Helper Functions ​

check_index_usage()

  • Returns index usage statistics
  • Helps identify unused indexes that can be dropped
  • Low idx_scan values indicate unused indexes

check_table_sizes()

  • Returns table size statistics
  • Helps identify large tables that may need partitioning
  • Shows row counts, table size, and index size

Usage ​

sql
-- Check index usage
SELECT * FROM public.check_index_usage()
WHERE idx_scan < 10
ORDER BY tablename, indexname;

-- Check table sizes
SELECT * FROM public.check_table_sizes()
ORDER BY row_count DESC;

Performance Improvements ​

Expected Improvements ​

  1. Dashboard Queries: 50-70% faster due to indexes and views
  2. RLS Policy Evaluation: 30-50% faster due to reduced nested EXISTS
  3. Multi-tenant Filtering: 60-80% faster due to tenant_id indexes
  4. Join Operations: 40-60% faster due to composite indexes

Monitoring ​

Monitor query performance using:

  • Supabase Dashboard → Database → Query Performance
  • PostgreSQL pg_stat_statements extension
  • Application-level query timing

Maintenance ​

Regular Tasks ​

  1. Weekly: Run ANALYZE on frequently updated tables
  2. Monthly: Review index usage with check_index_usage()
  3. Quarterly: Review table sizes with check_table_sizes()
  4. As Needed: Drop unused indexes identified by monitoring

Index Maintenance ​

sql
-- Rebuild indexes if needed
REINDEX INDEX CONCURRENTLY idx_zones_tenant_id;

-- Update statistics
ANALYZE public.zones;

Backward Compatibility ​

✅ All changes are backward compatible:

  • No schema changes
  • No data modifications
  • No API changes required
  • Existing queries continue to work
  • Views are optional (can still query tables directly)

Rollback ​

If needed, rollback can be performed by:

  1. Dropping views: DROP VIEW IF EXISTS public.admin_dashboard_zones CASCADE;
  2. Dropping indexes: DROP INDEX CONCURRENTLY IF EXISTS idx_zones_tenant_id;
  3. Dropping functions: DROP FUNCTION IF EXISTS public.get_user_tenant_ids();

Note: Index drops using CONCURRENTLY require a separate transaction.

References ​

Released under Commercial License

Copyright © 2025 HashMatrix