4 ms·

Based on their numbers, they should be archiving their historical data in parquet format partitioned by YYYYMMDD onto something like Clickhouse. This way, they

Based on their numbers, they should be archiving their historical data in parquet format partitioned by YYYYMMDD onto something like Clickhouse. This way, they can run a lean Postgres instance(s) at all times yet still get benefits of real-time reporting. Based on their use case, they can retain up-to 30 days of data in Postgres and offload the rest onto Clickhouse.

KptMarchewa 6y ago
You assume too much. For example, you assume that analytics aren't already run on a separate data warehouse. Ease of accessing older data is an important aspect of database holding transaction (not meaning transactional db). Wouldn't you want to check your transactions on bank page that are older than 30 days?
- manigandham 6y ago
  All modern columnnar/analytical databases have no problem in pulling back older data; in fact, that's the point of running large scale analytics.
  - deleted 6y ago
    [deleted]
  - KptMarchewa 6y ago
    I agree, but analytics is orthogonal topic. Unless you want to use columnar database to retrieve 15 transaction that particular user did month ago...
    - manigandham 6y ago
      There is nothing wrong with that use case. It works just fine. It may not have the same latency as an OLTP database but there are many factors (indexing, sort/partition keys, compression, etc) that can return similar performance for those queries.
      - KptMarchewa 6y ago
        Just to clarify what you advocate for: you want to run this type of queries on analytical databases (like Snowflake, Redshift etc) to display 10 rows on frontend device? select * from transactions where user_id='asdf' and completed_date <'2021-02-01' and completed_date >= '2021-01-01'
        manigandham 6y ago
        Sure. Again the database and data layout make a difference. Snowflake has startup latency since the "warehouse" might not be running but Redshift will do fine, along with many others that are always on.
        darksaints 6y ago
        Redshift has some pretty significant and fairly unpredictable per-query overhead. For example, all queries are compiled, then code is distributed to every node in the cluster, then executed and gathered before returning. And most analytics databases use a scheduled execution model, which can delay execution from a couple seconds to several minutes, depending on database loads.
- pupdogg 6y ago
  Your comment clearly illustrates that you have no working knowledge of Clickhouse or parquet file format or data archiving capabilities available in 2021. It's OK! I was in the same boat until I needed to implement such a solution for my use case. What I'm suggesting does not limit their customers from searching any historical data. Matter of fact, it might be 100x to 1000x faster for them to do so with the suggested solution. I strongly believe that mission critical transactional databases (postgres in this case) MUST be run very lean to keep their app running at hyper speeds at all times. 50TB overhead seems very inefficient when you take into account the the low cost solutions available in this day and age. Based on my personal experience of achieving 94% compression on 2TB of data using snappy parquet file format, they could be looking at a final dataset size of 3.5TB on Clickhouse.
  - KptMarchewa 6y ago
    >Matter of fact, it might be 100x to 1000x faster for them to do so with the suggested solution. That must be trolling. 1000x faster than single digit millisecond indexed query retrieving 15 rows? The fact that you keep talking about storage size means that you're talking about analytics not transactional needs. >Your comment clearly illustrates that you have no working knowledge of Clickhouse or parquet file format or data archiving capabilities available in 2021. It's OK! I was in the same boat until I needed to implement such a solution for my use case. Also, fuck off with that condescension.
    - pupdogg 6y ago
      Ok tiger! Thank you proving that the more inept you are, the smarter you think you are. Now, go beef with someone else.
    - dang 6y ago
      > fuck off with that condescension Please follow the site guidelines, regardless of whether or not someone else has broken them. Otherwise we just get a downward spiral. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html

KptMarchewa 6y ago

You assume too much. For example, you assume that analytics aren't already run on a separate data warehouse. Ease of accessing older data is an important aspect of database holding transaction (not meaning transactional db). Wouldn't you want to check your transactions on bank page that are older than 30 days?

manigandham 6y ago
All modern columnnar/analytical databases have no problem in pulling back older data; in fact, that's the point of running large scale analytics.
- deleted 6y ago
  [deleted]
- KptMarchewa 6y ago
  I agree, but analytics is orthogonal topic. Unless you want to use columnar database to retrieve 15 transaction that particular user did month ago...
  - manigandham 6y ago
    There is nothing wrong with that use case. It works just fine. It may not have the same latency as an OLTP database but there are many factors (indexing, sort/partition keys, compression, etc) that can return similar performance for those queries.
    - KptMarchewa 6y ago
      Just to clarify what you advocate for: you want to run this type of queries on analytical databases (like Snowflake, Redshift etc) to display 10 rows on frontend device? select * from transactions where user_id='asdf' and completed_date <'2021-02-01' and completed_date >= '2021-01-01'
      - manigandham 6y ago
        Sure. Again the database and data layout make a difference. Snowflake has startup latency since the "warehouse" might not be running but Redshift will do fine, along with many others that are always on.
        darksaints 6y ago
        Redshift has some pretty significant and fairly unpredictable per-query overhead. For example, all queries are compiled, then code is distributed to every node in the cluster, then executed and gathered before returning. And most analytics databases use a scheduled execution model, which can delay execution from a couple seconds to several minutes, depending on database loads.
pupdogg 6y ago
Your comment clearly illustrates that you have no working knowledge of Clickhouse or parquet file format or data archiving capabilities available in 2021. It's OK! I was in the same boat until I needed to implement such a solution for my use case. What I'm suggesting does not limit their customers from searching any historical data. Matter of fact, it might be 100x to 1000x faster for them to do so with the suggested solution. I strongly believe that mission critical transactional databases (postgres in this case) MUST be run very lean to keep their app running at hyper speeds at all times. 50TB overhead seems very inefficient when you take into account the the low cost solutions available in this day and age. Based on my personal experience of achieving 94% compression on 2TB of data using snappy parquet file format, they could be looking at a final dataset size of 3.5TB on Clickhouse.
- KptMarchewa 6y ago
  >Matter of fact, it might be 100x to 1000x faster for them to do so with the suggested solution. That must be trolling. 1000x faster than single digit millisecond indexed query retrieving 15 rows? The fact that you keep talking about storage size means that you're talking about analytics not transactional needs. >Your comment clearly illustrates that you have no working knowledge of Clickhouse or parquet file format or data archiving capabilities available in 2021. It's OK! I was in the same boat until I needed to implement such a solution for my use case. Also, fuck off with that condescension.
  - pupdogg 6y ago
    Ok tiger! Thank you proving that the more inept you are, the smarter you think you are. Now, go beef with someone else.
  - dang 6y ago
    > fuck off with that condescension Please follow the site guidelines, regardless of whether or not someone else has broken them. Otherwise we just get a downward spiral. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html