{
  "$type": "site.standard.document",
  "bskyPostRef": {
    "cid": "bafyreiericrst5mbfgsuwheaukfii7aiaqrhyzot4n5rsrmhnaqsqfqpoa",
    "uri": "at://did:plc:25rdn5elo5izoxrmtis34zuk/app.bsky.feed.post/3mpm3toadwml2"
  },
  "coverImage": {
    "$type": "blob",
    "ref": {
      "$link": "bafkreiagcyuxuz4fsy7dbuel6xlz3xrr4fbnou7e55zgfwofzakyvufebi"
    },
    "mimeType": "image/webp",
    "size": 188910
  },
  "path": "/douglas_carmo_cd84c5548f2/stop-guessing-batch-sizes-high-performance-bulk-inserts-in-spring-boot-with-postgresql-copy-4ebb",
  "publishedAt": "2026-07-01T17:22:23.000Z",
  "site": "https://dev.to",
  "tags": [
    "java",
    "springboot",
    "postgressql",
    "@Component",
    "@Transactional"
  ],
  "textContent": "_When you can't afford to guess the right batch size and don't want to pay for that lesson in production_\n\nWhen I designed the persistence layer of a real-time interbank settlement pipeline, I had a problem that many financial systems share: I didn't have a precise estimate of daily order volume. Some days would be normal. Others would be outliers and in a settlement system, outlier days are not a hypothesis. They are a certainty.\n\nThe question wasn't whether the system could handle average load. It was whether it could handle the days it wasn't designed for.\n\nThat question led me away from JDBC batch inserts and toward PostgreSQL's `COPY` protocol, exposed in Spring Boot via the `CopyManager` API.\n\n##  Why Not JDBC Batch?\n\nThe standard Spring Boot approach for bulk inserts is JDBC batch with `JdbcTemplate`:\n\n\n\n    jdbcTemplate.batchUpdate(\n        \"INSERT INTO settlement_order (id, amount, status, ...) VALUES (?, ?, ?, ...)\",\n        orders,\n        500, // batch size\n        (ps, order) -> {\n            ps.setObject(1, order.getId());\n            ps.setBigDecimal(2, order.getAmount());\n            ps.setString(3, order.getStatus().name());\n            // ...\n        }\n    );\n\n\nThis works. For predictable volumes, it works well. The problem is the number `500`.\n\nThat batch size is a tuning decision. Too small, and you make too many roundtrips to the database. Too large, and you risk memory pressure or statement timeouts under high load. Getting it right requires knowing your volume and knowing your volume requires data you may not have yet.\n\nIn a financial system with variable daily throughput, you are essentially betting that your batch size stays optimal across all load scenarios. Some days you win that bet. Others you don't, and you find out in production.\n\nI didn't want to pay for that lesson.\n\n##  The `COPY` Protocol\n\nPostgreSQL's `COPY` command was designed for bulk data transfer. It bypasses the standard row-by-row insert path entirely, streaming data directly into the table in a single operation. No per-row parsing overhead, no per-row plan execution, no per-row roundtrip.\n\nThe Java PostgreSQL driver exposes this through `CopyManager`, which accepts an `InputStream` and streams it into the database using the `COPY FROM STDIN` syntax.\n\nThe key difference from JDBC batch: **you don't choose a batch size**. You stream the entire dataset in one pass, and PostgreSQL handles the ingestion. The throughput ceiling is determined by the database and the network, not by a tuning parameter in your application code.\n\n##  Implementation in Spring Boot\n\nThe main challenge in a Spring Boot application is that `CopyManager` requires a native PostgreSQL connection, not the wrapped connection that HikariCP hands to your code by default. You need to unwrap it:\n\n\n\n    @Component\n    public class SettlementOrderCopyBulkAdapter {\n\n        private final JdbcTemplate jdbcTemplate;\n\n        public SettlementOrderCopyBulkAdapter(JdbcTemplate jdbcTemplate) {\n            this.jdbcTemplate = jdbcTemplate;\n        }\n\n        @Transactional\n        public void bulkInsert(List<SettlementOrder> orders, UUID batchId) {\n            if (orders.isEmpty()) return;\n\n            jdbcTemplate.execute((Connection connection) -> {\n                // Unwrap HikariCP's proxy to reach the real PostgreSQL driver connection\n                BaseConnection baseConnection = connection.unwrap(BaseConnection.class);\n                CopyManager copyManager = new CopyManager(baseConnection);\n\n                String copySql = \"\"\"\n                    COPY settlement_order (\n                        id, originator_id, destination_id, batch_id, order_type,\n                        amount, currency, settlement_date, status, created_at\n                    ) FROM STDIN WITH (FORMAT csv, DELIMITER ',', QUOTE '\"', ESCAPE '\"')\n                    \"\"\";\n\n                StringBuilder csv = new StringBuilder();\n                for (SettlementOrder order : orders) {\n                    csv.append(order.getId()).append(\",\")\n                       .append(order.getOriginator().getId()).append(\",\")\n                       .append(order.getDestination().getId()).append(\",\")\n                       .append(batchId).append(\",\")\n                       .append(order.getType().getCode()).append(\",\")\n                       .append(order.getAmount()).append(\",\")\n                       .append(\"BRL\").append(\",\")\n                       .append(order.getSettlementDate()).append(\",\")\n                       .append(order.getStatus().name()).append(\",\")\n                       .append(order.getCreatedAt()).append(\"\\n\");\n                }\n\n                byte[] bytes = csv.toString().getBytes(StandardCharsets.UTF_8);\n                try (InputStream inputStream = new ByteArrayInputStream(bytes)) {\n                    copyManager.copyIn(copySql, inputStream, 65536);\n                }\n\n                return null;\n            });\n        }\n    }\n\n\nA Note on Memory Usage: In the implementation above, I used a `StringBuilder` to serialize the data into CSV format in memory. This is concise and works perfectly for moderate volumes. However, for extremely large datasets, building a massive string in memory can lead to `OutOfMemoryError` or heavy Garbage Collection pressure. For high-throughput systems, consider streaming the data directly to the `InputStream` using a custom implementation or a library like Apache Commons CSV or Jackson CSV to write to a Writer or `OutputStream` directly. This keeps the memory footprint constant regardless of the batch size.\n\n###  Three Details Worth Explaining\n\n**`connection.unwrap(BaseConnection.class)`**\n\nHikariCP wraps the real JDBC connection in a proxy for connection pool management. `CopyManager` is a PostgreSQL-specific class that needs the actual driver connection, not the wrapper. `unwrap()` pierces through the proxy and returns the underlying `org.postgresql.core.BaseConnection`. Without this, you get a `ClassCastException` at runtime.\n\n**`FROM STDIN WITH (FORMAT csv, DELIMITER ',', QUOTE '\"', ESCAPE '\"')`**\n\nThis is the COPY format declaration. `FORMAT csv` tells PostgreSQL to parse the stream as CSV. The `QUOTE` and `ESCAPE` settings ensure that values containing commas or quotes are handled correctly. For a financial system where amounts and identifiers have predictable formats, CSV is the right choice; it's compact and fast to serialize.\n\n**`65536` as the buffer size**\n\nThe third argument to `copyIn()` is the read buffer size in bytes — 64KB here. This controls how much data is read from the `InputStream` per internal read cycle during the stream transfer. It is not a batch size in the JDBC sense; the entire dataset is still transferred in a single `COPY` operation. The buffer size affects internal I/O efficiency, not correctness. 64KB is a reasonable default for most workloads.\n\n##  Transactional Participation\n\nOne concern with native PostgreSQL features is whether they play nicely with Spring's transaction management. `CopyManager` does because the connection it uses is the same connection that Spring's transaction manager already enrolled in the active transaction.\n\nThe `@Transactional` annotation on the adapter method ensures that the entire `COPY` operation participates in the surrounding transaction. If anything fails after the bulk insert - a downstream status update, a Kafka publish anything - the `COPY` rolls back along with everything else. No partial state reaches the database.\n\n\n\n    @Transactional          ← Spring opens transaction\n      bulkInsert()          ← COPY streams into PostgreSQL\n      updateStatus()        ← JPQL update on same connection\n      publishToKafka()      ← fires only after commit (afterCommit)\n                            ← commit or rollback applies to all\n\n\n##  The Risk Argument\n\nThe choice between JDBC batch and `CopyManager` is not purely technical. It is also a risk decision.\n\nJDBC batch with a fixed batch size is a bet that your volume estimate stays valid. In a system where daily throughput varies significantly, and where outlier days are expected that bet has a real probability of failing in production. When it fails, you find out under load, at the worst possible time.\n\n`CopyManager` removes that bet. The application serializes the dataset and streams it. PostgreSQL ingests it. The throughput ceiling is the database, not a tuning parameter. On a normal day the difference is negligible. On an outlier day it is the difference between a pipeline that holds and one that doesn't.\n\nFor a settlement system processing interbank transactions under regulatory time constraints, that margin matters.\n\n##  When Not to Use CopyManager\n\n`CopyManager` is the right tool when you need to insert a large, complete dataset in one operation. It is not always the right tool:\n\n  * **Small, frequent inserts** — for single-row or low-volume writes, regular JPA or `JdbcTemplate` is simpler and sufficient\n  * **Upsert semantics** — `COPY` is insert-only; if you need `ON CONFLICT DO UPDATE`, you need a different approach (a staging table + merge, or `INSERT ... ON CONFLICT`)\n  * **Complex validation per row** — if each row needs application-level validation before insert, building that into the CSV serialization loop adds complexity that JDBC batch handles more naturally\n\n\n\n##  Takeaway\n\nIf your system has unpredictable or variable bulk insert volume, tuning a JDBC batch size is an ongoing maintenance burden with production risk attached. PostgreSQL's `COPY` protocol delegates that responsibility to the database, where it belongs.\n\nThe implementation in Spring Boot requires one non-obvious step: unwrapping HikariCP's connection proxy but once that is in place, the rest is straightforward: serialize your dataset to CSV, stream it in, let PostgreSQL do what it was designed to do.\n\n**Don't tune your way around the database. Use what the database gives you.**\n\n_This is part of a series on the STR-XML-Pipeline, a high-throughput interbank settlement system built with Spring Boot 3.5, Java 21, Apache Kafka, PostgreSQL 16, Redis 7, and AWS Fargate. The previous article covered how distributed locks were moved off the API hot path and onto the scheduler._",
  "title": "Stop Guessing Batch Sizes: High-Performance Bulk Inserts in Spring Boot with PostgreSQL COPY"
}