Efficient bulk imports via `pq.CopyIn`

[kyleconroy]

lib/pq has support for efficiently inserting bulk items using COPY. sqlc currently can not generate code that exposes this functionality.

See this StackOverflow answer for a great overview of the problem space.

[dharmjit]

Hi @kyleconroy, Any update on this.

[kyleconroy]

Nope, no update as of yet. It's low on my list, as I'm focusing on MySQL / SQLite support right now.

[fr3fou]

Hi! Any updates on this?

[aitva]

I found a way to do bulk insert and update with sqlc and Postgres:

-- name: CreateAuthors :exec
INSERT INTO authors
SELECT unnest(@ids::bigint[]) AS id,
  unnest(@names::text[]) AS name,
  unnest(@bios::text[]) AS bio;

-- name: UpdateAuthors :exec
UPDATE authors AS a
SET name = tmp.name, bio = tmp.bio
FROM (
  SELECT unnest(@ids::bigint[]) AS id,
  unnest(@names::text[]) AS name,
  unnest(@bios::text[]) AS bio
) AS tmp
WHERE a.id = tmp.id;

It uses arrays to serialize multiple rows into 1 parameter and unnest to deserialize them in Postgres. You can see the generated code in the playground.

It felt a bit hacky, so I made a benchmark to ensure that it is working as expected:

BenchmarkCreateAuthor-6              100          12656602 ns/op
BenchmarkCreateAuthors10-6           102          13837750 ns/op
BenchmarkCreateAuthors100-6           74          14967301 ns/op
BenchmarkCreateAuthors1000-6          30          38486604 ns/op
BenchmarkUpdateAuthor-6              100          11739085 ns/op
BenchmarkUpdateAuthors10-6            94          13677888 ns/op
BenchmarkUpdateAuthors100-6           66          15819271 ns/op
BenchmarkUpdateAuthors1000-6          28          56070949 ns/op

BenchmarkCreateAuthor and BenchmarkUpdateAuthor measure the time it takes to insert or update an author. The BenchmarkCreateAuthorsN and BenchmarkUpdateAuthorsN uses the above code to group N authors into a query.

By looking at BenchmarkCreateAuthor and BenchmarkCreateAuthors1000, we can see that it takes the same amount of time to create 100 authors with an insert and 30000 authors with the above method.

If you want to try it, you can find the code of the benchmark here.

[kyleconroy]

Now that pgx support has landed, we'll also need support their Copy Protocol.

[nd291195]

@aitva Does this solution also work for fields which are Nullable? Because right now when I am creating my inserts this way I get []int64 for ::bigInt[] but I need to have []sql.NullInt64 because this field is nullable? Any ideas on how this could work?

[Jille]

https://docs.sqlc.dev/en/latest/howto/insert.html#using-copyfrom has landed, so I think we can close this.

[ovadbar]

@aitva We have been using the json_populate_recordset function in order to do bulk inserts which does allow for null values. There are other json functions you may want to use like json_to_record. Unfortunately the models generated by sqlc do not work well with these methods as instead of using NullInt64 you would want int64 with omit empty

[giulianopz]

@Jille @kyleconroy, please consider reopening this issue to support the COPY instruction for bulk inserts with lib/pq, as originally mentioned above.
The documentation says:

The :copyfrom command requires either pgx/v4 or pgx/v5.

[Jille]

Let's make that a new feature request. Would you mind coming that?