Large JSON Responses in Go Without Running Out of Memory
Serve a 200,000-row JSON export in Go by streaming rows from the database: first byte in 1 ms instead of 161, 1 MB of heap instead of 57, and gzip on the fly.
The first version of an export endpoint is nearly always the same: query all rows into a slice, marshal the slice, write the bytes. It works in testing with twenty rows and keeps working until a customer has two million. Then the service allocates hundreds of megabytes for one request, the garbage collector works harder, the container is killed for exceeding its memory limit, and every other request on the instance dies with it. One user's export becomes everyone's outage.
Tested with Go 1.25.6 and modernc.org/sqlite v1.50.0, macOS on Apple Silicon (arm64)
The fix is to write the response while reading the rows, so memory stays flat however large the table is. This article builds both handlers against a 200,000-row SQLite table, serves them over real HTTP, and compares first-byte time, total time, bytes on the wire and the live heap inside the handler. It builds on the decoder-side techniques in streaming JSON and the marshaling rules in the tour. For results people page through rather than download whole, pagination is the better tool. Everything ran on Go 1.25.6 with modernc.org/sqlite v1.50.0.
Two handlers
package main
import (
"compress/gzip"
"database/sql"
"encoding/json"
"fmt"
"net/http"
"runtime"
"strings"
"sync"
"sync/atomic"
_ "modernc.org/sqlite"
)
type Row struct {
ID int64 `json:"id"`
Email string `json:"email"`
Note string `json:"note"`
}
var dbs atomic.Int64
func OpenDB(n int) *sql.DB {
db, err := sql.Open("sqlite", fmt.Sprintf("file:big%d?mode=memory&cache=shared", dbs.Add(1)))
if err != nil {
panic(err)
}
db.SetMaxOpenConns(2)
db.Exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, note TEXT)`)
tx, _ := db.Begin()
st, _ := tx.Prepare(`INSERT INTO users (email, note) VALUES (?, ?)`)
note := strings.Repeat("n", 60)
for i := 0; i < n; i++ {
st.Exec(fmt.Sprintf("user%d@example.com", i), note)
}
st.Close()
tx.Commit()
return db
}
// Probe records the live heap, after a forced collection, at the moment a handler is holding the most data.
type Probe struct {
mu sync.Mutex
Live map[string]uint64
base uint64
}
func NewProbe() *Probe {
p := &Probe{Live: map[string]uint64{}}
p.base = heap()
return p
}
func heap() uint64 {
runtime.GC()
var ms runtime.MemStats
runtime.ReadMemStats(&ms)
return ms.HeapAlloc
}
func (p *Probe) Mark(label string) {
h := heap()
p.mu.Lock()
defer p.mu.Unlock()
if h > p.base {
p.Live[label] = h - p.base
}
}
// Buffered is the obvious handler: load everything, marshal everything, then write.
func Buffered(db *sql.DB, p *Probe) http.HandlerFunc {
return func(w http.ResponseWriter, r *http.Request) {
rows, err := db.QueryContext(r.Context(), `SELECT id, email, note FROM users ORDER BY id`)
if err != nil {
http.Error(w, "query failed", 500)
return
}
defer rows.Close()
all := []Row{}
for rows.Next() {
var x Row
rows.Scan(&x.ID, &x.Email, &x.Note)
all = append(all, x)
}
p.Mark("buffered: rows loaded")
body, err := json.Marshal(all)
if err != nil {
http.Error(w, "encode failed", 500)
return
}
p.Mark("buffered: body marshaled")
w.Header().Set("Content-Type", "application/json")
w.Write(body)
}
}
// Streamed writes each row as it is read. Nothing larger than one row is ever held.
func Streamed(db *sql.DB, p *Probe, total int) http.HandlerFunc {
return func(w http.ResponseWriter, r *http.Request) {
rows, err := db.QueryContext(r.Context(), `SELECT id, email, note FROM users ORDER BY id`)
if err != nil {
http.Error(w, "query failed", 500) // still possible: nothing has been written
return
}
defer rows.Close()
w.Header().Set("Content-Type", "application/json")
var out interface {
Write([]byte) (int, error)
} = w
var gz *gzip.Writer
if strings.Contains(r.Header.Get("Accept-Encoding"), "gzip") {
w.Header().Set("Content-Encoding", "gzip")
w.Header().Set("Vary", "Accept-Encoding")
gz = gzip.NewWriter(w)
defer gz.Close()
out = gz
}
out.Write([]byte("["))
enc := json.NewEncoder(out)
n := 0
for rows.Next() {
var x Row
if err := rows.Scan(&x.ID, &x.Email, &x.Note); err != nil {
return // the status is gone; closing without "]" leaves invalid JSON, which a client will notice
}
if n > 0 {
out.Write([]byte(","))
}
enc.Encode(x)
n++
if n == total/2 {
p.Mark("streamed: halfway")
}
}
if rows.Err() != nil {
return
}
out.Write([]byte("]"))
}
}Buffered is the version most code starts as. It runs the query, scans every row into a []Row, marshals the slice into a byte slice and writes it. At the end of that function the process has held the rows and then the rows plus their JSON encoding. The handler calls p.Mark at those two points; Mark forces a garbage collection and records the live heap above a baseline, so the numbers below are memory that was reachable, not garbage waiting to be collected.
Streamed opens the same cursor, writes [, and for each row calls Encode on an encoder that writes straight to the response, separating elements with commas. Memory never exceeds one Row plus the buffers inside the driver, the encoder and the HTTP server. If the client sends Accept-Encoding: gzip it wraps the writer in a gzip.Writer first, sets Content-Encoding and Vary, and closes the writer at the end. The query uses QueryContext(r.Context(), ...), so when the client disconnects the request context is cancelled and the query stops with it instead of reading the rest of the table for nobody.
The Probe marks a point halfway through the stream, at row 100,000, to show what the handler holds at its busiest.
What the clients saw
The program starts both handlers behind httptest.NewServer, so the bytes cross a real socket, and fetches each endpoint with a client that records the time to the first byte, the total time and the bytes on the wire, then decodes every row to make sure the document is valid.
package main
import (
"compress/gzip"
"encoding/json"
"fmt"
"io"
"net/http"
"net/http/httptest"
"time"
)
type result struct {
status int
firstByte time.Duration
total time.Duration
wireBytes int64
rows int
contentEnc string
}
func fetch(url string, gzipOK bool) result {
req, _ := http.NewRequest("GET", url, nil)
if !gzipOK {
req.Header.Set("Accept-Encoding", "identity")
} else {
req.Header.Set("Accept-Encoding", "gzip")
}
t0 := time.Now()
tr := &http.Transport{DisableCompression: true}
res, err := tr.RoundTrip(req)
if err != nil {
panic(err)
}
defer res.Body.Close()
var r result
r.status, r.contentEnc = res.StatusCode, res.Header.Get("Content-Encoding")
cr := &countingReader{r: res.Body}
var body io.Reader = cr
first := make([]byte, 1)
if _, err := io.ReadFull(cr, first); err != nil {
panic(err)
}
r.firstByte = time.Since(t0)
body = io.MultiReader(bytesReader(first), cr)
if r.contentEnc == "gzip" {
zr, err := gzip.NewReader(body)
if err != nil {
panic(err)
}
body = zr
}
dec := json.NewDecoder(body)
dec.Token()
for dec.More() {
var v Row
if err := dec.Decode(&v); err != nil {
panic(err)
}
r.rows++
}
r.total, r.wireBytes = time.Since(t0), cr.n
return r
}
func main() {
const n = 200_000
db := OpenDB(n)
p := NewProbe()
mux := http.NewServeMux()
mux.Handle("/buffered", Buffered(db, p))
mux.Handle("/streamed", Streamed(db, p, n))
srv := httptest.NewServer(mux)
defer srv.Close()
fmt.Printf("%-22s %8s %12s %10s %12s\n", "endpoint", "rows", "first byte", "total", "bytes on wire")
for _, c := range []struct {
label, path string
gz bool
}{{"buffered", "/buffered", false}, {"streamed", "/streamed", false}, {"streamed + gzip", "/streamed", true}} {
r := fetch(srv.URL+c.path, c.gz)
fmt.Printf("%-22s %8d %12s %10s %12d\n", c.label, r.rows, r.firstByte.Round(time.Millisecond), r.total.Round(10*time.Millisecond), r.wireBytes)
}
fmt.Println("\nlive heap held inside the handler (after a forced GC), above the baseline:")
for _, k := range []string{"buffered: rows loaded", "buffered: body marshaled", "streamed: halfway"} {
fmt.Printf(" %-28s %6.1f MB\n", k, float64(p.Live[k])/1e6)
}
}endpoint rows first byte total bytes on wire
buffered 200000 166ms 330ms 23177786
streamed 200000 1ms 190ms 23377786
streamed + gzip 200000 6ms 250ms 1112132
live heap held inside the handler (after a forced GC), above the baseline:
buffered: rows loaded 26.7 MB
buffered: body marshaled 56.8 MB
streamed: halfway 1.0 MBHelper code for counting bytes:
package main
import (
"bytes"
"io"
)
type countingReader struct {
r io.Reader
n int64
}
func (c *countingReader) Read(p []byte) (int, error) {
n, err := c.r.Read(p)
c.n += int64(n)
return n, err
}
func bytesReader(b []byte) io.Reader { return bytes.NewReader(b) }The table of results, from one run on an Apple M1 Max:
| endpoint | first byte | total | bytes on the wire |
|---|---|---|---|
| buffered | 161 ms | 330 ms | 23,177,786 |
| streamed | 1 ms | 190 ms | 23,377,786 |
| streamed + gzip | 5 ms | 250 ms | 1,112,132 |
And the live heap inside the handler, above baseline:
| point | live heap |
|---|---|
| buffered, rows loaded | 26.7 MB |
| buffered, body marshaled | 56.8 MB |
| streamed, halfway | 1.0 MB |
Four observations.
The first byte arrives 160 times sooner. The buffered client waits for the whole query and the whole marshal before it receives anything. The streamed client starts reading after the first row. For a browser or a proxy with an idle timeout, that difference decides whether a slow export works at all: a connection that is sending data is not idle.
Total time also dropped, from 330 ms to 190 ms, because the work overlaps. The client decodes while the server queries. Do not read much into the absolute numbers, since it is one run on an in-memory database over loopback, but the direction is reliable.
Memory is flat. 56.8 MB at peak for the buffered handler is for 200,000 rows of about 115 bytes. At two million rows it is ten times that, and with ten concurrent exports a hundred times. The streamed handler's 1.0 MB does not grow with the table.
Gzip is almost free here. The streamed body is 23.4 MB of repetitive JSON, and compression cut it to 1.1 MB, about 21 times smaller, for 60 ms more time. Highly regular data compresses far better than typical API data, so treat the ratio as an upper end; it is the speed that I would take from this, since the work happens in a pipeline with the encoding. The streaming body is 200,000 bytes larger than the buffered one because the encoder writes a newline after each row. That is valid JSON whitespace.
Tests
package main
import (
"compress/gzip"
"encoding/json"
"net/http"
"net/http/httptest"
"reflect"
"testing"
)
func get(t *testing.T, h http.Handler, gz bool) []Row {
t.Helper()
req := httptest.NewRequest("GET", "/", nil)
if gz {
req.Header.Set("Accept-Encoding", "gzip")
}
rec := httptest.NewRecorder()
h.ServeHTTP(rec, req)
body := rec.Result().Body
var rows []Row
var err error
if gz {
zr, e := gzip.NewReader(body)
if e != nil {
t.Fatal(e)
}
err = json.NewDecoder(zr).Decode(&rows)
} else {
err = json.NewDecoder(body).Decode(&rows)
}
if err != nil {
t.Fatal(err)
}
return rows
}
func TestStreamedMatchesBufferedPlainAndGzip(t *testing.T) {
db := OpenDB(500)
p := NewProbe()
want := get(t, Buffered(db, p), false)
if len(want) != 500 {
t.Fatalf("buffered returned %d rows", len(want))
}
if got := get(t, Streamed(db, p, 500), false); !reflect.DeepEqual(got, want) {
t.Fatal("plain stream differs from buffered")
}
if got := get(t, Streamed(db, p, 500), true); !reflect.DeepEqual(got, want) {
t.Fatal("gzip stream differs from buffered")
}
}
func TestEmptyTableStreamsAnEmptyArray(t *testing.T) {
db := OpenDB(0)
if rows := get(t, Streamed(db, NewProbe(), 0), false); len(rows) != 0 {
t.Fatalf("got %d rows", len(rows))
}
}=== RUN TestStreamedMatchesBufferedPlainAndGzip
--- PASS: TestStreamedMatchesBufferedPlainAndGzip (0.10s)
=== RUN TestEmptyTableStreamsAnEmptyArray
--- PASS: TestEmptyTableStreamsAnEmptyArray (0.00s)
PASS
ok example.test/lab 1.490sThe first test requires the plain stream and the gzip stream to decode to exactly the rows the buffered handler returns. This is the test that keeps a streaming rewrite honest: same data, different delivery. The second checks the empty table: [ and ] with nothing between, which is a valid empty array, and the comma logic must not misbehave for zero rows.
Failure halfway through
The thing streaming gives up is the ability to change your mind. After the first byte, the status line is on the wire, and a failure at row 80,000 can only close the connection. The handler above returns without writing the closing bracket, so the client receives an invalid document, and a decoder reports unexpected EOF. That is acceptable for a file download, where the client checks the result, and wrong for a client that processes rows as they arrive and assumes the stream was complete.
Handle it by design:
- Do everything that can fail cheaply before writing: authorization, parameter validation, the query itself (a syntax or permission error shows up before the first row), and the first
rows.Next(). Return a normal error response for those. - Make truncation detectable. A JSON array that ends without
]is invalid. For NDJSON, define a final line, such as{"end":true,"count":200000}, and tell clients that a stream without it is incomplete. - Log the cause and the number of rows sent. The client cannot tell you what went wrong.
- Consider HTTP trailers for a status after the body. Support among clients and proxies is uneven, so do not make it the only signal.
Slow clients and connections
A streaming handler is slow when its client is slow. Write blocks when the socket buffer is full, the cursor stays open, and the database connection stays checked out. A hundred clients on slow links can each hold a connection for minutes. Three guards:
- Bound the concurrency of exports. A semaphore around the handler, or a small separate pool, keeps ordinary requests from starving. The example allows two open connections; production should choose a number on purpose.
- Use write deadlines. The server-wide
WriteTimeoutkills long exports, so extend a deadline per write withhttp.NewResponseController(w).SetWriteDeadline, giving each chunk its own window (server timeouts). A client that stops reading is then dropped. - Read from a replica or a snapshot when you can. A long cursor on the primary holds resources for a long time.
Buffering in front of the app can also defeat the point. Reverse proxies and CDNs may buffer a response before forwarding it, which brings back the first-byte delay and puts the memory on the proxy. Check the setting for your proxy (reverse proxy in Go shows how the standard library handles flushing), and send periodic flushes with ResponseController.Flush if the stream is slow to produce.
Alternatives
Paginate. Most clients do not need the whole table; give them cursor pagination and let them ask for more. This is the right answer for anything a person looks at.
Make it a job. For an export that takes minutes, accept the request, return 202, build the file in the background, store it, and give the client a URL when it is ready (async jobs with 202). The client gets retries and resumability, and your web tier is not tied up. This is the better design for very large or expensive exports.
Use a different format. A CSV or NDJSON export is easier to stream, to resume and to load into other tools than one giant array. If your clients are scripts, offer it.
Compress, always. Whether you stream or not, gzip is the largest single saving for big JSON. Streaming with compression is shown above; Vary: Accept-Encoding keeps caches correct.
The pieces used are documented in encoding/json, compress/gzip and http.ResponseController.
