4 ms·

In my experience yes. Example: We struggled with some read-queries taking a long time when aggregating over millions of rows. To solve this, we used triggers t

In my experience yes. Example: We struggled with some read-queries taking a long time when aggregating over millions of rows. To solve this, we used triggers to pre-compute aggregate groups in real-time during write, which allowed us to easily achieve sub-second response times. However, this caused a lot of write amplification and now this is becoming a bottleneck. We could probably solve some of these problems by looking into column stores, but that carries it's own downsides (operational complexity, real-time data loading, etc.). Or we could buy faster disks, but that gets expensive quickly. YMMV but I certainly still experience more database/disk related bottlenecks than application-level ones.

sparkling 7y ago
SQL in my experience is not designed for workloads where you want to compute together/return a ton of values from a ton of rows at once. Its good where you need to cherry-pick just a few rows belonging to one user from a databses of millions of row (e.g. CRUD operations). tl;dr: simple CRUD operations on few rows scale fine data-science/analytics stuff involving millions of in one query scale rather badly
- felixge 7y ago
  SQL isn't the problem IMO. The problem is that the implementation we're using (PostgreSQL) is a row-store which is indeed more optimal for the kind of operations you mention. However, SQL isn't limited to row-stores. There are column-store implementations that are quite amazing for aggregate queries, e.g. Clickhouse. Using one of those would very likely work for us, but my understanding is that loading data into them in real-time is problematic.
  - kwillets 7y ago
    How close is real time? Seconds? Minutes? Also, what type of aggregate?
granshaw 7y ago
Could you do something like computing the aggregate groups in background jobs instead, perhaps staggered, to take that out of the critical path?
- felixge 7y ago
  Yup, that would reduce write IOPs but increase the latency for making the data available to our users.

sparkling 7y ago

SQL in my experience is not designed for workloads where you want to compute together/return a ton of values from a ton of rows at once. Its good where you need to cherry-pick just a few rows belonging to one user from a databses of millions of row (e.g. CRUD operations). tl;dr: simple CRUD operations on few rows scale fine data-science/analytics stuff involving millions of in one query scale rather badly

felixge 7y ago
SQL isn't the problem IMO. The problem is that the implementation we're using (PostgreSQL) is a row-store which is indeed more optimal for the kind of operations you mention. However, SQL isn't limited to row-stores. There are column-store implementations that are quite amazing for aggregate queries, e.g. Clickhouse. Using one of those would very likely work for us, but my understanding is that loading data into them in real-time is problematic.
- kwillets 7y ago
  How close is real time? Seconds? Minutes? Also, what type of aggregate?

granshaw 7y ago

Could you do something like computing the aggregate groups in background jobs instead, perhaps staggered, to take that out of the critical path?

felixge 7y ago
Yup, that would reduce write IOPs but increase the latency for making the data available to our users.