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_eodis 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
| Function | Use |
|---|---|
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()callsappend_days([date])after each successful day (best-effort, never fails the sync).fo_backfillwrites the table; runexportafterwards.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)
- Deploy the code (
pull.sh) and add tobackend/.env:VPS_NSEFO_DATA_PATH=/home/kss/tradeentry/nsefoeoddata,VPS_MCXFO_DATA_PATH=.../mcxfoeoddata,VPS_MCXSP_DATA_PATH=.../mcxspeoddata,VPS_BSEFO_DATA_PATH=.../bsefoeoddata. export, thenverify- must reportmismatched: 0. Postgres is untouched so far.- Check the F&O pages: EOD chart FUT/OPT, Option Chain, OI Analysis.
trim --keep 90(dry run), then--apply --vacuum.VACUUM FULLlocks the table; run it in a quiet window.- Copy
NSEFO_DATA_PATHtogether 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:
| Store | Module | Staging table | Path env var |
|---|---|---|---|
| NSE F&O | nseeod/fo_store.py | scr_nsefo_eod | NSEFO_DATA_PATH |
| MCX F&O | mcxeod/mcx_fo_store.py | scr_mcxfo_eod | MCXFO_DATA_PATH |
| MCX spot | mcxeod/mcx_spot_store.py | scr_mcxsp_eod | MCXSP_DATA_PATH |
| BSE F&O | bseeod/bse_fo_store.py | scr_bsefo_eod | BSEFO_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.