On Friday, 20 September, 2019 10:49, Chi Ng <chi.wah.ng2...@gmail.com> wrote:
>What is the compression ratio for a compressed sqlite database file? >And what is the write/read speed for compressed file comparing to a none >compressed file? Are you referring to: (1) a compressed database file such as one would obtain by running the zip utility against it (2) a normal data file stored on a compressed filesystem (3) a database utilizing the ZIPVFS described here https://www.sqlite.org/zipvfs/doc/trunk/www/index.wiki The read/write speed is unchanged when the file is compressed. The I/O speed is determined by the hardware and the OS -- the entropy of the data passing though the I/O APIs is irrelevant. If you mean the application I/O throughput that would depend on how often you are "updating blocks in place" and what compression you are achieving on those small blocks. The compression ratio in (1) depends on the entropy of the file (the randomness of the data). The higher the information content the lower the compression. For example, English Prose in ASCII has a very low information density and is therefore highly compressible. Fractal encoded images (such as JPEG) and binary executables have very high information content and thus cannot be compressed very well at all. ZIP/COMPRESS etc. operate across the entire file so the bigger the file, the greater the possible compression. For (2) and (3) the compression can only work at the relatively tiny cluster (2) or page (3) level, so the compression achievable in (2) is much less than (1), and (3) is much less than (2). Plus, of course, if you intend to "work" on the file while it is compressed then you will have to use the small compression blocks in (2) or (3) plus significant overhead to keep track of the "compressed blocks" so they can be updated. Not to mention that your "compressed file" will also eventually contain lots of unused space as it is updated due to the changing size of the blocks. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
