Bulk load is 4.9x slower than heap, and import inherits it

[jdatcmd]

Plan: design/IMPORT_THROUGHPUT_PLAN.md.

docs/benchmarks.md called import "about 18x slower than export" and blamed the
full insert path including index maintenance. Measuring it says that description
is wrong twice over, and the real target is different.

Measured, PG17.10 non-assert, 6,000,000 rows, 5 columns

step	ms
heap INSERT INTO ... SELECT	2,667.9
columnar INSERT INTO ... SELECT, no file involved	12,989.6
export_arrow	881.9
import_arrow	12,150.2
full scan of the Parquet file through read_parquet	1,415.0
import_parquet	12,956.6
columnar INSERT, exhaustive selection (encoding_sample_rows = 0)	20,103.2

Import is not slower than an ordinary insert: 12,150 ms against 12,990 ms for
the same rows with no file involved. There is no import-specific overhead. The
real statement is that the columnar write path is about 15x slower than the read
path, and 4.9x slower than a heap insert of the same rows, which is the wrong
direction for a format that writes about a hundredth of the bytes.

The readers are not the bottleneck: reading the whole Parquet file costs
1,415 ms, 11% of import. The other 89% is the write path.

Encoding selection is not the bulk of it either: turning sampling off costs
55% more, so what remains after sampling is a minority of the write path.

Index maintenance is not in the path at all, which is its own bug: see #153.

Where the waste is

Both readers decode a column-oriented file into per-row Datum arrays, hand each
row to table_tuple_insert, and the write path copies each value back into
per-column buffers. Arrow and PGCN are both columnar; the row exists only for the
length of one call between two column stores.

The plan proposes a batching entry point first, then column-at-a-time transfer for
the cases where the representations already agree, with the reader cost of
1,415 ms as the floor and 3 to 5 seconds as the plausible target against today's
13.

Acceptance

The claim is a ratio against the read_parquet floor rather than a millisecond
count, so it means the same thing on other hardware. Existing round-trip suites
must pass untouched, and should run against an indexed target once #153 is
resolved.

Estimated 2 to 4 dev-months for two people.

[ChronicallyJD]

Took this one. I have not sent a patch, because the measurement says the thing I went looking for is not there. Posting what I found so the next attempt starts from it rather than from where I started.

The varlena path does not have a smoking gun either

#160 narrowed this to "text is where the 4.9x lives". I profiled the text write path on current main, 1,000,000 rows, assert build at -O2. Ablating each piece by building with it removed:

	1 text column
baseline	1867 ms
without zone min/max tracking	1659 ms (−11%)
without bloom hashing	1564 ms (−16%)
without both	1465 ms (−22%)

And by GUC, none of which moved: compression = none, encoding_sample_rows = 1, enable_bloom_filter = off. Heap on the same rows is 740 ms, so even with min/max and bloom removed entirely, columnar is still 2x.

I also tried the one structural thing that looked obvious: ColumnarEncodeValue calls pg_detoast_datum on every varlena, and PostgreSQL gives every value of 126 bytes or less a one-byte header, so that is a palloc, a copy and a free per value. Writing the four-byte header and the bytes straight into the stream produces identical bytes with none of it. It is worth about 4%, inside the run-to-run spread I measured on this box, and it trips an -Warray-bounds warning as written. I dropped it rather than tune a change the measurement does not support.

What actually drives it: entropy and length, not per-value overhead

This is the part worth having. Same 1,000,000 rows each, only the content varying:

data	heap	columnar
constant 8 chars	818 ms	600 ms	0.73x — faster than heap
's' || g, ~8 chars	760 ms	1831 ms	2.4x
two md5s, 64 chars	977 ms	6261 ms	6.4x
md5 repeated 8x, 256 chars	1464 ms	16392 ms	11.2x

Heap barely moves across that range. Columnar goes from beating it to 11x behind, on the same row count, purely on what the strings contain.

So "text is slow" is not the right statement. Low-entropy text is faster than heap; high-entropy text is 6-11x slower, and it gets worse with length. The 4.9x in this issue is one point on that curve, and a benchmark with different strings would report a different number — which is worth knowing before anyone tunes against it.

And the cost is buying something

Storage for the same shapes, 200,000 rows:

data	on disk
constant 8 chars	48 kB
's' || g	304 kB
64-char md5 pairs	6912 kB
256-char high entropy	4584 kB from about 51 MB raw, 11x

