database/sql Row Limits and Streaming Behavior in Go

Baseline recommendation

Treat the row limit as part of the SQL statement, not as a database/sql API feature. Pass a query containing the engine's row-limit clause—typically LIMIT or FETCH FIRST—to QueryContext, then consume the result set incrementally with Rows.Next. Close Rows as soon as iteration ends and check Rows.Err after the loop. Do not load all rows into a slice, and do not default to driver-specific extensions unless the dialect lacks the needed clause.

Confirmed facts

• database/sql defines no portable pagination or row-limit method.
• Rows.Next streams rows one at a time from the driver's result set.
• Rows must be closed to release the underlying connection and resources.
• Rows.Err reports iteration errors that occur after Next returns false.

Likely explanation

Because SQL syntax differs by engine, the standard library cannot enforce a single row-limit abstraction. The bound lives in the query text; database/sql only transports it. Streaming behavior depends on the driver, so a query that streams on one engine may buffer on another.

Steps for this case

1. Write the query with a row-limit clause appropriate for the target engine, for example LIMIT ? OFFSET ? or FETCH FIRST ? ROWS ONLY.
2. Execute it with QueryContext and pass any limit values as arguments, not string interpolation.
3. Iterate with Rows.Next, process each row, and return on error.
4. Call Rows.Close in a defer statement immediately after obtaining Rows.
5. Call Rows.Err after the loop finishes.

rows, err := db.QueryContext(ctx, `
  SELECT id, name FROM events
  ORDER BY created_at, id
  LIMIT ? OFFSET ?
`, limit, offset)
if err != nil {
    return err
}
defer rows.Close()

for rows.Next() {
    var id int64
    var name string
    if err := rows.Scan(&id, &name); err != nil {
        return err
    }
}
if err := rows.Err(); err != nil {
    return err
}

What to verify before relying on pagination

Current verification is required. Confirm the following against the exact database engine and driver version in use:

• Whether LIMIT, OFFSET, or FETCH FIRST is supported and what placeholder syntax the driver expects.
• Whether Rows.Next delivers rows incrementally or whether the driver buffers the full result set.
• Whether streaming remains available inside transactions, prepared statements, or multi-result statements.
• Whether context cancellation stops row delivery and releases resources promptly.

Offset versus keyset pagination

OFFSET is simple but often scans and discards skipped rows, so cost grows with the offset. Keyset pagination—filtering on the last seen ordered unique column—is usually more stable for large or changing datasets, but it requires a unique sort column and engine support. Results can still shift if the underlying data changes between pages.

Missing diagnostic detail

Which database engine and driver version are you targeting? The row-limit syntax and streaming guarantees depend on that combination, and the recommendation changes if the engine does not support the clause you plan to use.

Assumptions and uncertainty

• This guidance assumes a conventional row-based driver and a single active result set per connection.
• Driver-specific cursor or extension behavior is treated as unverified and should not be the default.
• Memory growth during iteration is not measured here; test it with your driver and data size.