Slow Data Insertion in Cloud Berry #1875

Hi @TanmayK2000 👋

The math is the clue here:

8M rows / 3 days ≈ 31 rows/sec → ~32 ms per row

Even a naive row-by-row INSERT with autocommit usually manages 300–1000 rows/sec, so you're ×ばつ below the worst reasonable approach. Two things are likely stacking up.

1. NiFi probably shouldn't be in this path.

You're moving data between two tables in the same warehouse. Right now every row is read out of Cloudberry, pushed through NiFi's JVM and FlowFile repository, and written back over JDBC — 17 million round trips for what is one server-side statement:

INSERT INTO target SELECT * FROM source;

That runs fully parallel across all segments with no data landing on the coordinator. Expect minutes, not days. If NiFi must stay for orchestration, have it issue that statement rather than move the rows.

2. If the target is an AO/AOCO table, per-row commits degrade toward O(n2).

Each write transaction into an AO table takes a serializing lock and sequentially scans pg_aoseg to pick a segment file — and each transaction also UPDATEs a row in pg_aoseg. That table isn't reached by autovacuum; it's only cleaned when you VACUUM the parent table. So after 8M single-row transactions, every new insert scans past millions of dead tuples. Per-row cost grows with row count.

This is a hypothesis, but there's a fast test that doubles as immediate relief:

VACUUM target_table;

If throughput jumps, that's your bottleneck. (Related: was the first million rows noticeably faster than the last? Progressive slowdown would confirm it.)

If you're stuck with JDBC: disable autocommit, batch 1,000–10,000 rows per commit, add reWriteBatchedInserts=true to the JDBC URL, and use PutDatabaseRecord (many records per FlowFile) instead of PutSQL (one row per FlowFile). Better still, use COPY or a gpfdist external table.

To confirm the diagnosis, could you share:

• \d+ target_table — AO/AOCO? distribution key? indexes?
• Which NiFi processor and what batch size?
• During the load: SELECT wait_event_type, wait_event, state, query FROM pg_stat_activity WHERE state <> 'idle';
• Does the flow do plain INSERTs, or UPDATEs/upserts? (AO tables serialize UPDATE/DELETE on the coordinator, which would lock throughput down completely.)

Short version: this looks like an OLTP-style row-by-row write pattern against an MPP warehouse, not a Cloudberry limit. INSERT INTO ... SELECT should turn 3 days into minutes.