3 ms·

Well, I certainly agree about benchmarking versus other options, especially if I/O proves to be a bottleneck. The other option above was not a single CSV or JSO

by mcbits 7y ago

Well, I certainly agree about benchmarking versus other options, especially if I/O proves to be a bottleneck. The other option above was not a single CSV or JSON-structured file, but millions of small files stored on the filesystem (with its requisite indexes, permissions, access logs, etc). And the comparison is not with the file contents being splayed out into a relational structure in SQLite, but just one flat key-blob table for storage and retrieval. It's possible that a multi-gigabyte CSV file would be faster still, depending on actual access patterns and how much effort you want to devote to this bespoke CSV database.

civility 7y ago
> The other option above was [...] millions of small files stored on the filesystem I don't have any direct comparisons of SQLite to this approach, but other projects I've worked on did have a simple directory tree organizing medium size chunks of data by date over 20+ years. We had one "DB" where everything was stored by the degree of latitude and longitude it fell into, and another where things were stored by date. Both were gigabytes in size at a time when that was considered a large data set, and it was very fast to open only what you needed. Depending on the problem, this can be a very good solution. It was trivially accessible from any programming language (including ones that didn't exist when the DB was started), and it was trivial to migrate to new operating systems (some of this started out on VMS). I like SQLite quite a bit, but it's not always the best solution to storing tabular/relational data.
- mcbits 7y ago
  How many files are you talking about? That's the relevant variable, not the amount of data or whether it's text, tabular or relational data, images, audio, etc. E.g. you can copy a single 10 GB file to another drive faster than 1 million files totaling 1 GB, all due to filesystem overhead. If there's a filesystem where that's not true, I'm interested. :)
  - civility 7y ago
    > How many files are you talking about? The numbers won't seem significant by today's standards. In the one case (stored by date), maybe 150 thousand files, each a few megabytes. > you can copy a single 10 GB file to another drive faster than 1 million files totaling 1 GB True, but I'll bet you can create (or read) a thousand files with a thousand records each faster than you can insert (or select) a million records into (or from) a SQLite table.
    - mcbits 7y ago
      150,000 is solidly in the realm where SQLite tested faster than the filesystems in the link above, although their files were only a few kilobytes. It's almost certainly different (worse) for multi-megabyte files. But what I've been trying to convey is that the number of records in the SQLite table will be identical to the number of files. If you need to parse the file contents, you'd parse the BLOB just the same. The difference is in how you interact with the disk. Is reading X-thousand files containing a thousand records each (or one thumbnail, or one HTML dump, or one JavaScript function, or whatever) faster than SELECTing exactly the same number of BLOBs containing exactly the same data? It's worth considering and testing once the number of files starts affecting performance or even just becomes a pain to deal with. If it turns out that storing many files is still a better fit for a particular application, that's cool too. Nothing is a panacea.

by mcbits 7y ago

civility 7y ago

> The other option above was [...] millions of small files stored on the filesystem I don't have any direct comparisons of SQLite to this approach, but other projects I've worked on did have a simple directory tree organizing medium size chunks of data by date over 20+ years. We had one "DB" where everything was stored by the degree of latitude and longitude it fell into, and another where things were stored by date. Both were gigabytes in size at a time when that was considered a large data set, and it was very fast to open only what you needed. Depending on the problem, this can be a very good solution. It was trivially accessible from any programming language (including ones that didn't exist when the DB was started), and it was trivial to migrate to new operating systems (some of this started out on VMS). I like SQLite quite a bit, but it's not always the best solution to storing tabular/relational data.

mcbits 7y ago
How many files are you talking about? That's the relevant variable, not the amount of data or whether it's text, tabular or relational data, images, audio, etc. E.g. you can copy a single 10 GB file to another drive faster than 1 million files totaling 1 GB, all due to filesystem overhead. If there's a filesystem where that's not true, I'm interested. :)
- civility 7y ago
  > How many files are you talking about? The numbers won't seem significant by today's standards. In the one case (stored by date), maybe 150 thousand files, each a few megabytes. > you can copy a single 10 GB file to another drive faster than 1 million files totaling 1 GB True, but I'll bet you can create (or read) a thousand files with a thousand records each faster than you can insert (or select) a million records into (or from) a SQLite table.
  - mcbits 7y ago
    150,000 is solidly in the realm where SQLite tested faster than the filesystems in the link above, although their files were only a few kilobytes. It's almost certainly different (worse) for multi-megabyte files. But what I've been trying to convey is that the number of records in the SQLite table will be identical to the number of files. If you need to parse the file contents, you'd parse the BLOB just the same. The difference is in how you interact with the disk. Is reading X-thousand files containing a thousand records each (or one thumbnail, or one HTML dump, or one JavaScript function, or whatever) faster than SELECTing exactly the same number of BLOBs containing exactly the same data? It's worth considering and testing once the number of files starts affecting performance or even just becomes a pain to deal with. If it turns out that storing many files is still a better fit for a particular application, that's cool too. Nothing is a panacea.