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!
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.