How to do upsert without causing too much memory spike?

[hanson1]

Batching is a good idea as I mentioned on

Write only replica for the table

Hi, We have one table where we need to do heavy write each day around 100 K records with upsert. Currently our table is columnar so upsert is taking too much memory. Is there a way that we can have write heavy and read heavy replica of the same table?

It’s possible even 1k batches are too big, though not likely. Consider reducing the batch size to 500 or 250, say.

I’m not sure what’s causing you to run out of memory. What error message are you getting? It might be that the in-memory rowstore segment is getting too big. Normally it would flush automatically though, so that’s probably not it. But you could try running OPTIMIZE TABLE FLUSH after every 10 batches or so. That would flush the contents of the in-memory rowstore segment to disk.