@Hadi: Can you say something about usage of cstore FDW in-memory?
We designed cstore_fdw with the applications in mind where volume of data is much larger than main memory. In general, columnar stores usually bring two benefits:
1. Doing less disk I/O than row stores. We can skip reading entire columns or column blocks that are not related to the given query. This is effective when (a) volume of data is larger than main memory so OS cannot cache whole dataset, (b) most of our queries only require a small subset of columns to complete.
2. Vector processing and making better use of CPU. This usually helps most when data is in memory. If data is in disk and is not cached, I/O cost is usually higher than CPU cost, and vector processing may not help much.
cstore_fdw tries to optimize for #1. Also note that because we use compression, more data can be cached in memory and chance of hitting disk decreases.
But we don't do vector processing yet, and it is not our three month timeline.
If you want to be able use more CPU cores in PostgreSQL, you can have a look at CitusDB [1] which is built upon PostgreSQL and distributes queries to use all cpu cores in a single or more machines.