And did I mention that the file handles are kept open for the whole program duration (might be multiple hours) ? That's fun, data loss is almost a daily occurrence. It's the main thing I'm refactoring right now.
Most data fits into hierarchical data structures that don't form a complicated memory graph with references.
Once you know how to write primitive types and arrays of them, you can write objects, and it means you can write anything (without having to worry about writing "before" or "after").
And yes, switching to SQLite absolutely addresses the "perpetual fopen” problem.
(I have an idea to implement an LSM tree where each layer is an arrow file, which should allow for faster mutations while maintaining a lot of the benefits of arrow. But I haven’t got around to it).
Duckdb can also read / import SQLite natively so we could ingest lazily from sqlite disk -> duckdb.
Biggest issue with duckdb is some memory leaks and crashes for the nodejs driver. Seems not production ready.
The software was writing vast arrays of numbers into a file, and reading them based on offsets.
As the software (and its data) evolved, versioning became a nightmare.
I ended up writing an ORM serializer which read from/wrote into an SQLite DB with a variation on an EAV schema.
One of my favorite projects, result-wise. Reading and writing was clean and efficient; the format was portable, language-agnostic, compact.
Versions of the new format were backwards- (and, within reason, forward-) compatible with each other (I forced specifying sane defaults for attributes if they're missing).
I'm still thinking of reimplementing a project like that as FOSS one day.
Serializing numeric data to something like JSON is a waste, and the structured binary formats I've looked at aren't nearly as thought out as SQLite.