Rental Marketplace Database Optimisation

We captured slow query logs during real peaks using pg_stat_statements rather than guessing, and ranked queries by total time consumed — the product of how slow each one is and how often it runs. That ranking showed that a handful of queries were responsible for most of the load.

For each, we read the execution plan with EXPLAIN ANALYZE to see what PostgreSQL was actually doing, which turned vague complaints about slowness into specific, fixable problems.

• Peak-time evidence from pg_stat_statements
• Queries ranked by total time, not just slowest
• Execution plans read for every top offender

Listing search was doing a sequential scan over 1.2 million listings because the combination of filters it used had no supporting index. A composite index matching those filters and the sort order let PostgreSQL go straight to the right rows, taking search p95 from 2,410ms to 38ms.

Availability checks were querying a date range per listing in a loop. Restructuring them and adding an index suited to range lookups turned hundreds of small queries into one efficient one.

• Composite index for search filters and sort
• Range-friendly indexing for availability
• Loops of small queries replaced by single queries

Owner dashboards ran many separate queries per listing. We rewrote them to aggregate in a single pass, bringing load times down sharply. The nightly payout job, which processed payouts one row at a time, was batched, shortening it from 38 minutes to four.

Read-heavy traffic moved to read replicas, connection pooling smoothed spikes, and a Redis cache sits in front of the hottest lookups. With the primary database no longer overloaded, six servers became two and peak CPU fell from 81% to 23%.

• Dashboard queries aggregated in one pass
• Batched payouts: 38 minutes down to four
• Replicas, pooling and Redis for read-heavy traffic

Performance improvements fade if nobody watches them. Grafana dashboards now track query latency, CPU and cache hit rates, with alerts when key queries slow down.

CI performance checks run critical queries against a realistic dataset on every change, so a new feature that accidentally removes an index or adds an expensive query is caught before it reaches production rather than after users notice.

• Dashboards and alerts for query latency
• CI performance checks on realistic data
• Regressions caught before release