Hacker News Reader

Comment by nodesocket | Hacker News Reader

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/

View on HN

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-mon...

[2] https://metabase.com/docs/latest/operations-guide/start.html...

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

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?

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

Very cool! I haven't been able to find a more real-time data source yet. Thanks for sharing!

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!

Reply on news.ycombinator.com