Hello Rinat, I compress my data into blobs using Gzip before insert, and decompress when I read the blobs back out but, leave the other data in the DB un-compressed. In that way, I get compression but, normal operations remain fast. This works if the data to compress > 4Kish. My blobs can be 200K to 20megs in size. If it's a bunch of small text columns, this technique won't be useful.
C Wednesday, May 28, 2014, 5:26:29 AM, you wrote: HR> Hi Roger HR> Where can I learn how to characterize my database? HR> I work read-only, single connection. HR> I have 13 tables, of which 2 are huge, 2 are medium and the rest are very small. HR> My huge tables have 3 columns: numeric, numeric and varchar with millions of rows. HR> I keep an index on the numeric columns. HR> Does that bring up any column ordering suggestion? HR> Thanks HR> Rinat HR> -----Original Message----- HR> From: sqlite-users-boun...@sqlite.org HR> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns HR> Sent: Wednesday, May 28, 2014 12:06 PM HR> To: General Discussion of SQLite Database HR> Subject: Re: [sqlite] Reading compressed database files HR> -----BEGIN PGP SIGNED MESSAGE----- HR> Hash: SHA1 HR> On 28/05/14 00:25, Hadashi, Rinat wrote: >> My databases are very big (almost 100 GB). I am looking for a >> compression solution. >> >> Did anyone have an experience with reading a compressed database? HR> It would be helpful if you characterise your data and queries. HR> For example if the size is due to blobs, then careful ordering of HR> columns, or moving them to separate tables will likely be very useful. HR> You can get compression external to SQLite by using a compressing HR> filesystem like NTFS or btrfs (make sure to pick an appropriate HR> page size), or by internal compression with cerod: HR> http://www.hwaci.com/sw/sqlite/cerod.html HR> Roger HR> -----BEGIN PGP SIGNATURE----- HR> Version: GnuPG v1 HR> iEYEARECAAYFAlOFpvsACgkQmOOfHg372QRSRACfcDqTprcD//n9yYXcGPl9yQfo HR> sTIAoLkIaQHR4JAwk1LbuRzCyQsx/5aN HR> =tYeT HR> -----END PGP SIGNATURE----- HR> _______________________________________________ HR> sqlite-users mailing list HR> sqlite-users@sqlite.org HR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users HR> --------------------------------------------------------------------- HR> Intel Israel (74) Limited HR> This e-mail and any attachments may contain confidential material for HR> the sole use of the intended recipient(s). Any review or distribution HR> by others is strictly prohibited. If you are not the intended HR> recipient, please contact the sender and delete all copies. HR> _______________________________________________ HR> sqlite-users mailing list HR> sqlite-users@sqlite.org HR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Teg mailto:t...@djii.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
