Database Roundtrip #

Imagine an API endpoint that needs 8 seconds to respond. You open the database dashboard: CPU 15%, memory fine, no slow queries in the log — every query finishes in 2-5ms. So where does the 8 seconds come from? The answer is often database roundtrips: the endpoint runs 400 small queries sequentially, and each query requires one full application-database communication cycle costing 1-2ms each. The result: 400 × 2ms = 800ms just from communication overhead, before the query execution time itself. Database roundtrips are a cost invisible in EXPLAIN plans and absent from slow query logs, but their accumulation is very real in application latency. This article covers the anatomy of one roundtrip’s cost, why many small queries can cost more than one large query, and concrete techniques for reducing roundtrip counts without sacrificing code clarity.

The Anatomy of One Database Roundtrip #

Every time the application sends a query to the database, a series of steps happens before the result returns to the application. Each step has a small but real time cost.

The anatomy of one database roundtrip (simple query: SELECT name FROM users WHERE id = 42):

sequenceDiagram
    autonumber
    participant App as "Application"
    participant Net as "Network"
    participant DB as "Database"

    Note over App: "Application side"
    App->>Net: "Serialize the query & parameters to the wire protocol (MySQL/PostgreSQL)"
    App->>Net: "Send to the TCP socket"
    Note over App: "Wait for the response (blocking)"

    Note over Net: "Network side"
    Net->>DB: "The packet travels through the kernel network stack"
    Net->>DB: "NIC -> kernel buffer -> TCP stack"

    Note over DB: "Database side"
    DB->>DB: "Receive the packet, demarshal from the wire protocol"
    DB->>DB: "Parse the SQL -> tokenize -> parse tree"
    DB->>DB: "Query planner: check the plan cache, build an execution plan"
    DB->>DB: "Acquire internal locks"
    DB->>DB: "Execute: index lookup, read data pages, filter"
    DB->>DB: "Serialize the result to the wire protocol"
    DB->>Net: "Send the response via TCP"

    Note over Net: "Network side"
    Net->>App: "Send the response to the application"

    Note over App: "Application side (after receiving the response)"
    App->>App: "Deserialize the wire protocol into Go structs"
    App->>App: "Release the connection to the pool"

The communication overhead above happens for EVERY query, even if the query only reads 1 row. For a query returning 1 row in 0.1ms, the total communication overhead can be 10-20× larger than the query itself.

This is why 1,000 queries at 1ms each can be far slower than 1 query at 50ms — the former bears the communication overhead 1,000 times, the latter only once.

Measuring Roundtrip Overhead for Real #

Before optimizing, it’s important to measure how much roundtrip overhead actually happens in your system. This helps justify the optimization effort.

// A simple benchmark to measure roundtrip overhead in Go
func BenchmarkRoundtripOverhead(b *testing.B) {
    db, _ := sql.Open("mysql", dsn)

    b.Run("single_query_1000x", func(b *testing.B) {
        for i := 0; i < b.N; i++ {
            // 1000 separate roundtrips
            for j := 0; j < 1000; j++ {
                var name string
                db.QueryRow("SELECT name FROM users WHERE id = ?", j%100+1).Scan(&name)
            }
        }
    })

    b.Run("batch_query_once", func(b *testing.B) {
        for i := 0; i < b.N; i++ {
            // 1 roundtrip with an IN clause for 1000 IDs
            ids := make([]interface{}, 1000)
            for j := range ids { ids[j] = j%100 + 1 }
            placeholders := strings.Repeat("?,", 999) + "?"
            rows, _ := db.Query("SELECT name FROM users WHERE id IN ("+placeholders+")", ids...)
            for rows.Next() {
                var name string
                rows.Scan(&name)
            }
            rows.Close()
        }
    })
}

// Typical results (MySQL, same host):
// BenchmarkRoundtripOverhead/single_query_1000x   →  ~1,800ms per op
// BenchmarkRoundtripOverhead/batch_query_once      →  ~   12ms per op
// Ratio: 150× slower due to roundtrip overhead

In production, you can measure roundtrip overhead simply: record the time before and after every query, then compare total DB time with total request time. The difference is the non-query overhead (network, protocol, connection pool).

