4 ms·

There are things like TimescaleDB[0] that build on PG to give you columnar support. Time series DBs need to handle a ton of entries and be able to slice on the

There are things like TimescaleDB[0] that build on PG to give you columnar support. Time series DBs need to handle a ton of entries and be able to slice on the column which is supported by vanilla PG[1] but most of the security tools are not looking to timeseries PG largely. [0] https://www.timescale.com/ https://www.timescale.com/ [1] https://blog.dbi-services.com/optimized-row-columnar-orc-format-in-postgresql/ https://blog.dbi-services.com/optimized-row-columnar-orc-for...

aseipp 8y ago
Timescale isn't really a columnar database, it's more like an advanced partitioning extension for time-series data ("any data you want to shard based on a time column") where you can optionally include other partition keys for the sharding. But it can be used very well for analytic cases like this thanks to that. Real columnar databases like MemSQL or Clickhouse are a different beast -- for example they give very good column-wise compression in the dataset, which can save dramatic amounts of space. They're also good very for use cases like this, since they're heavily optimized for OLAP style workloads. There is also cstore_fdw which does offer columnar, compressed storage for PostgreSQL as a foreign table, but it won't hold a candle to something like MemSQL or Clickhouse in terms of raw performance. Maybe one day. Ultimately it's not about columnar storage or partitioning support, though, it's about the data and the queries you want to run on it, in what amount of time. Timescale can do pretty good for a lot of cases like this I bet, and I'm investigating it myself for a project.
- stuntkite 8y ago
  Thank you for clarifying. Obviously I only have a shallow understanding of what the hell I'm talking about. :) I have been evaluating TimescaleDB and my company currently uses Splunk, ES, and Prometheus. I'm going to be giving Loki a go this weekend for our k8s cluster. Your explanation here really helped clarify some things I've only explored a bit.

aseipp 8y ago

Timescale isn't really a columnar database, it's more like an advanced partitioning extension for time-series data ("any data you want to shard based on a time column") where you can optionally include other partition keys for the sharding. But it can be used very well for analytic cases like this thanks to that. Real columnar databases like MemSQL or Clickhouse are a different beast -- for example they give very good column-wise compression in the dataset, which can save dramatic amounts of space. They're also good very for use cases like this, since they're heavily optimized for OLAP style workloads. There is also cstore_fdw which does offer columnar, compressed storage for PostgreSQL as a foreign table, but it won't hold a candle to something like MemSQL or Clickhouse in terms of raw performance. Maybe one day. Ultimately it's not about columnar storage or partitioning support, though, it's about the data and the queries you want to run on it, in what amount of time. Timescale can do pretty good for a lot of cases like this I bet, and I'm investigating it myself for a project.

stuntkite 8y ago
Thank you for clarifying. Obviously I only have a shallow understanding of what the hell I'm talking about. :) I have been evaluating TimescaleDB and my company currently uses Splunk, ES, and Prometheus. I'm going to be giving Loki a go this weekend for our k8s cluster. Your explanation here really helped clarify some things I've only explored a bit.