6 ms·

Serious question. Why not just put logs in postgres? Rich query language. Indexes. Has support for time-series based indexes. Optionally can support full text

Serious question. Why not just put logs in postgres? Rich query language. Indexes. Has support for time-series based indexes. Optionally can support full text search if needed.

reacharavindh 8y ago
I have been wondering the same thing. But, I spent the last hour looking for something like Kibana that would work with Postgres. Postgres full text search, and Timescale extension would be ideal for logging kind of application. I could just set data retention to something like 2 months to keep the data volume simple and manageable. But, all "data explorer" like tools for SQL databases obviously focus on having their end users write SQL. I'm hard pressed to find a good search interface that talks to Postgres at the backend..
- cetra3 8y ago
  We store our logs in PostgreSQL and use Metabase for visualisations & analysis: https://metabase.com/ https://metabase.com/ It's still focused on SQL but the "Question" flow is quite simple, and doesn't require you to write SQL. You can, of course, write raw SQL if you want to and have those results still visualised.
  - reacharavindh 8y ago
    I am playing with Metabase trying to see if there is any way for me to get a "search" on a column of table. It feels like I'm looking for a different use case. Metabase seems excellent for building a dash board composed of several SQL based questions, and to generally have a searchable list of questions(which are internally SQL queries). But, searching for logs seems to not be one of its use cases...
stuntkite 8y ago
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.
blaisio 8y ago
That could work for some people, but when you're ingesting 10+ GB / day (which is not difficult, even just from access logs) and need to keep them for several years, it quickly becomes infeasible.
- reacharavindh 8y ago
  Hmmm curious how much of a difference we're talking about between: Logging in plain text - syslog Logging to ELK(+indexes) Logging to a DB like Postgres Systemd's binary logs This Grafana Loki 10+ GB/day is big to deal with as you say. But, I'm wondering it would be around the same regardless of your choice above.. With all of the choices though, we can always set staging rules and stage old data out to archives. Personally, I'm struggling to find an equivalent search UI for Postgres.
ohthehugemanate 8y ago
Apart from the query support, which others have mentioned, there's the sheer scale of data. The most recent time series implementation I worked on had to ingest terabytes per minute from a wide variety of sources in a single factory... And the goal was a single system combining every factory worldwide. Easily measured in exabytes per hour, with thousands of sources. A traditional relational database - even a really good one like postgres - just isn't built with that kind of use case in mind. There really is something to a database specifically designed for a particular kind of query on ridiculous quantities of data.
- hnaccy 8y ago
  According to my napkin math that's more than the LHC's raw output, do they really need all that for factories? Seems nuts.
  - TylerE 8y ago
    Need? Probably not. But often you don't know what metrics you care about until something goes wrong - so you over-engineer it and log all the things.
kureikain 8y ago
It's weird but even for small company you can easily generate like 50GB a day :(. Some log are very verbose and devlloper want to put a lot more context into it. JSON log is supper verbose and increase space. And people expext to query arbitrary field out of these. Last I used(2 years ago), Postgres weren't able to keep up with it. Though I just use default RDS and didn't do anything to tune it.
komuW 8y ago
I believe you can. I asked myself that question over the past few months and ended up building a proto-type logging pipeline using Postgres/TimescaleDB. I wrote about it in a blogpost; https://www.komu.engineer/blogs/timescaledb/timescaledb-for-logs https://www.komu.engineer/blogs/timescaledb/timescaledb-for-...
SuperQue 8y ago
We generate 1T of logs per day. Doing this with PostgreSQL would be hilariously bad.

Serious question. Why not just put logs in postgres? Rich query language. Indexes. Has support for time-series based indexes. Optionally can support full text search if needed.

reacharavindh 8y ago

I have been wondering the same thing. But, I spent the last hour looking for something like Kibana that would work with Postgres. Postgres full text search, and Timescale extension would be ideal for logging kind of application. I could just set data retention to something like 2 months to keep the data volume simple and manageable. But, all "data explorer" like tools for SQL databases obviously focus on having their end users write SQL. I'm hard pressed to find a good search interface that talks to Postgres at the backend..

cetra3 8y ago
We store our logs in PostgreSQL and use Metabase for visualisations & analysis: https://metabase.com/ https://metabase.com/ It's still focused on SQL but the "Question" flow is quite simple, and doesn't require you to write SQL. You can, of course, write raw SQL if you want to and have those results still visualised.
- reacharavindh 8y ago
  I am playing with Metabase trying to see if there is any way for me to get a "search" on a column of table. It feels like I'm looking for a different use case. Metabase seems excellent for building a dash board composed of several SQL based questions, and to generally have a searchable list of questions(which are internally SQL queries). But, searching for logs seems to not be one of its use cases...

stuntkite 8y ago

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.

blaisio 8y ago

That could work for some people, but when you're ingesting 10+ GB / day (which is not difficult, even just from access logs) and need to keep them for several years, it quickly becomes infeasible.

reacharavindh 8y ago
Hmmm curious how much of a difference we're talking about between: Logging in plain text - syslog Logging to ELK(+indexes) Logging to a DB like Postgres Systemd's binary logs This Grafana Loki 10+ GB/day is big to deal with as you say. But, I'm wondering it would be around the same regardless of your choice above.. With all of the choices though, we can always set staging rules and stage old data out to archives. Personally, I'm struggling to find an equivalent search UI for Postgres.

ohthehugemanate 8y ago

Apart from the query support, which others have mentioned, there's the sheer scale of data. The most recent time series implementation I worked on had to ingest terabytes per minute from a wide variety of sources in a single factory... And the goal was a single system combining every factory worldwide. Easily measured in exabytes per hour, with thousands of sources. A traditional relational database - even a really good one like postgres - just isn't built with that kind of use case in mind. There really is something to a database specifically designed for a particular kind of query on ridiculous quantities of data.

hnaccy 8y ago
According to my napkin math that's more than the LHC's raw output, do they really need all that for factories? Seems nuts.
- TylerE 8y ago
  Need? Probably not. But often you don't know what metrics you care about until something goes wrong - so you over-engineer it and log all the things.

kureikain 8y ago

It's weird but even for small company you can easily generate like 50GB a day :(. Some log are very verbose and devlloper want to put a lot more context into it. JSON log is supper verbose and increase space. And people expext to query arbitrary field out of these. Last I used(2 years ago), Postgres weren't able to keep up with it. Though I just use default RDS and didn't do anything to tune it.

komuW 8y ago

I believe you can. I asked myself that question over the past few months and ended up building a proto-type logging pipeline using Postgres/TimescaleDB. I wrote about it in a blogpost; https://www.komu.engineer/blogs/timescaledb/timescaledb-for-logs https://www.komu.engineer/blogs/timescaledb/timescaledb-for-...

SuperQue 8y ago

We generate 1T of logs per day. Doing this with PostgreSQL would be hilariously bad.