Database/growth hygiene-(Fix Slow Queries and Keep the Database Fast as Data and Traffic Grow)

READ: https://www.debug.school/rakeshdevcotocus_468/laravel-microservices-performance-optimization-a-priority-wise-plan-to-speed-up-holidaylandmark-2o9l

Missing indexes, query fixes, limits; connection pooling where needed
============Expected output:==========================
Large data/traffic par benefit; slow query confirmed ho toh uska fix turant higher priority par lao.
Keep the Database Fast as Data Grows
Fix Slow Database Queries
Improve Database Speed and Capacity
Prepare the Database for More Traffic
Handle Large Data Without Slowdowns
============================Prompt=================================
Act as a senior Laravel and PostgreSQL performance engineer. Audit only database growth hygiene in HolidayLandmark: connection pooling, missing foreign-key indexes, date filtering, substring searches, and unbounded queries.

Enter Plan Mode first. Do not change code, database schema, extensions, configuration, or server processes until I review and explicitly approve your proposed approach. Begin with read-only inspection.

Read the project’s AGENTS.md and CLAUDE.md. Verify the installed Laravel and PostgreSQL versions, database topology, existing indexes, connection usage, and deployment process. Apply PostgreSQL-specific recommendations only to services using PostgreSQL.

Inspect these five areas separately:

1. PgBouncer:
   - Inspect existing pooling, PostgreSQL connection limits, active/idle connections, PHP-FPM pools, and queue-worker connections.
   - Determine whether connection overhead or saturation justifies PgBouncer.
   - Evaluate session versus transaction pooling against actual application behavior.
   - Check compatibility with prepared statements, session settings, temporary tables, advisory locks, LISTEN/NOTIFY, and long transactions.
   - Propose pool sizes within the total database connection budget.
   - Preserve authentication, TLS requirements, and service isolation. Show configuration, rollout, monitoring, and rollback requirements.

2. Missing foreign-key indexes:
   - Inventory foreign keys and existing indexes, including composite indexes and their leading columns.
   - Identify referencing columns used in joins, filters, and parent update/delete checks that lack useful index coverage.
   - Do not add duplicate indexes or assume every foreign key needs a separate index.
   - Support recommendations with query patterns, table sizes, and execution plans.
   - Propose safe index creation and rollback, explaining locking, storage, write overhead, and Laravel migration constraints for concurrent index creation.

3. Range queries instead of whereDate:
   - Locate date predicates that apply functions or casts to indexed timestamp columns.
   - Evaluate index-friendly half-open ranges: timestamp >= start AND timestamp < next boundary.
   - Preserve the original calendar-day meaning, application/database time zones, timestamp types, daylight-saving behavior, and inclusive/exclusive boundaries.
   - Show exact before/after query code and required indexes.

4. pg_trgm for LIKE '%x%' searches:
   - Identify substring searches, table sizes, search frequency, and existing query plans.
   - Evaluate pg_trgm with suitable GIN or GiST indexes for the actual operators and expressions used.
   - Preserve case sensitivity, wildcard escaping, collation, filters, and ordering.
   - Address short search terms, index storage, and write overhead.
   - Verify extension availability and permissions. Show proposed extension/index migrations without applying them.

5. LIMIT on unbounded queries:
   - Find unrestricted get(), all(), raw queries, and API endpoints that return growing datasets.
   - Recommend bounded page sizes, pagination, cursor pagination, or chunked processing according to the use case.
   - Preserve deterministic ordering, filters, permissions, totals, exports, and batch-processing completeness.
   - Do not silently truncate results or apply LIMIT to jobs that must process every record.
   - Identify frontend and API-contract changes needed for pagination.

For every finding, show:

- Affected service, page, endpoint, or background job.
- Exact file paths and current line numbers.
- Existing Laravel code and generated SQL where available.
- Relevant table sizes, indexes, connection metrics, and execution-plan evidence.
- Why performance may degrade as data or traffic grows.
- Whether the issue is confirmed or needs measurement.
- Recommended approach and exact locations to change.
- Clearly labelled proposed before/after code, SQL, migrations, or configuration—do not apply them.
- Risks, focused validation, deployment requirements, and rollback steps.

Use EXPLAIN for initial inspection. Propose EXPLAIN ANALYZE only for safe, bounded reads in an approved test environment because it executes the query. Do not run write statements, load tests, extension installation, or index creation during the audit.

Compare query duration, execution plans, rows scanned/returned, buffers where available, response payload size, connection counts, and database CPU/memory under consistent conditions. Do not invent improvement percentages.

Finish with a prioritized implementation plan based on confirmed bottlenecks, growth risk, expected benefit, and operational impact. Separate query changes, index migrations, extension installation, and PgBouncer rollout. Explain the proposed fixes clearly enough for me to review and recommend adjustments.

Stop and wait for my approval. After approval, implement only the agreed changes, preserve existing functionality, and report changed files, line numbers, executed migrations or commands, validation results, and measured improvements. Avoid unrelated refactoring.