Bottleneck when inserting many rows into different tables

Question

TLDR: I want to bulk insert into many initially empty tables at once, without the performance of each insert being affected too badly. I seem to be hitting a performance bottleneck I can't yet explain. I am looking for performance debugging avenues or advice on parallel inserts in Postgres.

---

I am prototyping a system and trying to import data into many Postgres tables at once as fast as possible. My example table has 100 columns (all short strings) and 20k rows. I can insert this data into a fresh Postgres table in around 30 seconds (*). However, if I run the same insert script 10 times against different (unrelated) tables, the performance of each individual insert drops dramatically - up to around 3.5 minutes for each table. The total insert (3 tables per process, 10 processes) takes around 8 minutes, so I am getting less than 2x speedup by running these queries in parallel vs. running them all one after the other for 30s each. I was hoping to get ~10x speedup with 10 parallel unrelated imports, until I reach some resource limit of the database host.

I am using an SSD-based Google Cloud SQL database with Postgres 12, 20vcpus and 75GB memory. While handling my 10 parallel fills, it uses less than 15% CPU, around 200 write ops per second (I am not clear what counts as one op in GCP), and around 10-15MiB/s data ingress. None of these feel like they should be a bottlneck for a relatively beefy host instance, but I can't understand what is slowing down these queries.

What should I check to investigate this bottleneck, and what should I be doing to import many tables in parallel efficiently?

My ideal solution would be to use 10x as much CPU (or whatever other resource) on the host instance, and complete the 10 parallel table loads in roughly the same time as a single one. I am hoping to be able to reach around 200 imports in parallel, even if it has to be spread over a few separate database hosts (non-ideally).

It seems clear that it is not (yet) CPU bound; att the tables at once can fit into memory a few times over, so it is not memory bound. I would be surprised to find it is IO or disk bound at this scale, but I don't know how to check or investigate these.

All the information I have been able to find has been about improving the speed of a single-table insert. Some examples:

• Populating a Database
• How to speed up insertion performance in PostgreSQL
• What's the fastest way to do a bulk insert into Postgres?

I haven't found anything addressing concurrent inserts into separate tables or databases on the same host.

I also tried having my 10 client processes import data into differewnt databases to each other, with almost exactly the same performance.

I tried switching my tables to be UNLOGGED. This made a big improvement to the speed when running 10 imports in parallel (down to around 45 seconds), but running 20 imports immediately jumped back up to 2-4 minutes per table.

---

(*) Currently for each single-table load I am using a single insert statement with all the rows at once:

insert into test_table values ('id1', 'Sample data 1', ...), ... ('idN', 'Sample data N', ...);

I have also tried bulk loading from a CSV file via copy, inserting in batches of 100 or 1000 records at a time, and increasing the power of the host instance, none of which makes any significant difference on my example. The tables have an externally-specified primary key and no other constraints. But improving the performance of a single-table import is not what this question is about!