Keyboard Shortcuts
Likes
Search
database recommendations/preference?
|
hi
I didn't see any post that asked this directly so, I'll ask it. I'm trying to get time/sales of options data end of day(EOD)? will use tws api and python. I think i should organize the data in a db rather than file, excel.... I may build a front end webpage to view the data? so which one: leaning SQLite i hear mySQL is popular as is PostGreSql My thought is to use SQLite and migrate to the other if I'll need it.... Any comments and opinions would be appreciated.... Thanks |
|
Jens Rohweder
Dear Hedgemoney, we are using MariaDB for large?dataset. However, storing the data into SQL and retrieving it back is more complicated?than using csv-files or Google Drive. I prefer storing the data in csv-files. Python offers so many tools and very efficient methods to calculate all kind of models or output. And you may visualize the output with Dash (plotly).? Best regards Jens Am Di., 27. Okt. 2020 um 20:47?Uhr schrieb <hedgedmoney@...>: hi |
|
I concur with Jens. For a majority of purposes text files are sufficient. Unless you are going to perform very heavy queries I would go with that. If you are still thinking to use a database consider one of object databases depending on your development practices? Ed On Tue, 27 Oct 2020 at 22:01 Jens Rohweder <twsapigroup@...> wrote:
--
Ed Gonen |
¿ªÔÆÌåÓýI agree with Jens and Ed.? I get the IB data and store it to csv
using python. Then another script reads the csv into a Pandas
dataframe and that where the fun starts. On 10/27/20 3:07 PM, Edward wrote:
|
|
In short - Untiil you need to manage more than 100 000 records and more than 20MB stay with file! CSV is the best (universal reader and humanly understandble) If you ask this question you probably aren't fluid with DB so even if theses limits seems huge for a DB specialiste , the rampup to manage a DB is huge too. - Use MySQL rather than MariaDB (or PostGres), because of available free DBMS (Tools to explore it) - Stay away of MongoDB if you can. - Consider Redis. ? ? In Long: SQL: 1- I recommend MySQL over MariaDB (even if I support MariaDB initiative against Oracle handover on MySQL) While being a "clone" of MySQL, MariaDB is sound but lack support for selecting some "storage engine" and is (at least "was") limited to InnoDB BTW innoDB is the default MySQL engine but 3 time more hungry in disk space and CPU ? I use MySQL with MyISAM storage engine. (And because running on windows I can use MemoryMapped IO, faster than TCP/IP) MyISAM is a non-transactional and seems that Maria DB cannot use it because of licensing issue. MyISAM is very fast, and in trading you are NOT doing things that require rollback of transactions. MySQL even support a "MEMORY" Storage engine that are the fastest before memorymap (until you switch off power!!) disputable with SSD theses days. ? 2- SQlite is very good But SQLite lack a ancillaries management tools (DBMS), If you enter the "world of DataBases" you NEED DB management tools. ( That is what NotePad do when you look at your text file DB :-) Tools like "MySQL workbench" is free and allow light but powerfull administration of your DB (Having tools to explore your DB are priceless when something doesn't work!) Need to allocate 2 full weeks to start scratch in the world of DB ? 3- MongoDB While being very popular MongoDB is nothing more than a memory map db in disguise. it is badly written (very Slow)? and the query langage is cubersome not standardized and there is boring difference of syntax depending upon langage of you application (Java/JS/C) MongoDB is an ogre in memory (fast until you reach 20M record (docs) then brutally slow) Mongo is popular because it allow messy Json to be stored in a messy way. (developper love to postpone cleaning code) MongoDB is Very easy to start with, but this is a devil in disguise! because very difficult to modify or do complex query. ? I allocated time to benchmark MySQL,PostGress,SQLite, Mongo, RobinHoodmap. In the context of Trading (i.e. a LOT of records, in a SMALL set of table (or document), and very simple request , generally a single table, but that better be executed fast) MongoDB was very disappointing, modern but bad, way to slow when more than 100M records if less than 4GB allocated to it. MySQL/PostGress are good and about same perf, PostGres faster on transactional insert, MySQL faster on non-transactional and better support. So I choose MySQL ? 4- If doing HFT (but what are you doing with IB then ?) The ONLY solution in HFT is Memory Map.? I like RobinHood map, 33Million insert/fetch by seconds Yes! you need to implement your own back-store of data (all is in RAM) but also consider Redis (key/Value DB) if you cannot do your own, Redis have a "Journal" and is a VERY GOOD thing. ? Sorry for the essay but DB in trading is NOT used in same way as a ledger or inventory DB or some other traditional use. ? ? ? |
|
Hello, Only go as complex as you need to. CSV files are quick and easy to examine. If you implement the interface to the data-getting functionality as a class, you can always swap it out for another implementation if you wish to enhance it later. My live DB for trading is around 60gb, and it's not because I harvested a lot of tick data. But it started small enough to run from CSV files. So plan for the fact your requirements will change. Best wishes, M On Tue, 27 Oct 2020 at 20:01, Jens Rohweder <twsapigroup@...> wrote:
-- +44 (0) 7528 551604? Gulfstream Software - Winner Risk Management Awards 2010 This message is subject to : |
PyStore - Fast data store for Pandas timeseries datathis is a very good, pretty easy to use like csv...db Best Regards, sv On Wed, Oct 28, 2020 at 3:15 PM mark collins <mark.collins@...> wrote:
|