Concurrency / Transaction Concerns with PostgreSQL Copy (bulk insert)

Question

We're building a CQRS system backed by Postgres. As part of this, we generate 'readmodels' which are precalculated projections of event sourced data. We regularly need to create new tables, query event sourced data, calculate a projection and insert thousands of rows (mostly containing jsonb data) into new tables.

These inserts need to be done inside a transaction which also includes reads and writes to other tables.

Our initial code used standard INSERT ... ON CONFLICT UPDATE commands and achieved about 100 rows per second(). When however I changed it to use COPY ... FROM STDIN (FORMAT BINARY) the throughput improved by a factor of about 100. I'm aware that I can't do 'upserts' in the same way with the second method but was just planning to delete records before the COPY insertion (inside a transaction).

I'm thinking to use COPY flow of our application. I've received criticism however along the lines of "I doubt it will work. COPY is designed for bulk import. You'll have concurrency issues".

My gut feel is that with the MVCC and transactional DDL of Postgres, it's probably robust enough to handle something like this without breaking a sweat. I've had fear, uncertainty and doubt sown in me though!

My questions:

1/ Can I use COPY in normal transactions as I would other statements?

2/ Can mass COPY loads happen while the database is still live with normal production inserts and selectes still running against other tables?

3/ Can multiple connections concurrently perform COPY operations? (against different tables)

4/ Am I crazy for thinking to treat this like any other PG command? Is it designed to be used against live, production databases or is it more of a housekeeping tool.

We're using NPGSQL as the client library to Postgres.

Thanks!

Answer 1

1. Can I use COPY in normal transactions as I would other statements?

Yes, You can. This will invoke the trigger, check constraint and other constraints as well. But it will not invoke the rules. It supports MVCC and ACID.

2. Can mass COPY loads happen while the database is still live with normal production inserts and selects still running against other tables?

Yes, But its based on your workload. Do a test in Dev infra.

3. Can multiple connections concurrently perform COPY operations?

Yes it'll. But keep an eye on the resource utilization.

4. Am I crazy for thinking to treat this like any other PG command? Is it designed to be used against live, production databases or is it more of a housekeeping tool.

You can trust and use it in production. No issues.

References:

1. Read the Notes section from PG documentation

2. Someone implemented the same in Production with 10m rows file