5 minute read

This article will outline the difference in efficient insert patterns between OLAP (analytical) and OLTP (transactional) databases, and discuss best practices in OLAP (specifically ClickHouse) for optimizing inserts, with code examples using MooseStack to set up a Redpanda streaming buffer and in front of a ClickHouse OLAP database.

For OLTP, many, small, parallelizable transactions win

Most of us know the multivariate calculus of OLTP write tuning: how many workers to run, whether interactive requests should stick to 1 row per transaction (or a tiny bundle) to keep locks short and reduce contention, and whether background ETL should use modest batches to amortize round-trips and fsyncs. Do we use multi-row INSERTs for higher throughput, or jump to bulk loaders like COPY/LOAD DATA when ingesting files? We’re balancing contention, commit latency/visibility (group commit helps), blast radius on failure, and network/parse overhead.

OLTP, as a row-oriented system, is optimized for small, individual transactions and can parallelize them while maintaining ACID guarantees. Indexes are updated with each write (though what is reindexed can be optimized), so the more indexes you have, the slower ingestion gets. Inserts don’t lock existing rows; deletes/updates lock only their target rows. So, for bulk inserts, you usually turn indexing off, load up all the data, and recreate indexes from scratch.

In practice, you either do whatever the source app allows on the interactive path, and for ETL you batch to your risk appetite: small enough to keep locks and replica lag tame, big enough to hit your throughput target.

Bottom line for OLTP: optimize for many small updates on the request path, use modest batches off the request path, and parallelize sensibly.

For OLAP, fewer larger writes win

Column stores write data per column and organize it into immutable parts/segments/files (e.g., parts in ClickHouse). Bigger, well-formed inserts mean fewer parts, less merge work, better compression, and stronger data skipping at query time.

It serves to have a look at what ClickHouse does behind the scene for a given insert (and I note this is for MergeTree-family engines):

1. A new batch arrives (best if 10-100k+ rows in that batch)
2. ClickHouse sorts that batch using the ORDER BY that you define (you can skip this step for more efficient inserts if you pre-sort the data), inserting the data into one or more partitions as optionally defined by the PARTITION BY keys
   • It writes a new part for that partition with column files (compressed / written in granules etc.)
   • Each granule gets its own entry in the primary index for scans (so you can skip that part if your queries don’t touch that range)
   • The data is immediately queryable
3. ClickHouse subsequently performs background merges to coalesce this new data with existing data into new, larger replacement parts within the partition

There are a few operations here that benefit from scale:

• Fewer round-trips: bigger batches → fewer sessions/inserts → lower per-insert overhead.
• Better clustering: larger batches sort better by ORDER BY, improving data locality and reducing merge work later.
• More full granules: batch fills many granules (size is fixed), yielding tighter min/max per granule → stronger skipping and less metadata churn.
  Fewer parts to touch: bigger batches produce fewer parts, so queries open/scan fewer files before merges happen.
• Less merge churn: starting with larger parts means fewer background merges and shallower merge cascades.

It seems obvious, then, that bigger batches are better in OLAP. But, like OLTP, there is still some light multi-variate calculus to be done. Most specifically on latency and freshness (large batches lead to less freshness). And if the size of the batch exceeds RAM, you can run into max_memory_usage issues (obviously). Finally, resilience, with blast radius, network replication and streaming consumer lag being compromised with larger writes.

Streaming buffers can help you batch your incoming data

How do you achieve this? There are in-database ways of working with this (like ClickHouse’s Buffer table engine), but the market standard is to prefix the database with a streaming buffer like Kafka or its drop-in sibling, Redpanda.

This allows you to decouple your producers from your database: a durable buffer.

• Producers write data to the queue, the consumer can read the data from the queue in the optimal batch size (e.g. 100k rows or 1s): turning a firehose of single inserts into a more OLAP friendly set of micro-batches
• The streaming buffer also smooths out bursts, if you get 5m rows of data one second, your consumers can still batch inserts in 50 100k row batches over the next few seconds.
• Resilience: if a write fails, the data remains in the streaming buffer for a retry. The consumer can also implement data safety features like dead-letter queues for failed writes if the data is rejected by the database.

Implementing streaming buffers

If you are implementing these buffers yourself (and, assuming you have your Kafka/RedPanda already set up in the same region as your OLAP DB), you need to:

1. Define your topics
2. Provision your producers and choose your ingest pattern into OLAP (e.g. batched INSERTs or using ClickHouse’s Kafka table engine + a MATERIALIZED VIEW (Kafka table engine supported by Open Source ClickHouse only, not ClickHouse Cloud))
3. Create your OLAP target table, with ORDER BY and PARTITION BY being congruent with the way you are streaming, and the appropriate MergeTree family table engine
4. Stand up your consumers (1-2 per shard) set up with your batching policy; set up offset commits on success, retries or DLQ on failure
5. Set up observability

MooseStack implementation

MooseStack’s Buffer, OLAP and API modules help you set this up in very few lines of code, whilst maintaining best practices. If you want to see this live, make sure to have your Moose Dev Server up and running moose dev (see quickstart guide).

Step 1: Define your data model in your TypeScript or Python data model. This should map to the data you want to ingest, and will be used to type the data from ingestion to ClickHouse table

Step 2: use Moose OLAP module to set up your ClickHouse table, and
Step 3: use Moose Buffer module to set up a streaming buffer
Step 4: test using direct insert

There you go! MooseStack creates the streaming buffer for you, synced with the ClickHouse table.

This includes setting up and connecting your topic, consumer, and table. If you want, you can optionally set up a dead-letter queue for failed inserts (at API, at streaming function, at consumer).
Best practices for micro-batching pre-set up!

Appendix: quick cheat-sheet for OLAP database micro-batch rules

• ClickHouse: batch 10k–100k+ rows per insert; keep 1–2 inserters per shard.
• Druid/Pinot: tune segment roll-over by rows/time/size; aim for fewer, larger segments.
• Snowflake: stage and load 100–250 MB compressed files; avoid many tiny files.
• BigQuery: batch load large files (≥100 MB); streaming is auto-batched but pricier.
• Redshift: COPY many 100–256 MB files in parallel.
• Delta/Lakehouse: write 128–512 MB Parquet; schedule compaction if streams produce small files.