5 ms·

Around 7 TB and growing

by truth_seeker 7y ago

FridgeSeal 7y ago
That’s cool, what sort of query latency do you get? (For a variety of queries).
- truth_seeker 7y ago
  We pay close attention to Data growth patterns and schema changes to keep around 70% of queries (which actually brings little over than 90% of business revenue) under 50 millisecond. Rest of 30% queries are either of low priority or backend jobs for which latency range of 200 ms to 1.5 seconds is viable for our use case.
  - binakot 7y ago
    And what hardware do you use for this stack and data volume?
  - FridgeSeal 7y ago
    Oh wow that’s really quite good. My team and I are re-architecting/upgrading our data infrastructure at work, knowing this kind of performance is possible at a scale quite a bit above where we’re at now is good to know. I also really like your approach of keeping your main revenue queries under a hard latency limit.
buremba 7y ago
I didn't really understand how you managed to get all these extensions working together. Last time I spoke to people at Citus, CitusDB was not fully compatible with cstore_fdw and it's a bit confusing to use TimescaleDB and CitusDB together. Do you mind letting us know the company that you're working at or writing a blog post about it?
- massaman_yams 7y ago
  Yeah, I can't imagine everything working perfectly when using various combinations of timescale/citus/pipelinedb functionality. cstore_fdw is... yeah, there might be other edge cases there, but frankly, it's not a particularly good solution as it severely underperforms vs. any mature column store DB. Though - there's an argument for using it in order to avoid more complicated ETL from the production relational DB to an analytics DB.
- michelpp 7y ago
  Perhaps there was a communication glitch, to my knowledge cstore_fdw has always worked with citus, here's an excerpt from the docs: "Cstore_fdw is developed by Citus Data and can be used in combination with Citus, a postgres extension that intelligently distributes your data and queries across many nodes so your database can scale and your queries are fast." https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw
  - buremba 7y ago
    That may be the case but if you look at the commit history of cstore_fdw on Github, it's not that active. Also, none of the paid Citus customers were using cstore_fdw with Citusdb last year to my knowledge so that was not their focus even though this was the initial idea. The Citus engineer that I asked this question was not even sure if cstore_fdw can be used with the distributed version of Citus. My conclusion was that most of the Citus users use it for OLTP workloads since PG doesn't really have a distributed query planner that works smoothly with the OLAP workloads.

Around 7 TB and growing

FridgeSeal 7y ago

That’s cool, what sort of query latency do you get? (For a variety of queries).

truth_seeker 7y ago
We pay close attention to Data growth patterns and schema changes to keep around 70% of queries (which actually brings little over than 90% of business revenue) under 50 millisecond. Rest of 30% queries are either of low priority or backend jobs for which latency range of 200 ms to 1.5 seconds is viable for our use case.
- binakot 7y ago
  And what hardware do you use for this stack and data volume?
- FridgeSeal 7y ago
  Oh wow that’s really quite good. My team and I are re-architecting/upgrading our data infrastructure at work, knowing this kind of performance is possible at a scale quite a bit above where we’re at now is good to know. I also really like your approach of keeping your main revenue queries under a hard latency limit.

buremba 7y ago

I didn't really understand how you managed to get all these extensions working together. Last time I spoke to people at Citus, CitusDB was not fully compatible with cstore_fdw and it's a bit confusing to use TimescaleDB and CitusDB together. Do you mind letting us know the company that you're working at or writing a blog post about it?

massaman_yams 7y ago
Yeah, I can't imagine everything working perfectly when using various combinations of timescale/citus/pipelinedb functionality. cstore_fdw is... yeah, there might be other edge cases there, but frankly, it's not a particularly good solution as it severely underperforms vs. any mature column store DB. Though - there's an argument for using it in order to avoid more complicated ETL from the production relational DB to an analytics DB.
michelpp 7y ago
Perhaps there was a communication glitch, to my knowledge cstore_fdw has always worked with citus, here's an excerpt from the docs: "Cstore_fdw is developed by Citus Data and can be used in combination with Citus, a postgres extension that intelligently distributes your data and queries across many nodes so your database can scale and your queries are fast." https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw
- buremba 7y ago
  That may be the case but if you look at the commit history of cstore_fdw on Github, it's not that active. Also, none of the paid Citus customers were using cstore_fdw with Citusdb last year to my knowledge so that was not their focus even though this was the initial idea. The Citus engineer that I asked this question was not even sure if cstore_fdw can be used with the distributed version of Citus. My conclusion was that most of the Citus users use it for OLTP workloads since PG doesn't really have a distributed query planner that works smoothly with the OLAP workloads.