.duckdb file size growing

[brianlagunas]

I am using DuckDB.NET as a cache database that stores a large amount of json data in a single field associated with a cache key.

CREATE TABLE IF NOT EXISTS query_cache (
    cache_key VARCHAR PRIMARY KEY,
    result_data JSON NOT NULL,
    user_id VARCHAR NOT NULL,
    tenant_id VARCHAR NOT NULL,
    dataset_id VARCHAR NOT NULL,
    cached_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    metadata JSON,
    row_count BIGINT,
    column_count INTEGER
);

As my application runs, it adds new cache entries, and then as the cache entries expire, they are removed from the table.

The problem is that the file size will continue to grow and never shrink even as large cache items are being removed from the table.

Even if I delete everything in the table:

const string sql = "DELETE FROM query_cache";
using var command = connection.CreateCommand();
command.CommandText = sql;
var rowsDeleted = await command.ExecuteNonQueryAsync();

The file size does not get reclaimed. I have tried calling the CHECKPOINT command, but that doesn't have any impact on the file size. It just continues to grow and grow.

Is there a way to keep the files size in proportion to the data within the .duckdb file?

[Giorgi]

Hi Brian,

The Reclaiming Space page explains the limitations of the CHECKPOINT command and outlines some alternatives that might help. That said, since this isn’t really related to the .NET driver itself, it might be worth opening an issue in the DuckDB GitHub repository so the core team can take a look.

By the way, I remember using Prism many years ago with Xamarin.Forms—never would’ve imagined you’d be using one of my projects now!