3 ms·

I guess I have never quite understood what is meant by analytics, and feel there is a different definition that is over my head. I do some “analytics” in a pos

• tomnipotent 3y ago
  > what is meant by analytics Disk I/O. Most analytical workloads only need a subset of columns for the tables under query, but Postgres has to read in every column due to how data is organized. Columnar and hybrid columnar can significantly reduce disk I/O by orders of magnitude, which makes a big difference beyond a few dozen gigabytes. Even if all your data can fit into memory, Postgres will still be slower because it needs to loop over values for aggregations. Columnar databases can use SIMD instructions and better utilize the CPU cache across multiple aggregations in the same query. Postgres is also built for lookup queries and point updates, so every row needs a separate entry in b-tree indexes. Analytical databases can generally use sparse indexes that resolve to groups of rows, which means indexes take up both less space in memory and on disk that make range queries more efficient.
• adgjlsfhk1 3y ago
  one of the biggest difference is whether you are doing more reads vs writes. reads want indices, writes don't. reads want denormalization, writes don't. reads often want column major, writes don't.