3 ms·

Once upon a time, I revamped a binary file format for a propriety industrial-scientific software. The software was writing vast arrays of numbers into a file,

Once upon a time, I revamped a binary file format for a propriety industrial-scientific software. 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.

gchamonlive 2y ago
For numeric values only, any advantage of using sqlite over let's say parquet with Apache Arrow/duckdb?
- avereveard 2y ago
  SQLite let's you decide when and if vacuuming after partial row modifications. Does arrow allows that I don't know but it's most likely the best SQLite feature you can open a massive database and do one value change really really fast without having to write and compact the entire thing all the times.
  - andyferris 2y ago
    Arrow isn’t great at random insertion and deletion, as each column is a contiguous array. If it’s on disk those arrays are probably packed tightly together, so you can’t even append rows. (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).
- CJefferson 2y ago
  Given SQLite is already distributed on basically every OS,is amazingly rock solid, I’d ask instead any advantage to using Apache Arrow/duckdb? I don’t have great experience with either.
  - jitl 2y ago
    DuckDB can have massive performance advantage over SQLite for things like aggregates on wide tables or or filters on unindexed columns, with advantage growing with the size of the table. We tried a few head to heads between the two and while we ultimately went with SQLite, we may go back and double write everything to DuckDB to get better performance for those kinds of queries on the same data set. 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.
    - datadrivenangel 2y ago
      They did just release DuckDB version 1.0, so it should be getting much more reliable and stable.
      - jitl 2y ago
        They said 1.0 means “we aren’t going to break the disk format backwards compatibility anymore” other than that, it’s just a number and doesn’t have any power to make the software more reliable. It really depends on their priorities how much they’ll actually stabilize my use-case. I’ll probably check back in a year and see if things improved.
- prirun 2y ago
  SQLite encodes integers in a variable-length format, so it can be very space-efficient compared to always storing integers in 4 or 8 bytes.
Jean-Papoulos 2y ago
Strangely enough I'm doing the exact same thing. My case seems a bit more complicated since we're dumping not only long arrays of floats, but also a bunch of miscellaneous data (datetimes, unique datapoints, ids) right before/after those numbers. So it's even more of a nightmare... Some of these files have version numbers in the 30's (oh yeah, and file versions are based on file size.) 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.
- romwell 2y ago
  Well, with an ORM you put everything in data structures, and write them. 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.

gchamonlive 2y ago

For numeric values only, any advantage of using sqlite over let's say parquet with Apache Arrow/duckdb?

avereveard 2y ago
SQLite let's you decide when and if vacuuming after partial row modifications. Does arrow allows that I don't know but it's most likely the best SQLite feature you can open a massive database and do one value change really really fast without having to write and compact the entire thing all the times.
- andyferris 2y ago
  Arrow isn’t great at random insertion and deletion, as each column is a contiguous array. If it’s on disk those arrays are probably packed tightly together, so you can’t even append rows. (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).
CJefferson 2y ago
Given SQLite is already distributed on basically every OS,is amazingly rock solid, I’d ask instead any advantage to using Apache Arrow/duckdb? I don’t have great experience with either.
- jitl 2y ago
  DuckDB can have massive performance advantage over SQLite for things like aggregates on wide tables or or filters on unindexed columns, with advantage growing with the size of the table. We tried a few head to heads between the two and while we ultimately went with SQLite, we may go back and double write everything to DuckDB to get better performance for those kinds of queries on the same data set. 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.
  - datadrivenangel 2y ago
    They did just release DuckDB version 1.0, so it should be getting much more reliable and stable.
    - jitl 2y ago
      They said 1.0 means “we aren’t going to break the disk format backwards compatibility anymore” other than that, it’s just a number and doesn’t have any power to make the software more reliable. It really depends on their priorities how much they’ll actually stabilize my use-case. I’ll probably check back in a year and see if things improved.
prirun 2y ago
SQLite encodes integers in a variable-length format, so it can be very space-efficient compared to always storing integers in 4 or 8 bytes.

Jean-Papoulos 2y ago

Strangely enough I'm doing the exact same thing. My case seems a bit more complicated since we're dumping not only long arrays of floats, but also a bunch of miscellaneous data (datetimes, unique datapoints, ids) right before/after those numbers. So it's even more of a nightmare... Some of these files have version numbers in the 30's (oh yeah, and file versions are based on file size.) 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.

romwell 2y ago
Well, with an ORM you put everything in data structures, and write them. 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.