Prisma's pgbouncer=true on Supabase made every query 4 round-trips (postmortem)
18 hours ago
- Every database query cost four round-trips for six months due to a misconfiguration in Prisma with pgbouncer, adding significant latency to multiple endpoints.
- A crash on the booking page was caused by loading original camera images, but fixing it revealed a separate, more critical performance issue: the availability check taking 8-10 seconds.
- The root cause was using transaction mode with pgbouncer=true, forcing Prisma to wrap each query in four statements (BEGIN, PREPARE, EXECUTE, DEALLOCATE), each crossing the network.
- The fix was switching to session mode on port 5432 with connection_limit=5, eliminating the extra round-trips and improving most screens' performance by 3-4 times.
- The issue was initially misdiagnosed as geographical latency, but proper measurement showed it was per-call overhead, not slow work, leading to wasted efforts on caching and other layers.
- Verification of the fix involved checking pg_stat_statements to confirm DEALLOCATE ALL calls stopped and using ss -tnp to ensure all connections used the corrected port.
- Key lessons: costs that don't scale with load are per-call overhead, repeated patches on the same symptom signal a need to re-measure, and load-bearing config should be documented.
- A practical diagnostic: time a trivial health check on localhost, measure TCP round-trip to the database, and if the former is much higher, investigate the database driver's behavior.