In praise of SQLite

[nooby_mcnoob]

I know everyone likes to use the latest thing but I've got SQLite running for Spartan (my trading/backtest platform) where it is currently:

1. Managing and adding to 7 million rows of quotes at the rate of about 1 million/day
2. Allowing complex instantaneous queries from the same DB for backtesting
3. Updating bar data

All 3 of these are occuring at the same instant and SQLite barely hiccups.

Don't knock it till you try it.

sqlite> select date(datetime),count(*) from quote group by date(datetime);
2019-02-28|139487
2019-03-01|3335677
2019-03-03|416006
2019-03-04|3225579

The database is around 1G right now.

 

[R1234]

SQLite user here. I agree - very lightweight/fast/stable. I use it linked up to Python.

 

[IAS_LLC]

Also a an sqlite(3) user....love it. Only issue that you may run into is concurrent read/writes,if you end up needing that...

 

[nooby_mcnoob]

Also a an sqlite(3) user....love it. Only issue that you may run into is concurrent read/writes,if you end up needing that...

You're right but I find that as long as you're not concurrently writing to the same table, you're fine. Which makes sense.

 

[stevegee58]

Yup, it's all I use for my IB API apps as well. I use it mainly for storing my transaction logs for PnL tracking and risk graphs.

 

[2rosy]

agreed. i like it for tests where you can grab the schema/data from a large database and load into it http://odo.pydata.org/en/latest/. also, the in memory option is good for embedding; saw it used in multiplayer games

 

[nooby_mcnoob]

agreed. i like it for tests where you can grab the schema/data from a large database and load into it http://odo.pydata.org/en/latest/. also, the in memory option is good for embedding; saw it used in multiplayer games

Never heard of odo, very cool!

 

[nooby_mcnoob]

Also a an sqlite(3) user....love it. Only issue that you may run into is concurrent read/writes,if you end up needing that...

I'm running 3 processes continuously: bar import, tick recording and backtesting from the same database. I ran into this issue today while backtesting as the backtester kept the connection open for the duration of the backtest. I had to refactor things to limit SQLite connection scope and everything is ticking along perfectly fine, and in fact, seems a lot faster. So just FYI, that's a potential workaround as well.

 

[Axon]

Another thumbs up for SQLite. I use it with Python and it is blazing fast with zero problems so far. In WAL mode, the issue of concurrent reads and writes is all but gone. I do a lot of stuff with Redis too and despite Redis being an "in memory" database, SQLite is still almost always faster since it doesn't suffer the latency of going through Unix domain sockets to pass data. Highly recommended.

 

[IAS_LLC]

Another thumbs up for SQLite. I use it with Python and it is blazing fast with zero problems so far. In WAL mode, the issue of concurrent reads and writes is all but gone. I do a lot of stuff with Redis too and despite Redis being an "in memory" database, SQLite is still almost always faster since it doesn't suffer the latency of going through Unix domain sockets to pass data. Highly recommended.

Sqlite can be in memory as well. You just have to set the file name to ":memory" ...or something like that. At least that's the case for the C API.