Golang slow scan() for multiple rows

Question

I am running a query in Golang where I select multiple rows from my Postgresql Database.

I am using the following imports for my query

"database/sql"
"github.com/lib/pq"

I have narrowed down to my loop for scanning the results into my struct.

// Returns about 400 rows
rows, err = db.Query('SELECT * FROM infrastructure')
if err != nil {
    return nil, err
}

var arrOfInfra []model.Infrastructure
for rows.Next() {
    obj, ptrs := model.InfrastructureInit()
    rows.Scan(ptrs...)
    arrOfInfra = append(arrOfInfra, *obj)
}
rows.Close()

The above code takes about 8 seconds to run, and while the query is fast, the loop in rows.Next() takes the entire 8 seconds over to complete.

Any ideas? Am I doing something wrong, or is there a better way?

My configuration for my database

// host, port, dbname, user, password masked for obvious reasons
db, err := sql.Open("postgres", "host=... port=... dbname=... user=... password=... sslmode=require")
if err != nil {
    panic(err)
}

// I have tried using the default, or setting to high number (100), but it doesn't seem to help with my situation
db.SetMaxIdleConns(1)
db.SetMaxOpenConns(1)

---

UPDATE 1:

I placed print statements in the for loop. Below is my updated snippet

for rows.Next() {
    obj, ptrs := model.InfrastructureInit()
    rows.Scan(ptrs...)
    arrOfInfra = append(arrOfInfra, *obj)
    fmt.Println("Len: " + fmt.Sprint(len(arrOfInfra)))
    fmt.Println(obj)
}

I noticed that in this loop, it will actually pause half-way, and continue after a short break. It looks like this:

Len: 221
Len: 222
Len: 223
Len: 224
<a short pause about 1 second, then prints Len: 225 and continues>
Len: 226
Len: 227
...
..
.

and it will happen again later on at another row count, and again after a few hundred records.

---

UPDATE 2:

Below is a snippet of my InfrastructureInit() method

func InfrastructureInit() (*Infrastructure, []interface{}) {
    irf := new(Infrastructure)
    var ptrs []interface{}
    ptrs = append(ptrs,
        &irf.Base.ID,
        &irf.Base.CreatedAt,
        &irf.Base.UpdatedAt,
        &irf.ListingID,
        &irf.AddressID,
        &irf.Type,
        &irf.Name,
        &irf.Description,
        &irf.Details,
        &irf.TravellingFor,
    )
    return irf, ptrs
}

I am not exactly sure what is causing this slowness, but I currently placed a quick patch on my server to using a redis database and precache my infrastructures, saving it as a string. It seems to be okay for now, but I now have to maintain both redis and my postgres.

I am still puzzled over this weird behavior, but I'm not exactly how rows.Next() work - does it make a query to the database everytime I call rows.Next()?

Answer 1

I went some weird path myself while consolidating my understanding of how rows.Next() work and what might be impacting performance so thought about sharing this here for posterity (despite the question asked a long time ago).

Related to:

I am still puzzled over this weird behavior, but I'm not exactly how rows.Next() work - does it make a query to the database everytime I call rows.Next()?

It doesn't make a 'query' but it reads (transfers) data from the db through a driver on each iteration which means it can be impacted by e.g. bad network performance. Especially true if, for example, your db is not local to where you are running your Go code. One approach to confirm whether network performance is an issue would be to run your go app on the same machine where your db is (if possible).

Assuming columns that are scanned in the above code are not of extremely large size or having custom conversions - reading ~400 rows should take in the order of 100ms at most (in a local setup).

For example - I had a case where I needed to read about 100k rows with about 300B per row and that was taking ~4s (local setup).

Answer 2

How do you think about just do like this?

defer rows.Close()

var arrOfInfra []*Infrastructure
for rows.Next() {
    irf := &Infrastructure{}

    err = rows.Scan(
        &irf.Base.ID,
        &irf.Base.CreatedAt,
        &irf.Base.UpdatedAt,
        &irf.ListingID,
        &irf.AddressID,
        &irf.Type,
        &irf.Name,
        &irf.Description,
        &irf.Details,
        &irf.TravellingFor,
    ) 

    if err == nil {
        arrOfInfra = append(arrOfInfra, irf)
    }
}

Hope this help.