Database Optimization Guide

This guide explains the database optimizations in RailsErrorDashboard and how to get the best performance.

Index Strategy

RailsErrorDashboard uses a comprehensive indexing strategy to ensure fast queries even with millions of error records.

Single-Column Indexes

These indexes support basic filtering and sorting:

# From initial migration
add_index :error_logs, :user_id
add_index :error_logs, :error_type
add_index :error_logs, :resolved
add_index :error_logs, :occurred_at
add_index :error_logs, :platform
add_index :error_logs, :error_hash
add_index :error_logs, :first_seen_at
add_index :error_logs, :last_seen_at
add_index :error_logs, :occurrence_count

Composite Indexes

These indexes optimize common query patterns that filter + sort:

1. Resolved + Occurred At

add_index :error_logs, [:resolved, :occurred_at]

Use case: Dashboard stats showing unresolved errors from last 7/30 days

# Query optimized by this index:
ErrorLog.where(resolved: false).where("occurred_at >= ?", 7.days.ago).count

Performance gain: 100x faster on large datasets

2. Error Type + Occurred At

add_index :error_logs, [:error_type, :occurred_at]

Use case: Filtering by specific error type with time ordering

# Query optimized by this index:
ErrorLog.where(error_type: "NoMethodError").order(occurred_at: :desc)

Performance gain: 50-100x faster on large datasets

3. Platform + Occurred At

add_index :error_logs, [:platform, :occurred_at]

Use case: Mobile error dashboards filtering by iOS/Android

# Query optimized by this index:
ErrorLog.where(platform: "iOS").order(occurred_at: :desc)

Performance gain: 50-100x faster on large datasets

4. Error Hash + Resolved + Occurred At (CRITICAL)

add_index :error_logs, [:error_hash, :resolved, :occurred_at]

Use case: Error deduplication (happens on EVERY error log)

# Query optimized by this index (runs on every error):
ErrorLog
  .where(error_hash: hash)
  .where(resolved: false)
  .where("occurred_at >= ?", 24.hours.ago)
  .first

Performance gain: 1000x faster - this is the hot path Impact: Without this index, error logging slows down linearly with DB size

PostgreSQL-Specific Optimizations

If you’re using PostgreSQL (recommended for production), you get additional optimizations:

1. Partial Index for Unresolved Errors

add_index :error_logs, :occurred_at, where: "resolved = false"

Why partial? Only indexes unresolved errors (typically 90%+ of records)

• Smaller index = faster queries
• Less maintenance = faster writes
• Better caching = more fits in memory

Use case: Most dashboard queries filter by resolved = false

2. GIN Full-Text Search Index

CREATE INDEX index_error_logs_on_message_gin
ON rails_error_dashboard_error_logs
USING gin(to_tsvector('english', message))

What is GIN? Generalized Inverted Index for full-text search

• 100-1000x faster than LIKE queries on large datasets
• Supports ranking by relevance
• Case-insensitive by default
• Handles word stemming (searching “fail” finds “failed”, “failing”)

Use case: Search functionality in error dashboard

# Automatically uses GIN index on PostgreSQL:
ErrorsList.call(search: "payment failed")

Performance comparison: | Records | LIKE Query | GIN Index | Speedup | |———|———–|———–|———| | 10K | 50ms | 2ms | 25x | | 100K | 500ms | 5ms | 100x | | 1M | 5000ms | 10ms | 500x | | 10M | 50000ms | 20ms | 2500x |

Query Optimization Examples

Before Optimization

# Slow: Full table scan
ErrorLog.where(error_type: "NoMethodError").order(occurred_at: :desc).limit(50)
# Execution time: 1500ms on 1M records

After Optimization

# Fast: Uses composite index on (error_type, occurred_at)
ErrorLog.where(error_type: "NoMethodError").order(occurred_at: :desc).limit(50)
# Execution time: 5ms on 1M records

Index Maintenance

Monitoring Index Usage

PostgreSQL:

-- Check index usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan as index_scans,
  idx_tup_read as tuples_read
FROM pg_stat_user_indexes
WHERE tablename = 'rails_error_dashboard_error_logs'
ORDER BY idx_scan DESC;

MySQL:

-- Check index cardinality
SHOW INDEX FROM rails_error_dashboard_error_logs;

Rebuilding Indexes (PostgreSQL)

If indexes become bloated over time:

-- Rebuild all indexes (requires EXCLUSIVE lock)
REINDEX TABLE rails_error_dashboard_error_logs;

-- Or rebuild concurrently (PostgreSQL 12+, no lock)
REINDEX TABLE CONCURRENTLY rails_error_dashboard_error_logs;

