Connection pooling and performance

Pooled versus session connections

Use the pooled string (DATABASE_URL) for app traffic. Use the regular or direct string for migrations, pg_dump, LISTEN/NOTIFY, advisory locks and SET. Plain SET does not survive between transactions on the pooled connection; use SET LOCAL.

Each database has a connection limit. On Connection, Reset Sessions clears stuck locks and idle transactions of your role.

Watch it

DB Metrics & Spikes shows load, spikes, active sessions and table sizes. Query Advisor:

1. Run Audit for schema issues.
2. Analyze Queries for slow queries over 100 ms, with index suggestions tested virtually.
3. Apply Index after reading the SQL.

The advisor needs pg_stat_statements enabled.

Costs

Compute is billed by active query time. Slow queries cost money and time.

Practice

Find your slowest query, apply a suggested index and compare.

Recap

• Pooled for apps, session for tools.
• Measure, fix, re-measure.