pgoptimize
“PostgreSQL query analyzer and index advisor for Go applications”
A Go library that wraps EXPLAIN ANALYZE output parsing, automatic index suggestion generation, and slow query detection. Integrates directly into your Go database layer to surface optimization opportunities without manual EXPLAIN runs.
Installation
Requirements
Go 1.21 or newer
PostgreSQL 13+ for full EXPLAIN JSON output support
Docker Compose setup
Use pgoptimize with a PostgreSQL Docker container
Platform Notes
- Enable pg_stat_statements in PostgreSQL config for query tracking
Quick Start
Add pgoptimize to your Go project in 2 steps
Install the package
Add pgoptimize to your Go module
go get github.com/lordofthemind/pgoptimize@v2💡 Tips
- •Requires Go 1.21+
- •Compatible with database/sql and pgx/v5
Wrap your database connection
Wrap *sql.DB with pgoptimize.Analyzer to capture query plans
import (
"database/sql"
"github.com/lordofthemind/pgoptimize/v2"
)
db, _ := sql.Open("pgx", os.Getenv("DATABASE_URL"))
analyzer := pgoptimize.New(db, pgoptimize.Config{
SlowQueryThreshold: 100 * time.Millisecond,
LogIndexSuggestions: true,
AlertFunc: func(q pgoptimize.SlowQuery) {
slog.Warn("slow query detected",
"sql", q.SQL,
"duration", q.Duration,
"suggestion", q.IndexSuggestion,
)
},
})You're all set! Check out the detailed usage guide below for more advanced features.
Usage Guide
Detect slow queries automatically
Wrap db.Query to automatically log slow queries with suggestions
rows, err := analyzer.Query(ctx,
"SELECT * FROM orders WHERE user_id = $1 AND status = $2",
userID, "completed",
)
// If this query exceeds SlowQueryThreshold, AlertFunc is called with
// the SQL, duration, and suggested index DDLManually analyze a query
Run EXPLAIN ANALYZE and get a structured result
plan, err := analyzer.Explain(ctx,
"SELECT id, amount FROM orders WHERE user_id = $1",
userID,
)
if err != nil { return err }
fmt.Printf("Total cost: %.2f
", plan.TotalCost)
fmt.Printf("Actual time: %s
", plan.ActualTime)
fmt.Printf("Scan type: %s
", plan.ScanType) // "SeqScan" or "IndexScan"
if plan.HasIndexSuggestion() {
fmt.Println("Suggested index:", plan.IndexSuggestion)
}API Reference
New
Creates a new Analyzer wrapping the given *sql.DB connection.
Signature
New(db *sql.DB, config Config) *AnalyzerParameters
db*sql.DBRequiredPostgreSQL database connection
configConfigRequiredConfiguration: threshold, sample rate, alert function
Returns
*AnalyzerExamples
analyzer := pgoptimize.New(db, pgoptimize.Config{SlowQueryThreshold: 100*time.Millisecond})Analyzer.Query
Drop-in replacement for db.Query. Runs EXPLAIN ANALYZE if the query exceeds the threshold.
Signature
func (a *Analyzer) Query(ctx context.Context, sql string, args ...any) (*sql.Rows, error)Parameters
ctxcontext.ContextRequiredRequest context
sqlstringRequiredSQL query string with $1, $2 parameter placeholders
args...anyQuery parameters
Returns
(*sql.Rows, error)Examples
rows, err := analyzer.Query(ctx, "SELECT id FROM users WHERE email = $1", email)Analyzer.Explain
Runs EXPLAIN (ANALYZE, FORMAT JSON) on the given query and returns a structured QueryPlan.
Signature
func (a *Analyzer) Explain(ctx context.Context, sql string, args ...any) (*QueryPlan, error)Parameters
ctxcontext.ContextRequiredRequest context
sqlstringRequiredSQL query to analyze
Returns
*QueryPlan — contains TotalCost, ActualTime, ScanType, IndexSuggestionExamples
plan, err := analyzer.Explain(ctx, "SELECT * FROM orders WHERE user_id = $1", id)Analyzer.Report
Returns a summary report of all collected slow queries since the Analyzer was created.
Signature
func (a *Analyzer) Report() *AnalysisReportReturns
*AnalysisReport — contains SlowQueries[], TotalQueriesAnalyzed, UniqueQueriesAnalyzedExamples
report := analyzer.Report()Frequently Asked Questions
3 questions answered
Still have questions?
Check out the source code or open an issue on GitHub
Related Content
Explore related articles, projects, and tools.