Hacker News Reader

Comment by arianvanp | Hacker News Reader

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.

View on HN

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.

According to my napkin math that's more than the LHC's raw output, do they really need all that for factories? Seems nuts.

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.

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/

[1] https://blog.dbi-services.com/optimized-row-columnar-orc-for...

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.

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.

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

We store our logs in PostgreSQL and use Metabase for visualisations & analysis: 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.

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

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.

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

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.

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.

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

Reply on news.ycombinator.com