Hello,
I have a question about controlling the size of sqlite database containing
records of the form id -> payload where payload has fixed size and id is an
integer column.
I am developing a simple storage for points in an sqlite database, the
problem is that space consumption on disk is slightly unsatisfying. Here is
what I mean:
A program to generate some data for the database:
#include <cstdlib>#include <cstdint>#include <iostream>
using namespace std;
int main() {
int n;
cin >> n;
for(int i = 0; i < n; i++) {
double lat = 180*(rand()*1.0/RAND_MAX);
double lon = 180*(rand()*1.0/RAND_MAX);
int64_t id = rand()*1LL*rand();
cout << id << "," << lat << "," << lon << "\n";
}
return 0;}
To import the data run:
g++ filldb.cpp
echo 100000 | ./a.out > db.text
sqlite3 test.db
sqlite> create table points(id UNSIGNED INT, lat REAL, lon REAL);
sqlite> .separator ,
sqlite> .mode csv
sqlite> .import db.text points
The size of the database right after this shows 3.3M, however the "real"
size of data is 100000*(8 bytes)*3 = 2.4M and after
sqlite> create index points_index on points(id);
the database size more than doubles to 5.1M
Changing id UNSIGNED INT to INTEGER PRIMARY KEY does help, but is there
anything else to be done, in particular when the database is static (we
know there wont be any inserts)?
Thank you
Igor
