I'm building a self-tracking system to log e.g. my phone's accelerometer, the keys I type, my mouse activity, and so on.

So which database should I use? My research so far suggests SQLite3, because the database won't be very big - my calculations give around 1 million new (small) records per day, which should be around 15MB per day.

Some software I use already stores its data in an SQLite3 database; would I have to write much special-case code to use the two different types of databases, or would the same code suffice?

  • Personally I'd use MySQL but that is solely due to my familiarity with it over other RDBM options. – Nick Dickinson-Wilde Apr 9 '14 at 21:06
  • I guess a requirement is SQL-type database? – dimzak Apr 10 '14 at 6:42
  • 1
    @NickWilde: MySQL is still more limited than most RDBMS and it's speed is no longer much better. But for single user, SQLite is better than any server beast. – Jan Hudec Jun 6 '14 at 14:42
  • SQLite3 is fine with large data. I use it regularly with databases on order of tens of gigabytes. It is pretty fast, but it has slow commit, so when you do many inserts it's absolutely necessary to wrap them in transaction. – Jan Hudec Jun 6 '14 at 14:45

I would suggest you keep using SQLite3: most applications that need an embedded database for local/client storage use it, and 20 writes per minutes doesn't sound to me like a lot, all the more so as from what you describe the writes are probably just inserts, which you can easily batch provided that you use SQLite 3.7.11 or up:

 INSERT INTO 'tablename'
      SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION SELECT 'data3', 'data4'
UNION SELECT 'data5', 'data6'
UNION SELECT 'data7', 'data8'
| improve this answer | |
  • No need for this complex SQL. Just put the inserts in a transaction. SQLite is very fast in most operations, but commit is slow. With proper use of transactions it is faster than most alternatives, without them it's unuseable. – Jan Hudec Jun 6 '14 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.