Discussion: Improve pgx.CollectRows to dynamically allocate sufficient slice capacity to avoid memory reallocations

[victorperezc]

Is your feature request related to a problem? Please describe.

I recently migrated parts of my codebase to pgx v5 and have started using the amazing pgx.CollectRows utility to easily serialise my structures. Some of my old business logic used to load up paginated queries into struct slices.

I usually followed very strict rules of allocating enough slice capacity at initialisation to avoid go's slice append from 'growing' my slice every now and then. In other words, paginated queries initialise a slice with capacity equal to the pagination limit, effectively avoiding slice resizing happening for worst-case scenarios.

Debates aside on whether this is a good practice or not, I found myself not having this option anymore since pgx.CollectRows will handle the slice initialisation and fill via append.

I would like to bring into discussion this topic and see what are everyone's thoughts.

Describe the solution you'd like

pgx CollectRows to intelligently allocate enough slice capacity to reduce the number of allocations due slice resizing particularly happening in paginated queries, which are somewhat predictable in upper bound capacity limit

Describe alternatives you've considered

Alternatives are to not make use of CollectRows

Additional context

const getPaginatedQuery = `
SELECT id, ...
FROM myschema.mytable WHERE id=@id
`

func (adapter adapterPostgreSql) Get(limit int, offset int) ([]MyStruct, error) {
	var mystructs = make([]MyStruct, 0, limit) // Allocate a slice with capacity equal to pagination limit

	args := pgx.NamedArgs{
		"limit":  limit,
		"offset": offset,
	}

	rows, err := adapter.db.Query(context.Background(), getPaginatedQuery, args)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		mystruct := MyStruct{}
		if err := rows.Scan(
			&i.ID,
			...
		); err != nil {
			return nil, nil
		}
		
		mystructs = append(mystruct, &i)
	}

	return mystructs, nil
}

[jackc]

I think https://pkg.go.dev/github.com/jackc/pgx/v5#AppendRows is what you want.

[victorperezc]

Thanks @jackc appreciate the quick response. Seems like that's about what I need!