// A simple middleware to measure the DB time vs total request ratio
type DBTimer struct {
    totalDBTime   int64  // nanoseconds, atomic
    queryCount    int64
}

func (t *DBTimer) TimeQuery(fn func() error) error {
    start := time.Now()
    err := fn()
    elapsed := time.Since(start).Nanoseconds()
    atomic.AddInt64(&t.totalDBTime, elapsed)
    atomic.AddInt64(&t.queryCount, 1)
    return err
}

// At the end of the request:
// DB time: totalDBTime
// Query count: queryCount
// Roundtrip overhead per query = (totalDBTime / queryCount) - avg_query_exec_time

Five Techniques for Reducing Roundtrips #

Technique 1: Batching with IN Clauses #

The simplest and most impactful pattern: replace serial one-by-one queries with a single query fetching all data at once.

// ANTI-PATTERN: one roundtrip per user — N roundtrips total
func getUserNames(ctx context.Context, userIDs []int64) (map[int64]string, error) {
    result := make(map[int64]string)
    for _, id := range userIDs {
        var name string
        // Every iteration = one roundtrip
        err := db.QueryRowContext(ctx,
            "SELECT name FROM users WHERE id = ?", id,
        ).Scan(&name)
        if err != nil {
            return nil, err
        }
        result[id] = name
    }
    return result, nil
}
// For 100 users: 100 roundtrips × 2ms overhead = 200ms just from overhead

// CORRECT: one roundtrip for all users
func getUserNamesBatch(ctx context.Context, db *sqlx.DB, userIDs []int64) (map[int64]string, error) {
    if len(userIDs) == 0 {
        return nil, nil
    }

    // sqlx.In generates a query with the correct placeholders
    query, args, err := sqlx.In(
        "SELECT id, name FROM users WHERE id IN (?)", userIDs)
    if err != nil {
        return nil, err
    }

    type row struct {
        ID   int64  `db:"id"`
        Name string `db:"name"`
    }
    var rows []row
    if err := db.SelectContext(ctx, &rows, db.Rebind(query), args...); err != nil {
        return nil, err
    }

    result := make(map[int64]string, len(rows))
    for _, r := range rows {
        result[r.ID] = r.Name
    }
    return result, nil
}
// For 100 users: 1 roundtrip × 2ms overhead = 2ms — 100× more efficient

Technique 2: Multi-Statements in One Connection #

MySQL supports multiple statements in one query string (with the multiStatements=true flag). This lets several operations be sent in one roundtrip.

// DSN with multiStatements enabled
dsn := "user:pass@tcp(host)/db?multiStatements=true"
db, _ := sql.Open("mysql", dsn)

// One roundtrip for two UPDATE operations at once
result, err := db.ExecContext(ctx, `
    UPDATE orders SET status = 'processing' WHERE id = ?;
    INSERT INTO order_logs (order_id, status, created_at)
    VALUES (?, 'processing', NOW());
`, orderID, orderID)

// Without multiStatements: 2 roundtrips
// With multiStatements: 1 roundtrip

multiStatements=true carries a security risk if queries are built from user input — SQL injection can insert additional statements. Use it only for internal queries with known structure, always use prepared statements or parameter binding, and never interpolate user input into multi-statement queries.

Technique 3: CTEs (Common Table Expressions) to Combine Logic #

CTEs allow writing complex, multi-level queries in one statement, replacing several roundtrips with one longer query.

-- ANTI-PATTERN: three separate roundtrips for one business need
-- Roundtrip 1: get users active this month
SELECT id FROM users WHERE last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Roundtrip 2: get those users' orders
SELECT user_id, COUNT(*) as order_count
FROM orders WHERE user_id IN (/* result of roundtrip 1 */);

-- Roundtrip 3: get the most purchased products
SELECT product_id, COUNT(*) as buy_count
FROM order_items WHERE order_id IN (/* result of roundtrip 2 */);

