Go Query Optimization Techniques for PostgreSQL/MySQL

To optimize Go applications interacting with PostgreSQL or MySQL, focus on indexing, selective queries, connection handling, caching, and ORM efficiency. 1) Use proper indexing—identify frequently queried columns, add indexes selectively, and use composite indexes for multi-column queries. 2) Reduce data transfer—select only necessary columns, avoid SELECT *, paginate large datasets, and prevent N 1 queries with joins or batching. 3) Optimize connection handling—use a connection pool, set MaxOpenConns, MaxIdleConns, and ConnMaxLifetime appropriately. 4) Cache frequently accessed data—use Redis or Memcached, implement TTLs, and consider materialized views in PostgreSQL. 5) Manage ORM overhead—avoid unnecessary preloading, inspect generated queries, and prefer raw SQL when performance is critical.

When it comes to optimizing Go applications that interact with PostgreSQL or MySQL, the database layer is often where performance bottlenecks hide. The key isn't just writing clean Go code—it's understanding how your queries are executed and how your data is accessed.

Here’s what you can do to improve query performance in Go apps using PostgreSQL or MySQL.

Use Proper Indexing Strategically

One of the most common reasons for slow queries is missing or inefficient indexing. You might have a query that works fine with 100 rows but grinds to a halt at 100,000.

• Identify frequently queried columns, especially those used in WHERE, JOIN, and ORDER BY clauses.
• Add indexes selectively—too many indexes can slow down writes and take up unnecessary space.
• For composite queries (e.g., filtering by user_id and created_at), consider composite indexes in the right order.

For example:

CREATE INDEX idx_user_created ON users (user_id, created_at);

Use EXPLAIN ANALYZE in PostgreSQL or EXPLAIN in MySQL to check if your query uses an index. If it says "Seq Scan" or "Using filesort", you probably need to revisit your indexing strategy.

Reduce Data Transfer with Selective Queries

Fetching more data than needed is a silent killer of performance. It increases memory usage on both the database and application side, and slows things down over the wire.

• Always specify only the columns you need instead of using SELECT *.
• Avoid fetching large text/blob fields unless absolutely necessary.
• Paginate results when dealing with large datasets using LIMIT and OFFSET, or cursor-based pagination for better scalability.

Example:

rows, err := db.Query("SELECT id, name FROM users WHERE status = $1 LIMIT 100", activeStatus)

Also, avoid N 1 queries by batching or joining related data upfront. Tools like pgx for PostgreSQL or gorm for MySQL can help manage eager loading effectively.

Optimize Connection Handling

Even the fastest queries won’t help if your app is waiting for a connection from the pool.

• Set appropriate connection limits based on your database capacity.
• Reuse connections using a connection pool (like database/sql in Go).
• Tune parameters such as MaxOpenConns, MaxIdleConns, and ConnMaxLifetime.

Example:

db, err := sql.Open("postgres", connString)
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(time.Minute * 5)

Too many open connections can overwhelm your DB. Too few can create contention in high-load scenarios. Monitor actual usage and adjust accordingly.

Cache Frequently Accessed Data

Caching isn't just for HTTP layers—it's also powerful at the database level.

• Use Redis or Memcached to cache read-heavy data like configuration values or user profiles.
• Implement short TTLs for data that changes occasionally but not constantly.
• Consider materialized views in PostgreSQL if you're doing complex aggregations.

In Go, you can wrap query calls with a cache layer:

func getCachedUser(id string) (*User, error) {
    val, _ := redisClient.Get(id).Result()
    if val != "" {
        var user User
        json.Unmarshal([]byte(val), &user)
        return &user, nil
    }

    // Fallback to DB
    var user User
    err := db.QueryRow("SELECT ...").Scan(...)
    if err == nil {
        redisClient.SetEx(id, ..., time.Minute*10)
    }
    return &user, err
}

This reduces repeated queries and speeds up response times significantly.

Bonus: Watch Out for ORM Overhead

ORMs like GORM are convenient, but they can introduce overhead if not used carefully.

• Avoid automatic preloading unless you really need it.
• Be cautious with auto-generated queries—they may not be optimized.
• Prefer raw SQL or builder libraries like squirrel or pgconn when performance matters.

If you're seeing unexpected query patterns, log what your ORM is actually sending to the database. Sometimes a simple refactor can cut query count by half.

Optimizing queries in Go doesn’t always mean rewriting everything—it’s usually about identifying and fixing a few critical points. Start with the slowest queries, use proper tools, and don’t ignore the basics like indexing and connection handling.
Most of these improvements are low-effort compared to the gains they bring.

The above is the detailed content of Go Query Optimization Techniques for PostgreSQL/MySQL. For more information, please follow other related articles on the PHP Chinese website!