I am using the COPY command of Postgres to bulk insert data once a minute from an in-memory CSV file. I written the COPY code so that it performs the COPY's in parallel on separate db connections.
My server has 32gb of ram and 16cpus.
I'm getting a high rate of insertion but would like it to be as high as possible.
What are some easy wins to optimize this? I do also have some read queries occurring on the server whilst this bulk COPY is happening (at present it takes about 10 seconds to finish each time). All inserts are being handled by this COPY setup I have.
Background: The DB server has a local SSD it's writing to, and the servers writing to the DB server are in the same data centre with private high speed connections. CPU and memory isn't being maxed out on the DB server either, which inclines me to think there is some config I am missing out on.
