7 ms·

> From my experience, the vast majority of complications in systems is people not realizing they are asking an OLAP question while wanting parts of OLTP semanti

> From my experience, the vast majority of complications in systems is people not realizing they are asking an OLAP question while wanting parts of OLTP semantics. If you could elaborate on this further, I and others are probably very interested in reading more about it.

juancn 2y ago
The main data access difference between OLAP systems and OLTP systems is how many records on average do you need to access: - OLAP: most queries need most records (aggregations span large swaths of data) - OLTP: most queries access just a few records Also, in OLAP, in many cases, you can live with a single-updater model without much trouble, where OLTP, the strength is to have many concurrent updaters (but mostly non-overlapping).
- hobs 2y ago
  OLAP - Most queries need an aggregate of records. Generally you do NOT need most records, but simply the records grouped by dimensions per interval (for almost all olap reporting). You do not change the data, you observe it. If you change it, you are not dealing with OLAP data. OLTP - You are dealing with the ins and outs of people using stuff to do things. You buy something, you check out something, you some way perturb the state of things. This should not require large amount of row lookups in 99.9% of cases.
- greggyb 2y ago
  - OLAP: read-mostly, table-scan heavy, many queries run ad-hoc by users - OLTP: write-mostly, index-seek heavy, ~all queries pre-defined up front
- yoda97 2y ago
  So the first focuses on analytics and reporting, the second on transactions and performance. They are not meant to replace each other, they serve different purposes. Some teams may need both.
taeric 2y ago
As the sibling says, it is easy to think about in terms of what you are doing with the data. Reporting on how many transactions of a type have happened over a duration? Probably OLAP. Really, if the word "report" shows up, at all, probably OLAP. Executing parts of a workflow for an active transaction? OLTP. Briefly looking, I can't find the books that I thought covered a lot of this in a good way. Will keep looking, apologies.
- datadrivenangel 2y ago
  Designing Data Intensive Applications is a very good book in this space.
  - bobnamob 2y ago
    Designing Data Intensive Applications by Martin Kleppmann is required reading if you're in this space. I can't recommend it highly enough. [1] https://www.amazon.com/Designing-Data-Intensive-Applications-Reliable-Maintainable/dp/1449373321 https://www.amazon.com/Designing-Data-Intensive-Applications...
    - taeric 2y ago
      Indeed, I upvoted that response to me, but I should have said as much. This is the book I couldn't remember the name of for the life of me. Really good book.
guru4consulting 2y ago
Apart from the above differences, another important difference is that OLAP is often columnar based, as opposed to the typical OLTP being row-based. So, OLAP queries use different kinds of index. Snowflake has introduced Hybrid tables where the same data is stored and indexed twice, once in OLAP columnar type and the other in OLTP style row index.

juancn 2y ago

The main data access difference between OLAP systems and OLTP systems is how many records on average do you need to access: - OLAP: most queries need most records (aggregations span large swaths of data) - OLTP: most queries access just a few records Also, in OLAP, in many cases, you can live with a single-updater model without much trouble, where OLTP, the strength is to have many concurrent updaters (but mostly non-overlapping).

hobs 2y ago
OLAP - Most queries need an aggregate of records. Generally you do NOT need most records, but simply the records grouped by dimensions per interval (for almost all olap reporting). You do not change the data, you observe it. If you change it, you are not dealing with OLAP data. OLTP - You are dealing with the ins and outs of people using stuff to do things. You buy something, you check out something, you some way perturb the state of things. This should not require large amount of row lookups in 99.9% of cases.
greggyb 2y ago
- OLAP: read-mostly, table-scan heavy, many queries run ad-hoc by users - OLTP: write-mostly, index-seek heavy, ~all queries pre-defined up front
yoda97 2y ago
So the first focuses on analytics and reporting, the second on transactions and performance. They are not meant to replace each other, they serve different purposes. Some teams may need both.

taeric 2y ago

As the sibling says, it is easy to think about in terms of what you are doing with the data. Reporting on how many transactions of a type have happened over a duration? Probably OLAP. Really, if the word "report" shows up, at all, probably OLAP. Executing parts of a workflow for an active transaction? OLTP. Briefly looking, I can't find the books that I thought covered a lot of this in a good way. Will keep looking, apologies.

datadrivenangel 2y ago
Designing Data Intensive Applications is a very good book in this space.
- bobnamob 2y ago
  Designing Data Intensive Applications by Martin Kleppmann is required reading if you're in this space. I can't recommend it highly enough. [1] https://www.amazon.com/Designing-Data-Intensive-Applications-Reliable-Maintainable/dp/1449373321 https://www.amazon.com/Designing-Data-Intensive-Applications...
  - taeric 2y ago
    Indeed, I upvoted that response to me, but I should have said as much. This is the book I couldn't remember the name of for the life of me. Really good book.

guru4consulting 2y ago

Apart from the above differences, another important difference is that OLAP is often columnar based, as opposed to the typical OLTP being row-based. So, OLAP queries use different kinds of index. Snowflake has introduced Hybrid tables where the same data is stored and indexed twice, once in OLAP columnar type and the other in OLTP style row index.