Context Cancellation and Query Timeouts in Go SQL Code

A database call without a deadline is a promise to wait as long as it takes. When the database is slow, locked or unreachable, that promise turns into goroutines piling up, a pool with every connection busy and a service that stops answering while looking healthy. The fix is one argument on every call: a context.Context with a deadline. This article shows what the deadline does on the client and on the server, on Go 1.25.6 and PostgreSQL 18.

The calls

database/sql has a Context variant of every operation. Use these and not their plain forms.

The context controls three waits: waiting for a free connection from the pool, the network round trip and the query's execution on the server. A cancelled context ends all three.

What happens at the deadline

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func main() {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 1. A query that would take 5 seconds, given 200ms.
	ctx, cancel := context.WithTimeout(context.Background(), 200*time.Millisecond)
	defer cancel()
	start := time.Now()
	var n int
	err = db.QueryRowContext(ctx, `select 1 from pg_sleep(5)`).Scan(&n)
	fmt.Printf("query error: %v\n", err)
	fmt.Printf("is DeadlineExceeded: %v, returned within 1s: %v\n", errors.Is(err, context.DeadlineExceeded), time.Since(start) < time.Second)

	// 2. The server side: was the backend cancelled, or is it still running?
	time.Sleep(300 * time.Millisecond)
	var running int
	_ = db.QueryRow(`select count(*) from pg_stat_activity where query like '%pg_sleep(5)%' and state = 'active' and pid <> pg_backend_pid()`).Scan(&running)
	fmt.Printf("backends still running the cancelled query: %d\n", running)

	// 3. statement_timeout is the server-side limit, independent of the client's context.
	_, err = db.Exec(`set statement_timeout = '100ms'`)
	if err != nil {
		log.Fatal(err)
	}
	conn, _ := db.Conn(context.Background())
	defer conn.Close()
	_, _ = conn.ExecContext(context.Background(), `set statement_timeout = '100ms'`)
	err = conn.QueryRowContext(context.Background(), `select 1 from pg_sleep(2)`).Scan(&n)
	fmt.Printf("server-side timeout: %.70s\n", fmt.Sprint(err))

	// 4. Cancel by hand.
	cctx, ccancel := context.WithCancel(context.Background())
	go func() { time.Sleep(100 * time.Millisecond); ccancel() }()
	err = db.QueryRowContext(cctx, `select 1 from pg_sleep(2)`).Scan(&n)
	fmt.Printf("cancelled by the caller: %v (is Canceled: %v)\n", err, errors.Is(err, context.Canceled))
}

go run . 2>&1

query error: timeout: context deadline exceeded
is DeadlineExceeded: true, returned within 1s: true
backends still running the cancelled query: 0
server-side timeout: ERROR: canceling statement due to statement timeout (SQLSTATE 57014)
cancelled by the caller: context canceled (is Canceled: true)

The call returns early. The program runs select 1 from pg_sleep(5) with a 200 ms timeout. The error is timeout: context deadline exceeded, and the program verifies errors.Is(err, context.DeadlineExceeded) is true and the call returned in under a second. The wrapped message adds the word timeout from the driver, and errors.Is sees through it, so use errors.Is and not string matching.

The server stops the query. It is not enough that the client stopped waiting; a query that keeps running on the server still consumes CPU and holds locks. When the context ends, pgx sends a cancel request on a separate connection, and PostgreSQL aborts the statement. The program waits 300 ms and then counts active backends running pg_sleep(5): backends still running the cancelled query: 0. The work really stopped. This is driver behaviour, so check it for the driver you use; MySQL's driver, for instance, kills the query by opening another connection and sending KILL QUERY, and not every driver or proxy does it.

A server-side limit is independent. statement_timeout is a PostgreSQL setting (max_execution_time on MySQL for SELECTs) that aborts any statement running longer than the limit, whatever the client says. The program sets it to 100 ms on a pinned connection and runs a 2 second query: the error is ERROR: canceling statement due to statement timeout (SQLSTATE 57014). Use it as the backstop for connections the client forgot to bound: set it per role (alter role app set statement_timeout = '30s'), per database, or per session in the DSN with options=-c statement_timeout=30000. A client deadline is the first line of defence and the server limit is the second.

A manual cancel looks different. A context cancelled by the caller after 100 ms returned context canceled, recognised by errors.Is(err, context.Canceled). Handlers should treat the two differently: a deadline means the database is slow, a cancel usually means the client went away and needs no alarm.

Choosing the deadline

There is no universal number. A reasonable way to choose:

1. Derive from the request. An HTTP handler's r.Context() already ends when the client disconnects. Wrap it with a timeout shorter than the handler's overall budget: if the API promises a response in 2 seconds, a database call gets one second at most.
2. Differentiate by query. An indexed lookup deserves a few hundred milliseconds. A report may legitimately take 30 seconds and should run from a different pool or a worker, not an HTTP handler.
3. Leave room for retries, so that attempts together fit the budget.
4. Look at the 99th percentile of the query under load and set the deadline a few times above it. A deadline at the median fails half the requests.

ctx, cancel := context.WithTimeout(r.Context(), 800*time.Millisecond)
defer cancel()
row := db.QueryRowContext(ctx, `select ...`, id)

defer cancel() releases the timer as soon as the function returns, which matters under high request rates.

Rows, transactions and the context

A context passed to QueryContext governs the whole life of the Rows: if it ends before you finish iterating, the iteration stops and rows.Err() returns the context error. That is another reason to check rows.Err(), as in the rows article. A context passed to BeginTx governs the transaction: when it ends, database/sql rolls the transaction back. A transaction with a WithTimeout context therefore cannot hold a connection past its deadline, which protects the pool from stuck work.

Errors to map

At the edge of your service, translate:

Instrument the timeouts: a counter by query name shows which statement is slow, which is more useful than a general latency graph.

Testing timeouts

Use a short deadline and pg_sleep as the lab does. A test with pg_sleep(5) and a 200 ms context takes 200 ms, not 5 seconds, because the cancel works. For code that wraps queries, assert that the function returns the context error through errors.Is, as in context cancellation tests. Assert too that no connection remains busy afterwards, with db.Stats().InUse.

A middleware for the whole service

Rather than wrapping each call, put the deadline where the request enters. An HTTP middleware that derives a context with a timeout from the incoming request and passes it down means every database call that uses r.Context() inherits a bound. Individual calls can tighten it further with a shorter child context, never loosen it, because a child context cannot outlive its parent. The same applies to gRPC deadlines, which arrive as the context of the handler. The failure mode to watch for is code that starts a goroutine or a background job from a request and passes context.Background(): the work then has no bound and no cancellation. Use context.WithoutCancel(ctx) only when you intend work to outlive the request, and then give it its own timeout.

Limits

A context cancels what the driver and server cooperate on. A hung network that drops packets without closing the connection is detected only when a read times out, so set TCP keepalives or the driver's read timeout for the case where the cancel request cannot reach the server. Poolers and proxies may not forward cancel requests. And cancellation is best effort for statements already in a non-interruptible phase, such as a long COMMIT flush. The PostgreSQL manual documents statement_timeout and the Go package page for context explains deadlines and cancellation.