The expensive cases are the ones where the encoders are working hardest and succeeding. That reframes the issue: a large part of this gap is not waste to be removed, it is CPU spent on the compression the format exists to provide. Attacking it means choosing a different point on the ratio/CPU curve, not finding a bug.

What I would do instead

The plan's remaining steps are aimed at per-call and per-row overhead. On this evidence there is none to recover: at one integer column the write path already beats heap (450 ms against 653 ms on the same 1,000,000 rows), and the per-value pieces I could ablate come to 22% of one text column.

The lever this measurement points at is letting a bulk load choose a cheaper point on that curve — a way to cap or skip the substring/dictionary search for high-cardinality varlena vectors, accepting a worse ratio for a much cheaper load, ideally as a setting rather than a fixed policy so the trade is the user's.

I have not built it, and I would want your view on whether a ratio/speed knob is something this format should have before I do. If you would rather it stay a fixed policy, then the honest conclusion is that most of this gap is inherent and #155 should say so and close, which is the second resolution in #165.

Method note

Every figure above is from the -O2 assert build, one shape at a time, on an otherwise idle box. The ablations are real builds with the code removed, not GUCs, because two of the GUCs I tried turned out not to gate what their names suggested: enable_bloom_filter = off did not stop the write side hashing, which is why the code ablation and the GUC disagree by 16%.

[jdatcmd]

Answering the design question, since it is blocking you and the measurement deserves a decision rather than a shrug.

Short answer

A knob is legitimate here, but not yet, and not as the first move. There is a measured lever already on the table that costs no design surface at all, and it should be spent before the format grows a setting.

The lever that is free: the bloom GUC does not do what it says

Your ablation puts bloom hashing at 16% of the text write path, and separately you noted that enable_bloom_filter = off did not stop the write side hashing. I checked that, and it is worse than a naming problem:

• columnar_enable_bloom_filter is consulted in exactly one place, columnar_reader.c:735, on the read side
• the write path gates on writeState->colDefs[c].bloomable and never looks at the GUC
• the filter is written to the catalog regardless

So a user who turns it off pays the hashing, pays the storage, and gets only the loss of read-side skipping. That is a plain defect, it is worth 16% of the write path on text, and fixing it hands people a real lever today with no new concept in the format. Worth its own issue rather than being folded into this one.

Zone min/max is the other 11%, and that one is load-bearing for skipping, so it is not a candidate for the same treatment.

On the knob itself

If you still want it after that, my view on shape:

A per-table option, not a GUC. pgcolumnar.set_options already carries compression and compression_level, which is exactly this kind of trade, and the precedent matters more than the mechanism. A GUC alone would let the same table be written two different ways depending on which session loaded it -- the "same data behaves differently depending on a session setting" shape you called the worst kind of defect on #170, and you would be right about it here too.

The default keeps today's effort. Let the option lower it, rather than lowering the default and making people opt back into the ratio they already have. A storage format that quietly got worse at compressing between releases is a bad trade even when it is faster.

And the story is already coherent, which is what makes me think this is a real feature and not a bolt-on: compact, recluster and vacuum_sorted exist, so "load cheap now, compress properly later" is a workflow this engine can already finish. The option is the missing first half of something the second half of which is built.

What would decide it

Not my opinion, one measurement: how much CPU comes back, at what cost in ratio, on the shapes where it is worst. Your own numbers already show the two ends -- 11x compression on 256-char high entropy, 6.4x the write time on 64-char md5 pairs. The number that decides the design is what happens in between when the substring and dictionary search is capped.

If capping recovers most of the CPU for a few percent of ratio, the option is clearly worth it. If it recovers a fifth for a third of the ratio, then the honest answer is the one you floated: this is inherent, and #155 says so.

On closing

I would not close #155 as inherent while an unmeasured lever and a plain GUC defect are both sitting in it. But I also would not leave it open as a general "bulk load is slow" ticket, because that is the shape of issue that never closes.

Concretely: fix the bloom GUC as its own issue, re-measure the text path with it genuinely off, and then either scope #155 down to "encode effort control" with the ratio/CPU numbers attached, or close it with those numbers as the evidence that the rest is the format doing its job. Either ending is defensible with data. Neither is without.

The reframing you did -- that the expensive cases are the ones where the encoders are working hardest and succeeding -- is the part of this that will still be true in a year, whichever way the knob goes.