pgx.CollectRows: Nice APIs Don't Have To Be Slow
If you’re reading this, you probably already know that pgx is the de facto standard PostgreSQL driver for Go. Hopefully, you’re familiar with some of the convenience APIs, like CollectRows and RowToStructByName. These take pgx from a library that merely works well to one that is nice to use.
I’m very familiar with these APIs. A few years ago I investigated how they worked, benchmarked them, and found them to be much slower than they should be. This sent me down a rabbit hole that led me to submit patches to pgx to speed them up substantially, as well as create my own drop-in compatible library, pgx-collect, that pushes performance even further.
pgx got the UX of these functions basically right, but it implemented them using abstractions that limited their performance. Most of the improvements came from changing the underlying abstractions, letting the CPU do much less work while preserving the calling syntax. These problems and their solutions demonstrate important practices for efficient library design, in Go and more generally.
The APIs
The core APIs in pgx are relatively low-level operations. Issue a query, advance a row cursor, scan the current values of the row cursor into pointers, etc. These operations closely mirror how applications talk to the database, and every database driver needs APIs like these to work efficiently. However, they can make high-level operations verbose.
If you’ve used pgx, you probably recognize this standard pattern for collecting the rows returned by a query into a slice of records.
rows, err := conn.Query(ctx, "select id, name, age, created_at from people")
if err != nil {
return err
}
defer rows.Close()
var records []Record
var record Record
for rows.Next() {
err := rows.Scan(&record.ID, &record.Name, &record.Age, &record.CreatedAt)
if err != nil {
return err
}
records = append(records, record)
}
if err := rows.Err(); err != nil {
return err
}It’s not too long or complicated, but it’s a little long and complicated. In particular:
It takes at least 10 lines to write correctly.
It requires multiple rounds of error handling, and it’s easy to forget to check
rows.Err()at the end.The arguments to
rows.Scanhave to match up with the fields of the query string, or it will return an error, or worse, silently do the wrong thing. If you get the number or order wrong, the compiler will not help you.If the schema changes, the scan call needs to be updated to match the query and struct.
Fortunately, all the code above can be replaced with this:
rows, err := conn.Query(ctx, "select id, name, age, created_at from people")
if err != nil {
return err
}
records, err := pgx.CollectRows(rows, pgx.RowToStructByName[Record])
if err != nil {
return err
}CollectRows, and its friends like AppendRows1, abstract the loop above, keeping your code short and simple, and ensuring all the error paths get handled correctly. They also come with a collection of adapters of type RowToFunc[T] — i.e. func(CollectableRow) (T, error) — which describe how to map the scanned row values into slice elements. I especially like RowToStructByName. It takes the target type as a generic parameter, and it automatically picks the fields to populate for each result column by matching their names.
From a UX perspective, these are some great APIs. The problem comes when one looks at how they perform.
The benchmark
I wrote a basic benchmark that runs a simple query against a small PostgreSQL instance, and collects 1000 records from that query into a pre-allocated slice.
const N = 1000
var conn *pgx.Conn
func BenchmarkPgxAppendRows(b *testing.B) {
ctx := context.Background()
records := make([]Record, N)
for i := 0; i < b.N; i++ {
records = records[:0]
rows, err := conn.Query(ctx, "select id, name, age, created_at from people") // Returns N rows
if err != nil {
b.Fatal(err)
}
records, err := pgx.AppendRows(records, rows, pgx.RowToStructByName[Record])
if err != nil {
b.Fatal(err)
}
if len(records) != N {
b.Fatal()
}
}
}I also benchmarked two alternative implementations.
BenchmarkBaselinewrites the loop to scan rows into records and collect them into a slice by hand. It’s optimized to scan rows as quickly as possible and represents the best-case performance.BenchmarkPgxCollectAppendRowsis character-for-character the same asBenchmarkPgxAppendRows, except that it importsAppendRowsandRowToStructByNamefrompgx-collect.
How do they compare?2
It doesn’t look great for pgx.AppendRows. A convenience function is nice, but when it adds 1.8× more time and allocates 10× more memory than the baseline, one wonders if they should write it out the long way. In the context of long, slow queries this might not matter, but for short, frequent queries or in an otherwise busy system, it really could!
But for pgx-collect.AppendRows, it’s very close — just about 3%. It definitely does have a bit of overhead, but the difference between them is dwarfed by the run-to-run variation. I would struggle to find a situation where this would make a measurable impact in a real system.3
This discrepancy raises questions.
What is AppendRows doing?
Before we look at how I improved the performance, it will help to understand what pgx actually does with this line:
pgx.AppendRows(records, rows, pgx.RowToStructByName[Record])AppendRows is almost the exact loop we looked at earlier.
func AppendRows[T any, S ~[]T](slice S, rows Rows, fn RowToFunc[T]) (S, error) {
defer rows.Close()
for rows.Next() {
value, err := fn(rows)
if err != nil {
return nil, err
}
slice = append(slice, value)
}
if err := rows.Err(); err != nil {
return nil, err
}
return slice, nil
}The only difference is what happens inside the loop. Rather than scan the rows directly, it passes them to the provided RowToFunc. In this case, that’s RowToStructByName.
func RowToStructByName[T any](row CollectableRow) (T, error) {
var value T
err := (&namedStructRowScanner{ptrToStruct: &value}).ScanRow(row)
return value, err
}
type namedStructRowScanner struct {
ptrToStruct any
lax bool
}
func (rs *namedStructRowScanner) ScanRow(rows CollectableRow) error {
typ := reflect.TypeOf(rs.ptrToStruct).Elem()
fldDescs := rows.FieldDescriptions()
namedStructFields, err := lookupNamedStructFields(typ, fldDescs)
// ...
scanTargets := setupStructScanTargets(rs.ptrToStruct, namedStructFields.fields)
return rows.Scan(scanTargets...)
}
RowToStructByName matches the columns returned by the query with the fields of T by name (lookupNamedStructFields), extracts the pointers to the fields from the target struct (setupStructScanTargets), and finally scans the value from the rows into the targets.
Where does the time go?
Running the benchmark and capturing the CPU profile shows two clear hotspots.
View the profile through speedscope.app here.
(Select “Left-Heavy” for a traditional flamegraph view.)
19% of the total time is spent in
lookupNamedStructFields.10.1% of the total time is spent allocating memory (excluding the core
pgx
calls), and another 17.3% of the total time is spent in the garbage
collector.
lookupNamedStructFields gets the mappings from the columns in the query to the struct fields, the most expensive portions of which are a sync.Map lookup and the construction of the map key.
In allocations, the two primary offenders are the scanTargets slice allocated in setupStructScanTargets and the declaration of value in RowToStructByName. The scanTargets slice is relatively straightforward — a slice is created, so the backing array needs to be allocated. value, on the other hand, is a failure of escape analysis. Ideally, value would live on the stack. However, the compiler cannot prove that the pointer to it won’t escape somewhere inside ScanRow and outlive the function call, so it must allocate it on the heap.
What could be optimized?
I mentioned that I submitted patches to pgx to improve the performance of AppendRows. That landed in version 5.6. Before that, here’s what the benchmark showed.4
View the profile through speedscope.app here.
The runtime compared to the baseline is at ~4.25x. 49% of the CPU time is spent in appendScanTargets, which uses reflection to match the database columns with struct fields. This is repeated for every row, even though the result is identical each time.
The solution: cache the result. The mapping for a given (type, columns) pair never changes, so instead of recomputing it every time RowToStructByName is called, pgx can compute it once, store it in a global cache, and reuse it on every subsequent call.
After the first lookup, RowToStructByName no longer needs to walk the struct with reflection. It just loads the cached field information and uses it to build the scan targets for the current row. That single change roughly halved the overhead of AppendRows.
Unfortunately, this strategy can’t be used to optimize the remaining overhead. The largest costs are the lookups from the cache itself and the repeated allocations. The cache lookups should be optimized if possible, but given the constraints of a concurrent map and the natural key of the entries, it’s not clear how to do much better. Some repeated allocations could be reduced by pooling objects globally, but in my testing, this didn’t help — the pool lookups suffer the same global lookup costs, and the work saved is much lower, leading to worse performance.
We seem to have hit the limit of the existing API.
Changing the abstraction
RowToFunc is an elegant abstraction. It’s simple and powerful. It presents a convenient UX to the caller. The code inside AppendRows looks exactly how you would expect. However, the overhead we see is downstream of this abstraction because it does not cleanly map to the work it needs to do.
AppendRows(..., RowToStructByName[T]) should look up the (type, columns) metadata for the query once, then for each row set up the scan targets and write data into the backing slice. It should do this and nothing else while allocating as little memory as possible. With RowToFunc, this sequence of operations is functionally impossible.
pgx-collect chooses to use different abstractions that can do this. Where the pgx APIs center around the RowToFunc, pgx-collect organizes itself around the Scanner.
type Scanner[T any] interface {
Initialize(rows pgx.Rows) error
ScanRowInto(receiver *T, rows pgx.Rows) error
}Scanner provides a different interface than the RowToFunc so pgx-collect can write its loop a bit differently.
if err := scanner.Initialize(rows); err != nil {
return nil, err
}
for rows.Next() {
i := len(slice)
var zero T
slice = append(slice, zero)
err := scanner.ScanRowInto(&slice[i], rows)
if err != nil {
return nil, err
}
}The caller still sees AppendRows(..., RowToStructByName[T]), but instead of being a function called per-row, in pgx-collect, RowToStructByName is used to construct the scanner. This change enables three optimizations that, together, solve our biggest problems.
Separate per-row and per-query work
Scanner has separate methods for work done once at the start of processing a query and work done for each row. This allows AppendRows to do setup just once, rather than redoing it for every row.
For RowToStructByName, the main purpose of this is to look up the cached struct field information just once and store it in the Scanner, making it cheap to access in each loop iteration. Because the Scanner is scoped to the query, it’s just a pointer dereference, not a map lookup.
Avoid temporary allocations
In pgx, the value returned by the RowToFunc escapes to the heap, causing an extra allocation. After the function returns, this allocation becomes garbage, and the value will be copied to the outer stack frame, then copied again into the slice.
Instead, Scanner.ScanRowInto takes a *T as an argument, rather than returning a T, which lets pgx-collect avoid all these costs. By taking the pointer as an argument, the loop can first extend the slice, then scan directly into the slice element. This allows it to avoid allocating a temporary value on the heap. The slice’s backing array already had to live on the heap, so passing a pointer into the scanner has no additional cost. As a side benefit, it eliminates any additional copying, since the data is placed directly in its final location.
Reuse allocated memory
Unlike RowToFunc, which only has access to the global namespace, a Scanner object acts as a query-scoped namespace to store state between the calls for each row. Therefore, it can allocate the scanTargets slice just once for the whole query, store it in the object, and reuse it for each subsequent iteration.
// structScanner encapsulates the logic to scan a row into fields of a struct.
type structScanner[T any] struct {
scanFields StructRowFields
scanTargets []any
}
func (rs *structScanner[T]) ScanRowInto(receiver *T, rows pgx.Rows) error {
if rs.scanTargets == nil {
rs.scanTargets = make([]any, rs.scanFields.NumFields())
}
// ...
}How does it do?
With these optimizations, the performance looks like this:
The overhead above the baseline drops from 180% in pgx to just 60% with pgx-collect. This is great progress, but it’s not the 3% overhead I promised
earlier. There are more optimizations to discuss, but this post is already too
long. I intend to write a follow-up describing how I used unsafe to claw back
the remaining CPU time. If you’d like to see how I did that, subscribe to get
notified when I publish it.
Lessons learned
I think there are a few useful lessons here.
First, when designing a library, it’s worth providing both low-level and convenience APIs. The handwritten loop works, but CollectRows is easier to read and harder to get wrong. If your library has patterns like this, provide concise APIs for common cases.
That said, benchmark them against the handwritten version. If they don’t perform well, profile them, fix the problem, and repeat. Fortunately, Go makes this easier than any other language I’ve used.
Make convenience APIs fast. If you provide a short, clear implementation, your users will call them. They should not pay a large performance penalty for doing so.
Second, when choosing an abstraction, think about the concrete work that needs to happen. Choose one that clearly models that work, not merely the simplest one that can do the job. RowToFunc can be used to write CollectRows, but it introduces inherent inefficiency. Solving this requires matching the code structure to the underlying operations.
Third, when using reflection in Go to process data, compute the exact metadata needed to handle each element, then cache and reuse it by type. This is almost always the easiest way to minimize the time spent in reflect calls. If a type is seen once, it will likely be seen thousands if not millions of times over the life of the program. In the end, all the time will be spent processing individual elements, so do whatever you can to make that as cheap as possible.
Finally, never stop thinking about memory. I have seen over and over that in otherwise reasonable, idiomatic Go code, heap allocation is the biggest cause of poor performance, followed by memory copying. Whenever possible, put data where you want it, leave it where it is, and operate on it in place. The less your code allocates memory and copies data, the faster it will run.
I’ll spend most of this post talking about AppendRows
rather than CollectRows, because it allows collecting into pre-allocated
slices, and because CollectRows is implemented by passing an empty slice
to AppendRows, which contains the interesting code.
These numbers are specific to this benchmark and its
query. The overhead varies based on which adapter is used, as well as
factors like the number of rows and types of columns returned by the
query.
These benchmarks were run using the current tip ofpgx-collect. This post focuses on specific optimizations that account for
most of the performance difference, but not all of it.
Actually, it was even worse than this. Historically, thereflect.Type.Field method would always allocate memory, and frequently
this allocation dominated the cost of struct analysis. Since Go 1.24, this
allocation is typically optimized away, though it can still show up in some
unusual situations.