Bulk insert/update

[AaronVasquez]

Would be nice to have a bulk insert or update function.

[stanislas-m]

Related issue from the previous repo: https://github.com/markbates/pop/issues/185

Here some points to work on it:

• https://godoc.org/github.com/lib/pq#hdr-Bulk_imports
• For others DBs, we can improve this case reusing prepared statements.

[mclark4386]

The linked doc looks like it will work for the CockroachDB implementation as well. (https://godoc.org/github.com/lib/pq#hdr-Bulk_imports)

(CockroachDB docs for reference: https://www.cockroachlabs.com/docs/stable/insert.html#insert-multiple-rows-into-an-existing-table )

[paganotoni]

This would be awesome, when i think on this one i'm thinking in something like:

err := tx.BatchSave(&records)
...

I would love to discuss if this sounds good, i don't usually do batch insert but wanted to add those 5 cents to the discussion

[derkan]

Hi, at gobenchorm, I used following code for benchmarking BulkInsert:

                ms = make([]PModel, 0, 100)
		for i := 0; i < 100; i++ {
			ms = append(ms, *NewPModel())
		}
		if err := popdb.Create(&ms); err != nil {
			fmt.Println(err)
			b.FailNow()
		}

Bu it was so time consuming that I had to disable benchmarking for BulkInsert. How can I make it better?

[markbates]

@derkan great question! I think it's time we, as a community, start looking at the performance of Pop and trying to improve it. Here's how you can help. Start by opening an issue saying Pop needs benchmarking and profiling so it's performance can be improved.

Then, encourage someone out there, or if you want to do it even better, start adding some benchmarking and profiling results to a new ticket that says "this method needs to be improved, here are the metrics".

Then, people can come along, grab a ticket, work on that one method, and before we know it Pop is humming along.

[OPhamster]

just curious as to the status of this. I'm not sure if the examples below are things already considered - but I would like to help(or try to help) move this forward. The suggestions given below currently only apply to dialect_postgres - but I would have to look in both dialect_cockroach and dialect_mysql to see if this is possible.

// Upsert - using on conflict clause of pg
db.Upsert(&records)
// RAW SQL follows the format of
// INSERT INTO %s(%s) VALUES %s ON CONFLICT (%s) DO UPDATE %s WHERE %s

// Updates
db.Update(&records)
// RAW SQL follows the format of
// UPDATE %s SET %s FROM (VALUES%s) AS %s(%s) WHERE %s

of course we won't have anything to compare it to - if we can't benchmark and improve the single Create calls currently implemented - so that would come first. I could work on a prototype so that it can be considered as a feature or not and people have a clear idea of it.

[Jwonsever]

Since it's not explicitly mentioned being able to do a bulk delete should also be part of this.

[vincentchu]

Is this still an open issue? In my app I wrote a small add-on library that will do bulk inserts using a prepared statement that would insert multiple records at a time. It leads to a pretty big improvement in INSERT times, especially for large numbers of records.

Though it's in our app currently as a library external to pop, it does use certain pop concepts like Model to introspect on the passed in rows to create the db query.

n, err := bulkinsert.Insert(tx, rowsToInsert) // n is the number of records inserted

A brief sketch is here (excludes tests):
https://gist.github.com/vincentchu/e56da652f012b357779a544c1e3831d1

If this is interesting to people I could use some help and pointers on how to get this into the actual pop library.

[sio4]

Hi @vincentchu

Yes, the issue is still open, and I think it is definitely nice if we improve it!

I quickly read your gist and I like the approach. The method may work for all supported engines. Just one concern is the eager mode. Currently, the eager mode is supported for Create() (not fully stable) but I think improving the method for the bulk inserting with eager mode support could be challenging.

One possible way to integrate the gist into Pop could be making the method switch using the eager mode, so it uses the existing way for eager mode and uses your improvement for non-eager mode.

Any idea?

[vincentchu]

Hi @vincentchu

Yes, the issue is still open, and I think it is definitely nice if we improve it!

I quickly read your gist and I like the approach. The method may work for all supported engines. Just one concern is the eager mode. Currently, the eager mode is supported for Create() (not fully stable) but I think improving the method for the bulk inserting with eager mode support could be challenging.

One possible way to integrate the gist into Pop could be making the method switch using the eager mode, so it uses the existing way for eager mode and uses your improvement for non-eager mode.

Any idea?

@sio4 Thanks for these notes. At least internally here in my company, we distinguish between two use cases:

1. .Create() which supports Eager, etc.. and we typically use for creating small (1-50) numbers of items
2. bulkinsert.Insert(...) which supports fast insertion of many (50+) but does not support any Eager functions

We could do something similar with pop by adding a pop.BulkInsert or pop.BulkCreate method where Eager was explicitly not supported and the tradeoff being much faster inserts. I think this makes sense because Eager will be really hard to support for bulk inserts- when we do bulk insertion of nested models, we've typically used bulk inserts to INSERT the parent models, then aggregate the nested models before bulk inserting those.