3 ms·

I've been ingesting stock market trading data into MongoDB and using Metabase[1] to visualize it. It is essentially date, price, volume, ticker symbol, exchange

I've been ingesting stock market trading data into MongoDB and using Metabase[1] to visualize it. It is essentially date, price, volume, ticker symbol, exchange. Around 400M documents so far. Queries out of Metabase take upwards of 2-5 minutes to run even simple questions like: Plot the average price of Apple for the last 5 days grouping by minute. Would Timescale Cloud be a better replacement in terms of performance? Is there a nice GUI visualization platform like Metabase for it? [1] https://metabase.com/ https://metabase.com/

RobAtticus 7y ago
We did benchmarks a while back comparing TimescaleDB to Mongo[1], and TimescaleDB was quite a bit better. So I think you'd definitely see much improved query time. Also it appears that Metabase supports a PostgreSQL connection[2], so you could probably continue to use it. [1] https://blog.timescale.com/how-to-store-time-series-data-mongodb-vs-timescaledb-postgresql-a73939734016/ https://blog.timescale.com/how-to-store-time-series-data-mon... [2] https://metabase.com/docs/latest/operations-guide/start.html#installing-and-running-metabase https://metabase.com/docs/latest/operations-guide/start.html...
dianasaur323 7y ago
Yeah - TimescaleDB comes with a time_bucket function that allows you to group things by minute, and specify a where clause that queries for just the last 5 days. You can build indexes that include the ticker, and also reorder data on disk to optimize how much data you scan on disk. So, TLDR - you should definitely try it! I did some quick googling, and it looks like Metabase supports PostgreSQL, so it should work with Timescale. We would love to hear how it goes!
DenseComet 7y ago
Hey I was wondering how you ingest stock market data. What source do you use, and do you use a connector, or did you write one yourself?
- nodesocket 7y ago
  I use Alpaca[1], specifically the Node.js `websocket.onStockTrades` method and a bit of custom JavaScript code. [1] https://github.com/alpacahq/alpaca-trade-api-js https://github.com/alpacahq/alpaca-trade-api-js
  - dianasaur323 7y ago
    Very cool! I haven't been able to find a more real-time data source yet. Thanks for sharing!
andrea_s 7y ago
MongoDB is not well suited for OLAP-style workloads - have you considered Yandex ClickHouse?

RobAtticus 7y ago

We did benchmarks a while back comparing TimescaleDB to Mongo[1], and TimescaleDB was quite a bit better. So I think you'd definitely see much improved query time. Also it appears that Metabase supports a PostgreSQL connection[2], so you could probably continue to use it. [1] https://blog.timescale.com/how-to-store-time-series-data-mongodb-vs-timescaledb-postgresql-a73939734016/ https://blog.timescale.com/how-to-store-time-series-data-mon... [2] https://metabase.com/docs/latest/operations-guide/start.html#installing-and-running-metabase https://metabase.com/docs/latest/operations-guide/start.html...

dianasaur323 7y ago

Yeah - TimescaleDB comes with a time_bucket function that allows you to group things by minute, and specify a where clause that queries for just the last 5 days. You can build indexes that include the ticker, and also reorder data on disk to optimize how much data you scan on disk. So, TLDR - you should definitely try it! I did some quick googling, and it looks like Metabase supports PostgreSQL, so it should work with Timescale. We would love to hear how it goes!

DenseComet 7y ago

Hey I was wondering how you ingest stock market data. What source do you use, and do you use a connector, or did you write one yourself?

nodesocket 7y ago
I use Alpaca[1], specifically the Node.js `websocket.onStockTrades` method and a bit of custom JavaScript code. [1] https://github.com/alpacahq/alpaca-trade-api-js https://github.com/alpacahq/alpaca-trade-api-js
- dianasaur323 7y ago
  Very cool! I haven't been able to find a more real-time data source yet. Thanks for sharing!

andrea_s 7y ago

MongoDB is not well suited for OLAP-style workloads - have you considered Yandex ClickHouse?