-- CORRECT: one roundtrip with a CTE
WITH active_users AS (
    SELECT id
    FROM users
    WHERE last_login >= DATE_SUB(NOW(), INTERVAL 30 DAY)
),
user_orders AS (
    SELECT o.user_id, o.id AS order_id, COUNT(*) OVER (PARTITION BY o.user_id) AS order_count
    FROM orders o
    JOIN active_users au ON o.user_id = au.id
),
popular_items AS (
    SELECT oi.product_id, COUNT(*) AS buy_count
    FROM order_items oi
    JOIN user_orders uo ON oi.order_id = uo.order_id
    GROUP BY oi.product_id
)
SELECT p.id, p.name, pi.buy_count
FROM popular_items pi
JOIN products p ON pi.product_id = p.id
ORDER BY pi.buy_count DESC
LIMIT 10;
-- One roundtrip, whatever the complexity

CTEs are also useful for write operations needing data read first:

-- One roundtrip for read-then-write with a CTE in PostgreSQL
WITH eligible_orders AS (
    SELECT id
    FROM orders
    WHERE status = 'pending'
      AND created_at < NOW() - INTERVAL '24 hours'
)
UPDATE orders
SET status = 'expired'
WHERE id IN (SELECT id FROM eligible_orders)
RETURNING id, status;
-- Without a CTE: SELECT first (roundtrip 1), then UPDATE (roundtrip 2)
-- With a CTE: one roundtrip, atomic

Technique 4: Parallel Query Pipelines #

For queries independent of each other (no data dependencies), run them in parallel instead of sequentially. This doesn’t reduce the roundtrip count, but it reduces the wait time because all roundtrips happen simultaneously.

// ANTI-PATTERN: sequential queries — total time = sum of all queries
func GetDashboardData(ctx context.Context, userID int64) (*Dashboard, error) {
    // Roundtrip 1: user profile (5ms)
    user, err := getUser(ctx, userID)
    if err != nil { return nil, err }

    // Roundtrip 2: recent orders (8ms)
    orders, err := getRecentOrders(ctx, userID)
    if err != nil { return nil, err }

    // Roundtrip 3: unread notifications (3ms)
    notifications, err := getUnreadNotifications(ctx, userID)
    if err != nil { return nil, err }

    // Total: 5 + 8 + 3 = 16ms sequential overhead
    return &Dashboard{User: user, Orders: orders, Notifications: notifications}, nil
}

// CORRECT: parallel queries — total time = max of all queries
func GetDashboardDataParallel(ctx context.Context, userID int64) (*Dashboard, error) {
    var (
        user          *User
        orders        []Order
        notifications []Notification
        userErr, ordersErr, notifErr error
    )

    var wg sync.WaitGroup
    wg.Add(3)

    go func() {
        defer wg.Done()
        user, userErr = getUser(ctx, userID)       // 5ms
    }()

    go func() {
        defer wg.Done()
        orders, ordersErr = getRecentOrders(ctx, userID)   // 8ms
    }()

    go func() {
        defer wg.Done()
        notifications, notifErr = getUnreadNotifications(ctx, userID)  // 3ms
    }()

    wg.Wait()

    // Check all errors
    if err := errors.Join(userErr, ordersErr, notifErr); err != nil {
        return nil, err
    }

    // Total: max(5, 8, 3) = 8ms — not 16ms
    return &Dashboard{User: user, Orders: orders, Notifications: notifications}, nil
}

Note that parallel queries use more connection pool connections simultaneously. If the pool size is small and concurrency is high, this can become a new bottleneck. Always consider the connection pool size when applying this pattern.

Technique 5: Prepared Statements for Repeated Queries #

Prepared statements move the parsing and planning process to a single initial step, so subsequent executions are faster by skipping the parse and plan steps.

// Without prepared statements: parse + plan every time
for _, id := range orderIDs {
    db.QueryRowContext(ctx, "SELECT total, status FROM orders WHERE id = ?", id)
    // Parse + plan + execute per iteration
}

// With prepared statements: parse + plan only once
stmt, err := db.PrepareContext(ctx, "SELECT total, status FROM orders WHERE id = ?")
if err != nil {
    return err
}
defer stmt.Close()

for _, id := range orderIDs {
    stmt.QueryRowContext(ctx, id)
    // Only execute — parse and plan are cached
}
// For 1000 iterations: saves ~999 parse + plan overheads

In Go with database/sql, prepared statements are also safe for concurrent use from multiple goroutines — the driver handles per-connection statement caching automatically.

