Laravel Optimize Database Laravel Package

Getting Started

Minimal steps to get value from nunomaduro/laravel-optimize-database:

1. Install as a dev dependency:

   composer require nunomaduro/laravel-optimize-database --dev
   

2. Publish the migrations (check the README for the exact tag; it is typically optimize-database-migrations):

   php artisan vendor:publish --tag="optimize-database-migrations"
   

3. Review the newly published files in database/migrations. They are plain Laravel migrations that add indexes and foreign keys to common tables (e.g., jobs, failed_jobs, password_resets, personal_access_tokens).
4. Run them:

   php artisan migrate
   

Where to look first: The published migrations themselves—they are the entire product. Read each one to see which columns get indexes and which constraints are added. Also skim the package README for supported Laravel/database versions and any publish-tag quirks.

First use case: On a new Laravel project, after your base migrations have created the standard tables, publish and run these migrations to add missing performance indexes and integrity constraints. On an existing project, run them in staging first to catch data-integrity issues before production.

Implementation Patterns

• One-time setup per project. This is not a runtime package. You publish migrations once, commit them to your repository, and then they become part of your app’s migration history. You can keep the package as a dev dependency for future re-publishing or remove it entirely after committing.
• Customize before running. Treat the published migrations as a starting point. Remove indexes that duplicate existing ones (Laravel already indexes users.email and personal_access_tokens.token, for example). Add indexes for your own frequently queried columns. Adjust foreign-key cascade rules to match your domain.
• Fix migration ordering. If the package publishes migrations with fixed timestamps (e.g., 2021_01_01_000000), they may run before your own base migrations. Rename the published files to a timestamp after your latest base migration, or use the package’s config if one is provided.
• Integrate with CI/CD. Commit the migrations. Your existing php artisan migrate step in deployment will apply them. Use php artisan migrate --pretend in staging to review the exact SQL before touching production.
• Test from scratch. Run php artisan migrate:fresh in your test suite to verify the migrations work on a clean database. Use a separate test database or transactions to keep tests isolated.
• Use as a reference for future work. When you add new tables or columns, look at the package’s migration patterns for writing your own optimization migrations. You can also re-publish after a package update and diff the new migrations against your existing ones to discover newly recommended indexes.
• Integration tips. For MySQL large tables, consider ALGORITHM=INPLACE; for PostgreSQL, use CREATE INDEX CONCURRENTLY (the package may not do this automatically). For SQLite, ensure foreign keys are enabled in your connection config.

Gotchas and Tips

• Migration timestamp collision is the #1 pitfall. Published migrations often carry a fixed date that may be earlier than your app’s migrations. They will run before the tables exist and fail. Rename the published files to a later timestamp (e.g., 2024_01_01_000000_...) after your base migrations.
• Foreign-key failures on existing data. Adding foreign keys to tables with orphaned rows will fail. Clean the data first, add the constraint as nullable and backfill, or skip that migration and add it later. Always test on a copy of production data.
• Redundant indexes. Laravel’s default migrations already add some indexes (e.g., users.email unique, personal_access_tokens.token unique). The package may add overlapping indexes. Review and remove duplicates to avoid unnecessary write overhead.
• Large table locking. Adding indexes to large tables can lock writes. For zero-downtime, use online schema-change tools (pt-online-schema-change, gh-ost) or run during maintenance. The published migrations are plain Laravel, so you can modify them to use raw SQL with CONCURRENTLY for PostgreSQL.
• Database driver differences. Some indexes (e.g., full-text) are not portable. The package likely sticks to standard Laravel schema-builder methods, but check your driver. SQLite has limited index support and foreign keys are off by default.
• Debugging. Use php artisan migrate --pretend to see the exact SQL. After running, verify indexes with SHOW INDEX FROM table (MySQL) or \d table (PostgreSQL). Laravel’s db:table command (Laravel 8+) can also show indexes.
• Extension points. The published migrations are plain PHP—edit them directly. You can also copy the package’s migration stubs into your own custom generator if you have a multi-tenant or modular setup. There is no runtime API to hook into.
• Version compatibility. Check the package README for supported Laravel versions. Older Laravel versions may not support some schema methods (e.g., ->cascadeOnDelete()). The package may not be actively maintained, so test with your Laravel version.
• Removing the package. Once migrations are published and committed, you can safely remove the package from composer.json. If you want to receive future updates, keep it as a dev dependency and re-publish periodically to see new migrations.