4 ms·

> 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close t

> 2B records of user events during their sessions. As it grew past a 500 million records it turned out to be impossible to query this table in any thing close to real-time - it was basically untouchable because it was so slow. This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding, caching, etc, can lower reads to a few seconds on disk.

bhouston 2y ago
> This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding, caching, etc, can lower reads to a few seconds on disk. Or just use BigQuery and it is works, it is cheaper to run (by 10x to 100x) and can be done by a junior dev rather than a PhD in Database configuration. I prefer simple solutions though - I also hate Kubernetes: https://benhouston3d.com/blog/why-i-left-kubernetes-for-google-cloud-run https://benhouston3d.com/blog/why-i-left-kubernetes-for-goog...
- DrFalkyn 2y ago
  Indices are one the first things you learn in any decent DB course, so you don’t need a PhD But if BigTable just solves the problem it seems the way to go PostGres is popular for a reason, it’s ACID unlike BigTable You may run into these problems later on, you may not
  - bhouston 2y ago
    I love Postgres and use it a lot but not for analytics.
  - furstenheim 2y ago
    ACID is good and it has an implementation cost (vacuum for example). When doing analytics you do not care for ACID
- majkinetor 2y ago
  I have 50 columns in a table with 10B records in a partitioned table. People can search using any combination. There are a huge number of possible composite indexes, so we don't have any, but we have every column indexed. Most queries take a minute or two, and we also experienced a sudden drop in performance even for queries that used to work OK. Not sure what to do next to improve query speed.
  - moonikakiss 2y ago
    This is an interesting workload. We've heard similar pains for fast filtering on many large column tables. For this workload, having a columnstore version of your table will help. DM us: https://join.slack.com/t/mooncakelabs/shared_invite/zt-2sepjh5hv-rb9jUtfYZ9bvbxTCUrsEEA https://join.slack.com/t/mooncakelabs/shared_invite/zt-2sepj.... We can help.
  - zhousun 2y ago
    Yea this is indeed a repeated pattern we saw people requesting (filter on many columns) and we are trying to solve with pg_mooncake. If you are interested, feel free to join mooncake-devs.slack.com to chat more about your use case.
Isn0gud 2y ago
Doesn't sound like solved problem to me if you have to employ more than four different mitigation strategies.

bhouston 2y ago

> This is a solved problem, and it seems the technical folks over there lacked the skills to make it work. Having indexes is just the tip of the iceberg. Composite indexes, partitioning, sharding, caching, etc, can lower reads to a few seconds on disk. Or just use BigQuery and it is works, it is cheaper to run (by 10x to 100x) and can be done by a junior dev rather than a PhD in Database configuration. I prefer simple solutions though - I also hate Kubernetes: https://benhouston3d.com/blog/why-i-left-kubernetes-for-google-cloud-run https://benhouston3d.com/blog/why-i-left-kubernetes-for-goog...

DrFalkyn 2y ago
Indices are one the first things you learn in any decent DB course, so you don’t need a PhD But if BigTable just solves the problem it seems the way to go PostGres is popular for a reason, it’s ACID unlike BigTable You may run into these problems later on, you may not
- bhouston 2y ago
  I love Postgres and use it a lot but not for analytics.
- furstenheim 2y ago
  ACID is good and it has an implementation cost (vacuum for example). When doing analytics you do not care for ACID
majkinetor 2y ago
I have 50 columns in a table with 10B records in a partitioned table. People can search using any combination. There are a huge number of possible composite indexes, so we don't have any, but we have every column indexed. Most queries take a minute or two, and we also experienced a sudden drop in performance even for queries that used to work OK. Not sure what to do next to improve query speed.
- moonikakiss 2y ago
  This is an interesting workload. We've heard similar pains for fast filtering on many large column tables. For this workload, having a columnstore version of your table will help. DM us: https://join.slack.com/t/mooncakelabs/shared_invite/zt-2sepjh5hv-rb9jUtfYZ9bvbxTCUrsEEA https://join.slack.com/t/mooncakelabs/shared_invite/zt-2sepj.... We can help.
- zhousun 2y ago
  Yea this is indeed a repeated pattern we saw people requesting (filter on many columns) and we are trying to solve with pg_mooncake. If you are interested, feel free to join mooncake-devs.slack.com to chat more about your use case.

Isn0gud 2y ago

Doesn't sound like solved problem to me if you have to employ more than four different mitigation strategies.