The Relationship Between Roundtrips and Connection Pools #

Database roundtrips and connection pools are closely related. Every roundtrip needs an active pool connection for the query’s duration. Too many simultaneous roundtrips can drain the pool.

Roundtrip and connection pool interaction:
──────────────────────────────────────────────────────────────
  Pool size: 20 connections

  Scenario A: 20 concurrent requests, each with 5 sequential queries
    → Each request needs 1 connection for 5 roundtrips
    → The pool can serve all (20 connections, 1 request each)

  Scenario B: 20 concurrent requests, each with 1 query but
              combined with parallel techniques (4 goroutines per request)
    → Each request needs 4 connections at once
    → 20 requests × 4 connections = 80 connections needed
    → The pool only has 20 → 60 goroutines waiting for connections!
    → Total time can be slower than sequential even though per-request is faster

  Conclusion:
    Parallel queries reduce per-request latency BUT add
    pressure on the connection pool. Adjust the pool size or limit
    the degree of parallelism based on pool capacity.
──────────────────────────────────────────────────────────────

The recommended connection pool configuration for systems with roundtrip optimization:

db.SetMaxOpenConns(25)     // maximum connections to the database
db.SetMaxIdleConns(10)     // idle connections maintained
db.SetConnMaxLifetime(5 * time.Minute)  // rotate connections to avoid staleness
db.SetConnMaxIdleTime(1 * time.Minute)  // close connections idle too long

Anti-Patterns Often Found #

// ✗ Anti-pattern 1: roundtrips for data that can be computed in the query
// Roundtrip 1: fetch all orders
orders, _ := db.QueryContext(ctx, "SELECT id, total FROM orders WHERE user_id = ?", userID)
// The app computes the total itself:
var grandTotal float64
for _, o := range orders { grandTotal += o.Total }

// ✓ Solution: compute in the database, one roundtrip only
var grandTotal float64
db.QueryRowContext(ctx,
    "SELECT COALESCE(SUM(total), 0) FROM orders WHERE user_id = ?", userID,
).Scan(&grandTotal)

// ✗ Anti-pattern 2: sequential roundtrips for data that can be joined
// Roundtrip 1:
var userCity string
db.QueryRowContext(ctx, "SELECT city FROM users WHERE id = ?", userID).Scan(&userCity)
// Roundtrip 2: (needs userCity from roundtrip 1)
db.QueryRowContext(ctx,
    "SELECT COUNT(*) FROM orders WHERE city = ?", userCity)

// ✓ Solution: a subquery or JOIN eliminates the sequential dependency
db.QueryRowContext(ctx, `
    SELECT COUNT(*)
    FROM orders o
    JOIN users u ON u.id = ?
    WHERE o.city = u.city
`, userID)

// ✗ Anti-pattern 3: roundtrips for validation that can be done in the INSERT
// Roundtrip 1: check whether the email already exists
var count int
db.QueryRowContext(ctx, "SELECT COUNT(*) FROM users WHERE email = ?", email).Scan(&count)
if count > 0 { return ErrEmailExists }
// Roundtrip 2: insert if it doesn't exist
db.ExecContext(ctx, "INSERT INTO users (email, name) VALUES (?, ?)", email, name)

// ✓ Solution: use INSERT ... ON DUPLICATE KEY (MySQL)
// or INSERT ... ON CONFLICT (PostgreSQL) — one roundtrip, atomic
result, err := db.ExecContext(ctx, `
    INSERT INTO users (email, name)
    VALUES (?, ?)
    ON DUPLICATE KEY UPDATE id = id  -- no-op, but we can detect it
`, email, name)
affected, _ := result.RowsAffected()
if affected == 0 { return ErrEmailExists }

// ✗ Anti-pattern 4: roundtrips for updates that can be conditional
// Roundtrip 1: read the current value
var currentStock int
db.QueryRowContext(ctx, "SELECT stock FROM products WHERE id = ?", productID).Scan(&currentStock)
if currentStock < qty { return ErrInsufficientStock }
// Roundtrip 2: update
db.ExecContext(ctx, "UPDATE products SET stock = stock - ? WHERE id = ?", qty, productID)

