Best practice for appending streams of rows

[adamjones2]

Could I get a bit of advice on the best way to this problem using delta-dotnet? I have an app which I want to stream out a large number of rows into a Delta table. Eg.:

Schema GetTableSchema();
IAsyncEnumerable<RecordBatch> GetBatches(); // Long-lived sequence, each RecordBatch element is a large payload

var engine = new DeltaEngine(EngineOptions.Default);
var tableSchema = GetTableSchema();
var tableLoadOptions = new TableOptions { WithoutFiles = true };
var tableInsertOptions = new InsertOptions { SaveMode = SaveMode.Append };

await foreach (var batch in GetBatches())
{
    var table = await engine.LoadTableAsync(tableLoadOptions, default);
    
    await table.InsertAsync([batch], tableSchema, tableInsertOptions, default);
}

My understanding is I have to first call LoadTableAsync in order to get a handle to the table that I want to append the rows to so I can call InsertAsync on it, but this won't pull the data in the table down to my app (which I'm not interested in), just the metadata, schema, etc.

My issue is that for my table LoadTableAsync takes quite a long time (~2-10s), which stalls the rate at which I can call InsertAsync. I'm not really clear on why this is given I thought it was only pulling metadata not Parquet files. If I instead try to move the LoadTableAsync outside of the await foreach, and load the table once at the beginning of the process and reuse it for each RecordBatch, I find that the memory of my app quickly blows up - it looks like retaining the ITable reference also retains in-memory all the RecordBatches that have been inserted into it, and they're only deallocated when the table is disposed, is that right?

What would be the best advised calling pattern for a stream of appends like this? (Ideally some way I can pull the metadata of the table once on startup while also releasing the memory of inserted rows immediately after insertion.) For what it's worth I think I'm seeing similar behaviour in delta-rs so it might be more a question for that repo, I'm not sure.

[mightyshazam]

You can actually move the call to LoadTableAsync outside of the loop. See this test code for an example. Each call to insert updates the table metadata. The only time you would need to load again is if you were inserting from multiple processes. That would get messy, and I don't recommend it.
The best way to do this, though, is to actually just write the parquet files and create the commit manually afterward. That isn't something the library supports right now, but it is coming once some other delta kernel PRs settle.
The batches should be cleaned up once they go out of scope, but there could be a memory leak somewhere. The rust code doesn't take ownership of the pointer, so it doesn't attempt any cleanup, and the corresponding C# code calls the Free method, but there are no guarantees. The only other place I think memory could be accumulating is with the RecordBatch enumerator. At that point, you are at the mercy of the garbage collector.
Lastly, another place you could be accumulating memory is if your table has a large transaction log, the call to LoadTableAsync has to read that repeatedly. Using the CheckpointAsync method periodically can help with that.

[adamjones2]

Thanks for this. I think there probably is a memory leak somewhere in either delta-dotnet or delta-rs from this use case but I haven't managed to put my finger on it and for now my usage pattern has changed (as you suggested by reducing the frequency of my writes and by manually adding checkpoints) so that it isn't a problem for me.

I have found though that it would be useful to be able to run a periodic Optimize on my table and it looks like this isn't exposed in delta-dotnet. Do you know if there's a reason for this? I would have thought it would be a relatively straightforward copy-paste from VacuumAsync, if that's the case I can submit a PR to add it (and can raise a new issue to track it), but I'm interested in the context if it turns out this is more involved than it sounds.

[mightyshazam]

Please do. Feel free to add optimize. I clearly overlooked it when adding operations.