Skip to main content

fo_store.py — F&O EOD Parquet History Store

scr_nsefo_eod was 11-12 GB (47M rows, about 65% of the whole database). Same model as eq_store:

  • The full F&O EOD history (2000-06-12 onward) lives in monthly Parquet files.
  • Postgres scr_nsefo_eod is a staging window of about 90 days. The daily F&O bhav loader keeps writing there, so the loader, its rollback (deleteadateabove) and the "latest date" checks (max(sdate)) do not change.

Layout​

NSEFO_DATA_PATH/ LOCAL_NSEFO_DATA_PATH / VPS_NSEFO_DATA_PATH in backend/.env
├── raw/{yyyy}/{yyyymm} one extensionless Parquet file per month, rows sorted (masid, sdate)
└── _catalog/days.parquet sdate, rows, sum_close, sum_volume, sum_oi (used by verify)

Per contract files (like equity) were rejected: scr_master holds ~1.9M contracts, mostly weekly options. A month file gives ~315 files, the daily append rewrites one file, and (masid, sdate) order lets DuckDB skip row groups on a masid lookup. Columns mirror the table (float32, NaN stored as NULL). About 0.9 GB for the whole history.

Reading - never read the files directly​

FunctionUse
get_fo(masids, frm, to)rows of one or many contracts (Parquet before the first staging date, Postgres from it)
get_day(day)every contract on one date
available_days(frm, to)dates with rows, from catalog + Postgres
masids_with_data(frm, to, among=None)contracts with a row in range; pass among (candidate masids from scr_master) to keep it small
all_masids()every contract with any row
fo_view(con, cols, frm, to)DuckDB temp view fo = history + staging
research_con(cols, frm, to, typs)DuckDB connection with fo and fm (scr_master subset) for joins and scans

Postgres is authoritative from its first date on. Parquet is only read before it, so a day rolled back out of staging is hidden from the history too.

A SQL join of scr_master to scr_nsefo_eod no longer sees history. The pattern is: pick candidate contracts from scr_master first, then ask fo_store (see chart_service.py, option_chain_service.py).

Writing​

  • FoBhav.update() calls append_days([date]) after each successful day (best-effort, never fails the sync).
  • fo_backfill writes the table; run export afterwards.
  • expiry_mismatch.fix_expiry_mismatch(apply=True) also moves the masid in Parquet (remap_masids).

Commands (from backend/)​

python -m nseeod.fo_store export # full rebuild from Postgres (one month at a time)
python -m nseeod.fo_store append --days 5 # add the last N staging days
python -m nseeod.fo_store verify # per date: rows and sums of sclose / svolume / oi
python -m nseeod.fo_store stats
python -m nseeod.fo_store trim --keep 90 # dry run; only dates that verify
python -m nseeod.fo_store trim --keep 90 --apply --vacuum

VPS migration (do in this order)​

  1. Deploy the code (pull.sh) and add to backend/.env: VPS_NSEFO_DATA_PATH=/home/kss/tradeentry/nsefoeoddata, VPS_MCXFO_DATA_PATH=.../mcxfoeoddata, VPS_MCXSP_DATA_PATH=.../mcxspeoddata, VPS_BSEFO_DATA_PATH=.../bsefoeoddata.
  2. export, then verify - must report mismatched: 0. Postgres is untouched so far.
  3. Check the F&O pages: EOD chart FUT/OPT, Option Chain, OI Analysis.
  4. trim --keep 90 (dry run), then --apply --vacuum. VACUUM FULL locks the table; run it in a quiet window.
  5. Copy NSEFO_DATA_PATH together with the DB dump when syncing to the PC.

The same store for MCX and BSE​

The machinery is nseeod/month_store.py (MonthStore); each exchange is a small instance:

StoreModuleStaging tablePath env var
NSE F&Onseeod/fo_store.pyscr_nsefo_eodNSEFO_DATA_PATH
MCX F&Omcxeod/mcx_fo_store.pyscr_mcxfo_eodMCXFO_DATA_PATH
MCX spotmcxeod/mcx_spot_store.pyscr_mcxsp_eodMCXSP_DATA_PATH
BSE F&Obseeod/bse_fo_store.pyscr_bsefo_eodBSEFO_DATA_PATH

Each has the same commands (python -m <module> export | append | verify | stats | trim). Loaders call STORE.note_days([date]) after a successful day (spot: masids=[masid], so one symbol never wipes the others); bulk loaders (run_backfill, run_import, run_range_download, spot run) run inside STORE.deferred() and rewrite each month file once. A loader rollback (deleteadateabove) calls drop_days_after(). last_date_by_masid() gives each contract's newest date over Parquet + staging (the spot downloader's resume point).

Not moved​

scr_master (still ~500 MB: every contract ever listed) and the other small tables.