On Wed, 10 Jun 2015 14:13:29 +0000 Igor Stassiy <istassiy at gmail.com> wrote:
> Thanks for all your replies. Please let me clarify: > > I need to do certain range operations efficiently and I prefer SQL > functionality, plus SQLite gives a cross platform file storage > format. The table having 3.2 megabytes is just an example. I am > storing much more (orders of 10^5 of points) however the effect is > similar. > > When I store several tables side by side the effect seems to be worse > (I assume SQLite leaves the possibility for inserts and hence needs > to have some space to do them efficiently, please correctly if I am > wrong). SQLite forums suggest to store tables in separate files to > avoid vacuum space in between the tables, are there any other methods > to save space? Have you look at R-Tree extension? https://www.sqlite.org/rtree.html It creates a table and indexs to store and query latitude and longitude efficiently. Or spatialite? https://en.wikipedia.org/wiki/SpatiaLite http://www.gaia-gis.it/gaia-sins/ It's opensource under MPL. A page is dedicated only to one thing, table A, table B, internal sqlite3 data, index C, whatever... A page can't store data from one table and data from another table or index. This means that if your page size (default 1024bytes) is not defined wise enough (f.ex. 64KB=65536byes) and your tables uses a few bytes (say 128bytes), the rest of the page is empty (65536-128=65408 bytes) for future table grows. There are page header and tail with data you must count too, so it's a bit less. Note that you want to store a big table with data, and the space lost on unwise page size is minimal. You can have empty pages inside the database because you deleted data or drop an index or a table, but in your static db, it shouldn't happen. In any case, a vacuum and analyze solve the issue. I use the trick to store tables in separate db files and attach them to main db for speed on mechanical hard disks, because those files on different disks maximize the iops. An autovacumm and proper db maintenance should minimize the vacuum space between tables. HTH --- --- Eduardo Morras <emorrasg at yahoo.es>
