Easy java backend

Multi-row INSERT or COPY instead of one row at a time

The 30 second answer

Inserting rows one at a time pays a network round trip and often a commit for each row. A multi-row INSERT or a batch sends many rows in one statement, and COPY is the fastest way to bulk load in PostgreSQL. For large loads I also wrap everything in one transaction, and sometimes drop and recreate indexes afterwards, which the PostgreSQL docs recommend.

The PostgreSQL "Populating a Database" page recommends COPY for bulk loads, since it has much less overhead than many INSERTs. If you use INSERT, batch many rows per statement and per transaction. In Java, JDBC batching (addBatch) plus the pgJDBC setting reWriteBatchedInserts=true turns batches into multi-row INSERTs.

INSERT INTO events (id, type) VALUES (1,'a'), (2,'b'), (3,'c');
Sources (4)
  1. PostgreSQL 17: Populating a Database
  2. COPY
  3. INSERT
  4. pgJDBC connection properties