// ✓ Solution: conditional update in one roundtrip, check affected rows
result, _ := db.ExecContext(ctx, `
    UPDATE products
    SET stock = stock - ?
    WHERE id = ? AND stock >= ?
`, qty, productID, qty)
if affected, _ := result.RowsAffected(); affected == 0 {
    return ErrInsufficientStock
}

Diagnosing Excessive Roundtrips in Production #

When the API is slow but the database looks healthy, follow these diagnosis steps:

Roundtrip overhead diagnosis steps:
──────────────────────────────────────────────────────────────
  1. Enable query logging with nanosecond timestamps
     → Look for many identical or very similar queries

  2. Count the total queries per request
     → More than 20 queries per request → needs investigation

  3. Measure the ratio: (total_db_time / total_request_time)
     → If > 80% of request time is spent in the DB → check the query count
     → If < 20% but the request is still slow → another bottleneck exists

  4. Check whether queries run sequentially or in parallel
     → Overlapping query timestamps → parallel (good)
     → Sequential timestamps without overlap → sequential (could be parallelized)

  5. Look for identical query patterns within one request
     → Queries "SELECT ... WHERE id = 1", "SELECT ... WHERE id = 2", etc.
     → This is the clearest N+1 / excessive roundtrip signal
──────────────────────────────────────────────────────────────

In Go, a practical way to monitor this uses sql.DB.Stats():

// Run after a request finishes to see pool health
stats := db.Stats()
log.Info("db pool stats",
    "open_connections", stats.OpenConnections,
    "in_use",           stats.InUse,
    "idle",             stats.Idle,
    "wait_count",       stats.WaitCount,      // how many times goroutines waited for connections
    "wait_duration",    stats.WaitDuration,   // total connection wait time
)

// A high WaitCount → the pool size needs raising or the query count reducing
// A high WaitDuration → some goroutine is holding connections too long

Roundtrip Audit Checklist #

CODE REVIEW:
  □ Are there queries inside loops (for/range) that could be batched with IN?
  □ Are there several sequential queries that could be combined with JOIN or CTEs?
  □ Are there read-then-write patterns that could be combined with INSERT ON CONFLICT
    or UPDATE WHERE ... RETURNING?
  □ Are there independent queries that could run in parallel?
  □ Is there validation (EXISTS/COUNT) that could be merged into INSERT/UPDATE operations?

CONFIGURATION REVIEW:
  □ Are prepared statements used for repeated queries within one scope?
  □ Is the connection pool size appropriate for expected concurrent requests?
  □ Is MaxIdleConns too small (causing new connections to be constantly created)?

MONITORING:
  □ Is there a query_count_per_request metric per endpoint?
  □ Is the db.Stats().WaitCount metric monitored?
  □ Is total DB time vs total request time measured and compared?

Summary #

• Every roundtrip has a fixed cost — network traversal, TCP stack, wire protocol parsing, query planning, lock acquisition. For simple queries, this fixed cost can be 10-20× larger than the query’s execution time itself.
• 1,000 small queries can be far slower than 1 large query — even when the total data read is the same. Per-roundtrip overhead accumulates into hundreds of milliseconds without a single “slow” query.
• Batching with IN is the easiest and most impactful approach — replace per-ID query loops with one WHERE id IN (...) query. This can reduce 100 roundtrips to 1.
• CTEs allow multi-level logic in one roundtrip — read-then-write operations, nested calculations, and chained filters can be written as one SQL statement sent in one roundtrip.
• Parallel queries reduce wait time, not the roundtrip count — three independent queries run in parallel take max(t1, t2, t3) instead of t1+t2+t3. But they need more pool connections simultaneously.
• INSERT ON CONFLICT and UPDATE WHERE combine read + write into one roundtrip — uniqueness validation or pre-write condition checks can often be eliminated with these atomic patterns.
• Connection pools and roundtrips affect each other — parallel queries need more simultaneous connections. If the pool size isn’t enough, goroutines wait for connections and the parallel benefit is lost.
• Diagnosis: count queries per request, measure WaitDuration — a slow API with a healthy database almost always means too many roundtrips or poorly managed connections.

← Previous: N+1 Effect   Next: Bulk CUD Operation →