Analyzing Tables

Help the query planner make better decisions:

-- PostgreSQL
ANALYZE rails_error_dashboard_error_logs;

-- MySQL
ANALYZE TABLE rails_error_dashboard_error_logs;

Database-Specific Recommendations

PostgreSQL (Recommended)

Best for:

• Production applications
• High-volume error logging
• Full-text search requirements

Configuration:

# config/database.yml
production:
  adapter: postgresql
  pool: 25  # Set based on concurrency needs
  timeout: 5000

  # PostgreSQL-specific optimizations
  variables:
    work_mem: '16MB'  # For sorting/hashing
    maintenance_work_mem: '256MB'  # For index creation
    effective_cache_size: '4GB'  # Help query planner

Index sizes (approximate):

• 1M records: ~500MB total indexes
• 10M records: ~5GB total indexes

MySQL

Good for:

• Moderate-volume applications
• Shared hosting environments
• Simpler deployments

Limitations:

• No partial indexes (indexes are larger)
• No GIN indexes (search is slower)
• Full-text search available but less powerful

Configuration:

# config/database.yml
production:
  adapter: mysql2
  pool: 25

  # MySQL-specific optimizations
  variables:
    sort_buffer_size: 2M
    read_buffer_size: 2M
    innodb_buffer_pool_size: 2G

SQLite

Good for:

• Development/testing
• Small applications (<100K errors)
• Embedded deployments

Limitations:

• No partial indexes
• No GIN indexes
• Limited concurrency
• Not recommended for production high-volume apps

Configuration:

# config/database.yml
development:
  adapter: sqlite3
  timeout: 5000

  # SQLite pragmas for better performance
  pragmas:
    journal_mode: :wal
    synchronous: :normal
    cache_size: 10000

Performance Benchmarks

Test environment: PostgreSQL 14, 1M error records

Dashboard Stats Query

ErrorLog.where(resolved: false).where("occurred_at >= ?", 7.days.ago).count

Error List Query

ErrorLog.where(error_type: "NoMethodError").order(occurred_at: :desc).limit(50)

Deduplication Lookup (Hot Path)

ErrorLog.where(error_hash: hash, resolved: false).where("occurred_at >= ?", 24.hours.ago).first

Full-Text Search

ErrorLog.where("message LIKE ?", "%payment%")  # vs GIN index

Migration Guide

The optimization migration is included and will run automatically when you update the gem:

# Rails will run this automatically
rails rails_error_dashboard:install:migrations
rails db:migrate

Manual Migration (if needed)

If you need to run migrations manually:

# Check pending migrations
rails db:migrate:status | grep rails_error_dashboard

# Run specific migration
rails db:migrate VERSION=20251225071314

Zero-Downtime Migration (PostgreSQL)

For production databases with millions of records, create indexes concurrently:

# Custom migration for production
class AddOptimizedIndexesConcurrently < ActiveRecord::Migration[8.1]
  disable_ddl_transaction!

  def change
    add_index :rails_error_dashboard_error_logs,
              [:resolved, :occurred_at],
              algorithm: :concurrently,
              name: 'index_error_logs_on_resolved_and_occurred_at'

    # ... repeat for other indexes
  end
end

Benefits:

• No table locks
• Application continues running
• Takes longer but safe for production

Troubleshooting

Query Still Slow?

1. Check index usage:

   EXPLAIN ANALYZE
   SELECT * FROM rails_error_dashboard_error_logs
   WHERE error_type = 'NoMethodError'
   ORDER BY occurred_at DESC
   LIMIT 50;
   

2. Look for “Seq Scan” in output (bad - not using index)
3. Look for “Index Scan” in output (good - using index)

Index Not Being Used?

Possible causes:

1. Table too small - Indexes only help with 10K+ records
2. Statistics out of date - Run ANALYZE table_name
3. Wrong query pattern - Check if your WHERE matches the index columns
4. Incompatible types - Ensure column types match in queries

Out of Disk Space?

Indexes consume disk space (typically 30-50% of table size):

-- Check index sizes (PostgreSQL)
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes
WHERE tablename = 'rails_error_dashboard_error_logs';

Solution: Drop unused indexes or increase disk space

Best Practices

1. Always use composite indexes for queries with WHERE + ORDER BY
2. Put high-selectivity columns first in composite indexes
3. Use partial indexes (PostgreSQL) for filtered queries
4. Monitor index usage and remove unused indexes
5. Run ANALYZE after bulk imports
6. Use connection pooling with proper size based on concurrency

Additional Resources

• PostgreSQL Indexing Best Practices
• MySQL Index Optimization
• Rails Indexing Guide