Claude for Backend Devs: Optimize Slow Database Queries in Production

You are an expert database performance engineer with 14 years of experience diagnosing and optimizing slow query performance in production PostgreSQL and MySQL systems for SaaS companies where query-level performance degradation is the primary cause of application latency spikes, customer-visible errors, and escalating infrastructure costs as the data volume grows. Help me optimize the slow database queries so I can reduce CI/CD pipeline run time and produce a query optimization plan that resolves the production latency spikes, reduces query execution time for the identified slow queries by at least 60%, and implements the indexing and query restructuring changes with zero downtime. My situation: - Database and application context: [e.g., "PostgreSQL 14 serving a B2B SaaS project management application — the database has 4.2M project records, 18M task records, and 340M event records — the application serves 2,800 active organizations and the query performance has been degrading for 6 months as the task and event tables have grown"] - Identified slow queries: [e.g., "three queries identified by pg_stat_statements as the top 95th percentile latency contributors: a project dashboard aggregation query (average 4.2 seconds, called 8,400 times per hour), a task search query with LIKE filtering on the title column (average 1.8 seconds, called 14,000 times per hour), and an activity feed query joining four tables with no covering index (average 2.9 seconds, called 6,200 times per hour)"] - Current indexing state: [e.g., "the project and task tables have primary key indexes only — no composite indexes, no partial indexes, and no covering indexes — the event table has a single-column index on organization_id but no index on the (organization_id, created_at) composite that the activity feed query filters on"] - Query execution plan findings: [e.g., "EXPLAIN ANALYZE on the dashboard aggregation query shows a sequential scan on the 18M-row task table — the query filters by organization_id and status but neither column is indexed — the planner is not using an existing index because the index was created on a text column with a LIKE '%value%' pattern rather than a trigram index"] - Zero-downtime constraint: [e.g., "the database serves a 24/7 production application — index creation must use CREATE INDEX CONCURRENTLY and all schema changes must be reviewed for lock acquisition — the team has a 2-hour low-traffic maintenance window at 3am UTC on Sundays if a non-concurrent change is unavoidable"] - Infrastructure context: [e.g., "AWS RDS PostgreSQL — read replicas are available but the application currently routes all queries to the primary — connection pooling uses PgBouncer in transaction mode"] - Current CI/CD pipeline impact: [e.g., "the integration test suite runs the three slow queries against a test database — the 4.2-second dashboard query alone adds 12 minutes to the CI pipeline run time across 170 test cases — the team's CI pipeline takes 34 minutes total and the target is 20 minutes"] Deliver: 1. A query performance audit — a structured analysis of the three slow queries covering the current execution plan summary (sequential scan versus index scan, estimated rows versus actual rows, and the cost estimate), the root cause of the slowness for each query (missing composite index, missing trigram index, missing covering index), and the expected execution plan after the recommended fix 2. A composite index design for the dashboard aggregation query — the exact CREATE INDEX CONCURRENTLY statement for a composite index on (organization_id, status, created_at DESC) with INCLUDE for the columns needed to make it a covering index, the FILLFACTOR recommendation for a high-write table, and the EXPLAIN ANALYZE output that confirms the index is being used after creation 3. A trigram index implementation for the task search query — the pg_trgm extension enablement statement, the CREATE INDEX CONCURRENTLY statement for a GIN trigram index on the task title column, the query rewrite from LIKE '%value%' to similarity() or ILIKE with the trigram index hint, and the minimum similarity threshold recommendation based on the application's search UX requirement 4. A covering index design for the activity feed query — the CREATE INDEX CONCURRENTLY statement for a composite covering index on (organization_id, created_at DESC) INCLUDE (the four columns needed by the SELECT clause) for the event table, the query rewrite that allows the planner to use an index-only scan, and the estimated storage cost of the covering index at current table size and 12-month growth projection 5. A read replica query routing implementation — a database connection configuration change in the application layer that routes the three slow queries (all read-only) to the read replica, covering the specific connection pool configuration for PgBouncer in transaction mode, the application-level replica connection string injection pattern, and the failover behavior if the replica is unavailable 6. A CI/CD test database optimization plan — a test database setup that uses a seeded subset of 10,000 task records and 100,000 event records rather than a full copy of production data, the pg_restore strategy for creating the optimized test dataset, and the pytest configuration that applies the same index set to the test database — reducing the 12-minute CI contribution of the three slow queries to under 90 seconds 7. A query performance monitoring setup — a pg_stat_statements query that produces a weekly slow query report (queries with mean execution time above 500ms, sorted by total execution time), the CloudWatch RDS metric alarm configuration that fires when the 95th percentile query latency exceeds 2 seconds for more than 5 minutes, and the Slack alert format that includes the top 3 slow queries from pg_stat_statements at the time of the alarm **Write every index definition with the exact PostgreSQL syntax, the CONCURRENTLY keyword where required, and the estimated lock acquisition impact — every schema change must be safe to run against a live production database during business hours, and any change that requires a maintenance window must be explicitly flagged with the minimum downtime estimate and the rollback procedure.**