Re:Building columnar compression in a row-oriented database
by 1996 on Thursday October 31, 2019 @06:27PM
(#21414432)
for dashboarding use something like pipelinedb (RIP)
Re:Building columnar compression in a row-oriented database
by mfreed on Thursday October 31, 2019 @07:30PM
(#21414869)
PipelineDB was useful primarily if you primarily wanted to maintain a _continuous_ materialization of some aggregate (e.g., an approximation of the distinct items in the database), not necessarily an aggregate per time period.
If you are interested in primarily showing the recent data -- which you see in many monitoring examples in IT/devops or IoT -- you often want the raw data or an aggregate per time period.
As an aside, TimescaleDB introduced continuous aggregations in v1.3. Here's a nice example of using it with Grafana for dashboarding: https://blog.timescale.com/blog/how-to-quickly-build-dashboa...
One of the interesting things of our technique is that it doesn't require low-level changes to Postgres, and actually then works with any version of PG that TimescaleDB supports (currently PG10, 11...PG12 coming soon).
That said, we're excited by the work Postgres has been doing with pluggable storage, particularly how PG13 will further open up possibilities such as Zedstore, and look to see how we can then marry some of these ideas. In terms of feature-by-feature comparison, haven't yet dug enough into the details.
Aside, one interesting aspects of our approach is discussed in the article: Having a "hybrid" row/column, rather than purely columnar, can actually be beneficial for many time-series workloads that constantly query very recent data (e.g., for dashboarding) as well as to improve ingest rates (although some column stores do build a temporary in-memory row-based cache before batch writing a column).