Migrating¶

Two starting points, and they are very different conversations. Moving from on-heap CQEngine is a real trade you should make deliberately. Moving from CQEngine's SQLite persistence is nearly free.

From on-heap CQEngine¶

One line changes:

// before
IndexedCollection<Car> cars = new ConcurrentIndexedCollection<>();

// after
IndexedCollection<Car> cars = new ConcurrentIndexedCollection<>(
        DuckDBPersistence.onPrimaryKey(Car.CAR_ID));

Everything after that line is unchanged — add, addAll, remove, update, retrieve, orderBy, and/or/not, ResultSet.size(), iteration, all of it.

Swap your index types too:

cars.addIndex(NavigableIndex.onAttribute(Car.PRICE));    // before
cars.addIndex(DuckDBIndex.onAttribute(Car.PRICE));       // after

What you are actually trading¶

Be clear-eyed about this. On a million objects:

	CQEngine on-heap	DuckDB (columnar file)
process memory	1,387 MB	145 MB
Java heap	862 MB	3 MB
on disk	–	36 MB
loading 1M objects	2.7 s	4.1 s
point lookup by key	7.2 µs	556 µs
narrow range	39 µs	3.2 ms
count matches	3.6 µs	906 µs
query returning 2%	1.1 ms	13.2 ms
iterate everything	210 ms	339 ms
single `add()`	4.6 µs	937 µs
bulk write per object	3.1 µs	2.3 µs
join 200k to 50k	0.373 s	0.270 s

On-heap CQEngine wins every small single-collection query, and it is not close — between 80x and 250x. No amount of tuning changes that, and the reason is structural: DuckDB sequentially scans a table even for an equality on its primary key, so no index you add will help. See Querying.

What you get back is 862 MB of Java heap the garbage collector no longer walks, a 36 MB file instead of 1.4 GB of process memory, joins across collections, and the whole of SQL.

And the table above asks only the heap's questions. Ask a database's and it inverts, on the same million objects:

	on-heap	quackjvm
sum a column over 200k matches	10.5 ms	1.0 ms	10x faster
group by make: count and average price	117.5 ms	1.9 ms	62x faster
pivot, median, approximate distinct counts	not possible	under 15 ms	–

The dividing line is whether a question needs the objects rebuilt. Fetching one object: stay on the heap. Asking something about many objects: this is 10–60x faster and expresses things the heap cannot. See Aggregates and projections.

Take this when memory is your constraint, not when latency is. If your p99 is dominated by many small lookups, stay on the heap.

Two rows go the other way and are worth noticing: bulk writing is faster than the heap, and a join across collections is faster than the heap. Those are the workloads a database is built for.

Two requirements, both from CQEngine rather than from this plugin¶

A primary key attribute. Persistence needs a SimpleAttribute that uniquely identifies each object. Any non-heap CQEngine persistence requires this.
Close your ResultSets. A result set holds a database connection until closed. Use try-with-resources, as you would with CQEngine's own disk or off-heap persistence. On the heap you get away with forgetting; here it eventually shows up as a checkpoint failure.

A migration order that works¶

Swap the persistence and the index types. Run your tests. Everything should pass.
Set memoryLimit. Nothing else affects memory as much — see Tuning.
Fix every unclosed ResultSet. Wrap them in try-with-resources.
Add a ColumnarLayout if your fields are types DuckDB understands. Smaller on disk, faster to iterate, and it is what makes SQL and joins possible: Storing objects.
Build collections through DuckDBDatabase rather than directly, so compound queries push down as one statement and collections can be joined: Joins.
Call optimize() after your bulk load.
Replace the hot "retrieve everything then aggregate in Java" paths with database.query(...). This is where the large wins are — 44x on a sum.

Steps 1–3 are the migration. Steps 4–7 are where it starts paying for itself.

From CQEngine's SQLite persistence¶

This one is close to free: you are already off the heap, already have a primary key, already close your result sets. The mapping is one-to-one:

CQEngine	quackjvm
`DiskPersistence.onPrimaryKey(pk)`	`DuckDBPersistence.onPrimaryKeyInTempFile(pk)`
`DiskPersistence.onPrimaryKeyInFile(pk, file)`	`DuckDBPersistence.onPrimaryKeyInFile(pk, file)`
`OffHeapPersistence.onPrimaryKey(pk)`	`DuckDBPersistence.onPrimaryKey(pk)` (the default)
`DiskIndex.onAttribute(attr)`	`DuckDBIndex.onAttribute(attr)`
`SQLiteIndexFlags.BULK_IMPORT`	`DuckDBFlags.BULK_IMPORT` (the same flag value)

What changes¶

	CQEngine SQLite (file)	DuckDB (columnar file)
process memory	88 MB	145 MB
on disk	225 MB	36 MB
loading 1M objects	11.6 s	4.1 s
point lookup by key	546 µs	556 µs
narrow range	1.4 ms	3.2 ms
count matches	7.1 ms	906 µs
query returning 2%	98.6 ms	13.2 ms
iterate everything	751 ms	339 ms
single `add()`	1.1 ms	937 µs
joins across collections	not possible	yes

DuckDB is now ahead on almost everything: counts (7.8x), large result sets (7.5x), iteration (2.2x), disk footprint (6.3x), loading (2.8x) and single writes (1.2x), and level on point lookups. SQLite keeps two columns — a narrow range scan, where a B-tree descends while a columnar index table is scanned, and resident memory.

Unless narrow range scans dominate your workload, there is little reason left to stay on CQEngine's SQLite persistence — and it cannot join.

Two CQEngine bugs you stop having¶

CQEngine 3.6.0 pins sqlite-jdbc 3.27.2.1, which ships no native library for macOS on aarch64 — its disk and off-heap persistence cannot start at all on Apple Silicon. quackjvm bumps the dependency so both work.
CQEngine's SQLite indexes cannot store an enum attribute (Type class ... not supported). quackjvm stores enums as their ordinal, so they index normally.

Not using CQEngine at all¶

You do not need it. quackjvm-core on its own gives you Java objects as DuckDB columns (ColumnarLayout, TableWriter), typed SQL results (Rows), and Arrow columnar reads — against any DuckDB connection:

<dependency>
    <groupId>io.github.bdarwin</groupId>
    <artifactId>quackjvm-core</artifactId>
    <version>1.1.0</version>
</dependency>

Start at Storing objects and Aggregates, and see examples/CoreColumnarRecords.java.

Reproducing these numbers¶

Every table on this page comes from one harness, forking a JVM per configuration because resident memory is not comparable within one process:

java -cp <classpath> io.quackjvm.cqengine.bench.Comparison 1000000 6g 256MB

Memory is reported as process RSS, not JVM heap: DuckDB and SQLite both keep data in native memory that Runtime.totalMemory() cannot see, so heap alone would flatter them enormously. Measured on JDK 25, Apple Silicon, 32 GB.