[ledjon]

I'm a little disappointed the answer was "batch inserts"

This is write performance optimization 101. I bet you are getting wins in way more places in the pipeline than the evaluation pointed to by doing this.

Try doing the same optimization on a table with zero partial indexes and you will get the same 10x bump. It is better for many many reasons.

Still, super cool dig into performance tools and source code. It shows great aptitude and willingness to deep dive.

[drob]

Batch inserts usually increase throughput by reducing the number of write IOPS, fsyncs, etc. They usually aren't associated with a 10x _CPU_ savings, which is the finding here.

There are a million 'db best practices' you can go implement blindly, but the point is that this methodology – determining the bottlenecking resource and then profiling to determine exactly what is consuming it – will _reliably_ yield huge wins, whereas implementing 'best practices' on gut alone is a very inefficient way to improve performance.

[ledjon]

I'm not saying you need to do all "best practices" blindly but if insert speed is your problem (at a high level) batching is the very first thing to explore.

Without any specific numbers backing up the 10x we can only guess what improved 10x. All of those things you listed show up as CPU wait events as well. Without specifics I assume he means they inserted the same row count in 1/10th of the time. Not that there was a direct drop in CPU tasks.

[malisper]

Author here. We were under the assumption that CPU was mostly being used for evaluating the partial index predicates. Under this assumption, we figured batching was unlikely to yield much of a benefit. It wasn't until we actually profiled Postgres did we realize batching would be worth a try.

As for the numbers, we specifically got a 10x improvement in ingestion throughput.

[anarazel]

> Batch inserts usually increase throughput by reducing the number of write IOPS, fsyncs, etc. They usually aren't associated with a 10x _CPU_ savings, which is the finding here.

Often the saving on roundtrips is bigger than any of these. Most database client libraries work synchronously, so if you insert via single row INSERT statements you'll approximately get a two context switches, and a roundtrip for each row. That's often more costly wall clock time wise than the insertion itself.

[quizotic]

While it's good that you know about this optimization, the key point is look at the evidence first. It's surprisingly hard to temporarily ignore what you know, and just use your eyes, but it's so important.

[paulddraper]

The enormous advantage of batch inserts (in Java with SQLite) was one of the very first lessons in my undergrad database class.