Data Warehouse vs. Operational Database What? How? Which One? (opens in new tab)

(airbyte.com)

4 pointsalexmarquardt3y ago4 comments

4 comments

swyx3y ago

> For example, loading data from an SSD is more than 3 orders of magnitude (more than 1000x) slower than referencing main memory, and a disk seek on a spinning disk is 5 orders of magnitude (100,000x) slower than referencing data that is in memory. The above latencies should make it clear that there is a huge performance advantage to minimizing disk access.

this is the first explanation i've seen that directly links disk latency and data architecture decisions. always felt it intuitively but never did the math.

zX41ZdbW3y ago

The advantage of OLAP vs. OLTP on analytical queries is clearly visible on the benchmark: https://benchmark.clickhouse.com/

If we take the extreme - ClickHouse as the most thoroughly optimized column-oriented DBMS and compare it with Postgres, the difference will be more than 100 times on average.

sspaeti3y ago

> The column-oriented storage format used by data warehouses allows them to efficiently leverage modern SIMD computer architectures for columnar-vectorized processing.

I find it interesting how these vectorized processing engines with DuckDB and Photon Engine of Databricks try to combine row and columnar-oriented strengths.

zX41ZdbW3y ago

The author of the article underestimates the capabilities of the modern OLAP DBMS. The old beliefs are becoming less and less relevant.

> When to use a data warehouse > Data warehouses are good for OLAP (online analytical processing) workloads such as the following:

> A small number of users, each of which may execute heavy analytics workloads

Not necessarily. ClickHouse is being used for user-facing analytics where every query can take in the order of 10 ms while supporting many concurrent queries.

> Downtime is permitted – generally not used as the one-and-only operational system

Not necessarily. ClickHouse is being used for HA setups replicated across multiple regions when the service has to survive the outage of a whole region.

j / k navigate · click thread line to collapse

4 comments

swyx3y ago

this is the first explanation i've seen that directly links disk latency and data architecture decisions. always felt it intuitively but never did the math.

zX41ZdbW3y ago

The advantage of OLAP vs. OLTP on analytical queries is clearly visible on the benchmark: https://benchmark.clickhouse.com/

If we take the extreme - ClickHouse as the most thoroughly optimized column-oriented DBMS and compare it with Postgres, the difference will be more than 100 times on average.

sspaeti3y ago

> The column-oriented storage format used by data warehouses allows them to efficiently leverage modern SIMD computer architectures for columnar-vectorized processing.

I find it interesting how these vectorized processing engines with DuckDB and Photon Engine of Databricks try to combine row and columnar-oriented strengths.

zX41ZdbW3y ago

The author of the article underestimates the capabilities of the modern OLAP DBMS. The old beliefs are becoming less and less relevant.

> When to use a data warehouse > Data warehouses are good for OLAP (online analytical processing) workloads such as the following:

> A small number of users, each of which may execute heavy analytics workloads

Not necessarily. ClickHouse is being used for user-facing analytics where every query can take in the order of 10 ms while supporting many concurrent queries.

> Downtime is permitted – generally not used as the one-and-only operational system

Not necessarily. ClickHouse is being used for HA setups replicated across multiple regions when the service has to survive the outage of a whole region.

j / k navigate · click thread line to collapse