A lot of the SQLite overhead is going to be in the stuff surrounding your actual data; I'd be surprised if you saved much space by using fixed-size ints vs the varints used by SQLite. You didn't mention about indexes; if you have any, they will take a lot of space because your row size is so small.
Maybe write your own VFS for SQLite? Definitely run sqlite3_analyzer before deciding anything. Jim -- HashBackup: easy onsite and offsite Unix backup http://sites.google.com/site/hashbackup On Tue, Dec 7, 2010 at 10:57 AM, Laszlo Nemeth <lnem...@cs.bilgi.edu.tr>wrote: > Hi, > > I have a database (6 in fact) of high-frequency data > > create table eurusd (tick integer not null, bid float not null, ask > float not null); > > with 80M records currently and growing, freshly inserted, no deletions > will ever take place, the schema will never change, and neither of the > fields can be null. The size is already 3.6G (I put an index on it > after bulk insert), which is a bit too much for me considering that > the existing infrastructure (ie binary file 12bytes per record) is > 800M (which fits into memory and I don't even need to index). > > Having checked older posts on sqlite-users, I noticed that this issue > comes up frequently and normally the problem is either wrong choice of > datatypes (ie text instead of integer), or unnecessary indices. None > of which applies here. > > Question: is it possible to recompile sqlite to force the > representation of integers to be 4 bytes, and that of floats to be > also 4 bytes. I would like to have no observable change in the > behaviour of sqlite. > > I > (1) am quite comfortable with the hacking, > (2) understand that the database will no longer be platform > independent, nor compatible with anything else, > (3) tried to run the analyser to see if there is something fishy with > half full pages, but it wants tcl8.6 and haven't gotten around to > install it (will do), > (4) also checked the file format document, but that didn't give me any > immediate hint how to achieve what I'd like, though I only skimmed it > through. > > The point of doing this is that I get a smaller db, and I still get > all the beautiful machinery built for sqlite. > > Any suggestions, a complete solution, or a "no that's not possible, > because..." will be much appreciated. > Thanks, Z > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
