Bulk insert optimisations using raw sql in Golang (mysql/psql)
Bulk inserts is common requirement bases on relationship database. Someone would create records in a loop inside a transaction and finally commit to database, this would make sense if there is few records but when we need to create huge numbers of records, the solution isn't a good sign.
When looping over a set of objecrs, a new sql operation is triggered each time. we should bulk insert batch records in one time to reduce db operate time. Just as preload related records between has_many relationship models to avoid n+1 queries.
In golang world, gorm is a nice orm framework, but it still not support Batch insert operation. Feature Request: Batch Insert . I will show you guy how to add the feature combine with real world case.
Take a look at code snippet:
func (repo *repo) CreateBalancesForAsset(ctx context.Context, wallets []*Wallet, asset *SimpleAsset) (error) {
valueStrings := []string{}
valueArgs := []interface{}{}
for _, w := range wallets {
valueStrings = append(valueStrings, "(?, ?, ?, ?)")
valueArgs = append(valueArgs, w.Address)
valueArgs = append(valueArgs, asset.Symbol)
valueArgs = append(valueArgs, asset.Identify)
valueArgs = append(valueArgs, asset.Decimal)
}
smt := `INSERT INTO balances(address, symbol, identify, decimal)
VALUES %s ON CONFLICT (address, symbol) DO UPDATE SET address = excluded.address`
smt = fmt.Sprintf(smt, strings.Join(valueStrings, ","))
fmt.Println("smttt:", smt)
tx := repo.db.Begin()
err := tx.Exec(smt, valueArgs...).Error
if err != nil {
tx.Rollback()
return err
}
return tx.Commit().Error
}
Business requirement is that: once a merchant adding a new kind of digital currency, we should add balances records related with the wallets, which are belong to merchant, to maintain added asset's balances for merchant wallets.
Back to code, the key info is insert statement with an array value, and do something when meeting CONFLICT contidion(the same as DUPLICATE in mysql)
ON CONFLICT to raising a unique violation or exclusion constraint violation error, it can be follow by DO NOTHING simply avoids inserting a row, or DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. excluded in DO UPDATE SET represent the current conflict row.
