Zipline Database postgresql + pydantic

[MBounouar]

• Some classes like DataPortal in zipline would be quite amenable to a conversion to pydantic attrs models and would even make some of the code less verbose (validators, settings, ...). attrs is great simplifies the code in many places (slots, validators, etc)
• Using postgresql as another possible database along sqlite would be feasible. However, there are a few caveats and decisions to be made.
• Does it make sense to retain the old database version downgrades ? Wouldn't it make more sense to do a reset there?
• Should sqlite be dropped completely ?
• Why are dates stored as integers? Is that for historical reasons or is there a real advantage to it?
• Would it be an issue to simply remove the volume constraint to be UINT32? (zipline truncates)
• Can the current schema design be improved without breaking the whole code ?
  

[RichardDale]

Some random thoughts from someone who hasn't been intimately involved in Zipline's development (so I can't really give any historical context to the underlying Zipline constraints) but as someone who has shoe-horned an existing environment to operate with Zipline....

Just looking at the scehma for futures - it appears there was never really a "2 character root_symbol" limitation - I guess that was some sort of hard-coded convention somewhere along the way, probably by a US-centric person who hadn't considered much outside of USA (where only 2 letters are used). Some additional mechanism to replace the hardcoded slippage values would probably be helpful as these can certainly change over time.

SQLite is a good choice because of its lightweight nature and its ubiquitous nature Since it's only used for the security data (and the vast bulk of the data - pricing) it's not really a bottleneck, and requiring postgresql would be an overkill for most people.

Some additional metadata fields would be helpful.. even a free-form json-type approach adopted by many NoSQL databases could provide an extensible - perhaps even a .json file on disk....

On the futures side there have been variations in tick size over the years, sometimes mid-contract, so that's a consideration. Also, margins can and do vary over time. We don't expose any of the historical margin rates (we don't have a complete set, and the exact dates they change are difficult to determine). It's probably not much of an issue, other than producing a nice equity graph. In my experience, experienced futures trades want to know their per-contract-traded trading stats, not "how much money can I make with a starting balance of X", which is obviously predicated by margin values.

Dates as integers are OK as long as you can show the entire range of values applicable. I believe integer was chosen simply because it's a small-in-RAM structure (32 bit), but I believe SQLite will expand it to 64 bit as required for overflow. Perhaps more interesting is how smaller non-daily time periods are handled (with, or without timezone data). Time format conversion does take a reasonable amount of CPU, so choice is important here.

Volume truncation is only evident in securities that have significant amounts of reverse splits. For the most part, using a float gives "good enough" accuracy (6-7 significant figures) but could of course be extended (e.g. a double precision floating point) further at the cost of RAM, reduction in effectiveness of CPU cache, and of course storage. The same issues exist for pricing data too.

Regarding database/schema upgrades - Zipline has been doing this for a while now (Zipline 1.0 was different to 1.3 etc.) - I don't think there's any need for databases to have any sort of backward compatibility/downgrade capability.

Cheers,
Richard.

[MBounouar]

@RichardDale , Thanks for your feedback.

postgresql is indeed an overkill for a majority of users and adds complexity on top, but I believe that it could bring zipline to another level.

Maybe we should see if dropping some of these types limitations or at least making them easily configurable for the user makes more sense.

FYI zipline already does have the capability via sqlalchemy to do databases migrations, I am just not sure how useful it is for anyone to keep and test these old versions.

[JZ-BRMGT]

I am currently working on finishing up this implementation for myself. Personally I store 15TB of intraday market data on my PostgreSQL server. I do not have space to have a second copy in my user/.zipline/custom data folder.

Writing out a method that can inject a bundle (only containing relevant securities and timeseries to the analysis on hand.) I will share when finished up. I use Polygon.io's S3 buckets so the prices are not adjusted (currently working in corporate actions calcualtion's as part of the bundle prefetcher.)

I was curious if anyone else had worked on a better integration with zipline --> postgresql then the method I am working on. I will share if it works.

[MBounouar]

Hi,
Zipline data bundles are split into two parts, the sqlite database that contains the instruments "attributes" and the timeseries data that are stored in the .bcolz file.
The first part should work without too much trouble with postgresql (though the tests have been disabled, see test_assets.py for instance). The second part, reading from the bcolz, is where the bulk of the work is. I have done some preliminary work on that (awhile ago now). Switching to reading timeseries from postgresql or another storage system (parquet, hdf5, ..) is not per se difficult, but time consuming. Implementing a solution that allows a zipline release to offer both the old solution and full database reading solution without making drastic code changes is where I personally struggled.

[mysyzygy]

Looks like there is already an implementation of postgres for zipline within zipline-trader: https://zipline-trader.readthedocs.io/en/latest/database-backend.html.

As an aside i did extract the live trading portion of zipline-trader into a separate module to work with zipline-reloaded, if people are interested.

[mf12ca]

@mysyzygy , just reading this now, so it's been a while. I am just getting more and more familiar with zipline, but I'd be really interested in the live trading extension of zipline (it's on my ToDo's eventually also...) would you be happy to share sth by any chance?