this is the kind of research that creates new db types, or super optimized postgres im not sure yet

This paper gives a really nice end-to-end treatment of an entire problem domain that is usually taken piecemeal. Almost all of the techniques mentioned are already used in databases in some form. It won't lead to new database types but it provides a framework for thinking about the write amplification problem.

Not every database architecture will be able to easily take advantage of all these techniques. Some designs are much more easily optimizable than others.

can be both, psql has pluggable storage engines. See any of the numerous columnar or sharding extensions for postgres for examples of prior art.