Extension advisor
The Extension Advisor recommends PostgreSQL extensions based on detected query patterns combined with performance evidence. Suggestions are conservative — every recommendation requires both a pattern match AND a performance signal.
How it works
After query analysis, pocketPG checks installed extensions against query patterns in pg_stat_statements. Already-installed extensions are excluded automatically. The remaining recommendations are sorted by severity.
Recommendations
| Extension | When recommended |
|---|---|
| pg_trgm | LIKE/ILIKE with leading wildcard patterns — B-tree indexes cannot accelerate LIKE '%value' |
| GIN on JSONB | JSON containment (@>, ?) or path-access (->>) operators on unindexed columns |
| pg_partman | Time-range queries on large tables (>5M rows) for automated partition management |
| auto_explain | High P99/average variance suggesting unstable query plans |
| pg_repack | Tables with >20% dead tuples and stalled vacuum — online table rebuild without locks |
| Prepared statements | High-frequency stable queries with measurable planning overhead |
| TCP_NODELAY | High-frequency small-result queries with high variance and minimal DB work |
Each recommendation includes:
- Severity — HIGH or MEDIUM
- Evidence — matching query count, example query, total execution time
- Install SQL —
CREATE EXTENSIONcommand or configuration snippet - Index hint — sample DDL for the recommended index type
- Documentation link — official PostgreSQL docs for the extension