How I built a PostgreSQL research engine for 6.4M+ market observations

Case study: 6.4M+ market observations in one PostgreSQL research engine

Problem

Quantitative research usually begins with historical price files. A researcher downloads data for one instrument, tests an idea, and stores the result in a spreadsheet or a folder of CSV files. For a single experiment this is a reasonable way to work, because the data is small and the question is narrow.

The difficulty appears once the same research continues for years. Data accumulates across many symbols and timeframes, each experiment prepares its own copy of the dataset, and small differences in how the files were cleaned begin to change the results. When two tests disagree, it becomes hard to say whether the market behaved differently or whether the underlying data was handled differently. What happens to the credibility of a backtest when nobody can reproduce the dataset behind it? And how much research time is spent preparing data again instead of studying markets?

Action

I treated the dataset as a long-term asset rather than a by-product of each experiment, and designed a PostgreSQL architecture for storing and researching financial time-series data. The system standardises OHLCV price data across multiple assets and keeps 21 native MT5 timeframes as they were recorded, instead of rebuilding higher timeframes from lower-resolution data, which would have introduced differences that are difficult to trace later.

The architecture includes PostgreSQL as the central research database, consistent handling of symbols and timeframes, historical OHLCV ingestion, indexing for repeated analytical queries, a clear separation between raw market data and derived research outputs, dedicated structures for research runs and detected market events, and Python-based analysis workflows on top of it.

I deliberately avoided heavy normalisation and partitioning while the dataset remained manageable. Added complexity should be justified by scale, and keeping the design simple until that point made the system easier to understand and easier to change.

Result

The database now holds more than 6.4 million historical price records, and a single instrument contains more than 1.3 million observations across its available timeframes. Several markets have roughly twenty years of history or more. Studying markets across instruments, resolutions and long historical periods now happens in one research environment, instead of in datasets prepared again for every experiment.

  • 6.47M+ historical price rows
  • 1.36M+ observations for a single multi-timeframe instrument
  • 21 native MT5 timeframes
  • roughly 20+ years of history for several markets
  • one centralised PostgreSQL research environment

The lesson

Research becomes more reliable when the data underneath it is consistent and reproducible. Before adding more indicators or more complicated models, it is worth asking whether every result can be traced back to the same, well-structured source of data.